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
I'd like to have a checkmark (image) show up if a certain value shows up in my dataset. For example, if the column Status has the value "Quote" I'd like to have an img show up, otherwise that column in the table would be blank. I was able to eas [More]
Having trouble with Snow Leo iTunes, I was having trouble from last 2-3 week, what happen is some time sudden my whole computer just freeze-up \-: but now I notice it causing because of iTunes, can any one have any idea what mi8 be the problem is? -V [More]
Hello I know this is a java forum but since I'm using java to connect to the database I thought of asking it here too. I have this table: TermName | CREATE TABLE `TermName` ( `id` varchar(45) character set utf8 NOT NULL, `term_name` varchar(255) char [More]
I'm trying I reset my security questions, because I made them a long time ago and they seem to have left me! Please help.Hi johnmills10, Welcome to the Support Communities! If you require additional assistance with this, click on the link below for g [More]
I have a video that is shot in HDV 1080i but i want to be able to have an HD verssion of the video and a Standard version of the Video for people who don't have acces to HD viewing on TV's. Is there a way to do this with capturing the video in the fo [More]
Good day everyone, I am required to reverse engineer .csv files stored in a UNIX file server. I am really new in using ODI so I'm having problems doing this menial task. So far, these are the steps I have done: 1. Inserted a File Data Server and a Ph [More]
Hi, Is it possible to access PSPAR in include ZXPADU01???? (PBAS0001 Enhancement) I want the variable ACTIO from PSPAR. In debugging mode when i tried ()PSPAR-ACTIO it s working, but when i tried it in report, showing error. (  '(' must follewed by a [More]
When i go into web browser I get PagePlus. When I ht home I get PagePlus. It is not in my Programs to uninstall it. Do I have to totally uninstall Mozilla?    Hello RTM58, I'm sorry you haven't heard back about your ticket. The representative that op [More]
I am new to eBusiness Suite. I have a myth about whether eBusiness Suite is all written by Oracle Forms and Reports. If yes, are these Forms and Reports source code supplied to the customer for customisation? Any help is welcome.Hi, The EBusiness Sui [More]
Revision: 12612 Revision: 12612 Author:   [email protected] Date:     2009-12-07 10:22:37 -0800 (Mon, 07 Dec 2009) Log Message: Merged 3.x revisions 12301,12311,12366,12369,12371-12372,12434,12483,12547 -> trunk   r12301 | [email protected] | 2009- [More]