Select Statement-Oracle

[emmim44]

What is wrong with the query be;ow ? Dont work ...
======

SELECT ctrData.FACILITY AS Facil,ctrData.CONTRACTNO AS CONTRACTNO,ctrData.CONTRACTDATE AS CONTRACTDATE,ctrData.CONTRACTTITLE AS CONTRACTTITLE,ctrData.DRWPROVIDEBY AS DRWPROVIDEBY,ctrData.TOTALNODRW AS TOTALNODRW,ctrData.COMMENTS AS COMMENTS,ctrDocs.DRWSTATUS AS DRWSTATUS,ctrDocs.DRWNO AS DRWNO,ctrDocs.SHEETNO AS SHEETNO,ctrDocs.CONTRACTNO AS DocCtrNo,ctrDocs.DRWTYPE AS DRWTYPE,ctrDocs.DISCIPLINE AS DISCIPLINE,ctrDocs.DRWTITLE AS DRWTITLE,ctrDocs.CONFCADDFILENAME AS ImageName,ctrDocs.DRWDATE AS DRWDATE ,ctrDocs.DOCID AS DOCID,ctrLoc.READONLYMEDIAPATH AS READONLYMEDIAPATH, ctrLoc.CONTRACTNO AS LocCtrNo
FROM CONTRACTS ctrData,CONTRACT_DOCS ctrDocs,IMGS_LOCATION_FOLDER ctrLoc
WHERE LOWER(ctrData.CONTRACTNO)=LOWER(ctrDocs.CONTRACTNO(+)) AND LOWER(ctrData.CONTRACTNO)=LOWER(ctrLoc.CONTRACTNO(+))
NOT IN (
SELECT DECODE(INSTR('AW-06,AW-13',CONTRACTNO),0,CONTRACTNO,NULL)C FROM CONTRACTS )

[Newsgroup_User]

What is wrong with the query below? Don't work ...
======

I'm sorry, etiquette lesson here, not an answer.

How does it not work? Is it returning the wrong data? If so a
description of what is desired and an example of what is being returned
would be very helpful. Is it throwing some kind of error? If so please
provide the error message. I'm I expected to have intimate knowledge of
your data schema so that I can just look at all that and know what the
heck it does? I can't run it for myself and see what it does, I am not
on your system!

Add to that, please format the output so that is is readable, whitespace
please!!!! There very well may be a simple syntax error in all that,
but I am afraid I do not have the time to dissect it to make sure every
comma is correctly placed.

Back to your regular forum reading.

[Dan_Bracuk]

quote:

---

Originally posted by: emmim44
What is wrong with the query be;ow ? Dont work ...

---

Not only that, it's very hard to read with all those capital letters.

[paross1]

First off, this is not a valid statement:

LOWER(ctrData.CONTRACTNO)=LOWER(ctrLoc.CONTRACTNO(+))
NOT IN (
SELECT DECODE(INSTR('AW-06,AW-13',CONTRACTNO),0,CONTRACTNO,NULL)C FROM CONTRACTS )

What is NOT IN? The LOWER(ctrData.CONTRACTNO)=LOWER(ctrLoc.CONTRACTNO(+)) statement is stand alone, so what are you saying is NOT IN the sub-select? In other words, what COLUMN is NOT IN (SELECT DECODE(INSTR.... etc.?

Plus, if you are on Oracle 9i or above, you should work on your OUTER JOIN syntax, since you are using the old Oracle (+) syntax.

Phil