Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2003
    Posts
    1,074

    Unanswered: 2 field reference in subquery

    I am off in the syntax below, where I'm trying to match up 2 fields in the main query with 2 fields in the subquery. What do I have wrong?
    Code:
      select * from ua_agent_class_n_licenses a
      where a.date_class_removed is NULL 
            and (a.ssn, a.agents_license_nmbr) in (SELECT (b.SSN, b.agents_license_nmbr) 
                                                   FROM INSLIC.UA_AGENT_LICENSES b 
      	                                           where license_status in (1, 7, 8)
      	                                                and b.date_license_expires <= '1-Aug-2005');
    This is ultimately for an UPDATE query, otherwise both tables would be in the FROM clause.

    -Chuck

  2. #2
    Join Date
    Dec 2003
    Posts
    1,074
    For some reason, it was the parenthesis in the subquery
    Code:
    select * from ua_agent_class_n_licenses a
      where a.date_class_removed is NULL 
            and (a.ssn, a.agents_license_nmbr) in (SELECT b.SSN, b.agents_license_nmbr 
                                                   FROM INSLIC.UA_AGENT_LICENSES b 
      	                                           where license_status in (1, 7, 8)
      	                                                and b.date_license_expires <= '1-Aug-2005');
    -cf

  3. #3
    Join Date
    Jan 2004
    Posts
    492
    Just kidding i'm an idiot - nothing to see here
    Oracle OCPI (Certified Practicing Idiot)

  4. #4
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    btw,

    Code:
    and b.date_license_expires <= '1-Aug-2005'
    would probably be better as something like

    Code:
    and b.date_license_expires <= TO_DATE('01-08-2005','DD-MM-YYYY')
    or even

    Code:
    and b.date_license_expires <= DATE '2005-08-01'

  5. #5
    Join Date
    Dec 2003
    Posts
    1,074
    I know, in fact we usually take time down to seconds. This is just test sql to get things working.
    -cf

Posting Permissions

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