5 Replies Latest reply: May 2, 2012 8:15 AM by jagan mohan rao appala RSS

    LEFT in Script

      Hi

       

      Dates from a SQL Server Databases are formatted this way: 20120301

       

      If I write:

       

      SELECT X, Y, Z

           FROM MyTable

                 WHERE LEFT(MyDateField,4)= '2012' ;

       

      In a SQL Query (ODBC) I get what I want

       

      BUT If I just want write:

       

      SELECT LEFT(MyDateField,4), X, Y, Z

           FROM MyTable;

       

      I get a SCRIPT error:

       

      ODBC read failed

       

      What do I miss?

       

      Thanks

        • LEFT in Script
          Jagan Nalla

          Hello,

           

          Did i tested whether your qvw file is connected to server?

          Did you tried with preceding load?

           

          Load

          LEFT(MyDateField,4) as DateField,

          X, Y, Z;

          SELECT MyDateField, X, Y, Z

               FROM MyTable;

            • LEFT in Script

              Hi thanks

               

              My qvw is connected to the server

               

              Just the LEFT(MyDateField) prevents me from running the query

                • LEFT in Script
                  Jagan Nalla

                  Hello,

                   

                  Do you have MyDateField in your sql server dataset?

                   

                  If yes you need to use your code in preceding load

                  i.e load left(MyDateField,4) as DateField;

                   

                  Please attach your sample file.

                  • LEFT in Script
                    jagan mohan rao appala

                    Hi,

                     

                    Try this way

                     

                    TableName:

                    LOAD

                         X,

                         Y,

                         Z

                    WHERE Left(MyDateField,4)= '2012' ;

                     

                    SQL SELECT

                         MyDateField,

                         X,

                         Y,

                         Z

                    FROM MyTable;

                     

                     

                    But above method is not an efficient one, because we are loading all the data and then filtering only 2012 data.  Check for method like substring() in your database.

                     

                    Below example is for SQL server

                     

                    TableName:

                    SQL SELECT

                         MyDateField,

                         X,

                         Y,

                         Z

                    FROM MyTable

                    WHERE substring(MyDateField, 1, 4) = '2012';

                     

                    Hope this helps you.

                     

                    Regards,

                    jagan.