topic LEFT in Script in Water Cooler

LEFT in Script

Wed, 02 May 2012 10:44:49 GMT

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

jagannalla — Wed, 02 May 2012 11:03:22 GMT

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

Wed, 02 May 2012 11:37:01 GMT

Hi thanks

My qvw is connected to the server

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

LEFT in Script

jagannalla — Wed, 02 May 2012 11:59:30 GMT

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

Wed, 02 May 2012 12:10:20 GMT

Thank you very much

I understand the syntax now

Problem solved!

LEFT in Script

jagan — Wed, 02 May 2012 12:15:32 GMT

Hi,

Try this way

TableName:

LOAD

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

SQL SELECT

MyDateField,

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,

FROM MyTable

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

Hope this helps you.

Regards,

jagan.