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 everyone .. Ok .. keeping it as short as I could .. Current setup: 2 Dell PowerEdge 2950 Servers Windows Server 2008 R2 each connected to it's own TP-Link router (TL-WR741ND) with static IP for dedicated internet connection .. Switch: Cisco Cat [More]
Dear Friends,      I have created a query in SQ02 for grdelay. but in that i am facing a problem. suppose in material document there are 3 materials, in this case it prints that material 9 times. so please provide a good soln. regards, nileshhi, read [More]
How do I move photos to show on my tvkarenfromtacoma wrote: How do I move photos to show on my tv I assume you want to connect your TV to your iMac (you are posting in the iMac forums) or your MBP (according to your profile). I'll also assume your co [More]
Hi.. I am having problems with installing and running a sample java program.I just want to run a simple java program.I installed j2sdk.Now i want to set the class path to run my java program.I am not getting that.Please help me. chaitupaste in here w [More]
Hi All,       I have a requirement to get ALV display with Main Headings and Under each Main heading there are Sub headings. The Output is somewhat like shown below       Costing view    |    MEQ1 View      VER. ID | Desc.    VER. ID | Desc. If some [More]
I made a photo slideshow in imovie and the photos have white dots showing up on the dvd (but not on the computer). Any thoughts?Boot in safe mode - Before ordering your book preview it using this method -http://support.apple.com/kb/HT1040 - and save [More]
I have attempted to download Photoshop CC and get an error - Installation completed though some optional components failed to install correctly (6) - RETRY. I have done this several times with no success.  Lightroom downloaded fine. Any thoughts.Erro [More]
Hello, My scenario is File to IDoc and we are using RFC lookup in message mapping also. While executing this interface I am getting below error in quality system environment. Please find attached trace file. RuntimeException during appliction Java ma [More]
I'm getting unwanted borders around the active tabs in my Spry tabbed panels that seem to be added by the browser. For example I'm geting a fuzzy caterpillar border in Safari. Does anyone know how to turn off this effect? ThanksIts called "outline&qu [More]
I like iweb.... Is there a way to use the styles outside the program? Let's say I have a picture, when I publish the picture in iWeb on a photo page I can choose a style I want. But I also want to print that picture....with that style. Can I export t [More]