SQL script in Qlikview

[mohan2391]

Hi,

I got the following script from 1 of the existed qvw.

But when I run the same script in my Qlikview desktop, it is throwing error msgs as TRIM, CASE not existed functions.

My question is how they are using this file and why I can't ?

/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

$(Include=..\..\..\Include\WW\QuoteTAT\vertica.qvs);

Details:

Load *;

SQL

WITH PL_Mapp AS (select distinct PROD_MLTGRN_CD, PROD_LN_ID, GBL_BUS_UNIT_DN, BUS_GRP_DN from egig_App_Sales_Pipeline_A.PROD_MLTGRN_D_V)

SELECT      

        TRIM(fsqd.Sales_Quote_Id) as "Quote ID",

        TRIM(fsqd.Product_Line_Id) as "Product Line",   

       floor( SUM(fsqd.Sales_Quote_Detail_Standard_Price_USD_Amount ) )as "Standard price USD",

  floor(SUM(fsqd.Sales_Quote_Detail_Authorized_Price_USD_Amount)) as "Auth Price USD",

  floor(SUM(fsqd.Sales_Quote_Detail_Margin_USD_Amount)) as "Margin USD",    

        CASE (fsqd.Sales_Quote_Source_System_Name)

                WHEN 'ICC' THEN COALESCE(NULLIF(TRIM(pmdv.BUS_GRP_DN),''), 'Not Mapped')

                WHEN 'DirectPlus' THEN COALESCE(NULLIF(TRIM(pmdv.BUS_GRP_DN),''), 'Not Mapped')

                WHEN 'Manual Input' THEN COALESCE(NULLIF(TRIM(pmdv.BUS_GRP_DN),''), 'Not Mapped')

                WHEN 'DealReg' THEN COALESCE(NULLIF(TRIM(pmdv.BUS_GRP_DN),''), 'Not Mapped')

                WHEN 'SFDC' THEN COALESCE(NULLIF(TRIM(pmdv.BUS_GRP_DN),''), 'Not Mapped')

                ELSE COALESCE(NULLIF(TRIM(pmdv.GBL_BUS_UNIT_DN),''), 'Not Mapped')

        END as "GBU",      

        TRIM(fsqd.Sales_Quote_Deal_Size_Name) as "Deal Size bucket",

        CASE WHEN fsqd.Sales_Quote_Source_System_Name NOT IN ('Eclipse','Manual Input')

  THEN CASE WHEN EXISTS

  (SELECT Sales_Quote_Id FROM egig_App_Quote_Effectiveness.DIM_Sales_Quote WHERE Sales_Quote_Id=fsqd.Sales_Quote_Id

  AND Sales_Quote_Include_Indicator='Y') THEN 'Y'  ELSE 'N' END

  ELSE TRIM(fsqd.Sales_Quote_Include_Indicator) END as "Include Flag"       

FROM egig_App_Quote_Effectiveness.Fact_Sales_Quote_Detail fsqd 

        left join PL_Mapp pmdv ON fsqd.Product_Line_Id=pmdv.PROD_MLTGRN_CD

  WHERE   EXISTS (SELECT * FROM egig_App_Quote_Effectiveness.Fact_Sales_Quote WHERE Sales_Quote_Id=fsqd.Sales_Quote_Id

  AND Sales_Quote_Approved_Timestamp >= trunc(add_months(SYSDATE,-13),'MM') )

  GROUP BY "Quote ID","GBU","Deal Size Bucket",pmdv.BUS_GRP_DN,"Include Flag","Product Line";

[tresesco]

Perhaps you are not using the same database. And the fact could be their db supports those functions and yours doesn't.

[sushil353]

Hi,

Are you using the same database/connection string?

also try to run the sql script using some SQL Developer first.

HTH

Sushil

[mohan2391]

Hi Amit & Sushil

Thanks for quick reply.

Yes, I'm not using their database.

So, as per you if i get access to that database and then if i reload the qvw, it won't throw me error right ?

they also included a file on top with include statement, so do i need to collect that file also ?

[tresesco]

Yes, the included file is a script file. You should get that file too and place on your system at the proper path.

[sharuta]

Hi.

Try IT

Create first on SQL DB view with your script

Then: select  * from "your_view_name" in QVw

In this case Time requery  - minimum