Help in query

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Anna
    #1

    Help in query

    Hi: Can any one please tell me what i do changes in the query so that
    the in clause will be shows with comma when i select multiple location
    as right now it is showing in clause like that:
    user_view.User_ Office_Location in ('PASC|INSS|NYM L|FLWP|')

    Required in clause
    user_view.User_ Office_Location in ('PASC','INSS', 'NYML','FLWP')
    and when i select one location then in ('PASC')

    I try to use the replace function AssignID = Replace(AssignI D, "|",
    ",") but its not showing the way which i need

    ORIGINAL QUERY in the program
    SQL = "select distinct(dbtr_v iew.debtor_id),
    dbtr_view.Next_ Contact_Date,de bt.clt_id, clt_ref_no, name1, name2,

    list_date, cur_bal, assign_id, dbtr_view.statu s_code,
    dbtr_view.statu s_date, dbtr_view.next_ contact_date " & _
    "From dm.dbtr_view " & _
    "left outer join dm.debt on (debt.debt_id =
    dbtr_view.debto r_id,0) " & _
    "inner join dm.user_view on user_view.user_ id =
    dbtr_view.assig n_id " & _
    "left outer join dm.pmt on (dbtr_view.debt or_id =
    pmt.debtor_id,0 ) where "

    SQL = SQL & "user_view.User _Office_Locatio n in ('" & AssignID
    & "')"
    debug.print SQL

    select distinct(dbtr_v iew.debtor_id),
    dbtr_view.Next_ Contact_Date,de bt.clt_id, clt_ref_no, name1, name2,
    list_date, cur_bal,

    assign_id, dbtr_view.statu s_code, dbtr_view.statu s_date,
    dbtr_view.next_ contact_date From dm.dbtr_view left outer join

    dm.debt on (debt.debt_id = dbtr_view.debto r_id,0) inner join
    dm.user_view on user_view.user_ id = dbtr_view.assig n_id left

    outer join dm.pmt on (dbtr_view.debt or_id = pmt.debtor_id,0 ) where
    user_view.User_ Office_Location in ('PASC|INSS|NYM L|FLWP|')

    REQUIRED QUERY
    select distinct(dbtr_v iew.debtor_id),
    dbtr_view.Next_ Contact_Date,de bt.clt_id, clt_ref_no, name1, name2,
    list_date, cur_bal,

    assign_id, dbtr_view.statu s_code, dbtr_view.statu s_date,
    dbtr_view.next_ contact_date From dm.dbtr_view left outer join

    dm.debt on (debt.debt_id = dbtr_view.debto r_id,0) inner join
    dm.user_view on user_view.user_ id = dbtr_view.assig n_id left

    outer join dm.pmt on (dbtr_view.debt or_id = pmt.debtor_id,0 ) where
    user_view.User_ Office_Location in ('PASC','INSS', 'NYML','FLWP')

    Tags: None
    • RobinS
      #2
      Re: Help in query

      Why can't you replace "|" with "','" ?

      Robin S.
      Ts'i mahnu uterna ot twan ot geifur hingts uto.
      -----------------------------------------------
      "Anna" <colleen1980@gm ail.comwrote in message
      news:1171469791 .186323.48160@s 48g2000cws.goog legroups.com...
      Hi: Can any one please tell me what i do changes in the query so that
      the in clause will be shows with comma when i select multiple location
      as right now it is showing in clause like that:
      user_view.User_ Office_Location in ('PASC|INSS|NYM L|FLWP|')
      >
      Required in clause
      user_view.User_ Office_Location in ('PASC','INSS', 'NYML','FLWP')
      and when i select one location then in ('PASC')
      >
      I try to use the replace function AssignID = Replace(AssignI D, "|",
      ",") but its not showing the way which i need
      >
      ORIGINAL QUERY in the program
      SQL = "select distinct(dbtr_v iew.debtor_id),
      dbtr_view.Next_ Contact_Date,de bt.clt_id, clt_ref_no, name1, name2,
      >
      list_date, cur_bal, assign_id, dbtr_view.statu s_code,
      dbtr_view.statu s_date, dbtr_view.next_ contact_date " & _
      "From dm.dbtr_view " & _
      "left outer join dm.debt on (debt.debt_id =
      dbtr_view.debto r_id,0) " & _
      "inner join dm.user_view on user_view.user_ id =
      dbtr_view.assig n_id " & _
      "left outer join dm.pmt on (dbtr_view.debt or_id =
      pmt.debtor_id,0 ) where "
      >
      SQL = SQL & "user_view.User _Office_Locatio n in ('" & AssignID
      & "')"
      debug.print SQL
      >
      select distinct(dbtr_v iew.debtor_id),
      dbtr_view.Next_ Contact_Date,de bt.clt_id, clt_ref_no, name1, name2,
      list_date, cur_bal,
      >
      assign_id, dbtr_view.statu s_code, dbtr_view.statu s_date,
      dbtr_view.next_ contact_date From dm.dbtr_view left outer join
      >
      dm.debt on (debt.debt_id = dbtr_view.debto r_id,0) inner join
      dm.user_view on user_view.user_ id = dbtr_view.assig n_id left
      >
      outer join dm.pmt on (dbtr_view.debt or_id = pmt.debtor_id,0 ) where
      user_view.User_ Office_Location in ('PASC|INSS|NYM L|FLWP|')
      >
      REQUIRED QUERY
      select distinct(dbtr_v iew.debtor_id),
      dbtr_view.Next_ Contact_Date,de bt.clt_id, clt_ref_no, name1, name2,
      list_date, cur_bal,
      >
      assign_id, dbtr_view.statu s_code, dbtr_view.statu s_date,
      dbtr_view.next_ contact_date From dm.dbtr_view left outer join
      >
      dm.debt on (debt.debt_id = dbtr_view.debto r_id,0) inner join
      dm.user_view on user_view.user_ id = dbtr_view.assig n_id left
      >
      outer join dm.pmt on (dbtr_view.debt or_id = pmt.debtor_id,0 ) where
      user_view.User_ Office_Location in ('PASC','INSS', 'NYML','FLWP')
      >

        Comment

        Previous template Next
        Working...