Load Script - if statement with string concatenation

[#1]

Hi all,

I am having problems to have string concatenation in an if statement in a load script SQL statement.

Example:

SQL

Select IF(Month(`TxDate`) < 8, 'Season ' + Year(`TxDate`), 'Season ' + (Year(`TxDate`)+1)) AS Season

Can anyone explain what I am doing wrong?

[antoniotiman]

Hi Colin,

If() not is sintax SQL, is sintax QV.

You use

case
When ...   then

else ..

end

as Season.

But Month Sintax depending on Your DB.

You can use Preceding Load :

Load *,

If(Month(...),........) as Season;

SQL Select *

from Table;

Regards,

Antonio

[#3]

Hi Antonio,

Thanks for your reply.

However the If statement is not the issue as it is working fine. It is the concatenation which is working not as expected.

This expression:

('Season ' & (Year(`TxDate`)-1) & '-' & (Year(`TxDate`))

is resulting in 0.

I am sure that Year works fine as I tried it as a separate expression. I am afraid that & is being interpreted as a logical AND expression.

Btw - I am using MySQL but this is not the issue.

Thanks,

Colin

[antoniotiman]

Colin,

Ok, MySql has If() Sintax.

Tray to remove ' in Year('TxDate')  -> Year(TxDate)

[Anonymous]

Hi

Just to be sure, try replacing the &'-'& with &'_'& or some other separator.

[#6]

I solved this issue with a preceding load as follows:

LOAD      *,

               IF(Month(`TxDate`) < 8, 'Season ' + Year(`TxDate`), 'Season ' + (Year(`TxDate`)+1)) AS Season;

SQL

Select      TxDate

From ...