ERROR IN EXECUTION OF SCRIPT

Report Inappropriate Content · ‎2010-10-27

Hi community,

I have written a script to load data from a ORACLE Database.

My script is the follow :

ODBC CONNECT TO [BGFI DATA SRCE 01;DBQ=BDDELTA ] (XUserId is aRKTKSVMMDdCWLC, XPassword is XAeTKSVMMDdCGBD);

LOAD CDOS,
NUMCPT,
INTCPT,
Debit_OU,
Credit_OU,
Debit_MVT,
Credit_MVT,
Debit_CLO,
Credit_CLO
;
SQL SELECT B.*,

( B.Debit_OU + B.Debit_MVT ) AS Debit_CLO,

( B.Credit_OU + B.Credit_MVT ) AS Credit_CLO

FROM (

SELECT eg.CDOS AS CDOS, eg.NUMCPT AS NUMCPT, cpt.INTCPT AS INTCPT,

( SELECT nvl(SUM(eg1.DOS_MTDB),0)

FROM COMECRGEN eg1

WHERE eg1.CDOS = eg.CDOS

AND eg1.NUMCPT = eg.NUMCPT

AND to_char(eg1.DATCPT, 'yyyy') = to_char( to_char(to_date('01/01/2007'), 'yyyy')-1 ) ) AS Debit_OU,

( SELECT nvl(SUM(eg2.DOS_MTCR),0)

FROM COMECRGEN eg2

WHERE eg2.CDOS = eg.CDOS

AND eg2.NUMCPT = eg.NUMCPT

AND to_char(eg2.DATCPT, 'yyyy') = to_char( to_char(to_date('01/01/2007'), 'yyyy')-1 ) ) AS Credit_OU,

( SELECT nvl(SUM(eg3.DOS_MTDB),0)

FROM COMECRGEN eg3

WHERE eg3.CDOS = eg.CDOS

AND eg3.NUMCPT = eg.NUMCPT

AND eg3.DATCPT BETWEEN to_date('01/01/2007') AND to_date('31/12/2007') ) AS Debit_MVT,

( SELECT nvl(SUM(eg4.DOS_MTCR),0)

FROM COMECRGEN eg4

WHERE eg4.CDOS = eg.CDOS

AND eg4.NUMCPT = eg.NUMCPT

AND eg4.DATCPT BETWEEN to_date('01/01/2007') AND to_date('31/12/2007') ) AS Credit_MVT

FROM COMECRGEN eg

LEFT JOIN COMCPT cpt ON cpt.CDOS = eg.CDOS

AND cpt.NUMCPT = eg.NUMCPT

WHERE eg.CDOS = '04'

GROUP BY eg.CDOS, eg.NUMCPT, cpt.INTCPT

ORDER BY eg.CDOS, eg.NUMCPT, cpt.INTCPT) B;

When i reloaded data, an error is displaying.

The following error message is:

Champ non trouvé - <Debit_OU>
SQL SELECT B.*,

( B.Debit_OU + B.Debit_MVT ) AS Debit_CLO,

( B.Credit_OU + B.Credit_MVT ) AS Credit_CLO

FROM (

SELECT eg.CDOS AS CDOS, eg.NUMCPT AS NUMCPT, cpt.INTCPT AS INTCPT,

( SELECT nvl(SUM(eg1.DOS_MTDB),0)

FROM COMECRGEN eg1

WHERE eg1.CDOS = eg.CDOS

AND eg1.NUMCPT = eg.NUMCPT

AND to_char(eg1.DATCPT, 'yyyy') = to_char( to_char(to_date('01/01/2007'), 'yyyy')-1 ) ) AS Debit_OU,

( SELECT nvl(SUM(eg2.DOS_MTCR),0)

FROM COMECRGEN eg2

WHERE eg2.CDOS = eg.CDOS

AND eg2.NUMCPT = eg.NUMCPT

AND to_char(eg2.DATCPT, 'yyyy') = to_char( to_char(to_date('01/01/2007'), 'yyyy')-1 ) ) AS Credit_OU,

( SELECT nvl(SUM(eg3.DOS_MTDB),0)

FROM COMECRGEN eg3

WHERE eg3.CDOS = eg.CDOS

AND eg3.NUMCPT = eg.NUMCPT

AND eg3.DATCPT BETWEEN to_date('01/01/2007') AND to_date('31/12/2007') ) AS Debit_MVT,

( SELECT nvl(SUM(eg4.DOS_MTCR),0)

FROM COMECRGEN eg4

WHERE eg4.CDOS = eg.CDOS

AND eg4.NUMCPT = eg.NUMCPT

AND eg4.DATCPT BETWEEN to_date('01/01/2007') AND to_date('31/12/2007') ) AS Credit_MVT

FROM COMECRGEN eg

LEFT JOIN COMCPT cpt ON cpt.CDOS = eg.CDOS

AND cpt.NUMCPT = eg.NUMCPT

WHERE eg.CDOS = '04'

GROUP BY eg.CDOS, eg.NUMCPT, cpt.INTCPT

ORDER BY eg.CDOS, eg.NUMCPT, cpt.INTCPT) B

It means that, the field Debit_OU is not found.

Thanks in advance for your help.

Report Inappropriate Content · ‎2010-10-27

In load script, change fields to UPPERCASE

CDOS,
NUMCPT,
INTCPT,
DEBIT_OU,
CREDIT_OU,
DEBIT_MVT,
CREDIT_MVT,
DEBIT_CLO,
CREDIT_CLO;

Report Inappropriate Content · ‎2010-10-27

IN QLikview ,you need to use all the alias (as) in load statement rather than in Select statement.even for adding the fields also you need to do the same. As per my knowledge , i think you need to load the tables individually (or load view ) and make relations through joins rather than in select statement

Report Inappropriate Content · ‎2010-10-27

Thanks Eduardo, You have a great experience with QlikView.

Thank you very much my friend.

It's OK

Report Inappropriate Content · ‎2010-10-27

Now, i have declared three variables to replace the dates in the srcipt. Theses variables are:

SET dateDebutExercice = Date('01/01/2007');

SET dateFinExercice = Date('31/12/2007');

Set dossier = '04';

I would like to replace the values that are hard in the script by parameters and these parameters are the variables above

When i replace i obtain the following script

SET dateDebutExercice = Date('01/01/2007');

SET dateFinExercice = Date('31/12/2007');

Set dossier = '04';

//-------------- Chargement de la balance de CAMRAIL -----------------------

ODBC CONNECT TO [BGFI DATA SRCE 01;DBQ=BDDELTA ] (XUserId is aRKTKSVMMDdCWLC, XPassword is XAeTKSVMMDdCGBD);

Balance_CAMRAIL:
LOAD CDOS,
NUMCPT,
INTCPT,
DEBIT_OU,
CREDIT_OU,
DEBIT_MVT,
CREDIT_MVT,
DEBIT_CLO,
CREDIT_CLO
;
SQL SELECT B.*,

( B.Debit_OU + B.Debit_MVT ) AS Debit_CLO,

( B.Credit_OU + B.Credit_MVT ) AS Credit_CLO

FROM (

SELECT eg.CDOS AS CDOS, eg.NUMCPT AS NUMCPT, cpt.INTCPT AS INTCPT,

( SELECT nvl(SUM(eg1.DOS_MTDB),0)

FROM COMECRGEN eg1

WHERE eg1.CDOS = eg.CDOS

AND eg1.NUMCPT = eg.NUMCPT

AND to_char(eg1.DATCPT, 'yyyy') = to_char( to_char($(dateDebutExercice), 'yyyy')-1 ) ) AS Debit_OU,

( SELECT nvl(SUM(eg2.DOS_MTCR),0)

FROM COMECRGEN eg2

WHERE eg2.CDOS = eg.CDOS

AND eg2.NUMCPT = eg.NUMCPT

AND to_char(eg2.DATCPT, 'yyyy') = to_char( to_char($(dateDebutExercice), 'yyyy')-1 ) ) AS Credit_OU,

( SELECT nvl(SUM(eg3.DOS_MTDB),0)

FROM COMECRGEN eg3

WHERE eg3.CDOS = eg.CDOS

AND eg3.NUMCPT = eg.NUMCPT

AND eg3.DATCPT BETWEEN $(dateDebutExercice) AND $(dateFinExercice) ) AS Debit_MVT,

( SELECT nvl(SUM(eg4.DOS_MTCR),0)

FROM COMECRGEN eg4

WHERE eg4.CDOS = eg.CDOS

AND eg4.NUMCPT = eg.NUMCPT

AND eg4.DATCPT BETWEEN $(dateDebutExercice) AND $(dateFinExercice) ) AS Credit_MVT

FROM COMECRGEN eg

LEFT JOIN COMCPT cpt ON cpt.CDOS = eg.CDOS

AND cpt.NUMCPT = eg.NUMCPT

WHERE eg.CDOS = $(dossier)

GROUP BY eg.CDOS, eg.NUMCPT, cpt.INTCPT

ORDER BY eg.CDOS, eg.NUMCPT, cpt.INTCPT) B;

When i reload data, an error displays. the error as follows:

SQL Error:[Oracle][ODBC][Ora]ORA-00936: expression absente

SQL Scriptline:
SQL State:S1000
SQL SELECT B.*,

( B.Debit_OU + B.Debit_MVT ) AS Debit_CLO,

( B.Credit_OU + B.Credit_MVT ) AS Credit_CLO

FROM (

SELECT eg.CDOS AS CDOS, eg.NUMCPT AS NUMCPT, cpt.INTCPT AS INTCPT,

( SELECT nvl(SUM(eg1.DOS_MTDB),0)

FROM COMECRGEN eg1

WHERE eg1.CDOS = eg.CDOS

AND eg1.NUMCPT = eg.NUMCPT

AND to_char(eg1.DATCPT, 'yyyy') = to_char( to_char(Date('01/01/2007'), 'yyyy')-1 ) ) AS Debit_OU,

( SELECT nvl(SUM(eg2.DOS_MTCR),0)

FROM COMECRGEN eg2

WHERE eg2.CDOS = eg.CDOS

AND eg2.NUMCPT = eg.NUMCPT

AND to_char(eg2.DATCPT, 'yyyy') = to_char( to_char(Date('01/01/2007'), 'yyyy')-1 ) ) AS Credit_OU,

( SELECT nvl(SUM(eg3.DOS_MTDB),0)

FROM COMECRGEN eg3

WHERE eg3.CDOS = eg.CDOS

AND eg3.NUMCPT = eg.NUMCPT

AND eg3.DATCPT BETWEEN Date('01/01/2007') AND Date('31/12/2007') ) AS Debit_MVT,

( SELECT nvl(SUM(eg4.DOS_MTCR),0)

FROM COMECRGEN eg4

WHERE eg4.CDOS = eg.CDOS

AND eg4.NUMCPT = eg.NUMCPT

AND eg4.DATCPT BETWEEN Date('01/01/2007') AND Date('31/12/2007') ) AS Credit_MVT

FROM COMECRGEN eg

LEFT JOIN COMCPT cpt ON cpt.CDOS = eg.CDOS

AND cpt.NUMCPT = eg.NUMCPT

WHERE eg.CDOS = 04

GROUP BY eg.CDOS, eg.NUMCPT, cpt.INTCPT

ORDER BY eg.CDOS, eg.NUMCPT, cpt.INTCPT) B

Thanks in advance for your help

Report Inappropriate Content · ‎2010-10-27

Hi Yimen,

Try declare your variables:

SET dateDebutExercice = '01/01/2007';
SET dateFinExercice = '31/12/2007';
Set dossier = '04';

When you call this variables you need to put '$(dateDebutExercice)'

Ex.:

.......

AND eg4.DATCPT BETWEEN '$(dateDebutExercice)' AND '$(dateFinExercice)'

.......

WHERE eg.CDOS = '$(dossier)'

Need to put char '

QlikView App Dev