Results 1 to 2 of 2

Thread: In Clause

  1. #1
    Join Date
    Jul 2006
    Posts
    157

    Unanswered: In Clause

    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|NYML|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(AssignID, "|", ",") but its not showing the way which i need

    ORIGINAL QUERY in the program
    SQL = "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) " & _
    "inner join dm.user_view on user_view.user_id = dbtr_view.assign_id " & _
    "left outer join dm.pmt on (dbtr_view.debtor_id = pmt.debtor_id,0) where "

    SQL = SQL & "user_view.User_Office_Location in ('" & AssignID & "')"
    debug.print SQL

    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) inner join dm.user_view on user_view.user_id = dbtr_view.assign_id left

    outer join dm.pmt on (dbtr_view.debtor_id = pmt.debtor_id,0) where user_view.User_Office_Location in ('PASC|INSS|NYML|FLWP|')

    REQUIRED 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) inner join dm.user_view on user_view.user_id = dbtr_view.assign_id left

    outer join dm.pmt on (dbtr_view.debtor_id = pmt.debtor_id,0) where user_view.User_Office_Location in

    ('PASC','INSS','NYML','FLWP')

  2. #2
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    assuming that you are using VB6, and that the In clause is available as a string,
    Code:
    dim strInClause as string
    
    strInClause = "'PASC|INSS|NYML|FLWP|'"
    Note that there's an extra separator symbol at the end. Plus, you need to eliminate the existing apostrophe characters. So, first remove the apostophes, then remove any trailing or leading (non SEPARATING 'separators'), SPLIT the string into an array of string, pad each substring with leading/trailing apostrophes, and finally JOIN thearray elements into a single string.
    Code:
    strClauses() as String  ' Dynamic array of string to hold the individual clauses...
    Dim N as Integer
    
    ' Pre-process
    strInClause = Trim (strInClause)  ' Remove leading/trailing spaces
    strInClause  = replace(strInClause, "'", "")  ' remove apostrophes
    
    Do while InstrRev(strInClause, "|") = Len(StrInCLause) ' Do While the last character is a "|"
      strInClause = Left(strInClause, Len(StrInClause)-1)  ' Remove the last character
    Loop
    
    Do while InstrRev(strInClause, "|") = 1 ' Do While the First character is a "|" (possibly not needed)
      strInClause = Right(strInClause, Len(StrInClause)-1)  ' Remove the First character
    Loop
    
    ' Split into an array
    strClauses = SPLIT (strInClause, "|")
    
    ' Now we have an array of clauses
    For N = 0 to UBound(strClauses)
      strClauses(N) = "'" & strClauses & "'"  ' Pad the individual clause elements with apostrophes...
    Next N
    
    ' Finally, join them back together, using a comma as a delimiter
    strInClause = JOIN (strCLauses, ",")
    Last edited by loquin; 02-14-07 at 17:37.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


Posting Permissions

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