Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Not applicable

Problem SQL statement

Hi.

Please, it's very urgent.

I work with Qlikview Version 9.00.7502.8 SR5 (Personnal Edition).

I work on Windows XP OS.

This is my SQL Statement on TOAD FOR ORACLE:


SQL SELECT c.nomredclass libpere, c1.nomredclass libfils1,
c2.nomredclass libfils2, e.numcpt num1, nvl(SUM (e.cpt_mtdb), 0) Brut, e1.numcpt num2, nvl(SUM (e1.cpt_mtdb), 0) Amor_Prov, ( nvl(SUM(e.cpt_mtdb),0)-nvl(SUM(e1.cpt_mtdb),0) ) Net
FROM comclass c

LEFT JOIN comclass c1
ON c1.cdos = c.cdos AND c1.numpere = c.codclass

LEFT JOIN comclass c2
ON c2.cdos = c1.cdos AND c2.numpere = c1.codclass

LEFT JOIN deltaerp.comecrgen e
ON e.cdos = c2.cdos AND SUBSTR (e.numcpt, 0, 3) = c2.codclass /* filtre sur les dates : */

LEFT JOIN deltaerp.comecrgen e1
ON e1.cdos = c2.cdos AND e1.numcpt = '2'||'8'||substr(e.numcpt,2,length(e.numcpt)-1)

LEFT JOIN deltaerp.comdos d
ON d.cdos = c2.cdos AND d.nred = 'BGFI' AND e.datcpt between d.ddex and d.dfex

WHERE c.cdos = '01'
AND c.numpere = 'MAIN'
AND c.ctab = '20105'
AND c.codclass = '2'
GROUP BY c.nomredclass,
c1.nomredclass,
c2.nomredclass,
e.numcpt,
e1.numcpt
ORDER BY c.nomredclass,
c1.nomredclass,
c2.nomredclass,
e.numcpt,
e1.numcpt;

The screenshot of the result of this application is in attached file.

In my script editor on QV, this is the code :

//************************************************************//
//******** Connexion a la base de donnée ORACLE *******
//***********************************************
*************//
ODBC CONNECT TO [BGFI DATA SRCE 01;DBQ=BDDELTA ] (XUserId is bQaPPSVMMDdCGDA, XPassword is TBNNJSVMMDdCWDC);

Actif_immobilise:
LOAD nomredclass,
libfils1,
libfils2,
num1,
Brut,
num2,
Amor_Prov,
Net;

SQL SELECT c.nomredclass nomredclass, c1.nomredclass libfils1,
c2.nomredclass libfils2, e.numcpt num1, nvl(SUM (e.cpt_mtdb), 0) Brut, e1.numcpt num2, nvl(SUM (e1.cpt_mtdb), 0) Amor_Prov, ( nvl(SUM(e.cpt_mtdb),0)-nvl(SUM(e1.cpt_mtdb),0) ) Net
FROM comclass c

LEFT JOIN comclass c1
ON c1.cdos = c.cdos AND c1.numpere = c.codclass

LEFT JOIN comclass c2
ON c2.cdos = c1.cdos AND c2.numpere = c1.codclass

LEFT JOIN deltaerp.comecrgen e
ON e.cdos = c2.cdos AND SUBSTR (e.numcpt, 0, 3) = c2.codclass /* filtre sur les dates : */

LEFT JOIN deltaerp.comecrgen e1
ON e1.cdos = c2.cdos AND e1.numcpt = '2'||'8'||substr(e.numcpt,2,length(e.numcpt)-1)

LEFT JOIN deltaerp.comdos d
ON d.cdos = c2.cdos AND d.nred = 'BGFI' AND e.datcpt between d.ddex and d.dfex

WHERE c.cdos = '01'
AND c.numpere = 'MAIN'
AND c.ctab = '20105'
AND c.codclass = '2'
GROUP BY c.nomredclass,
c1.nomredclass,
c2.nomredclass,
e.numcpt,
e1.numcpt
ORDER BY c.nomredclass,
c1.nomredclass,
c2.nomredclass,
e.numcpt,
e1.numcpt;

When i launche the reloading, the script error displays the following error message:

Champ non trouvé - <nomredclass>
SQL SELECT c.nomredclass nomredclass, c1.nomredclass libfils1,
c2.nomredclass libfils2, e.numcpt num1, nvl(SUM (e.cpt_mtdb), 0) Brut, e1.numcpt num2, nvl(SUM (e1.cpt_mtdb), 0) Amor_Prov, ( nvl(SUM(e.cpt_mtdb),0)-nvl(SUM(e1.cpt_mtdb),0) ) Net
FROM comclass c

LEFT JOIN comclass c1
ON c1.cdos = c.cdos AND c1.numpere = c.codclass

LEFT JOIN comclass c2
ON c2.cdos = c1.cdos AND c2.numpere = c1.codclass

LEFT JOIN deltaerp.comecrgen e
ON e.cdos = c2.cdos AND SUBSTR (e.numcpt, 0, 3) = c2.codclass

LEFT JOIN deltaerp.comecrgen e1
ON e1.cdos = c2.cdos AND e1.numcpt = '2'||'8'||substr(e.numcpt,2,length(e.numcpt)-1)

LEFT JOIN deltaerp.comdos d
ON d.cdos = c2.cdos AND d.nred = 'BGFI' AND e.datcpt between d.ddex and d.dfex

WHERE c.cdos = '01'
AND c.numpere = 'MAIN'
AND c.ctab = '20105'
AND c.codclass = '2'
GROUP BY c.nomredclass,
c1.nomredclass,
c2.nomredclass,
e.numcpt,
e1.numcpt
ORDER BY c.nomredclass,
c1.nomredclass,
c2.nomredclass,
e.numcpt,
e1.numcpt

I've searched the error, and I am even tired. I beg your help

I wanted to use the pivot table to display data in a worksheet Qlikview.

Thanks.