Problem with SQL request

[OmarBenSalem]

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

[kaushiknsolanki]

Just a try.

Add SQL Keyword before Select statement and check.

Regards,

Kaushik Solanki

[PrashantSangle]

can you post error screen shot?

Regards,

[OmarBenSalem]

did not solve it:

[PrashantSangle]

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

Regards,

[PrashantSangle]

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,

[OmarBenSalem]

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

;

[sunny_talwar · Accepted solution]

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;