8 Replies Latest reply: Sep 2, 2014 7:46 AM by John Cahill RSS

    If Then with Connect and Store

    John Cahill


      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
          Jonathan Poole

          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
              John Cahill

              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
                  Jonathan Poole

                  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
                      John Cahill

                      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
                  Ishfaque Ahmed

                  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
                      John Cahill

                      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;