3 Replies Latest reply on Mar 3, 2007 9:39 AM by paross1

    Select Statement-Oracle

    emmim44 Level 1
      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 )
        • 1. Re: Select Statement-Oracle
          Level 7
          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.



          • 2. Re: Select Statement-Oracle
            Dan Bracuk Level 5
            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.
            • 3. Re: Select Statement-Oracle
              paross1 Level 2
              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