rel0aded0ne rel0aded0ne - 6 months ago 9
SQL Question

Split SELECT statement

I wrote a script which is connecting to an Oracle database to select multiple entries in a specific table.

The statement looks like this:

rs.open "SELECT PATH301 FROM NC301B WHERE EDIPROC like 'P30_' AND (LF301M > 0) AND (PATH301 NOT LIKE '%saptemp%') AND (PATH301 NOT LIKE '%SAPTEMP%') AND (PATH301 NOT LIKE '%usr%') AND (PATH301 NOT LIKE '%Windows%');", cn, 3


Now I want to know if it is possible to split this statement over multiple lines. For example like this:

rs.open "SELECT PATH301 FROM NC301B
WHERE EDIPROC like 'P30_'
AND (LF301M > 0)
AND (PATH301 NOT LIKE '%saptemp%')
AND (PATH301 NOT LIKE '%SAPTEMP%')
AND (PATH301 NOT LIKE '%usr%')
AND (PATH301 NOT LIKE '%Windows%');", cn, 3


That first
SELECT
Statement is way to big and not looking good at all.
I hope you understand what I mean.

EDIT: Sorry, but i dont think that my question is a duplicate.
I dont want to know how to split an IF statement :)

Answer

The SQL statement is a string, so you can split it into multiple strings and concatenate them. Use the line continuation operator (_) for wrapping the statement. For readability reasons I'd put the query in a variable though, like this:

qry = "SELECT PATH301 FROM NC301B " & _
      "WHERE EDIPROC like 'P30_' AND (LF301M > 0) " & _
      "AND (PATH301 NOT LIKE '%saptemp%') " & _
      "AND (PATH301 NOT LIKE '%SAPTEMP%') " & _
      "AND (PATH301 NOT LIKE '%usr%') " & _
      "AND (PATH301 NOT LIKE '%Windows%');"
rs.open qry, cn, 3

Multiline strings are not supported in VBScript.