My agent qry ODBC connection is very very long. How can I move it to a new line? Thanks!
qry.SQL =“SELECT S.Staff_ID,S.Supervisor,D.STAFFID,D.name,D.practitioner_category_code, D.practitioner_category_value FROM Supervisor S,SYSTEM.staff_current_demographics D WHERE ((S.Supervisor_End_date IS NULL) AND (S.Staff_ID=D.STAFFID) AND (D.practitioner_category_code=‘693’ OR D.practitioner_category_code=‘887’ OR D.practitioner_category_code=‘021’ OR D.practitioner_category_code=‘748’ OR D.practitioner_category_code=‘586’ OR D.practitioner_category_code=‘637’ OR D.practitioner_category_code=‘888’ OR D.practitioner_category_code=‘331’ OR D.practitioner_category_code=‘455’ OR D.practitioner_category_code=‘216’ OR D.practitioner_category_code=‘856’ OR D.practitioner_category_code=‘855’ OR D.practitioner_category_code=‘865’ OR D.practitioner_category_code=‘858’ OR D.practitioner_category_code=‘857’ OR D.practitioner_category_code=‘859’ OR D.practitioner_category_code=‘688’ OR D.practitioner_category_code=‘864’ OR D.practitioner_category_code=‘583’ ))”
Subject: ODBC question
somestring = "foo " & _“bar”
You should use in to make the query a lot shorter:
qry.SQL ="SELECT "+_
"S.Staff_ID,S.Supervisor,D.STAFFID,D.name,D.practitioner_category_code, D.practitioner_category_value " & _
"FROM " & _
"Supervisor S,SYSTEM.staff_current_demographics D "& _
"WHERE " &_
"S.Supervisor_End_date IS NULL " & _
"AND S.Staff_ID=D.STAFFID " & _
“AND D.practitioner_category_code in ('331,‘355’,‘628’)”
You can use the plus sign (+) to concatentate strings but the ampersand is more efficient if the values are always going to be strings.
And be sure to look over the LCLSX, the LotusScript extension to the Lotus connectors. In many cases you can use this instead of odbc. It’s generally faster and you don’t need a dsn - no more fooling around in the control panel. Look under \Help.
Subject: RE: ODBC question
You can also wrap a string in { and }:
qry.SQL ={SELECT
S.Staff_ID,S.Supervisor,D.STAFFID,D.name,D.practitioner_category_code,D.practitioner_category_value FROM Supervisor S, SYSTEM.staff_current_demographics D WHERE S.Supervisor_End_date IS NULL AND S.Staff_ID=D.STAFFID AND D.practitioner_category_code in ('331,‘355’,‘628’)}
You can put in line breaks, it doesn’t matter. Whitespace is ignored in SQL.