long SQL strings..

Td04 · #1 (**permalink**) 10-01-04, 08:36

I wonder how to make this code shorter..(smarter)
and (or) how to do a linebreak in the SQL string, it gets to long in the end.
Thanks//Martin
fnr_flera = request.queryString("Fnr_flera")
myArray=split(fnr_flera,",")
'As the selections are different every time (1-75 records)
if UBound(myArray)+1 = 1 then
SQL = "SELECT * from owner_alla WHERE Fnr = '"&myArray(0)&"'order by NAMN"
elseif UBound(myArray)+1 = 2 then
SQL = "SELECT * from owner_alla WHERE Fnr = '"&myArray(0)&"'OR Fnr='"&myArray(1)&"'order by NAMN"
elseif UBound(myArray)+1 = 3 then
SQL = "SELECT * from owner_alla WHERE Fnr = '"&myArray(0)&"'OR Fnr='"&myArray(1)&"' OR Fnr='"&myArray(2)&"'order by NAMN"
elseif UBound(myArray)+1 = 4 then
SQL = "SELECT * from owner_alla WHERE Fnr = '"&myArray(0)&"'OR Fnr='"&myArray(1)&"' OR Fnr='"&myArray(2)&"'OR Fnr='"&myArray(3)&"'order by NAMN"
elseif UBound(myArray)+1 = 5 then
SQL = "SELECT * from owner_alla WHERE Fnr = '"&myArray(0)&"'OR Fnr='"&myArray(1)&"' OR Fnr='"&myArray(2)&"'OR Fnr='"&myArray(3)&"'OR Fnr='"&myArray(4)&"'order by NAMN"
elseif UBound(myArray)+1 = 6 then
SQL = "SELECT * from owner_alla WHERE Fnr = '"&myArray(0)&"'OR Fnr='"&myArray(1)&"' OR Fnr='"&myArray(2)&"'OR Fnr='"&myArray(3)&"'OR Fnr='"&myArray(4)&"'OR Fnr='"&myArray(5)&"'order by NAMN"
elseif UBound(myArray)+1 = 7 then
SQL = "SELECT * from owner_alla WHERE Fnr = '"&myArray(0)&"'OR Fnr='"&myArray(1)&"' OR Fnr='"&myArray(2)&"'OR Fnr='"&myArray(3)&"'OR Fnr='"&myArray(4)&"'OR Fnr='"&myArray(5)&"'OR Fnr='"&myArray(6)&"'order by NAMN"
elseif UBound(myArray)+1 = 8 then
SQL = "SELECT * from owner_alla WHERE Fnr = '"&myArray(0)&"'OR Fnr='"&myArray(1)&"' OR Fnr='"&myArray(2)&"'OR Fnr='"&myArray(3)&"'OR Fnr='"&myArray(4)&"'OR Fnr='"&myArray(5)&"'OR Fnr='"&myArray(6)&"' OR Fnr='"&myArray(7)&"'order by NAMN"
elseif UBound(myArray)+1 = 9 then ....
it goes on until 75... :O

RBARAER · #2 (**permalink**) 10-01-04, 09:18

Hello,

1. Using IN will shorten your SQL a little :

Code:

SQL = "SELECT * from owner_alla WHERE Fnr IN ('"&myArray(0)&"','"&myArray(1)&"','"&myArray(2)&"','"&myArray(3)&"','"&myArray(4)&"','"&myArray(5)&"','"&myArray(6)&"' ,'"&myArray(7)&"') order by NAMN"

2. You can try something like this (not sure about the correct syntax, but here is the idea) :

Code:

SQL = "SELECT * from owner_alla WHERE Fnr IN("

FOR i=0 TO UBound(myArray)-1 
    IF (i < UBound(myArray)-1) THEN
        SQL = SQL & "'"&myArray(i)&"',"
    ELSE
        SQL = SQL & "'"&myArray(i)&"'"
    END IF
NEXT i

SQL = SQL & ") order by NAMN"

Hope that helps,

Regards,

RBARAER

Td04 · #3 (**permalink**) 10-01-04, 11:06

That loop was brilliant! It works excellent after just removeing the
i after next..
Thanks a million RBARAER, that saved alot of headache
//Martin :-)

RBARAER · #4 (**permalink**) 10-04-04, 06:23

Glad it helped you !

Regards,

RBARAER

Internet Services

Web Development

Digital Marketing

Consumer Tech