LEFT in Script

[#1]

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

[jagannalla]

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;

[#3]

Hi thanks

My qvw is connected to the server

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

[jagannalla · Accepted solution]

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.

[#5]

Thank you very much

I understand the syntax now

Problem solved!

[jagan]

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.