Results 1 to 4 of 4

Thread: Subquery

  1. #1
    Join Date
    Dec 2003
    Posts
    13

    Unanswered: Subquery

    I am trying to create a subquery to return results. Can someone please look at this query and tell me what I am doing wrong. The error message I am getting is invalid sql statement. The following is an alias I am trying to create because the query already has a paramid, and transformvalue in the select statement.

    SELECT
    ADDRESS.ADDRESSDESC, ADDRESS.ADDRESS3, ADDRESS.VOICEMAIL,
    SOURCE.SOURCEDESC, SAMPLE.U_WORKORDERNUMBER, SAMPLE.U_SAMPLEDATETIME,
    SDIDATAITEM.PARAMID, SDIDATAITEM.ENTEREDVALUE, SDIDATAITEM.DISPLAYVALUE, TSSPARAMID, TSSTRANSFORMVALUE
    FROM
    LIMS.SAMPLE SAMPLE,
    LIMS.SDIDATAITEM SDIDATAITEM,
    LIMS.ADDRESS ADDRESS,
    LIMS.SOURCE SOURCE

    WHERE
    SAMPLE.SAMPLEID = SDIDATAITEM.KEYID1 AND
    TSSPARAMID,TSSTRANSFORMVALUE IN (SELECT PARAMID TSSPARAMID,TRANSFORMVALUE TSSTRANSFORMVALUE
    FROM SDIDATAITEM WHERE PARAMID= TSS) AND
    ADDRESS.U_WORKORDERNUMBER = SOURCE.SOURCEID AND
    SOURCE.SOURCEID = SAMPLE.U_WORKORDERNUMBER AND
    (SOURCE.SOURCEDESC = 'PPUD' OR
    SOURCE.SOURCEDESC = 'Cycle 8' OR
    SOURCE.SOURCEDESC = 'Cycle 7' OR
    SOURCE.SOURCEDESC = 'Cycle 6' OR
    SOURCE.SOURCEDESC = 'Cycle 5' OR
    SOURCE.SOURCEDESC = 'Clovis') AND
    SDIDATAITEM.PARAMID = 'BOD' AND
    SAMPLE.U_SAMPLEDATETIME >= TO_DATE ('01-09-2003 00:00:00', 'DD-MM-YYYY HH24:MIS') AND
    SAMPLE.U_SAMPLEDATETIME <= TO_DATE ('03-09-2003 23:59:59', 'DD-MM-YYYY HH24:MIS') AND
    (SAMPLE.U_WORKORDERNUMBER = '9962' OR
    SAMPLE.U_WORKORDERNUMBER = '9799' OR
    SAMPLE.U_WORKORDERNUMBER = '9707' OR
    SAMPLE.U_WORKORDERNUMBER = '9706' OR
    SAMPLE.U_WORKORDERNUMBER = '9705' OR
    SAMPLE.U_WORKORDERNUMBER = '9696B' OR
    SAMPLE.U_WORKORDERNUMBER = '9696A' OR
    SAMPLE.U_WORKORDERNUMBER = '9693' OR
    SAMPLE.U_WORKORDERNUMBER = '9686B' OR
    SAMPLE.U_WORKORDERNUMBER = '9686A' OR
    SAMPLE.U_WORKORDERNUMBER = '9686' OR
    SAMPLE.U_WORKORDERNUMBER = '9684' OR
    SAMPLE.U_WORKORDERNUMBER = '9683B' OR
    SAMPLE.U_WORKORDERNUMBER = '9683A' OR
    SAMPLE.U_WORKORDERNUMBER = '9682B' OR
    SAMPLE.U_WORKORDERNUMBER = '9682A' OR
    SAMPLE.U_WORKORDERNUMBER = '9675' OR
    SAMPLE.U_WORKORDERNUMBER = '9674B' OR
    SAMPLE.U_WORKORDERNUMBER = '9674A' OR
    SAMPLE.U_WORKORDERNUMBER = '9555' OR
    SAMPLE.U_WORKORDERNUMBER = '9518' OR
    SAMPLE.U_WORKORDERNUMBER = '9275' OR
    SAMPLE.U_WORKORDERNUMBER = '9213A' OR
    SAMPLE.U_WORKORDERNUMBER = '9212' OR
    SAMPLE.U_WORKORDERNUMBER = '9210' OR
    SAMPLE.U_WORKORDERNUMBER = '9209' OR
    SAMPLE.U_WORKORDERNUMBER = '9196' OR
    SAMPLE.U_WORKORDERNUMBER = '9193' OR
    SAMPLE.U_WORKORDERNUMBER = '9177' OR
    SAMPLE.U_WORKORDERNUMBER = '9176' OR
    SAMPLE.U_WORKORDERNUMBER = '9175' OR
    SAMPLE.U_WORKORDERNUMBER = '9173' OR
    SAMPLE.U_WORKORDERNUMBER = '9172' OR
    SAMPLE.U_WORKORDERNUMBER = '9157' OR
    SAMPLE.U_WORKORDERNUMBER = '9144' OR
    SAMPLE.U_WORKORDERNUMBER = '10099')
    ORDER BY
    ADDRESS.ADDRESSDESC ASC

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    your error is here:

    AND TSSPARAMID,TSSTRANSFORMVALUE IN
    (SELECT PARAMID TSSPARAMID,TRANSFORMVALUE TSSTRANSFORMVALUE
    FROM SDIDATAITEM WHERE PARAMID= TSS)

    i'm not sure whether that's valid in oracle, it may very well be, provided that you add parentheses --

    AND ( TSSPARAMID,TSSTRANSFORMVALUE) IN
    (SELECT PARAMID TSSPARAMID,TRANSFORMVALUE TSSTRANSFORMVALUE
    FROM SDIDATAITEM WHERE PARAMID= TSS)

    however, here's an equivalent --

    AND EXISTS
    ( SELECT 1 FROM SDIDATAITEM
    WHERE PARAMID= TSS
    AND PARAMID = XXX.TSSPARAMID
    AND TRANSFORMVALUE = XXX.TSSTRANSFORMVALUE )

    where XXX is whichever of the 4 tables in the outer query those columns come from

    oh, and PARAMID=TSS looks suspect, it basically says the values in two columns of the same row have to match
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2003
    Location
    NY
    Posts
    208

    Re: Subquery

    What is TSS?

    AND TSSPARAMID,TSSTRANSFORMVALUE IN
    (SELECT PARAMID SPARAMID,
    TRANSFORMVALUE TSSTRANSFORMVALUE
    FROM SDIDATAITEM
    WHERE PARAMID= TSS)

    Some coding advice

    -- Use IN () instead of the nested ORs

    AND SOURCE.SOURCEDESC in('PPUD','Cycle 8','Cycle 7','Cycle 6','Cycle 5','Clovis')

    -- Find a better way to list out values. Maybe a temp table

    -- Looks like your subquery isn't needed

    HIH

  4. #4
    Join Date
    Dec 2003
    Posts
    13

    Re: Subquery

    Hi this is a query I have taken over. I am in the process of rewriting it. TSS is an suppose to be an alias for the table sdidataitem. I didn't know if I need to included another join like sdidataitem = sdidataitem TSS. or if a subquery could eliminate that join. I am going to attach the originial sql. It may not even make sense to use a subquery. The query that I am attaching does not include the subquery. Subquery's are anew thing to me. As far as the OR's are concerned, I am rewriting it in a sql query to pass to a crystal report. The person before me created this report. All I am trying to do is rewrite the joins to be more efficient if possible.

    Thanks for you help!

    SELECT
    "ADDRESS"."ADDRESSDESC", "ADDRESS"."ADDRESS3", "ADDRESS"."VOICEMAIL", "SOURCE"."SOURCEDESC", "SAMPLE"."U_WORKORDERNUMBER", "SAMPLE"."U_SAMPLEDATETIME", "SDIDATAITEM"."PARAMID", "SDIDATAITEM"."ENTEREDVALUE", "SDIDATAITEM"."DISPLAYVALUE", "TSS_1"."PARAMID", "TSS_1"."TRANSFORMVALUE", "EC_1"."PARAMID", "EC_1"."ENTEREDVALUE", "EC_1"."DISPLAYUNITS", "PH_1"."PARAMID", "PH_1"."ENTEREDVALUE", "PH_1"."DISPLAYVALUE"
    FROM
    "LIMS"."ADDRESS" "ADDRESS",
    "LIMS"."SOURCE" "SOURCE",
    "LIMS"."SAMPLE" "SAMPLE",
    "LIMS"."SDIDATAITEM" "SDIDATAITEM",
    "LIMS"."SDIDATAITEM" "TSS_1",
    "LIMS"."SDIDATAITEM" "EC_1",
    "LIMS"."SDIDATAITEM" "PH_1"
    WHERE
    "ADDRESS"."U_WORKORDERNUMBER" = "SOURCE"."SOURCEID" AND
    "SOURCE"."SOURCEID" = "SAMPLE"."U_WORKORDERNUMBER" AND
    "SAMPLE"."SAMPLEID" = "SDIDATAITEM"."KEYID1" AND
    "SDIDATAITEM"."KEYID1" = "TSS_1"."KEYID1" AND
    "TSS_1"."KEYID1" = "EC_1"."KEYID1" AND
    "EC_1"."KEYID1" = "PH_1"."KEYID1" AND
    "EC_1"."PARAMID" = 'EC' AND
    "TSS_1"."PARAMID" = 'TSS' AND
    "PH_1"."PARAMID" = 'pH_IWI' AND
    ("SOURCE"."SOURCEDESC" = 'PPUD' OR
    "SOURCE"."SOURCEDESC" = 'Cycle 8' OR
    "SOURCE"."SOURCEDESC" = 'Cycle 7' OR
    "SOURCE"."SOURCEDESC" = 'Cycle 6' OR
    "SOURCE"."SOURCEDESC" = 'Cycle 5' OR
    "SOURCE"."SOURCEDESC" = 'Clovis') AND
    "SDIDATAITEM"."PARAMID" = 'BOD' AND
    "EC_1"."DISPLAYUNITS" <> 'umho/cm IWI' AND
    "SAMPLE"."U_SAMPLEDATETIME" >= TO_DATE ('01-12-2003 00:00:00', 'DD-MM-YYYY HH24:MIS') AND
    "SAMPLE"."U_SAMPLEDATETIME" <= TO_DATE ('31-12-2003 23:59:59', 'DD-MM-YYYY HH24:MIS') AND
    ("SAMPLE"."U_WORKORDERNUMBER" = '9962' OR
    "SAMPLE"."U_WORKORDERNUMBER" = '9799' OR
    "SAMPLE"."U_WORKORDERNUMBER" = '9707' OR
    "SAMPLE"."U_WORKORDERNUMBER" = '9706' OR
    "SAMPLE"."U_WORKORDERNUMBER" = '9705' OR
    "SAMPLE"."U_WORKORDERNUMBER" = '9696B' OR
    "SAMPLE"."U_WORKORDERNUMBER" = '9696A' OR
    "SAMPLE"."U_WORKORDERNUMBER" = '9693' OR
    "SAMPLE"."U_WORKORDERNUMBER" = '9686B' OR
    "SAMPLE"."U_WORKORDERNUMBER" = '9686A' OR
    "SAMPLE"."U_WORKORDERNUMBER" = '9686' OR
    "SAMPLE"."U_WORKORDERNUMBER" = '9684' OR
    "SAMPLE"."U_WORKORDERNUMBER" = '9683B' OR
    "SAMPLE"."U_WORKORDERNUMBER" = '9683A' OR
    "SAMPLE"."U_WORKORDERNUMBER" = '9682B' OR
    "SAMPLE"."U_WORKORDERNUMBER" = '9682A' OR
    "SAMPLE"."U_WORKORDERNUMBER" = '9675' OR
    "SAMPLE"."U_WORKORDERNUMBER" = '9674B' OR
    "SAMPLE"."U_WORKORDERNUMBER" = '9674A' OR
    "SAMPLE"."U_WORKORDERNUMBER" = '9555' OR
    "SAMPLE"."U_WORKORDERNUMBER" = '9518' OR
    "SAMPLE"."U_WORKORDERNUMBER" = '9275' OR
    "SAMPLE"."U_WORKORDERNUMBER" = '9213A' OR
    "SAMPLE"."U_WORKORDERNUMBER" = '9212' OR
    "SAMPLE"."U_WORKORDERNUMBER" = '9210' OR
    "SAMPLE"."U_WORKORDERNUMBER" = '9209' OR
    "SAMPLE"."U_WORKORDERNUMBER" = '9196' OR
    "SAMPLE"."U_WORKORDERNUMBER" = '9193' OR
    "SAMPLE"."U_WORKORDERNUMBER" = '9177' OR
    "SAMPLE"."U_WORKORDERNUMBER" = '9176' OR
    "SAMPLE"."U_WORKORDERNUMBER" = '9175' OR
    "SAMPLE"."U_WORKORDERNUMBER" = '9173' OR
    "SAMPLE"."U_WORKORDERNUMBER" = '9172' OR
    "SAMPLE"."U_WORKORDERNUMBER" = '9157' OR
    "SAMPLE"."U_WORKORDERNUMBER" = '9144' OR
    "SAMPLE"."U_WORKORDERNUMBER" = '10099')
    ORDER BY
    "ADDRESS"."ADDRESSDESC" ASC


    Originally posted by Ashar
    I am trying to create a subquery to return results. Can someone please look at this query and tell me what I am doing wrong. The error message I am getting is invalid sql statement. The following is an alias I am trying to create because the query already has a paramid, and transformvalue in the select statement.

    SELECT
    ADDRESS.ADDRESSDESC, ADDRESS.ADDRESS3, ADDRESS.VOICEMAIL,
    SOURCE.SOURCEDESC, SAMPLE.U_WORKORDERNUMBER, SAMPLE.U_SAMPLEDATETIME,
    SDIDATAITEM.PARAMID, SDIDATAITEM.ENTEREDVALUE, SDIDATAITEM.DISPLAYVALUE, TSSPARAMID, TSSTRANSFORMVALUE
    FROM
    LIMS.SAMPLE SAMPLE,
    LIMS.SDIDATAITEM SDIDATAITEM,
    LIMS.ADDRESS ADDRESS,
    LIMS.SOURCE SOURCE

    WHERE
    SAMPLE.SAMPLEID = SDIDATAITEM.KEYID1 AND
    TSSPARAMID,TSSTRANSFORMVALUE IN (SELECT PARAMID TSSPARAMID,TRANSFORMVALUE TSSTRANSFORMVALUE
    FROM SDIDATAITEM WHERE PARAMID= TSS) AND
    ADDRESS.U_WORKORDERNUMBER = SOURCE.SOURCEID AND
    SOURCE.SOURCEID = SAMPLE.U_WORKORDERNUMBER AND
    (SOURCE.SOURCEDESC = 'PPUD' OR
    SOURCE.SOURCEDESC = 'Cycle 8' OR
    SOURCE.SOURCEDESC = 'Cycle 7' OR
    SOURCE.SOURCEDESC = 'Cycle 6' OR
    SOURCE.SOURCEDESC = 'Cycle 5' OR
    SOURCE.SOURCEDESC = 'Clovis') AND
    SDIDATAITEM.PARAMID = 'BOD' AND
    SAMPLE.U_SAMPLEDATETIME >= TO_DATE ('01-09-2003 00:00:00', 'DD-MM-YYYY HH24:MIS') AND
    SAMPLE.U_SAMPLEDATETIME <= TO_DATE ('03-09-2003 23:59:59', 'DD-MM-YYYY HH24:MIS') AND
    (SAMPLE.U_WORKORDERNUMBER = '9962' OR
    SAMPLE.U_WORKORDERNUMBER = '9799' OR
    SAMPLE.U_WORKORDERNUMBER = '9707' OR
    SAMPLE.U_WORKORDERNUMBER = '9706' OR
    SAMPLE.U_WORKORDERNUMBER = '9705' OR
    SAMPLE.U_WORKORDERNUMBER = '9696B' OR
    SAMPLE.U_WORKORDERNUMBER = '9696A' OR
    SAMPLE.U_WORKORDERNUMBER = '9693' OR
    SAMPLE.U_WORKORDERNUMBER = '9686B' OR
    SAMPLE.U_WORKORDERNUMBER = '9686A' OR
    SAMPLE.U_WORKORDERNUMBER = '9686' OR
    SAMPLE.U_WORKORDERNUMBER = '9684' OR
    SAMPLE.U_WORKORDERNUMBER = '9683B' OR
    SAMPLE.U_WORKORDERNUMBER = '9683A' OR
    SAMPLE.U_WORKORDERNUMBER = '9682B' OR
    SAMPLE.U_WORKORDERNUMBER = '9682A' OR
    SAMPLE.U_WORKORDERNUMBER = '9675' OR
    SAMPLE.U_WORKORDERNUMBER = '9674B' OR
    SAMPLE.U_WORKORDERNUMBER = '9674A' OR
    SAMPLE.U_WORKORDERNUMBER = '9555' OR
    SAMPLE.U_WORKORDERNUMBER = '9518' OR
    SAMPLE.U_WORKORDERNUMBER = '9275' OR
    SAMPLE.U_WORKORDERNUMBER = '9213A' OR
    SAMPLE.U_WORKORDERNUMBER = '9212' OR
    SAMPLE.U_WORKORDERNUMBER = '9210' OR
    SAMPLE.U_WORKORDERNUMBER = '9209' OR
    SAMPLE.U_WORKORDERNUMBER = '9196' OR
    SAMPLE.U_WORKORDERNUMBER = '9193' OR
    SAMPLE.U_WORKORDERNUMBER = '9177' OR
    SAMPLE.U_WORKORDERNUMBER = '9176' OR
    SAMPLE.U_WORKORDERNUMBER = '9175' OR
    SAMPLE.U_WORKORDERNUMBER = '9173' OR
    SAMPLE.U_WORKORDERNUMBER = '9172' OR
    SAMPLE.U_WORKORDERNUMBER = '9157' OR
    SAMPLE.U_WORKORDERNUMBER = '9144' OR
    SAMPLE.U_WORKORDERNUMBER = '10099')
    ORDER BY
    ADDRESS.ADDRESSDESC ASC

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •