Results 1 to 2 of 2

Thread: Subquery

  1. #1
    Join Date
    Jul 2005
    Posts
    276

    Unanswered: Subquery

    Hi All -
    I have a table with data as mentioned below.
    Whenever you find a row with suffix 00 and null first_dt, second_dt column,
    the earliest date from a non-zero suffix has to be loaded.

    cc_id suf first_dt second_dt
    1234 02 11/15/2007
    1234 01 06/5/2008 9/9/2009
    1234 00

    cc_id suf first_dt second_dt
    1234 02 11/15/2007
    1234 01 06/5/2008 9/9/2009
    1234 00 11/15/2007 9/9/2009 ******
    Here is the cursor query what I wrote. I feel its not doing everything i want.
    I'll write a procedure to update the row correspondingly but I want to pull that row in the cursor as mentioned above. Can someone please take a look.

    SELECT b.cc_id, a.suf,
    a.fisr_dt, a.SECOND_DT
    FROM
    ( SELECT c.cc_id,
    MIN(c.FIRST_DT) AS FIRST_DT,
    MIN(A.SECOND_DT) AS SECOND_DT
    FROM Sum_tab c
    WHERE c.suf <>'00'
    GROUP BY c.CC_ID
    ) B, SUM_TAB A
    WHERE
    A.cc_id = b.cc_id
    --AND b.suffix = '00'
    AND ((a.FIRST_DT IS NULL AND b.FIRST_DT IS NOT NULL)
    OR (a.SECOND_DT IS NULL AND b.SECOND_DT IS NOT NULL))
    Thanks in advance!

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    17:15:27 SQL> SELECT a.cc_id, a.suf, b.date1	, c.date2
    17:15:27   2  FROM nandinir A, nandinir b, nandinir c
    17:15:27   3  WHERE A.cc_id = b.cc_id
    17:15:27   4   AND  A.cc_id = c.cc_id
    17:15:27   5   and  a.suf = 0
    17:15:27   6   and  a.date1 is null
    17:15:27   7   and  a.date2 is null
    17:15:27   8   and  b.date1 = (SELECT min(DATE1) FROM NANDINIR WHERE A.cc_id = b.cc_id AND B.SUF <> 0)
    17:15:27   9   and  c.date2 = (SELECT min(DATE2) FROM NANDINIR WHERE A.cc_id = c.cc_id AND c.SUF <> 0)
    17:15:27  10  /
    
         CC_ID	  SUF DATE1		  DATE2
    ---------- ---------- ------------------- -------------------
          1234	    0 2007-11-15 00:00:00 2009-09-09 00:00:00
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

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