Using a variable within SQL select statement

[#1]

Hi All,

My first foray into incremental loads is not going as planned I have used the following steps and am now somewhat stuck.

1. load previous qvd file

2. Create new table with max date of file

3. Set variable to equal max data from file (using let and peek statement)

all of this works fine...i will drop the temp table once I can get this to work...my problem is getting the SQL Select (via ODBC connection) to recognise the variable within the where statement. I have tried a few different methods (preceding load, Setting the variable, letting the variable, adding quatations, removing quotations, but I cant get the syntax right. Any help would be greatly appreciated

[Anonymous]

Apparently it calculates: 2014 minus 1 minus 6

Try using in single quotes:

='$(vMaxNPS)'

[#3]

All

I want to thank you for all of your help...looks like the problems I was having was in thinking that I had to load the previous .qvd file before attempting to set the variable. I accidentally commented that out and it now runs ok

so Thank you Sokkorn, Dathu and Michael...we have a fantastic community here!!

[Sokkorn]

Good to hear that Rick .

Regards,

Sokkorn

[#5]

Hi All,

    I'm having a problem with a variable inserted in the FROM line in a SQL SELECT sentence accesing to a MDB database ... With the red line I get a ODBC driver error .... but the variable is replaced correctly and the text are the same in booth cases.

Thanks.

[#6]

Solved ....

I've enclosed the table's name and field's name with "[" and "]" instead " ' " and now works fine.

Thanks

José María

[#7]

Hi Jose and QV community, I am trying to pass the table name using a variable in SQL select statement, and QV does not seem to like the square brackets "[" nor the " ' " around the table name.  Any suggestions?

Examples I tried:

SQL SELECT [field1], [field2]

-- FROM [$(DatabaseName).dbo."vw_abc"];

-- FROM $(DatabaseName).dbo."vw_abc";

It works fine if the DatabaseName is hardcoded:

-- FROM myDB.dbo."vw_abc";

[#8]

Hi Nithya,

I'm using the next statements and works fine ....

SET DR_Dir=''; ' DRA_V1 is the databse name

without the extension, the full name is DRA_v1.mdb

SQL SELECT

format(,'dd/mm/yyyy') as ,

Record,

Today as Today_RM,

Budget

FROM $(DR_Dir).[QV_Resumen] ¡ QV_Resumen is the table

Best Regards

Jose M Cerezo