Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
I think you want to call SQL function in to Qlikview script
Is your function working in SQL?
Vinay
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
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.