Post reply

Need help with the query. Can not filter out data

  • April 30, 2010 at 7:20 am

    #224022

    Hi,

    I seriously need help with this query. Sorry about the lots of info but I thought more is better. Anyway I need help sorting this dilema out. Will appreciate any help. Thank you.

    effective_dt acct_no tran_code amt origin_tracer_no

    11/6/2009 400006 156 6541474.582009110600342 -No

    11/6/2009 400006 157 252009110600342 - No

    11/6/2009 400006 156 189000 2009110601704

    11/13/2009 400006 156 477506.712009111301369

    11/13/2009 400006 156 64002009111301444

    11/17/2009 400006 156 134002009111701419

    11/18/2009 400006 156 3588002009111800593

    11/20/2009 400006 156 294802009112003176 -No

    11/20/2009 400006 157 252009112003176 - No

    11/25/2009 400006 156 120002009112501366

    11/25/2009 400006 156 124585.582009112501368

    11/30/2009 400006 156 380002009113000691

    I would like the lines that has "No" on the side above that has same origin_tracer_no and the tran code of 156 and 157 to be taken out from the query output. I want the rest of the transaction with trancode of 156 to be left in the result. I want the result to be like below

    effective_dt acct_no tran_code amt origin_tracer_no

    11/6/2009 400006 156 189000 2009110601704

    11/13/2009 400006 156 477506.712009111301369

    11/13/2009 400006 156 64002009111301444

    11/17/2009 400006 156 134002009111701419

    11/18/2009 400006 156 3588002009111800593

    11/25/2009 400006 156 120002009112501366

    11/25/2009 400006 156 124585.582009112501368

    11/30/2009 400006 156 380002009113000691

    I am using the following query to get the above result. Please help me as I can not figure out how to solve the problem. I am working with one table. The query looks like below

    select distinct

    dh.effective_dt,

    dh.acct_no,

    dh.acct_type,

    dh.tran_code,

    dh.amt,

    dh.origin_tracer_no

    from dp_history dh, dp_history dh1

    where

    dh.acct_no='00400003406'

    and dh.effective_dt between '2009-11-06 00:00:00' and '2009-11-30 00:00:00'

    and dh.origin_tracer_no<>dh1.origin_tracer_no

    order by dh.acct_no,dh.origin_tracer_no

  • April 30, 2010 at 8:00 am

    #1160428

    Would simply adding this additional condition give you what you need?

    .. and right(origin_tracer_no,2) <> 'No'

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • April 30, 2010 at 8:25 am

    #1160454

    Thank you for the reply. But my mistake. I put No to specify what rows i want to be taken out from the result.

  • April 30, 2010 at 2:27 pm

    #1160750

    Got it working thank you.

  • April 30, 2010 at 10:53 pm

    #1160858

    yogesh_pandey (4/30/2010)

    Got it working thank you.

    Two way steet here... please post your solution. Thanks.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply