Skip to Content
avatar image
Former Member

ERROR: [Microsoft][SQL Server Native Client 11.0]Invalid precision value '' (SWEI)

Hi,

When I am executing this below query it's showing error :

SELECT (CAST(DAY(T0.[DocDate]) AS VARCHAR(2))) as 'Date',(LEFT(DATENAME(MONTH, T0.[DocDate]), 3)) as 'Month',(CAST(YEAR(T0.[DocDate]) AS VARCHAR(4))) as 'Year', T1.[ItemCode] as 'Item Code',T2.ItmsGrpNam as 'Category',T2.U_Colour as 'Sub Category-Colour',T2.U_Kit as 'Sub Category-Kit',T1.Dscription as 'Item Description', T0.[CardCode] as 'Customer Code', T0.[CardName] as 'Customer Name',T3.City as 'City',T3.State as 'State',T3.Country as 'Country',T3.GroupName as 'Customer Group', T8.[SlpName] as 'Sales Employee',T1.[Quantity] as 'Quantity',T1.LineTotal as 'Sales Amount',T1.[GrssProfit] as 'Gross Profit', (convert(numeric(12,4),(T1.[GrssProfit] / CASE WHEN T1.[LineTotal] = 0 THEN 1 ELSE T1.[LineTotal] END) * 100)) AS 'Gross Profit %' FROM OINV T0 INNER JOIN INV1 T1 ON T0.[DocEntry] = T1.[DocEntry] left join (SELECT T2.ItemCode, T2.ItemName, T2.ItmsGrpCod, T3.ItmsGrpNam,T2.U_Colour,T2.U_Kit FROM dbo.OITM AS T2 INNER JOIN dbo.OITB AS T3 ON T2.ItmsGrpCod = T3.ItmsGrpCod) T2 on T1.[ItemCode]=T2.ItemCode inner join(Select DISTINCT T6.CardCode AS [Customer code], T6.CardName AS [Customer Name],T8.GroupName, T4.City, T5.Name AS State, T3.Name AS Country From dbo.OCST AS T5 INNER JOIN dbo.CRD1 AS T4 ON T5.Code = T4.State INNER JOIN dbo.OCRY AS T3 ON T5.Country = T3.Code AND T4.Country = T3.Code INNER JOIN dbo.OINV AS T6 ON T4.CardCode = T6.CardCode INNER JOIN dbo.OCRD AS T7 ON T7.CardName = T6.CardName AND T4.CardCode = T7.CardCode left join dbo.OCRG AS T8 ON T7.GroupCode=T8.GroupCode WHERE (T4.AdresType = 'S')) T3 on T3.[Customer code]=T0.[CardCode] left join OSLP T8 on T8.SlpCode=T0.SlpCode where T0.Series=243 and T1.U_FreeSample!='Y' and T1.TargetType!=14 and (T0.DacDate>=('[%0]') and T0.DacDate<=('[%1]')) order by T0.DocDate desc,T0.DocNum desc,T0.DocEntry desc


It gives me the result when I run query without (T0.DacDate>=('[%0]') and T0.DacDate<=('[%1]')) But i just want user input as date for it

How to solve this issue

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • avatar image
    Former Member
    Jan 30 at 05:24 AM

    Hi Johan,

    I tried above syntax bt it gives me following error

    1). [Microsoft][SQL Server Native Client 11.0][SQL Server]Must specify table to select from.

    2). [Microsoft][SQL Server Native Client 11.0][SQL Server]Statement '' (SWEI) (s) could not be prepared.

    This same error occurs when I run my query...

    I read almost all document related to this errors.But i cannot got it

    Regards,

    Sapna

    Add comment
    10|10000 characters needed characters exceeded

  • Jan 29 at 11:56 AM

    Hi,

    When you use embedded queries, you cannot use B1 query parameters in the usual way. Please try this syntax:

    /* select * from OINV x */
    DECLARE @FROM AS DATETIME
    DECLARE @TO AS DATETIME
    SET @FROM = /* x.DocDate */ [%0]
    SET @TO = /* x.DocDate */ [%1]
    SELECT (CAST(DAY(T0.[DocDate]) AS VARCHAR(2))) as 'Date'
          ,(LEFT(DATENAME(MONTH, T0.[DocDate]), 3)) as 'Month'
      ,(CAST(YEAR(T0.[DocDate]) AS VARCHAR(4))) as 'Year'
      , T1.[ItemCode] as 'Item Code'
      ,T2.ItmsGrpNam as 'Category'
      ,T2.U_Colour as 'Sub Category-Colour'
      ,T2.U_Kit as 'Sub Category-Kit'
      ,T1.Dscription as 'Item Description'
      , T0.[CardCode] as 'Customer Code'
      , T0.[CardName] as 'Customer Name'
      ,T3.City as 'City'
      ,T3.State as 'State'
      ,T3.Country as 'Country'
      ,T3.GroupName as 'Customer Group'
      , T8.[SlpName] as 'Sales Employee'
      ,T1.[Quantity] as 'Quantity'
      ,T1.LineTotal as 'Sales Amount'
      ,T1.[GrssProfit] as 'Gross Profit'
      , (convert(numeric(12,4),(T1.[GrssProfit] / CASE WHEN T1.[LineTotal] = 0 THEN 1 ELSE T1.[LineTotal] END) * 100)) AS 'Gross Profit %' 
    FROM OINV T0
         INNER JOIN INV1 T1 ON T0.[DocEntry] = T1.[DocEntry]
     left join (SELECT T2.ItemCode, T2.ItemName, T2.ItmsGrpCod, T3.ItmsGrpNam,T2.U_Colour,T2.U_Kit 
                FROM dbo.OITM AS T2
         INNER JOIN dbo.OITB AS T3 ON T2.ItmsGrpCod = T3.ItmsGrpCod) T2 on T1.[ItemCode]=T2.ItemCode
         inner join(Select DISTINCT T6.CardCode AS [Customer code], T6.CardName AS [Customer Name],T8.GroupName, T4.City, T5.Name AS State, T3.Name AS Country 
                From dbo.OCST AS T5
         INNER JOIN dbo.CRD1 AS T4 ON T5.Code = T4.State
         INNER JOIN dbo.OCRY AS T3 ON T5.Country = T3.Code AND T4.Country = T3.Code
     INNER JOIN dbo.OINV AS T6 ON T4.CardCode = T6.CardCode 
     INNER JOIN dbo.OCRD AS T7 ON T7.CardName = T6.CardName AND T4.CardCode = T7.CardCode 
     left join dbo.OCRG AS T8 ON T7.GroupCode=T8.GroupCode WHERE (T4.AdresType = 'S')) T3 on T3.[Customer code]=T0.[CardCode] 
         left join OSLP T8 on T8.SlpCode=T0.SlpCode
    where T0.Series=243
      and T1.U_FreeSample!='Y'
      and T1.TargetType!=14 
      and (T0.DacDate between @FROM and @TO) 
    order by T0.DocDate desc,T0.DocNum desc,T0.DocEntry desc

    Regards,

    Johan

    Add comment
    10|10000 characters needed characters exceeded

    • Hi Sapna,

      Could you please try this version of the query? Let's test if it has something to do with the part where you parse the date:

      /* select * from OINV x */
      DECLARE @FROM AS DATETIME
      DECLARE @TO AS DATETIME
      SET @FROM = /* x.DocDate */ [%0]
      SET @TO = /* x.DocDate */ [%1]
      SELECT (CAST(DAY(T0.[DocDate]) AS NVARCHAR)) as 'Date'
            ,DATENAME(MONTH, T0.[DocDate]) as 'Month'
        ,(CAST(YEAR(T0.[DocDate]) AS NVARCHAR)) as 'Year'
        /*, T1.[ItemCode] as 'Item Code'
        ,T2.ItmsGrpNam as 'Category'
        ,T2.U_Colour as 'Sub Category-Colour'
        ,T2.U_Kit as 'Sub Category-Kit'
        ,T1.Dscription as 'Item Description'
        , T0.[CardCode] as 'Customer Code'
        , T0.[CardName] as 'Customer Name'
        ,T3.City as 'City'
        ,T3.State as 'State'
        ,T3.Country as 'Country'
        ,T3.GroupName as 'Customer Group'
        , T8.[SlpName] as 'Sales Employee'
        ,T1.[Quantity] as 'Quantity'
        ,T1.LineTotal as 'Sales Amount'
        ,T1.[GrssProfit] as 'Gross Profit'
        , (convert(numeric(12,4),(T1.[GrssProfit] / CASE WHEN T1.[LineTotal] = 0 THEN 1 ELSE T1.[LineTotal] END) * 100)) AS 'Gross Profit %' */
      FROM OINV T0
      /*     INNER JOIN INV1 T1 ON T0.[DocEntry] = T1.[DocEntry]
           left join (SELECT T22.ItemCode, T22.ItemName, T22.ItmsGrpCod, T33.ItmsGrpNam,T22.U_Colour,T22.U_Kit 
                      FROM dbo.OITM AS T22
                           INNER JOIN dbo.OITB AS T33 ON T22.ItmsGrpCod = T33.ItmsGrpCod) T2 on T1.[ItemCode]=T2.ItemCode
           inner join(Select DISTINCT T66.CardCode AS [Customer code], T66.CardName AS [Customer Name],T88.GroupName, T44.City, T55.Name AS State, T333.Name AS Country 
                      From dbo.OCST AS T55
                           INNER JOIN dbo.CRD1 AS T44 ON T55.Code = T44.State
                           INNER JOIN dbo.OCRY AS T333 ON T55.Country = T333.Code AND T44.Country = T333.Code
                           INNER JOIN dbo.OINV AS T66 ON T44.CardCode = T66.CardCode 
                           INNER JOIN dbo.OCRD AS T77 ON T77.CardName = T66.CardName AND T44.CardCode = T77.CardCode 
                           left join dbo.OCRG AS T88 ON T77.GroupCode=T88.GroupCode 
                      WHERE (T44.AdresType = 'S')) T3 on T3.[Customer code]=T0.[CardCode] 
           left join OSLP T8 on T8.SlpCode=T0.SlpCode */
      where /* T0.Series=243
        and T1.U_FreeSample!='Y'
        and T1.TargetType!=14 
        and */(T0.DacDate between @FROM and @TO) 
      order by T0.DocDate desc,T0.DocNum desc,T0.DocEntry desc

      regards,

      Johan

  • avatar image
    Former Member
    Jan 31 at 11:51 AM

    Hi Johan,

    Thank you.

    I Created new Query and it successfully run..

    Thank you for giving your valuable time..

    Regards,

    Sapna

    Add comment
    10|10000 characters needed characters exceeded