Error using Year() function into a SQL SELECT statement

[#1]

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

[Miguel_Angel_Baeyens]

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.

[#3]

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

[Miguel_Angel_Baeyens]

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.

[#5]

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

[Miguel_Angel_Baeyens]

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.

[#7]

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

[#8]

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.