OraFAQ Forum: Client Tools » SQLDeveloper/TOAD

Home » Other » Client Tools » SQLDeveloper/TOAD (w XP)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

SQLDeveloper/TOAD [message #416283]

Fri, 31 July 2009 08:06

LanaKat
Messages: 3
Registered: July 2009
Location: Liverpool

Junior Member

hello

here is my query :

select ......., d.docno,
(select decode(sign(docno),'1','Process started','0','Process not Started') from
(select d1.docno from ccdocument d1 left join w2process p1 on d1.docno = p1.startdocno where d1.docno = d.docno )) StartedProcess
from ccdocument d
left join w2process p on d.processno = p.processno
join ...............
join ...............
join ................
where d.typecode in ('PAY-TEMET','TELQUEST')

funny enough it works in SQL developer - Oracle 10g
but in TOAD doesn like the "D"."DOCNO" in the nested select
it gives me ORA00904 error

any ideas?

the TOAD version is 9.0.1

Report message to a moderator

Re: SQLDeveloper/TOAD [message #416286 is a reply to message #416283]

Fri, 31 July 2009 08:25

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Get rid of TOAD.

quoting Ana (anacedent):

Those who live by the GUI, die by the GUI.

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Regards
Michel

Report message to a moderator

Re: SQLDeveloper/TOAD [message #416287 is a reply to message #416283]

Fri, 31 July 2009 08:27

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

You've got an unecessary level of nesting there.

Instead of

(SELECT decode...
FROM (SELECT col FROM ... WHERE ....))

Just do

(SELECT decode FROM ... WHERE ...)

And next time you post code can you please use code tags - have a look in the forum guide if you're not sure how.

Report message to a moderator

Re: SQLDeveloper/TOAD [message #416290 is a reply to message #416286]

Fri, 31 July 2009 08:32

LanaKat
Messages: 3
Registered: July 2009
Location: Liverpool

Junior Member

fair enough

im not using it but wrote the query for someone that only uses toad and it doesnt work so was curious

Report message to a moderator

Re: SQLDeveloper/TOAD [message #416295 is a reply to message #416287]

Fri, 31 July 2009 08:43

LanaKat
Messages: 3
Registered: July 2009
Location: Liverpool

Junior Member

here you go:

SELECT ......
d.docno,
(SELECT Decode(Sign(startdocno),'1','Process started',
'Process not Started')
FROM ccdocument d1
LEFT JOIN w2process p1
ON d1.docno = p1.startdocno
WHERE d1.docno = d.docno) startedprocess
FROM ccdocument d
LEFT JOIN w2process p
ON d.processno = p.processno
JOIN .....
ON .....
...........
WHERE d.typecode IN ('PAY-TEMET','TELQUEST')

cookiemonster wrote on Fri, 31 July 2009 14:27

You've got an unecessary level of nesting there.

Instead of

(SELECT decode...
FROM (SELECT col FROM ... WHERE ....))

Just do

(SELECT decode FROM ... WHERE ...)

And next time you post code can you please use code tags - have a look in the forum guide if you're not sure how.

it worked thanks Smile

[Updated on: Fri, 31 July 2009 08:54]

Report message to a moderator

Previous Topic:	SPOOL
Next Topic:	set command

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Apr 17 21:48:20 CDT 2024