Error in Function SQL

matfurrier — Fri, 28 Mar 2014 12:41:53 GMT

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

Re: Error in Function SQL

jonathandienst — Fri, 28 Mar 2014 12:52:13 GMT

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

Re: Error in Function SQL

Fri, 28 Mar 2014 12:52:40 GMT

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

Is your function working in SQL?

Vinay

Re: Error in Function SQL

flipside — Fri, 28 Mar 2014 12:59:04 GMT

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

Re: Error in Function SQL

matfurrier — Fri, 28 Mar 2014 13:28:28 GMT

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.

topic Re: Error in Function SQL in QlikView

Error in Function SQL

Re: Error in Function SQL

Re: Error in Function SQL

Re: Error in Function SQL

Re: Error in Function SQL