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

[former_member520086]

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

[former_member520086]

Hi Johan,

Thank you.

I Created new Query and it successfully run..

Thank you for giving your valuable time..

Regards,

Sapna

[Johan_Hakkesteegt]

Hi Sapna,

Glad to hear that you got it to work.

Please close this question by selecting "best answer" (below the answer).

Regards,

Johan

[former_member520086]

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

[Johan_Hakkesteegt]

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

[former_member520086]

Hi,

Is there any modifications in query..

[Johan_Hakkesteegt]

Hi,

The basic query has not been altered, only the line: (T0.DacDate between @FROM and @TO). The only other change is that I added this at the top:

/* select * from OINV x */
DECLARE @FROM AS DATETIME
DECLARE @TO AS DATETIME
SET @FROM= /* x.DocDate */ [%0]
SET @TO= /* x.DocDate */ [%1]

This syntax is needed if you want to use parameters in a query with embedded queries.

regards,

Johan

[former_member520086]

Hi,

I modify query and add your suggested code..IT returns me following error

1). [Microsoft][SQL Server Native Client 11.0][SQL Server]String or binary data would be truncated. 2). [Microsoft][SQL Server Native Client 11.0][SQL Server]The statement has been terminated. '' (SWEI)

reguards,

Sapna

[Johan_Hakkesteegt]

Hi Sapna,

Please try this:

/* 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 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

[former_member520086]

Hi Johan,

Still its not working...It gives same error...I modify the query as below and it also give an error

/* SELECT FROM [dbo].[OINV] T0 */

DECLARE @FromDate As datetime/* WHERE */SET @FromDate = /* T0.DocDate */ '[%0]'

DECLARE @ToDate As datetime/* WHERE */SET @ToDate = /* T0.DocDate */ '[%1]'

/----------------creating temporary table--------------------------

DECLARE @InvoiceSalesReport TABLE (DocDate datetime,DocNum int,DocEntry int,Date varchar(2), Month varchar(3), Year varchar(4), ItemCode nvarchar(50), ItmsGrpNam nvarchar(100), Colour nvarchar(100), Kit nvarchar(100), Dscription nvarchar(100), CardCode nvarchar(15), CardName nvarchar(100), City nvarchar(50), State nvarchar(50),Country nvarchar(50), GroupName nvarchar(100),SlpName nvarchar(100), Quantity numeric(19, 6), LineTotal numeric(19, 6), GrssProfit numeric(19, 6),GrossProfitPer numeric(12,6))

/-----------------------------inserting values in it----------------------------------------------------

INSERT INTO @InvoiceSalesReport (DocDate,DocNum,DocEntry, Date, Month, Year, ItemCode, ItmsGrpNam, Colour, Kit, Dscription, CardCode, CardName, City,State,Country,GroupName, SlpName,Quantity,LineTotal,GrssProfit, GrossProfitPer)

SELECT T0.DocDate,T0.DocNum,T0.DocEntry, (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.DocDate>=@FromDate and T0.DocDate<=@ToDate)

/------------------------------------executing it --------------------------------------------------

SELECT Date as 'Date', Month as 'Month', Year as 'Year', ItemCode as 'Item Code', ItmsGrpNam as 'Category', Colour as 'Sub Category-Colour', Kit as 'Sub Category-Kit', Dscription as 'Item Description', CardCode as 'Customer Code', CardName as 'Customer Name', City as 'City',State as 'State',Country as 'Country',GroupName as 'Customer Group', SlpName as 'Sales Employee',Quantity as 'Quantity',LineTotal as 'Sales Amount',GrssProfit as 'Gross Profit', GrossProfitPer as 'Gross Profit %'

FROM @InvoiceSalesReport

ORDER BY DocDate desc,DocNum desc,DocEntry desc

Above code gives me following error when select date before 01/01/2018

1). [Microsoft][SQL Server Native Client 11.0][SQL Server]String or binary data would be truncated. 2). [Microsoft][SQL Server Native Client 11.0][SQL Server]The statement has been terminated. '' (SWEI)

Otherwise it gives me result

Regards,

Sapna

[Johan_Hakkesteegt]

Hi Sapna,

Could you please try this version of the first query? I bet 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 VARCHAR)) as 'Date'
      ,DATENAME(MONTH, T0.[DocDate]) as 'Month'
  ,(CAST(YEAR(T0.[DocDate]) AS VARCHAR)) 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

[former_member520086]

Hi Johan,

I tried the query suggested by you..it gives me error.

1). [Microsoft][SQL Server Native Client 11.0]Invalid precision value '' (SWEI)

Regards,

Sapna

[Johan_Hakkesteegt]

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