topic Re: If Then with Connect and Store in Water Cooler

If Then with Connect and Store

johncahillhp — Fri, 29 Aug 2014 21:10:16 GMT

Hello,

I am looking for a solution that would allow me to connect to one of two data source and store the results in one QVD file. The data source is determined with an input box selection: PRO;ITG. Both have the exact same schema and support the same SQL. On a refresh the script would determine which source prior to executing the SQL. The resultant table would then be stored to a QVD file.

This is the what I expect the code to look like;

Test:

If InputSrc = 'PRO' Theb

CONNECT TO [....]

Else

CONNECT TO [...]

End If

SQL

Select *

From mytable ;

STORE Test into Local.QVD;

With the label the script returns an error of;

Unknown statement

Test:

if tSrc = 'PRO' then

CONNECT TO [...]

With no label, the script returns an error of;

ErrorSource: OraOLEDB, ErrorMsg: ORA-00942: table or view does not exist

SQL

Select *

From mytable ;

Without the IF statement, the CONNECT and SQL Works fine.

It appears that the label TEST required a CONNECT and STORE and does not allow a If statement.

Is this even possible or should I look at a different solution? Any recommendations welcome.

Thanks,

John

Re: If Then with Connect and Store

JonnyPoole — Fri, 29 Aug 2014 21:14:13 GMT

Does this work ? Move the Test: to in front of the SQL. I added a preceding load in case you decide to manipulate the raw SQL data. optionally drop the test table if you don't need it anymore at the end of the script.

If InputSrc = 'PRO' Theb

CONNECT TO [....]

Else

CONNECT TO [...]

End If

Test:

load

SQL

Select *

From mytable ;

STORE Test into Local.QVD;

drop table Test;

Re: If Then with Connect and Store

johncahillhp — Fri, 29 Aug 2014 21:23:13 GMT

No, the script returns the same error as if there were no label;

ErrorSource: OraOLEDB, ErrorMsg: ORA-00942: table or view does not exist

SQL

Select *

From mytable ;

Re: If Then with Connect and Store

JonnyPoole — Sat, 30 Aug 2014 01:05:56 GMT

And if you take out the if statement and leave just the correct CONNECT statement it all works correct ? can you confirm and also paste the successful script here ?

If that still works for you, i would use the 'debug' button option to step through the script line by line to ensure its evaluating the IF statement correctly and that the variable is set correctly.

Re: If Then with Connect and Store

engishfaque — Sat, 30 Aug 2014 06:23:31 GMT

Dear John,

I have checked through OLEDB, It is working fine. Here is the script.

IF vInput = 1 then

OLEDB CONNECT TO [Provider=MSDASQL.1;Persist Security Info=False;Extended Properties="DSN=MS Access Database;DBQ=E:\Ishfaque Ahmed\QlikView\mdb\TestDB\db1.mdb;DefaultDir=E:\Ishfaque Ahmed\QlikView\mdb\TestDB;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;UID=admin;"];

ELSE

OLEDB CONNECT TO [Provider=MSDASQL.1;Persist Security Info=False;Extended Properties="DSN=MS Access Database;DBQ=E:\Ishfaque Ahmed\QlikView\mdb\TestDB\db2.mdb;DefaultDir=E:\Ishfaque Ahmed\QlikView\mdb\TestDB;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;UID=admin;"];

TestTbl:

SQL SELECT * FROM Account;

STORE TestTbl into C:\TestQVD.qvd (qvd);

If need be, please let me know.

Kind regards,

Ishfaque Ahmed

Re: If Then with Connect and Store

johncahillhp — Sat, 30 Aug 2014 14:32:21 GMT

Thanks Jonathon,

The if statement works correctly and successfully connects to the right data source. I am using the debug function to step through the script.

Here is the successful script;

if tSrc = 'PRO' then

CONNECT TO [Provider=OraOLEDB.Oracle.1;Persist Security Info=True;User ID=xxxxx;Data Source=xxx_PRO;Extended Properties=""] (XPassword is RBCEAUBOTbYIWQNOILMETZQEDC);

elseif tSrc = 'ITG' then

CONNECT TO [Provider=OraOLEDB.Oracle.1;Persist Security Info=True;User ID=xxxxx;Data Source=xxx_ITG;Extended Properties=""] (XPassword is FeIYKUBOTbYGCRRNBbYWWPJGQJMEDDC);

End If

Re: If Then with Connect and Store

johncahillhp — Sat, 30 Aug 2014 14:51:33 GMT

Thank you Ishfaque and Jonathon,

This is the correct syntax;

IF tSrc = 'PRO' then

OLEDB CONNECT TO [Provider=OraOLEDB.Oracle.1;Persist Security Info=True;User ID=xxxxx;Data Source=xxx_PRO;Extended Properties=""] (XPassword is RBCEAUBOTbYIWQNOILMETZQEDC);

ELSE

OLEDB CONNECT TO [Provider=OraOLEDB.Oracle.1;Persist Security Info=True;User ID=xxxxx;Data Source=xxx_ITG;Extended Properties=""] (XPassword is FeIYKUBOTbYGCRRNBbYWWPJGQJMEDDC);

TestTbl:

SQL SELECT *

FROM mytable

where ROWNUM <= 5;

STORE TestTbl into C:\TestQVD.qvd (qvd);

drop table TestTbl;

Re: If Then with Connect and Store

JonnyPoole — Sat, 30 Aug 2014 18:23:31 GMT

OK. something is weird. It sounds like connectivity is good but the SQL is bad.

Here is my next suggestion.

1. Create a brand new QVW

2. Add a connect statement to the script

3. browse the data source and reselect the same table as before with all fields (select * from <tablename>)

4. Test that the SQL runs fine

5. if it does , add a table name in the line above the SQL statementfront as you have done like this: Table1:

6. rerun the sql.

> what happens ?

Re: If Then with Connect and Store

johncahillhp — Tue, 02 Sep 2014 11:45:59 GMT

Thank you for your diligence. The SQL was bad. It is working now.