Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
Partner
Partner

Problem with SQL request

Hi all ( stalwar1‌)

I have an issue with this request:

select t."ID_DEMANDE_TAMPON", t."DATE_CREATION",

d."ID_DEMANDE__C", d."CREATEDDATE",

c."NAME" as "NAMEC", c."CREATEDDATE",

p."ID_PA__C", p."NAME" as "NAMEP" ,p."CREATEDDATE", p."ID_PA__C", p."MODEPRODUCTION__C",

i."NUMERO_PRESTATION"

from "SOCLE"."DEMANDE_TAMPON" t, "REPSFDC"."SF_PRESTATIONACTIVE" p, "REPSFDC"."SF_CONSOMMATION" c, "REPSFDC"."SF_DEMANDE" d, "SOCLE"."INSCRIPTION_ATELIER" i

where

d."NUMERO_DE_LA_DEMANDE__C" (+)=t."ID_DEMANDE_TAMPON"

and c."DEMANDE__C" (+)=d."ID_DEMANDE__C"

and p."IDCONSOMMATION__C" (+)=c."IDCONSOMMATION__C"

and i."NUMERO_PRESTATION" (+)= p."ID_PA__C"

and trunc(t."DATE_CREATION")>=trunc(sysdate-1)

and (d."NUMERO_DE_LA_DEMANDE__C" is null or c."DEMANDE__C" is null or p."IDCONSOMMATION__C" is null)

order by 2

;

It works just fine when I execut it with ORACLE SQL developer but does not with QLIK.

Any idea why?

Is it a syntax thing? If yes, do u know what should be changed so that this works with Qlik?

Thanks

1 Solution

Accepted Solutions

Re: Problem with SQL request

Try this....

SELECT  t."ID_DEMANDE_TAMPON",

          t."DATE_CREATION",

          d."ID_DEMANDE__C",

          d."CREATEDDATE" AS "CREATEDDATE_D",

          c."NAME" AS "NAMEC",

          c."CREATEDDATE" AS "CREATEDDATE_C",

          p."ID_PA__C",

          p."NAME" AS "NAMEP",

          p."CREATEDDATE" AS "CREATEDDATE_P",

          p."MODEPRODUCTION__C",

          i."NUMERO_PRESTATION"

    FROM  "SOCLE"."DEMANDE_TAMPON" t,

          "REPSFDC"."SF_PRESTATIONACTIVE" p,

          "REPSFDC"."SF_CONSOMMATION" c,

          "REPSFDC"."SF_DEMANDE" d,

          "SOCLE"."INSCRIPTION_ATELIER" i

  WHERE      d."NUMERO_DE_LA_DEMANDE__C"(+) = t."ID_DEMANDE_TAMPON"

          AND c."DEMANDE__C"(+) = d."ID_DEMANDE__C"

          AND p."IDCONSOMMATION__C"(+) = c."IDCONSOMMATION__C"

          AND i."NUMERO_PRESTATION"(+) = p."ID_PA__C"

          AND TRUNC (t."DATE_CREATION") >= TRUNC (SYSDATE - 1)

          AND (  d."NUMERO_DE_LA_DEMANDE__C" IS NULL

                OR c."DEMANDE__C" IS NULL

                OR p."IDCONSOMMATION__C" IS NULL)

ORDER BY  2;

View solution in original post

7 Replies

Re: Problem with SQL request

Just a try.

Add SQL Keyword before Select statement and check.

Regards,

Kaushik Solanki

Re: Problem with SQL request

can you post error screen shot?

Regards,

Great dreamer's dreams never fulfilled, they are always transcended.
Partner
Partner

Re: Problem with SQL request

did not solve it:

Capture.PNG

Re: Problem with SQL request

Well you can see p."ID_PA__C" this field repeated twice in your query.

Regards,

Great dreamer's dreams never fulfilled, they are always transcended.

Re: Problem with SQL request

When you run in Oracle SQL developer, Oracle SQL will take care of duplicate name. it will automatically rename it by appending numeric no. like

So when you run your query in oracle sql the output will consist 2 column 1 as 

p.ID_PA__C and 2nd as p.ID_PA__C_1


Regards,

Great dreamer's dreams never fulfilled, they are always transcended.
Partner
Partner

Re: Problem with SQL request

well spotted !!

But even when I delete one of the 2, I still have the same error :

new request :

sql select t."ID_DEMANDE_TAMPON", t."DATE_CREATION",

d."ID_DEMANDE__C", d."CREATEDDATE",

c."NAME" as "NAMEC", c."CREATEDDATE",

p."ID_PA__C", p."NAME" as "NAMEP" ,p."CREATEDDATE", p."MODEPRODUCTION__C",

i."NUMERO_PRESTATION"

from "SOCLE"."DEMANDE_TAMPON" t, "REPSFDC"."SF_PRESTATIONACTIVE" p, "REPSFDC"."SF_CONSOMMATION" c, "REPSFDC"."SF_DEMANDE" d, "SOCLE"."INSCRIPTION_ATELIER" i

where

d."NUMERO_DE_LA_DEMANDE__C" (+)=t."ID_DEMANDE_TAMPON"

and c."DEMANDE__C" (+)=d."ID_DEMANDE__C"

and p."IDCONSOMMATION__C" (+)=c."IDCONSOMMATION__C"

and i."NUMERO_PRESTATION" (+)= p."ID_PA__C"

and trunc(t."DATE_CREATION")>=trunc(sysdate-1)

and (d."NUMERO_DE_LA_DEMANDE__C" is null or c."DEMANDE__C" is null or p."IDCONSOMMATION__C" is null)

order by 2

;

Capture.PNG

Re: Problem with SQL request

Try this....

SELECT  t."ID_DEMANDE_TAMPON",

          t."DATE_CREATION",

          d."ID_DEMANDE__C",

          d."CREATEDDATE" AS "CREATEDDATE_D",

          c."NAME" AS "NAMEC",

          c."CREATEDDATE" AS "CREATEDDATE_C",

          p."ID_PA__C",

          p."NAME" AS "NAMEP",

          p."CREATEDDATE" AS "CREATEDDATE_P",

          p."MODEPRODUCTION__C",

          i."NUMERO_PRESTATION"

    FROM  "SOCLE"."DEMANDE_TAMPON" t,

          "REPSFDC"."SF_PRESTATIONACTIVE" p,

          "REPSFDC"."SF_CONSOMMATION" c,

          "REPSFDC"."SF_DEMANDE" d,

          "SOCLE"."INSCRIPTION_ATELIER" i

  WHERE      d."NUMERO_DE_LA_DEMANDE__C"(+) = t."ID_DEMANDE_TAMPON"

          AND c."DEMANDE__C"(+) = d."ID_DEMANDE__C"

          AND p."IDCONSOMMATION__C"(+) = c."IDCONSOMMATION__C"

          AND i."NUMERO_PRESTATION"(+) = p."ID_PA__C"

          AND TRUNC (t."DATE_CREATION") >= TRUNC (SYSDATE - 1)

          AND (  d."NUMERO_DE_LA_DEMANDE__C" IS NULL

                OR c."DEMANDE__C" IS NULL

                OR p."IDCONSOMMATION__C" IS NULL)

ORDER BY  2;

View solution in original post