If Then with Connect and Store

[johncahillhp]

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

[JonnyPoole]

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;

[johncahillhp]

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 ;

[JonnyPoole]

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.

[engishfaque]

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

[johncahillhp]

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

[johncahillhp]

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;

[JonnyPoole · Accepted solution]

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  ?

[johncahillhp]

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