Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
matfurrier
New Contributor II

Error in Function SQL

friends,

I'm having trouble using a function created in sql.

Can anyone help me please?

SQL##f - SqlState: 37000, ErrorCode: 4121, ErrorMsg: [Microsoft][SQL Server Native Client 10.0][SQL Server]Cannot find either column "dbo" or the user-defined function or aggregate "dbo.fndiasuteis", or the name is ambiguous.

SELECT DISTINCT

CASE IT.IMPORTERID  WHEN '0000001' THEN '000048'

     WHEN '0000002' THEN '000160'

     WHEN '0000003' THEN '009052'

     ELSE

(SELECT I.PRIMARYVENDORID

FROM AX501_SRL_PROD2.dbo.INVENTTABLE I

WHERE I.ITEMID = F2.CODPROD) END

CODFORN,

CASE IT.IMPORTERID WHEN '0000001' THEN 'SERILON BRASIL LTDA CD'

    WHEN '0000002' THEN 'SERILON BRASIL LTDA MANAUS'

    WHEN '0000003' THEN 'SERILON BRASIL LTDA LDB SERVIC'

    ELSE

(SELECT  V.NAME FROM AX501_SRL_PROD2.dbo.VENDTABLE V

WHERE V.ACCOUNTNUM = (SELECT I.PRIMARYVENDORID

FROM AX501_SRL_PROD2.dbo.INVENTTABLE I

WHERE I.ITEMID = F2.CODPROD))END NOMEFORN,

F2.CODPROD,

(SELECT I.ITEMNAME

FROM AX501_SRL_PROD2.dbo.INVENTTABLE I

WHERE I.ITEMID = F2.CODPROD)NOMEPROD,

F2.UNIDADE, F2.SITE,

ISNULL((SELECT TOP 1 E.TRANSITO FROM AX501_SRL_PROD2.dbo.ESTTRANSITO E WHERE E.CODITEM = F2.CODPROD AND E.FINALIDADE = F2.SITE),0) TRANSITO,

ISNULL((SELECT TOP 1 T.QTDESTQ FROM AX501_SRL_PROD2.dbo.TMPESTOQUE T

WHERE T.CODPROD = F2.CODPROD AND T.EMPRESA = F2.EMPRESA),0) ESTOQUE,

F2.GIRO1,

F2.GIRO2, F2.GIRO3, F2.GIRO4, F2.GIRO5, F2.GIRO6, F2.GIRO7,

F2.GIRO8, F2.GIRO9, F2.GIRO10, F2.GIRO11, F2.GIRO12,

F2.DIAS, F2.TOTALGIRO * (ISNULL((SELECT TOP 1 U.FACTOR FROM AX501_SRL_PROD2.dbo.UNITCONVERT U WHERE U.ITEMID = F2.CODPROD),1)) GIROTOTAL,

CASE DATEPART(mm,Getdate()) WHEN '01' THEN

((SELECT diasuteis = dbo.fndiasuteis( GETDATE(), (SELECT GETDATE() - DAY(GETDATE()) + 1))*-1) +

  (SELECT DIAS FROM AX501_SRL_PROD2.dbo.DIASUTEIS D WHERE D.MES = '12'AND D.ANO = DATEPART(YY,Getdate())-1)+

  (SELECT DIAS FROM AX501_SRL_PROD2.dbo.DIASUTEIS D WHERE D.MES = '11'AND D.ANO = DATEPART(YY,Getdate())-1)+

  (SELECT DIAS FROM AX501_SRL_PROD2.dbo.DIASUTEIS D WHERE D.MES = '10'AND D.ANO = DATEPART(YY,Getdate())-1))

  WHEN '02' THEN

  ((SELECT diasuteis = dbo.fndiasuteis( GETDATE(), (SELECT GETDATE() - DAY(GETDATE()) + 1))*-1) +

  (SELECT DIAS FROM AX501_SRL_PROD2.dbo.DIASUTEIS D WHERE D.MES = DATEPART(mm,Getdate())-1 AND D.ANO = DATEPART(YY,Getdate())) +

  (SELECT DIAS FROM AX501_SRL_PROD2.dbo.DIASUTEIS D WHERE D.MES = '12'AND D.ANO = DATEPART(YY,Getdate())-1)+

  (SELECT DIAS FROM AX501_SRL_PROD2.dbo.DIASUTEIS D WHERE D.MES = '11'AND D.ANO = DATEPART(YY,Getdate())-1))

  WHEN '03' THEN

  ((SELECT diasuteis = dbo.fndiasuteis( GETDATE(), (SELECT GETDATE() - DAY(GETDATE()) + 1))*-1) +

  (SELECT DIAS FROM AX501_SRL_PROD2.dbo.DIASUTEIS D WHERE D.MES = DATEPART(mm,Getdate())-1AND D.ANO = DATEPART(YY,Getdate()))  +

  (SELECT DIAS FROM AX501_SRL_PROD2.dbo.DIASUTEIS D WHERE D.MES = DATEPART(mm,Getdate())-2 AND D.ANO = DATEPART(YY,Getdate()))  +

  (SELECT DIAS FROM AX501_SRL_PROD2.dbo.DIASUTEIS D WHERE D.MES = '12'AND D.ANO = DATEPART(YY,Getdate())-1))

  ELSE 

((SELECT diasuteis = dbo.fndiasuteis( GETDATE(), (SELECT GETDATE() - DAY(GETDATE())))*-1) +

(SELECT DIAS FROM AX501_SRL_PROD2.dbo.DIASUTEIS D WHERE D.MES = DATEPART(mm,Getdate())-1 AND D.ANO = DATEPART(YY,Getdate()))  +

  (SELECT DIAS FROM AX501_SRL_PROD2.dbo.DIASUTEIS D WHERE D.MES = DATEPART(mm,Getdate())-2 AND D.ANO = DATEPART(YY,Getdate()))  +

  (SELECT DIAS FROM AX501_SRL_PROD2.dbo.DIASUTEIS D WHERE D.MES = DATEPART(mm,Getdate())-3 AND D.ANO = DATEPART(YY,Getdate()))  ) END DIASUTEIS3M,

ISNULL((SELECT top 1

CASE I.ITEMABC

  WHEN '1' THEN 'A'

  WHEN '2' THEN 'B'

  WHEN '3' THEN 'C'

  ELSE 'C' END

FROM AX501_SRL_PROD2.dbo.INVENTITEMSITECLASSIFICAT30055 I WHERE I.ITEMID = F2.CODPROD AND I.INVENTSITEID = F2.INVENTSITE),'C') ABC,

  ISNULL((SELECT top 1

  CASE I.ITEMABC

  WHEN '1' THEN '7'

  WHEN '2' THEN '15'

  WHEN '3' THEN '30'

  ELSE '30'

  END

  FROM AX501_SRL_PROD2.dbo.INVENTITEMSITECLASSIFICAT30055 I WHERE I.ITEMID = F2.CODPROD AND I.INVENTSITEID = F2.INVENTSITE),'30')DIAS_ABC,

ISNULL((SELECT top 1 V.LEADTIME FROM AX501_SRL_PROD2.dbo.VENDTABLE V WHERE  V.ACCOUNTNUM = (SELECT I.PRIMARYVENDORID FROM AX501_SRL_PROD2.dbo.INVENTTABLE I WHERE I.ITEMID = F2.CODPROD)),'0')LEADTIME,

CASE DATEPART(mm,Getdate()) WHEN '1' THEN

  (ISNULL((select SUM(U.quantidade) from AX501_SRL_PROD2.dbo.user_vendapura U

   where  MONTH(CONVERT(DATETIME,U.DATAEMISSAO,103)) = '12' and YEAR(CONVERT(DATETIME,U.DATAEMISSAO,103)) = DATEPART(YY,Getdate())-1 and U.CODALT = F2.CODPROD AND U.EMPRESA = F2.EMPRESA),0) +

   ISNULL((select SUM(U.quantidade) from AX501_SRL_PROD2.dbo.user_vendapura U

   where MONTH(CONVERT(DATETIME,U.DATAEMISSAO,103)) = '11' and YEAR(CONVERT(DATETIME,U.DATAEMISSAO,103)) = DATEPART(YY,Getdate())-1 and U.CODALT = F2.CODPROD AND U.EMPRESA = F2.EMPRESA),0) +

   ISNULL((select SUM(U.quantidade) from AX501_SRL_PROD2.dbo.user_vendapura U

   where MONTH(CONVERT(DATETIME,U.DATAEMISSAO,103)) = '10' and YEAR(CONVERT(DATETIME,U.DATAEMISSAO,103)) = DATEPART(YY,Getdate())-1 and U.CODALT = F2.CODPROD AND U.EMPRESA = F2.EMPRESA),0) +

   ISNULL((select SUM(U.quantidade) from AX501_SRL_PROD2.dbo.user_vendapura U

   where MONTH(CONVERT(DATETIME,U.DATAEMISSAO,103)) = DATEPART(mm,Getdate()) and YEAR(CONVERT(DATETIME,U.DATAEMISSAO,103)) = DATEPART(YY,Getdate())     and U.CODALT = F2.CODPROD AND U.EMPRESA = F2.EMPRESA),0))

   WHEN '2' THEN

   (ISNULL((select SUM(U.quantidade) from AX501_SRL_PROD2.dbo.user_vendapura U

   where  MONTH(CONVERT(DATETIME,U.DATAEMISSAO,103)) = '12' and YEAR(CONVERT(DATETIME,U.DATAEMISSAO,103)) = DATEPART(YY,Getdate())-1 and U.CODALT = F2.CODPROD AND U.EMPRESA = F2.EMPRESA),0) +

   ISNULL((select SUM(U.quantidade) from AX501_SRL_PROD2.dbo.user_vendapura U

   where MONTH(CONVERT(DATETIME,U.DATAEMISSAO,103)) = '11' and YEAR(CONVERT(DATETIME,U.DATAEMISSAO,103)) = DATEPART(YY,Getdate())-1 and U.CODALT = F2.CODPROD AND U.EMPRESA = F2.EMPRESA),0) +

   (ISNULL((select SUM(U.quantidade) from AX501_SRL_PROD2.dbo.user_vendapura U

   where  MONTH(CONVERT(DATETIME,U.DATAEMISSAO,103)) = DATEPART(mm,Getdate()) - 1 and YEAR(CONVERT(DATETIME,U.DATAEMISSAO,103)) = DATEPART(YY,Getdate()) and U.CODALT = F2.CODPROD AND U.EMPRESA = F2.EMPRESA),0) +

   ISNULL((select SUM(U.quantidade) from AX501_SRL_PROD2.dbo.user_vendapura U

   where MONTH(CONVERT(DATETIME,U.DATAEMISSAO,103)) = DATEPART(mm,Getdate()) and YEAR(CONVERT(DATETIME,U.DATAEMISSAO,103)) = DATEPART(YY,Getdate())     and U.CODALT = F2.CODPROD AND U.EMPRESA = F2.EMPRESA),0)))

   WHEN '3' THEN

   (ISNULL((select SUM(U.quantidade) from AX501_SRL_PROD2.dbo.user_vendapura U

   where  MONTH(CONVERT(DATETIME,U.DATAEMISSAO,103)) = '12' and YEAR(CONVERT(DATETIME,U.DATAEMISSAO,103)) = DATEPART(YY,Getdate())-1 and U.CODALT = F2.CODPROD AND U.EMPRESA = F2.EMPRESA),0) +

   (ISNULL((select SUM(U.quantidade) from AX501_SRL_PROD2.dbo.user_vendapura U

   where  MONTH(CONVERT(DATETIME,U.DATAEMISSAO,103)) = DATEPART(mm,Getdate()) - 2 and YEAR(CONVERT(DATETIME,U.DATAEMISSAO,103)) = DATEPART(YY,Getdate()) and U.CODALT = F2.CODPROD AND U.EMPRESA = F2.EMPRESA),0) +

   (ISNULL((select SUM(U.quantidade) from AX501_SRL_PROD2.dbo.user_vendapura U

   where  MONTH(CONVERT(DATETIME,U.DATAEMISSAO,103)) = DATEPART(mm,Getdate()) - 1 and YEAR(CONVERT(DATETIME,U.DATAEMISSAO,103)) = DATEPART(YY,Getdate()) and U.CODALT = F2.CODPROD AND U.EMPRESA = F2.EMPRESA),0) +

   ISNULL((select SUM(U.quantidade) from user_vendapura U

   where MONTH(CONVERT(DATETIME,U.DATAEMISSAO,103)) = DATEPART(mm,Getdate()) and YEAR(CONVERT(DATETIME,U.DATAEMISSAO,103)) = DATEPART(YY,Getdate())  and U.CODALT = F2.CODPROD AND U.EMPRESA = F2.EMPRESA),0))))

   ELSE

     (ISNULL((select SUM(U.quantidade) from AX501_SRL_PROD2.dbo.user_vendapura U

   where  MONTH(CONVERT(DATETIME,U.DATAEMISSAO,103)) = DATEPART(mm,Getdate()) - 1 and U.CODALT = F2.CODPROD AND U.EMPRESA = F2.EMPRESA and YEAR(CONVERT(DATETIME,U.DATAEMISSAO,103)) = DATEPART(YY,Getdate())),0) +

   ISNULL((select SUM(U.quantidade) from AX501_SRL_PROD2.dbo.user_vendapura U

   where MONTH(CONVERT(DATETIME,U.DATAEMISSAO,103)) = DATEPART(mm,Getdate()) - 2  and U.CODALT = F2.CODPROD AND U.EMPRESA = F2.EMPRESA and YEAR(CONVERT(DATETIME,U.DATAEMISSAO,103)) = DATEPART(YY,Getdate())),0) +

   ISNULL((select SUM(U.quantidade) from AX501_SRL_PROD2.dbo.user_vendapura U

   where MONTH(CONVERT(DATETIME,U.DATAEMISSAO,103)) = DATEPART(mm,Getdate()) - 3  and U.CODALT = F2.CODPROD AND U.EMPRESA = F2.EMPRESA and YEAR(CONVERT(DATETIME,U.DATAEMISSAO,103)) = DATEPART(YY,Getdate())),0) +

   ISNULL((select SUM(U.quantidade) from AX501_SRL_PROD2.dbo.user_vendapura U

   where MONTH(CONVERT(DATETIME,U.DATAEMISSAO,103)) = DATEPART(mm,Getdate())      and U.CODALT = F2.CODPROD AND U.EMPRESA = F2.EMPRESA and YEAR(CONVERT(DATETIME,U.DATAEMISSAO,103)) = DATEPART(YY,Getdate())),0) )END   GIRO3MATUAL,   

(SELECT top 1 convert(decimal(10,2),SQRT(ISNULL((SELECT top 1 V.LEADTIME FROM AX501_SRL_PROD2.dbo.VENDTABLE V WHERE  V.ACCOUNTNUM = (SELECT I.PRIMARYVENDORID FROM AX501_SRL_PROD2.dbo.INVENTTABLE I WHERE I.ITEMID = F2.CODPROD)),'0')))) RAIZLEADTIME ,

F2.ANO,

ISNULL((SELECT top 1

  CASE I.ITEMABC

  WHEN '1' THEN '1,60'

  WHEN '2' THEN '2,10'

  WHEN '3' THEN '2,60'

  ELSE '2,60'

  END

  FROM AX501_SRL_PROD2.dbo.INVENTITEMSITECLASSIFICAT30055 I WHERE I.ITEMID = F2.CODPROD AND I.INVENTSITEID = F2.INVENTSITE),'2,60')FATOR

FROM

AX501_SRL_PROD2.dbo.TMPFATANO F2, AX501_SRL_PROD2.dbo.INVENTTABLE IT

WHERE

F2.CODPROD = IT.ITEMID AND

EXISTS

(SELECT TOP 1 1

FROM AX501_SRL_PROD2.dbo.INVENTTABLE II WHERE

II.ITEMID = F2.CODPROD AND

II.FAMILYID0 NOT IN ('17', '19', '20')) AND F2.ANO NOT IN ( '0', '2012', '2013')

AND

NOT EXISTS

(SELECT TOP 1 1

FROM AX501_SRL_PROD2.dbo.INVENTTABLE I

WHERE I.ITEMID = F2.CODPROD AND I.PRIMARYVENDORID = '')

GROUP BY F2.CODPROD, F2.UNIDADE, F2.SITE,  F2.GIRO1, F2.GIRO2, F2.GIRO3, F2.GIRO4, F2.GIRO5, F2.GIRO6, F2.GIRO7, IT.IMPORTERID,

F2.GIRO8, F2.GIRO9, F2.GIRO10, F2.GIRO11, F2.GIRO12, F2.DIAS, F2.TOTALGIRO, F2.CODPROD, F2.EMPRESA, F2.INVENTSITE, F2.ANO

4 Replies
MVP
MVP

Re: Error in Function SQL

Hi

With such a complex SELECT statement, its going to be very hard to see what's wrong. The error suggests that the problem may lie with dbo.fndiasuteis - this is what I would look into at first.

Next I would break this SELECT into components and test each component separately. Once you have debugged and tested each of the components, you can then start to rebuild the query. That will be much easier than trying wrestle with such a complex statement - how would you ever know that you have not unintentionally changed the result?

HTH

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable

Re: Error in Function SQL

I think you want to call SQL function in to  Qlikview script

Is your function working in SQL?

Vinay


flipside
Valued Contributor II

Re: Error in Function SQL

That's a lot of code there! I'm assuming it works SQL side and is failing when you try calling it through QV load script. Is this right?

Could it possibly be a permissions issue? Does the account running the script have the right to use the function? Or it might not like the sub-selects.

Your error also states it cannot find column 'dbo' - is there a script error?

Have you also tried moving this code to a stored proc and calling that?

flipside


matfurrier
New Contributor II

Re: Error in Function SQL

friends,

I made a rookie mistake.

To verify in detail the script, the lines where I used a function, which was not mentioned the instance of the database (AX501_SRL_PROD2).

I thank you all.

Thank you very much.

Hugs.

Community Browser