Big SQL queries and passing Variables in the SQL queries

[Adagio]

Hello, I'm trying to execute some big SQL queries in TestComplete to fetch the data for the scripts. I'm able to execute small and simple queries, but when it comes to execute a query which has more than 1 lines, I see errors. The error is never so informative to help me fix the problem. does anyone know how to overcome this issue?

here's a snapshot of the query. I understand that there are quite a few special characters in the Query, but how to deal with this situation?

I'm using JAVASCRIPT for creating the test scripts.

Also, I'm trying to parameterize the SQL using a variable, but I've not had any success so far. any pointers for that would help too.

Thank you

Abhi

[NisHera · Accepted solution]

if one line works but not two lines....I guess the problem is in concatenating .

The query sending to  SQL should a single string. 

to concatenate string in java script you can use + sign for more read this 

first create string like....

str1 = "select distinnct...." ;
str1 = str1+"where ..blah blah" ;
str1 = str1+"AND..blah blah" ;
str1 = str1+"order by ..blah blah" ;

and then send to your object

var set conn.Execute(str1)

 special characters doesn't matter  as far as they are in side of string 

[tristaanogre]

I would also add that, since you're using JavaScript, any instance of single quotes (') should be replaced with \'.  This will ensure that the actual character is sent.

[Adagio · Accepted solution]

Thank you, NisHera

I got it working. I didn't have to split it did it in a similar fashion but without creating the string variable.

here's the working code: 

Thank you

Abhi