4 Replies Latest reply: Mar 28, 2014 9:28 AM by Mateus Furrier RSS

    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
      
        • Re: Error in Function SQL
          Jonathan Dienst

          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

          • Re: Error in Function SQL
            Vinay Naran

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

            Is your function working in SQL?

             

            Vinay

             

             

             

             

             

             


            • Re: Error in Function SQL
              Dave Riley

              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

                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.