Sitemap

Amicuk Programming Answers

Sub-Select Count query breaking TOAD

-0001-11-30   Views:0

Advertisement

Oracle 10.2.0.4.0 Running TOAD 9.1 I am running some SQL on our eBusiness Suite: SELECT pha.segment1      , pha.type_lookup_code      , (SELECT COUNT(DISTINCT pha2.po_header_id)           FROM po.po_headers_all pha2              , po.po_lines_all pla

Oracle 10.2.0.4.0
Running TOAD 9.1
I am running some SQL on our eBusiness Suite:
SELECT pha.segment1
     , pha.type_lookup_code
     , (SELECT COUNT(DISTINCT pha2.po_header_id)
          FROM po.po_headers_all pha2
             , po.po_lines_all pla
         WHERE pha2.po_header_id = pla.po_header_id
           AND pla.contract_id = pha.po_header_id) po_count
     , (SELECT MAX(pha2.creation_date)
          FROM po.po_headers_all pha2
             , po.po_lines_all pla
         WHERE pha2.po_header_id = pla.po_header_id
           AND pla.contract_id = pha.po_header_id) latest_cpa_po
  FROM po.po_headers_all pha
     , po.po_vendors pv
     , po.po_vendor_sites_all pvsa
WHERE pha.vendor_id = pv.vendor_id
   AND pha.vendor_site_id = pvsa.vendor_site_id
--   AND pv.VENDOR_NAME LIKE 'H%'
   AND pha.vendor_id = 98
   AND pha.type_lookup_code = 'CONTRACT'
   AND pha.org_id IN(7041, 7042);The above query runs quicky (approx. 1 second). If I take out the AND pha.vendor_id = 98 then the query takes a few minutes to run.
When I try to export it, or scroll down to view > 500 rows, TOAD crashes.
I know this isn't a TOAD forum, but I think that this is probably an issue with my no doubt rubbish SQL.
If I take out this sub-select, then the problem doesn't happen:
     , (SELECT COUNT(DISTINCT pha2.po_header_id)
          FROM po.po_headers_all pha2
             , po.po_lines_all pla
         WHERE pha2.po_header_id = pla.po_header_id
           AND pla.contract_id = pha.po_header_id) po_countHowever, I can't work out a better way of getting the data I need.
The sub-select counts POs which have been raised where the contractID on the PO line is the same as the PO Header ID from the main query.
Any advice please, on what I could do to sort this out would be much appreciated.
Thanks!

The replay answer
Advertisement
Hi,
It looks like you can replace both scalar sub-queries with a join, like this:
WITH     header_lines_summary     AS
     SELECT    pla.contract_id
          ,       COUNT (DISTINCT pha2.po_header_id)     AS po_count
          ,       MAX (pha2.creation_date)          AS latest_cpa_po
          FROM        po.po_headers_all pha2
             ,        po.po_lines_all   pla
         WHERE        pha2.po_header_id = pla.po_header_id
      GROUP BY       pla.contract_id
)                                        -- Everything up to this line is new
SELECT pha.segment1
     , pha.type_lookup_code
     , hls.po_count                              -- Changed
     , hls.latest_cpa_po                         -- Changed
  FROM po.po_headers_all     pha
     , po.po_vendors           pv
     , po.po_vendor_sites_all      pvsa
     , header_lines_summary     hls                    -- New
WHERE pha.vendor_id          = pv.vendor_id
   AND pha.vendor_site_id     = pvsa.vendor_site_id
   AND pha.po_header_id          = hls.contract_id (+)          -- New
--   AND pv.VENDOR_NAME      LIKE 'H%'
   AND pha.vendor_id           = 98
   AND pha.type_lookup_code      = 'CONTRACT'
   AND pha.org_id           IN (7041, 7042);Aside from the sub-query (which is entirely new), the query above is just what you posted, with 2 lines changed and 2 lines added, as marked.
This should be more efficient, but I don't know for certain that it will solve the Toad problem.
I hope this answers your question.
If not, post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) for all tables, and also post the results you want from that data.
It never hurts to say what version of Oracle you're using.

Go to See the other 6 answers

Sub-Select Count query breaking TOAD

Category:DefaultRelease time:-0001-11-30Views:130

Oracle 10.2.0.4.0 Running TOAD 9.1 I am running some SQL on our eBusiness Suite: SELECT pha.segment1      , pha.type_lookup_code      , (SELECT COUNT(DISTINCT pha2.po_header_id)           FROM po.po_headers_all pha2              , po.po_lines_all pla[More]

Problem to get count from resultset with select count(1) like query

Category:DefaultRelease time:-0001-11-30Views:130

Hi, The snippet of code is like this ...! preparedStatement = connection.prepareStatement("select count(1) as count from acf_users where acf_user_name like ? and acf_user_password like ?"); preparedStatement.setString(1,stringUsername);         [More]

Difference in count(*) query and select * query results

Category:DefaultRelease time:-0001-11-30Views:130

i am using Oracle9i Enterprise Edition Release 9.2.0.8.0. in a table select count(*) returns 34, but when i run select * statement it returns only 6 rows. Results are pasted below. Can any one knows the reason of this mismatch SQL> select * from pay_[More]

Select count(*) not working in Apex

Category:DefaultRelease time:-0001-11-30Views:130

Hello, The following sql works in both SQL*Plus and Toad but not in apex.  The variable seems always to be 0 no matter what data I have on the table. Basically I try to flip the status from "Inactive" to "Active" only when there is no[More]

Is select count(*) correct way to find speed

Category:DefaultRelease time:-0001-11-30Views:130

Environment I am using: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production Toad 11.6 In oracle in general, if i want to know how fast I can get data from a table (or a pipelined function), is select count(*) accurate way to[More]

Attn: PDO Experts (COUNT query)

Category:DefaultRelease time:-0001-11-30Views:130

This is an example of the type of a PHP/MySQL query I use to display dynamic web pages. This particular query is associated with a website/section that displays URL's like MySite/People/Crazy_Horse, where Crazy_Horse = both $MyURL and a value in the[More]

Count query taking time

Category:DefaultRelease time:-0001-11-30Views:130

I have a query -->select c1,c2,c3 from table1 . This query takes only few milliseconds. But when I take count from the same query i.e. when I execute select count(c1,c2,c3) from table1 then it takes a very long time (about 1 min). The table1 contains[More]

How to get resultset from procedure having dynamic select sql query ?

Category:DefaultRelease time:2015-10-11Views:130

Hi, I have created a procedure, in which there is dynamic select query. The procedure has one out put parameter which gives error code. When I compile that procedure it compiles successufully. When I run it it executes successfully and gives output e[More]

Performance issue when using select count on large tables

Category:DefaultRelease time:2015-10-11Views:130

Hello Experts, I have a requirement where i need to get count of data  from a database table.Later on i need to display the count in ALV format. As per my requirement, I have to use this select count inside a nested loops. Below is the count snippet:[More]

Invalid SQL generated for count query.

Category:DefaultRelease time:2015-10-11Views:130

A SQL statement was generated with the table name information missing when a count SQL query was issued (e.g. SELECT COUNT(*) FROM WHERE <SOME EXPRESSION>). The same query worked fine before switching to vertical class mapping. Basically no queries[More]

Select count(*) for each row of a table

Category:DefaultRelease time:2015-10-11Views:130

Hello All, Following query gives a statistics for each user (how many items he owns, home many tickets authored, how many objects he is subscribed to etc...) select auser.userid, (select count(*) from item where owner like '%' || auser.id || '%') ITE[More]

Hot
Hello I have a "Iphone 3G" , OS 3.1.3 I have a "Iphone 4" , IOS 4 I have 2 Mobile subscription, one for me and one for my work, I would like to use my two Iphones, with all my contacts. Now I have the ITunes 9.02 installed When I will [More]
I have been trying to reinstall iTunes but I have been getting error messages. One says I need a fileMSVCR80.dll, one says error 7 and the last one says windows error126. This problem started yesterday when I tried to download the newest iTune update [More]
What indexes are created when we check dimension as line item. and also What indexes are created when we check dimension as high cardinality. Where can i see that information in the system. Answers are highly appreciated. Regards KunalHi, Line item m [More]
I have a JPanel that I am putting inside of a JScrollpane. The scrollPane is always a fixed size. The problem is that if the Panel is smaller than the scrollpane it gets centered in the pane. I would like it to get anchored to the top of the scroll p [More]
Hi, I have a weird photo sync problem (Itunes 10.0.1, Aperture 3.0.3). Sometimes when I add a project to Aperture and an album under that project, I can't sync that to my ipad as Itunes doesn't see any pictures in the album. My Aperture library is hu [More]
I already have a 15" ADC display & have another one on the way. I require the use of a seperate display for using music software etc. Can anyone tell me how I can connect the extra display to my G5? Do I need an extra connection?I have the same p [More]
psc 2170 all in one printer, error message "remove and check cartridge" this has appeared for no obvious reason when I now switch on. I have removed and cleaned contact points on both cartridges and printed to no avail so would be most grateful [More]
When I go to pull pictures from Aperture into iMovie, the pictures have rotated 90 degrees.  They were not like that originally and I will fix them in Aperture but then other pictures rotate the next time I access that library.  Any ideas what is cau [More]
I have a 15" late 2011 MBP with 16GB Ram, SSD, high res screen and when I try to use dodge / burn on photos from either my Canon 7D or 5D MKII sometimes the adjustment brush takes 5 seconds to process the brush marks. I am using aperture 3.2.2 raw 3. [More]
Hi - I'm in the UK and require a dock for my 2G Touch that will charge and present good quality audio line out to input into an amplifier. My preference would be for the dock to be mains powered, rather than requiring a USB. Any ideas? There's a Belk [More]