Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
matfurrier
Contributor III
Contributor III

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
jonathandienst
Partner - Champion III
Partner - Champion III

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

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

Is your function working in SQL?

Vinay


flipside
Partner - Specialist II
Partner - Specialist II

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
Contributor III
Contributor III
Author

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.