Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2005
    Posts
    38

    Unanswered: Urgent Help with Nested Query

    I have a question with respect to a query which will work in T-SQL but I cannot get it to work with PL/SQL. Can someone please help.

    Select L3D_PARTICIPANT.CASEID, L3D_PARTICIPANT.ID , L3D_PARTICIPANT.LAST_NAME,
    L3D_PARTICIPANT.FIRST_NAME , ORIGINAL_COUNTS.COUNTID ,
    ORIGINAL_COUNTS.INSTRUMENT_DESC, ORIGINAL_COUNTS.INST_FILING_DATE
    from lions.l3d_participant, LIONS.L3D2_CR_INST_PARTS_COUNTS ORIGINAL_COUNTS
    WHERE L3D_PARTICIPANT.CASEID (+) = ORIGINAL_COUNTS.CASEID
    AND L3D_PARTICIPANT.ID (+) = ORIGINAL_COUNTS.PARTID
    AND L3D_PARTICIPANT.CASEID = '2000R01439'
    AND original_counts.CASEID = (SELECT ORIGINAL_COUNTS.CASEID,
    ORIGINAL_COUNTS.PARTID, MAX NEWER_COUNTS.INST_FILING_DATE) AS MAXDATE
    FROM LIONS.L3D2_CR_INST_PARTS_COUNTS ORIGINAL_COUNTS,
    LIONS.L3D2_CR_INST_PARTS_COUNTS NEWER_COUNTS
    GROUP BY NEWER_COUNTS.CASEID, NEWER_COUNTS.PARTID
    WHERE ORIGINAL_COUNTS.CASEID = NEWER_COUNTS.CASEID
    AND ORIGINAL_COUNTS.PARTID = NEWER_COUNTS.PARTID ).


    The error message I am getting is that it THERE IS NO PARENTHESIS around the where statement in the nested part of my query.

    Thanks.

  2. #2
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Your missing a ( for your max function. Its also not good practise to use the same table alias in your main statement and in the subquery. The group by should be after the where clause in your subquery. It also looks like your invalidating the outer join by doing the in clause.

    Alan

  3. #3
    Join Date
    Feb 2004
    Location
    Dublin, Ireland
    Posts
    212
    Your subquery is completely wrong

    1) it returns more than one attribute
    2) you select ORIGINAL_COUNTS.CASEID, ORIGINAL_COUNTS.PARTID
    but you group by NEWER_COUNTS.CASEID, NEWER_COUNTS.PARTID
    it's not possible

    I'm just wonderring how this could work in T-SQL :-)

Posting Permissions

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