Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Community,
could you help me?
Wrong is the bold line.
SQL SELECT
GETDATE () AS "Data Giorno",
year("xlscad")*100+Month("xlscad") "Annomese",
case
when xlscad < ("Data Giorno" - 4 Month - 1 day)
then 'SCADUTO DA OLTRE 120'
when xlscad between (current_date - 4 Month) and (current_date - 3 Month - 1 day)
then 'SCADUTO DA 120'
WHEN xlscad between (current_date - 3 month) and (current_date - 2 Month - 1 day)
then 'SCADUTO DA 90'
WHEN xlscad between (current date - 2 Month) and (current_date - 1 month - 1 day)
then 'SCADUTO DA 60'
when xlscad between (current_date -1 month) and (current_date)
then 'SCADUTO DA 30'
when xlscad between (current_date) and (current_date + 1 month)
then 'A SCADERE A 30'
when xlscad between (current_date + 1 Month + 1 day) and (current_date + 2 month)
then 'A SCADERE A 60'
when xlscad between (current_date + 2 month + 1 day) and (current_date + 3 month)
then 'A SCADERE A 90'
when xlscad between (current_date + 3 month + 1 day) and (current_date + 4 month)
then 'A SCADERE A 120'
when xlscad > (current_date + 4 month + 1 day)
then 'A SCADERE OLTRE 120'
Else
'ZERO'
END
"SCAGLIONI"
Hi Fabio,
This is more a SQL question than a QlikSense question.
But i think the problem is your field 'Data Giorno' has not been created when you try to use it in the case statement.
You could try to replace that with the GETDATE() function, or even move all the case statement into a QV script statement and use the IF() function instead, although as you've already written the SQL it might be better to stick with that.
try:
when xlscad < (GETDATE() - 4 Month - 1 day)
I would think that you need to change it to:
when xlscad < (current_date - 4 Month - 1 day)
SQL SELECT
CONVERT(VARCHAR(10),GETDATE(),112) AS "Data Giorno",
year("Data Documento")*100+ Month("Data Documento") "Annomese",
case
when "Data Documento" < (GETDATE() -4 Month -1 day)
END
"SCAGLIONI"
Si è verificato il seguente errore:
ErrorSource: Microsoft OLE DB Provider for SQL Server, ErrorMsg: Sintassi non corretta in prossimità di 'Month'.
In SQL you cant reference an aliased column in the same SELECT statement. BTW that is exactly how it works in a LOAD statement too.
You are making an ALIAS here:
GETDATE () AS "Data Giorno"
and later in the same select you try to use this ALIAS - that does not work. So just stick to GetDate() all the way through for your entire SELECT:
SQL SELECT
GETDATE () AS "Data Giorno",
year("xlscad")*100+Month("xlscad") "Annomese",
case
when xlscad < (GetDate() - 4 Month - 1 day)
then 'SCADUTO DA OLTRE 120'
when xlscad between (current_date - 4 Month) and (current_date - 3 Month - 1 day)
then 'SCADUTO DA 120'
WHEN xlscad between (current_date - 3 month) and (current_date - 2 Month - 1 day)
then 'SCADUTO DA 90'
WHEN xlscad between (current date - 2 Month) and (current_date - 1 month - 1 day)
then 'SCADUTO DA 60'
when xlscad between (current_date -1 month) and (current_date)
then 'SCADUTO DA 30'
when xlscad between (current_date) and (current_date + 1 month)
then 'A SCADERE A 30'
when xlscad between (current_date + 1 Month + 1 day) and (current_date + 2 month)
then 'A SCADERE A 60'
when xlscad between (current_date + 2 month + 1 day) and (current_date + 3 month)
then 'A SCADERE A 90'
when xlscad between (current_date + 3 month + 1 day) and (current_date + 4 month)
then 'A SCADERE A 120'
when xlscad > (current_date + 4 month + 1 day)
then 'A SCADERE OLTRE 120'
Else
'ZERO'
END
"SCAGLIONI"
hI,
i0m trying but i receive the same syntax error .
case
when "Data Documento" < (GETDATE() - 4 month - 1 day)
then 'SCADUTO DA OLTRE 120'
when "Data Documento" between (current_date - 4 Month) and (current_date - 3 Month - 1 day)
then 'SCADUTO DA 120'
WHEN "Data Documento" between (current_date - 3 month) and (current_date - 2 Month - 1 day)
then 'SCADUTO DA 90'
WHEN "Data Documento" between (current_date - 2 Month) and (current_date - 1 month - 1 day)
then 'SCADUTO DA 60'
when "Data Documento" between (current_date -1 month) and (current_date)
then 'SCADUTO DA 30'
when "Data Documento" between (current_date) and (current_date + 1 month)
then 'A SCADERE A 30'
when "Data Documento" between (current_date + 1 Month + 1 day) and (current_date + 2 month)
then 'A SCADERE A 60'
when "Data Documento" between (current_date + 2 month + 1 day) and (current_date + 3 month)
then 'A SCADERE A 90'
when "Data Documento" between (current_date + 3 month + 1 day) and (current_date + 4 month)
then 'A SCADERE A 120'
when "Data Documento" > (current_date + 4 month + 1 day)
then 'A SCADERE OLTRE 120'
Else
'ZERO'
END
"SCAGLIONI"
Si è verificato il seguente errore:
ErrorSource: Microsoft OLE DB Provider for SQL Server, ErrorMsg: Sintassi non corretta in prossimità di 'month'.
ha ha - now you show a different SQL - and it is not even a complete SELECT statement 😉
So I cant judge if you are continuing to reference an alias ...
I am not a human syntax checker - you have to put in some effort yourself - honestly. Read the community guidelines about posting ....
hi,
no problem.
Thanks anyway.
As I can notice you´re using SQL Server, right?
(GETDATE() -4 Month -1 day) is not the right way of doing dates math
I´m guessing that
DateAdd(day,-1,DateAdd(Month,-4,getdate()))
is what you need