topic Invalide Expression in SQL Load Statement in App Development

Invalide Expression in SQL Load Statement

lessassy — Sat, 16 Nov 2024 19:57:59 GMT

Hello everyone,

I'm working both on oracle and Qliksense and i'm trying to extract datas from oracle.

My script looks the following:

TMP_SOLL:
LOAD
COD_RLX,
Date#(COD_DAT,'DD/MM/YYYY') as COD_DAT,
Year(Date#(COD_DAT,'DD/MM/YYYY')) as ANNEE,
Month(Date#(COD_DAT,'DD/MM/YYYY')) as MOIS,
COD_RES,
LIB_RES,
COD_SOC,
COD_DAT_CLO,
LIB_FAM,
LIB_NAT,
LIB_STA,
LIB_TYP_DEM,
COD_AGE_TRAI,
LIB_AGE_RLX,
COD_SEC_RLX,
LIB_SEC_RLX,
COD_INSEE,
COD_AGE_GEO,
LIB_AGE_GEO;

SQL SELECT
"COD_RLX",
"COD_DAT",
"COD_RES",
"LIB_RES",
"COD_SOC",
"COD_DAT_CLO",
"LIB_FAM",
"LIB_NAT",
"LIB_STA",
"LIB_TYP_DEM",
"COD_AGE_TRAI",
"LIB_AGE_RLX",
"COD_SEC_RLX",
"LIB_SEC_RLX",
"COD_INSEE",
"COD_AGE_GEO",
"LIB_AGE_GEO"
FROM "LMH"."V_SOLLI_VS_QS";

The first thing i noticed is that it's impossible for me to do a preceding LOAD LIKE

TMP_SOLL:

LOAD*,

Count (RLX) as NB_FILES ;
LOAD
COD_RLX,
Date#(COD_DAT,'DD/MM/YYYY') as COD_DAT,
Year(Date#(COD_DAT,'DD/MM/YYYY')) as ANNEE,
Month(Date#(COD_DAT,'DD/MM/YYYY')) as MOIS,

(more fields not showed);

SQL SELECT
"COD_RLX",
"COD_DAT",
"COD_RES",
"LIB_RES",
(more fields not showed);

I get an error message saying that the statement is not good.

Then i created another table to do the transformation :

SOLL:
LOAD
COD_DAT,
ANNEE,
MOIS,
COD_RES,
LIB_RES,
COD_SOC,
//Date#(COD_DAT,'DD/MM/YYYY') as COD_DAT_CLO,
LIB_FAM,
LIB_NAT,
LIB_STA,
LIB_TYP_DEM,
COD_AGE_TRAI,
LIB_AGE_RLX,
COD_SEC_RLX,
LIB_SEC_RLX,
COD_INSEE,
COD_AGE_GEO,
LIB_AGE_GEO,
Count(Trim(Text(COD_RLX))) as COD_RLX // I get an error message when i add the count (Invalid expressiojn)
Resident TMP_SOLL

GROUP BY "COD_RES",
/COD_INSEE",
"COD_AGE_GEO",
"LIB_NAT",
"LIB_STA",

"ANNEE"

"MOIS"

I also get an error message when i add the field in the clause group by .

SOmeone can help me please.

thank you

Re: Invalide Expression in SQL Load Statement

sunny_talwar — Thu, 03 Oct 2019 12:25:15 GMT

What is RLX here? I see COD_RLX, but do you have RLX also?

Also, you need to add group by in your preceding load... but the way it is structured, you will have a count of 1 for each row because you will be grouping it by all the fields unless you have multiple rows where all columns can repeat. I guess what exactly is your underlying goal here?

Re: Invalide Expression in SQL Load Statement

lessassy — Thu, 03 Oct 2019 14:25:42 GMT

Thank you for your reply.

I just want to do a preceding load but i have issue with the group by i guess:

TMP_SOLL:
LOAD *,
COUNT(COD_RLX) as NB_DOSS
Group by
COD_DAT,
ANNEE,
MOIS,
COD_RES,
LIB_RES,
COD_SOC,
LIB_FAM,
LIB_NAT,
LIB_STA,
LIB_TYP_DEM,
COD_AGE_TRAI,
LIB_AGE_RLX,
COD_SEC_RLX,
LIB_SEC_RLX,
COD_INSEE,
COD_AGE_GEO,
LIB_AGE_GEO;

LOAD
COD_RLX,
Date#(COD_DAT,'DD/MM/YYYY') as COD_DAT,
Year(Date#(COD_DAT,'DD/MM/YYYY')) as ANNEE,
Month(Date#(COD_DAT,'DD/MM/YYYY')) as MOIS,
COD_RES,
LIB_RES,
COD_SOC,
COD_DAT_CLO,
LIB_FAM,
LIB_NAT,
LIB_STA,
LIB_TYP_DEM,
COD_AGE_TRAI,
LIB_AGE_RLX,
COD_SEC_RLX,
LIB_SEC_RLX,
COD_INSEE,
COD_AGE_GEO,
LIB_AGE_GEO;

I get the same message "unvalid expression"

What i did was to use the count expression and put all other fields in the group by but it doesn't work and i don't know why.

Re: Invalide Expression in SQL Load Statement

sunny_talwar — Thu, 03 Oct 2019 14:33:12 GMT

How about this?

TMP_SOLL: LOAD COD_DAT, ANNEE, MOIS, COD_RES, LIB_RES, COD_SOC, LIB_FAM, LIB_NAT, LIB_STA, LIB_TYP_DEM, COD_AGE_TRAI, LIB_AGE_RLX, COD_SEC_RLX, LIB_SEC_RLX, COD_INSEE, COD_AGE_GEO, LIB_AGE_GEO, COUNT(COD_RLX) as NB_DOSS Group by COD_DAT, ANNEE, MOIS, COD_RES, LIB_RES, COD_SOC, LIB_FAM, LIB_NAT, LIB_STA, LIB_TYP_DEM, COD_AGE_TRAI, LIB_AGE_RLX, COD_SEC_RLX, LIB_SEC_RLX, COD_INSEE, COD_AGE_GEO, LIB_AGE_GEO; LOAD COD_RLX, Date#(COD_DAT,'DD/MM/YYYY') as COD_DAT, Year(Date#(COD_DAT,'DD/MM/YYYY')) as ANNEE, Month(Date#(COD_DAT,'DD/MM/YYYY')) as MOIS, COD_RES, LIB_RES, COD_SOC, COD_DAT_CLO, LIB_FAM, LIB_NAT, LIB_STA, LIB_TYP_DEM, COD_AGE_TRAI, LIB_AGE_RLX, COD_SEC_RLX, LIB_SEC_RLX, COD_INSEE, COD_AGE_GEO, LIB_AGE_GEO; SQL SELECT "COD_RLX", "COD_DAT", "COD_RES", "LIB_RES", "COD_SOC", "COD_DAT_CLO", "LIB_FAM", "LIB_NAT", "LIB_STA", "LIB_TYP_DEM", "COD_AGE_TRAI", "LIB_AGE_RLX", "COD_SEC_RLX", "LIB_SEC_RLX", "COD_INSEE", "COD_AGE_GEO", "LIB_AGE_GEO" FROM "LMH"."V_SOLLI_VS_QS";

Re: Invalide Expression in SQL Load Statement

lessassy — Thu, 03 Oct 2019 15:13:34 GMT

Thank you it worked !

SO basically you just replace the '*' symbol by all the fields, right ?

Can you tell me why it doesn't work with a simple LOAD *, Count(n) as NB_DOSS ?

Thank you for your help !!

Re: Invalide Expression in SQL Load Statement

sunny_talwar — Thu, 03 Oct 2019 15:23:59 GMT

because I don't think when you listed fields .. you actually listed all of them? Did you list COD_RLX? I don't see it... so what that means is that * includes is, but listing the fields explicitly... it did not and that was the difference