7 Replies Latest reply: Oct 8, 2018 6:49 AM by Sunny Talwar RSS

    Problem with SQL request

    omar bensalem

      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

        • Re: Problem with SQL request
          Kaushik Solanki

          Just a try.

           

          Add SQL Keyword before Select statement and check.

           

          Regards,

          Kaushik Solanki

            • Re: Problem with SQL request
              omar bensalem

              did not solve it:

              Capture.PNG

                • Re: Problem with SQL request
                  Prashant Sangle

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

                   

                  Regards,

                    • Re: Problem with SQL request
                      Prashant Sangle

                      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,

                      • Re: Problem with SQL request
                        omar bensalem

                        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
                            Sunny Talwar

                            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;

                    • Re: Problem with SQL request
                      Prashant Sangle

                      can you post error screen shot?

                       

                      Regards,