topic Error using Year() function into a SQL SELECT statement in QlikView

Error using Year() function into a SQL SELECT statement

Tue, 22 Mar 2011 14:37:33 GMT

Hi.

I have tried to use the Year function into a SQL SELECT statement. I'm using QV 10 SR1.

I have written:

Tbl1:

LOAD Year(myDate) as YYYY

SQL SELECT Year(myDate) FROM myDB.dbo.myTable

STORE Tbl1 INTO c:\qvapp\myTables.QVD

and I have myDate field not found error.

Any helps to me, please? Thanks

Error using Year() function into a SQL SELECT statement

Miguel_Angel_Baeyens — Tue, 22 Mar 2011 14:46:56 GMT

Hi,

There is no myDate field coming from the SQL part:

Tbl1:LOAD Year(myDate) AS YYYY; // provided myDate is a date field formatted as suchSQL SELECT myDateFROM myDB.dbo.myTable; STORE Tbl1 INTO C:\qvapp\myTables.QVD;

According to your code above, the name of the field would be "Year(myDate)" because you are not renaming in the SQL part.

Hope that helps.

Error using Year() function into a SQL SELECT statement

Tue, 22 Mar 2011 15:07:14 GMT

Ok, thanks,

but this syntax is right:

Tbl1: LOAD myDate as DDDD; SQL SELECT myDate FROM myDb.dbo.myTable;

but this not (it is a bit strange!):

Tbl1: LOAD Year(myDate) as YYYY; SQL SELECT Year(myDate) FROM myDb.dbo.myTable;

Thanks

Error using Year() function into a SQL SELECT statement

Miguel_Angel_Baeyens — Tue, 22 Mar 2011 15:22:38 GMT


pscorca69 wrote:
but this not (it is a bit strange!):
Tbl1: LOAD "Year(myDate)" as YYYY; SQL SELECT Year(myDate) FROM myDb.dbo.myTable;

The name of the field in the SQL part is Year(myDate) (bold), so you need to double quote it in the LOAD part (bold italic).

Hope that helps.

Error using Year() function into a SQL SELECT statement

Tue, 22 Mar 2011 15:41:52 GMT

Thanks, but with double quote equally I have that Year(myDate) doesn't found.

Error using Year() function into a SQL SELECT statement

Miguel_Angel_Baeyens — Tue, 22 Mar 2011 15:47:29 GMT

I figured that. So you have to rename the field in either the LOAD part or the SQL part to get it work (because you don't know how the field "Year(myDate)" is actually called in SQL):

Tbl1: LOAD Year as YYYY; SQL SELECT Year(myDate) AS Year FROM myDb.dbo.myTable;

Tbl1: LOAD Year(myDate) as YYYY; SQL SELECT myDate FROM myDb.dbo.myTable;

Hope that helps.

Error using Year() function into a SQL SELECT statement

Tue, 22 Mar 2011 17:55:54 GMT

Ok, thanks. Perhaps the 2nd option it could be useful for me.

Error using Year() function into a SQL SELECT statement

Wed, 23 Mar 2011 20:06:20 GMT

Im new to QlickView, but know my way around SQL

It seems that QlickView associates the column name with the column name returned from SQL.

If you perform the query in SQL Query analyser you will notice it has a blank column name:

SELECT Year(myDate) FROM myDB.dbo.myTable

This is indirectly what Miguel said in the first reply.

To get around this you need to alias it:

SELECT Year(myDate) as myDate FROM myDB.dbo.myTable

I think this will resolve the problem if you simply add that to the SQL statement as per your original post.