WITH T1
AS ( SELECT A
,B
,MAX (PERIOD_END_DATE) MAXD
,MIN (PERIOD_END_DATE) MIND
,COUNT () PECOUNT
,CASE WHEN COUNT () < 30 THEN 1 END L30D
,CASE WHEN COUNT () = 30 THEN 1 END E30D
,CASE WHEN COUNT () > 30 THEN 1 END G30D
FROM Ta1_INFO
GROUP BY a,b)
,T2
AS ( SELECT a,b
,PECOUNT
,MAXD
,MIND
,MAXD - MIND
,L30D
,E30D
,G30D
FROM T1
ORDER BY PECOUNT DESC)
,T3
AS (SELECT SUM (CASE WHEN L30D = 1 THEN 1 END) L30DS
,SUM (CASE WHEN E30D = 1 THEN 1 END) E30DS
,SUM (CASE WHEN G30D = 1 THEN 1 END) G30DS
,COUNT () TVALS
FROM T2)
SELECT T3., trunc (t3.L30DS / t3.TVALS, 6) PL30D,trunc(t3.E30DS / t3.TVALS, 6) PE30D,
trunc(t3.G30DS / t3.TVALS,6) PG30D
FROM T3
/
I left my aluminum cap on this morning so I can’t read your mind. What’s the problem?
The query’s interesting in a couple of ways. First the use of WITH – not needed here as there’s only standard subqueries (not correlated) and no tables joined outside the WITH clause. Second, there’s a lot of unnecessary columns in both “t1” and “t2”. Rewriting this query could have dramatic performance improvements.
So, what’s the problem that Toad has with this query?
Rich
-
I need that info for further processing. There are pieces I have left off. J
-
I love the with clause. JJJ
-
the query should work! SQLPLUS has on issues nor SQLDEVeloper with it. JJJJ
-
Toad formats it with no syntax issues. When it tries to run the sql it stops with Q(38) error from toad about duplicate column names. L
L
JJJ
From: Rich J. [mailto:bounce-rjesse@toadworld.com]
Sent: Wednesday, November 18, 2015 12:31
To: toadoraclebeta@toadworld.com
Subject: RE: [Toad for Oracle - Beta Discussion Forum] toad has big problem with this sqlplus runs it just fine.
RE: toad has big problem with this sqlplus runs it just fine.
Reply by Rich J.
The query’s interesting in a couple of ways. First the use of WITH – not needed here as there’s only standard subqueries (not correlated) and no tables joined outside the WITH clause. Second, there’s a lot of unnecessary columns in both “t1” and “t2”. Rewriting this query could have dramatic performance improvements.
So, what’s the problem that Toad has with this query?
Rich
To reply, please reply-all to this email.
Stop receiving emails on this subject.
Or
Unsubscribe from Toad for Oracle - Beta notifications altogether.
Toad for Oracle - Beta Discussion Forum
Flag
this post as spam/abuse.
The information contained in this e-mail and in any attachments is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. This message has been scanned for known computer viruses.
Interesting!
Could you post a very small sample (5 lines) of the output of the query from SQL*Plus, including the headings?
Rich
L30DS
E30DS
G30DS
TCOUNT
PL30D
354849
1
37
354887
0.9998929236630251319434073381104407881946
–this is what I see in toad 12.9.0.6 ( just updated ).
Now it is working as expected.
12.9.0.5 had issues with this sql.
From: Rich J. [mailto:bounce-rjesse@toadworld.com]
Sent: Wednesday, November 18, 2015 15:35
To: toadoraclebeta@toadworld.com
Subject: RE: [Toad for Oracle - Beta Discussion Forum] toad has big problem with this sqlplus runs it just fine.
RE: toad has big problem with this sqlplus runs it just fine.
Reply by Rich J.
Interesting!
Could you post a very small sample (5 lines) of the output of the query from SQL*Plus, including the headings?
Rich
To reply, please reply-all to this email.
Stop receiving emails on this subject.
Or
Unsubscribe from Toad for Oracle - Beta notifications altogether.
Toad for Oracle - Beta Discussion Forum
Flag
this post as spam/abuse.
The information contained in this e-mail and in any attachments is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. This message has been scanned for known computer viruses.
Since it’s a duplicate column name, I tried this query in an attempt to break Toad 12.9.0.6, but it handled it just fine:
WITH
t1 AS
(
SELECT dummy FROM DUAL
),
t2 AS
(
SELECT dummy FROM t1
),
t3 AS
(
SELECT dummy FROM t2
)
SELECT t3.*, SUBSTR(dummy||dummy,1,1) dummy FROM t3;
Rich
The problem has disappeared with 12.9.0.6. JJ
From: Michael Staszewski [mailto:bounce-mstaszew@toadworld.com]
Sent: Wednesday, November 18, 2015 10:44
To: toadoraclebeta@toadworld.com
Subject: RE: [Toad for Oracle - Beta Discussion Forum] toad has big problem with this sqlplus runs it just fine.
RE: toad has big problem with this sqlplus runs it just fine.
Reply by Michael Staszewski
I left my aluminum cap on this morning so I can’t read your mind. What’s the problem?
To reply, please reply-all to this email.
Stop receiving emails on this subject.
Or
Unsubscribe from Toad for Oracle - Beta notifications altogether.
Toad for Oracle - Beta Discussion Forum
Flag
this post as spam/abuse.
The information contained in this e-mail and in any attachments is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. This message has been scanned for known computer viruses.
That’s interesting, since your post doesn’t contain all of the columns specified in the query. There should be:
L30DS
E30DS
G30DS
TVALS
PL30D
PE30D
PG30D
But I’m glad it’s working for you now! 
Rich
That is not the same JJJ
From: Rich J. [mailto:bounce-rjesse@toadworld.com]
Sent: Wednesday, November 18, 2015 15:45
To: toadoraclebeta@toadworld.com
Subject: RE: [Toad for Oracle - Beta Discussion Forum] toad has big problem with this sqlplus runs it just fine.
RE: toad has big problem with this sqlplus runs it just fine.
Reply by Rich J.
Since it’s a duplicate column name, I tried this query in an attempt to break Toad 12.9.0.6, but it handled it just fine:
WITH
t1 AS
(
SELECT dummy FROM DUAL
),
t2 AS
(
SELECT dummy FROM t1
),
t3 AS
(
SELECT dummy FROM t2
)
SELECT t3.*, SUBSTR(dummy||dummy,1,1) dummy FROM t3;
Rich
To reply, please reply-all to this email.
Stop receiving emails on this subject.
Or
Unsubscribe from Toad for Oracle - Beta notifications altogether.
Toad for Oracle - Beta Discussion Forum
Flag
this post as spam/abuse.
The information contained in this e-mail and in any attachments is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. This message has been scanned for known computer viruses.
As in lots of SQL. JJ
From: Rich J. [mailto:bounce-rjesse@toadworld.com]
Sent: Wednesday, November 18, 2015 15:48
To: toadoraclebeta@toadworld.com
Subject: RE: [Toad for Oracle - Beta Discussion Forum] toad has big problem with this sqlplus runs it just fine.
RE: toad has big problem with this sqlplus runs it just fine.
Reply by Rich J.
That’s interesting, since your post doesn’t contain all of the columns specified in the query. There should be:
L30DS
E30DS
G30DS
TVALS
PL30D
PE30D
PG30D
But I’m glad it’s working for you now!
Rich
To reply, please reply-all to this email.
Stop receiving emails on this subject.
Or
Unsubscribe from Toad for Oracle - Beta notifications altogether.
Toad for Oracle - Beta Discussion Forum
Flag
this post as spam/abuse.
The information contained in this e-mail and in any attachments is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. This message has been scanned for known computer viruses.