Home » SQL & PL/SQL » Client Tools » SQLDeveloper/TOAD (w XP)
SQLDeveloper/TOAD [message #416283] Fri, 31 July 2009 08:06 Go to next message
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
Re: SQLDeveloper/TOAD [message #416286 is a reply to message #416283] Fri, 31 July 2009 08:25 Go to previous messageGo to next message
Michel Cadot
Messages: 59279
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
Re: SQLDeveloper/TOAD [message #416287 is a reply to message #416283] Fri, 31 July 2009 08:27 Go to previous messageGo to next message
cookiemonster
Messages: 10981
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.
Re: SQLDeveloper/TOAD [message #416290 is a reply to message #416286] Fri, 31 July 2009 08:32 Go to previous messageGo to next message
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
Re: SQLDeveloper/TOAD [message #416295 is a reply to message #416287] Fri, 31 July 2009 08:43 Go to previous message
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:
  


Current Time: Tue Sep 30 19:19:06 CDT 2014

Total time taken to generate the page: 0.08585 seconds