Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2006
    Posts
    157

    Unanswered: Alter old query on the basis of new query

    Hi: Can any one please tell me how to i alter my old query on the basis of new query
    Old Query
    ---------
    select distinct(dbtr_view.debtor_id), dbtr_view.Next_Contact_Date,debt.clt_id, clt_ref_no, name1, name2, list_date, cur_bal,

    assign_id, dbtr_view.status_code, dbtr_view.status_date, dbtr_view.next_contact_date From dm.dbtr_view left outer join

    dm.debt on (debt.debt_id = dbtr_view.debtor_id,0) left outer join dm.pmt on (dbtr_view.debtor_id = pmt.debtor_id,0) where

    dbtr_view.assign_id in ('GIB', 'HPP', 'VTT') and pmt_date < '2006-12-21' and (debt.clt_id like 'LCAH%' or debt.clt_id

    like 'DLF%' or debt.clt_id like 'DRTV%' or debt.clt_id like 'CAVA%' or debt.clt_id like 'THOM%' or debt.clt_id like

    'DLFG%' ) and dbtr_view.status_code not in ('112', '120', '123', '140', '145') and dbtr_view.status_code < '400'

    Alter old query on the basis of this query
    -------------------------------------------
    select distinct(dbtr_view.debtor_id), dbtr_view.Next_Contact_Date,debt.clt_id, clt_ref_no, name1, name2, list_date, cur_bal,
    assign_id, dbtr_view.status_code, dbtr_view.status_date, dbtr_view.next_contact_date
    ---FOR TESTING ONLY RETURN MAX PMT INFO---------------------------------------------------------------
    ,(Select max(PMT_ID)
    from pmt where pmt.debtor_id = dbtr_view.debtor_id) as LastPmtID, (Select max(pmt_date)
    from pmt where pmt.debtor_id = dbtr_view.debtor_id and pmt_id = LastPmtID) as LastPmtDate,
    (Select princ_amt from pmt where pmt.debtor_id = dbtr_view.debtor_id and pmt_id = LastPmtID and pmt_date = LastPmtDate) as

    LastPmtAmt
    From dm.dbtr_view left outer join dm.debt on (debt.debt_id = dbtr_view.debtor_id,0)
    ----------NO NEED TO JOIN PMT TABLE IN MAIN FROM CLAUSE WHEN USING SUBQUERY-----------------------------------------------
    --left outer join dm.pmt on (dbtr_view.debtor_id = pmt.debtor_id,0)
    where dbtr_view.assign_id in ('MIK') and (debt.clt_id like 'CERT%' or debt.clt_id like 'NDST%')
    and dbtr_view.status_code not in ('112', '120', '123', '140', '145') and dbtr_view.status_code < '400' AND

    LIST_DATE<='2006-11-30'
    --------------COMPARISON MADE IN SUBQUERY AT BOTTOM-------------------------------------------
    --and pmt_date < '2006-12-21'
    -----------------------------------------------------------------------------------------------------
    AND (select count(*) from pmt where pmt.debtor_id = dbtr_view.debtor_id and pmt_date> today(*)-60) =0

    Thanks.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Umm, a very confusing question...

    Do you mean: "How do I change the criteria of the second query based on the results of the first query?"

    More explaination needed!
    George
    Home | Blog

  3. #3
    Join Date
    Jul 2006
    Posts
    157
    Yes please.

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Without understanding what your queries do (other that looking complicated) my advice would be:

    Build your first query via "Create query in Design View"

    Then create a second query and instead of selecting the table you want to base the query on - select your first query.

    This will allow you to put criteria against the first queries results.
    George
    Home | Blog

Posting Permissions

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