July 18, 2018 at 8:46 am
Hi All,
I'm not good at SQL at all (self-taught), and could use your help. I have the following query and I would like to replace the numeric value T7.SlpCode (yellow highlighted) with alphanumeric Value T9.SlpName. However, I get a conversion error, that I don't know how to correct "Coversion failed when converting the nvarchar value 'Name' to date type int. I would appreciate any input. I've tried CONVERT and CAST, but failed...
thanks so much
SELECT distinct T0.[TransId] as 'Num', T0.[Ref1], T0.[RefDate],
(CASE WHEN T0.[TransType] = 13 THEN T3.[CardCode] ELSE T5.[CardCode] END) as 'BP Code',
(CASE WHEN T0.[TransType] = 13 THEN T3.[CardName] ELSE T5.[CardName] END) as 'BP Name',
T2.[Segment_0], T2.[AcctName], T1.[Debit]*-1+ T1.[Credit] as 'Line Total',
(CASE WHEN T0.[TransType] = 13 THEN T4.[StateB] ELSE T6.[StateB] END) as 'Bill-To State',
(CASE WHEN T0.[TransType] = 13 THEN T7.[SlpCode] ELSE T8.[SlpCode] END) as 'Bill-To Slp',
(CASE WHEN T0.[TransType] = 13 THEN T4.[StateS] ELSE T6.[StateS] END) as 'Ship-To State',
(CASE WHEN T0.[TransType] = 13 THEN T7.[U_LBSISlpName] ELSE T8.[U_LBSISlpName] END) as 'Ship-To Slp' FROM OJDT T0
INNER JOIN JDT1 T1 ON T0.[TransId] = T1.[TransId]
INNER JOIN OACT T2 ON T1.[Account] = T2.[AcctCode]
left JOIN OINV T3 ON T0.[TransId] = T3.[TransId]
left JOIN INV12 T4 ON T3.[DocEntry] = T4.[DocEntry]
left JOIN INV1 T7 ON T3.[DocEntry] = T7.[DocEntry]
left JOIN ORIN T5 ON T0.[TransId] = T5.[TransId]
left JOIN RIN12 T6 ON T5.[DocEntry] = T6.[DocEntry]
left JOIN RIN1 T8 ON T5.[DocEntry] = T8.[DocEntry]
left JOIN OSLP T9 ON T3.[SlpCode] = T9.[SlpCode]
WHERE T0.[RefDate] between [%0] and [%1] and
T2.[Segment_0] between '40000' and '49999' and T1.[ContraAct] <> '_SYS0000000000830' and T0.[TransType] <>-3
July 18, 2018 at 8:54 am
dinaalesin - Wednesday, July 18, 2018 8:46 AMHi All,I'm not good at SQL at all (self-taught), and could use your help. I have the following query and I would like to replace the numeric value T7.SlpCode (yellow highlighted) with alphanumeric Value T9.SlpName. However, I get a conversion error, that I don't know how to correct "Coversion failed when converting the nvarchar value 'Name' to date type int. I would appreciate any input. I've tried CONVERT and CAST, but failed...
thanks so muchSELECT distinct T0.[TransId] as 'Num', T0.[Ref1], T0.[RefDate],
(CASE WHEN T0.[TransType] = 13 THEN T3.[CardCode] ELSE T5.[CardCode] END) as 'BP Code',
(CASE WHEN T0.[TransType] = 13 THEN T3.[CardName] ELSE T5.[CardName] END) as 'BP Name',
T2.[Segment_0], T2.[AcctName], T1.[Debit]*-1+ T1.[Credit] as 'Line Total',
(CASE WHEN T0.[TransType] = 13 THEN T4.[StateB] ELSE T6.[StateB] END) as 'Bill-To State',
(CASE WHEN T0.[TransType] = 13 THEN T7.[SlpCode] ELSE T8.[SlpCode] END) as 'Bill-To Slp',
(CASE WHEN T0.[TransType] = 13 THEN T4.[StateS] ELSE T6.[StateS] END) as 'Ship-To State',
(CASE WHEN T0.[TransType] = 13 THEN T7.[U_LBSISlpName] ELSE T8.[U_LBSISlpName] END) as 'Ship-To Slp' FROM OJDT T0
INNER JOIN JDT1 T1 ON T0.[TransId] = T1.[TransId]
INNER JOIN OACT T2 ON T1.[Account] = T2.[AcctCode]
left JOIN OINV T3 ON T0.[TransId] = T3.[TransId]
left JOIN INV12 T4 ON T3.[DocEntry] = T4.[DocEntry]
left JOIN INV1 T7 ON T3.[DocEntry] = T7.[DocEntry]
left JOIN ORIN T5 ON T0.[TransId] = T5.[TransId]
left JOIN RIN12 T6 ON T5.[DocEntry] = T6.[DocEntry]
left JOIN RIN1 T8 ON T5.[DocEntry] = T8.[DocEntry]
left JOIN OSLP T9 ON T3.[SlpCode] = T9.[SlpCode]
WHERE T0.[RefDate] between [%0] and [%1] and
T2.[Segment_0] between '40000' and '49999' and T1.[ContraAct] <> '_SYS0000000000830' and T0.[TransType] <>-3
Would help if posted the DDL (CREATE TABLE statement) for the tables. Would also help if you formatted your code to make it human readable.
July 18, 2018 at 8:54 am
dinaalesin - Wednesday, July 18, 2018 8:46 AMHi All,I'm not good at SQL at all (self-taught), and could use your help. I have the following query and I would like to replace the numeric value T7.SlpCode (yellow highlighted) with alphanumeric Value T9.SlpName. However, I get a conversion error, that I don't know how to correct "Coversion failed when converting the nvarchar value 'Name' to date type int. I would appreciate any input. I've tried CONVERT and CAST, but failed...
thanks so muchSELECT distinct T0.[TransId] as 'Num', T0.[Ref1], T0.[RefDate],
(CASE WHEN T0.[TransType] = 13 THEN T3.[CardCode] ELSE T5.[CardCode] END) as 'BP Code',
(CASE WHEN T0.[TransType] = 13 THEN T3.[CardName] ELSE T5.[CardName] END) as 'BP Name',
T2.[Segment_0], T2.[AcctName], T1.[Debit]*-1+ T1.[Credit] as 'Line Total',
(CASE WHEN T0.[TransType] = 13 THEN T4.[StateB] ELSE T6.[StateB] END) as 'Bill-To State',
(CASE WHEN T0.[TransType] = 13 THEN T7.[SlpCode] ELSE T8.[SlpCode] END) as 'Bill-To Slp',
(CASE WHEN T0.[TransType] = 13 THEN T4.[StateS] ELSE T6.[StateS] END) as 'Ship-To State',
(CASE WHEN T0.[TransType] = 13 THEN T7.[U_LBSISlpName] ELSE T8.[U_LBSISlpName] END) as 'Ship-To Slp' FROM OJDT T0
INNER JOIN JDT1 T1 ON T0.[TransId] = T1.[TransId]
INNER JOIN OACT T2 ON T1.[Account] = T2.[AcctCode]
left JOIN OINV T3 ON T0.[TransId] = T3.[TransId]
left JOIN INV12 T4 ON T3.[DocEntry] = T4.[DocEntry]
left JOIN INV1 T7 ON T3.[DocEntry] = T7.[DocEntry]
left JOIN ORIN T5 ON T0.[TransId] = T5.[TransId]
left JOIN RIN12 T6 ON T5.[DocEntry] = T6.[DocEntry]
left JOIN RIN1 T8 ON T5.[DocEntry] = T8.[DocEntry]
left JOIN OSLP T9 ON T3.[SlpCode] = T9.[SlpCode]
WHERE T0.[RefDate] between [%0] and [%1] and
T2.[Segment_0] between '40000' and '49999' and T1.[ContraAct] <> '_SYS0000000000830' and T0.[TransType] <>-3
Use CONVERT( nvarchar(50), T7.SlpCode) or CAST( T7.SlpCode AS nvarchar(50)) replacing the length for the correct one. You're facing an implicit transaction and there's a precedence that takes place. You can check how this works in here: https://docs.microsoft.com/en-us/sql/t-sql/data-types/data-type-precedence-transact-sql?view=sql-server-2017
July 18, 2018 at 9:07 am
The query above works, I just need to modify it a little and show: instead of codes T7.SlpCode the actual names from table T9.
(CASE WHEN T0.[TransType] = 13 THEN T9.[SlpName] ELSE T8.[SlpCode] END) as 'Bill-To Slp', <= this is the part where I'm failing. CONVERT/CAST don't work
I'm a total amateur, sorry... 🙂
July 18, 2018 at 9:11 am
dinaalesin - Wednesday, July 18, 2018 9:07 AMThe query above works, I just need to modify it a little and show: instead of codes T7.SlpCode the actual names from table T9.
(CASE WHEN T0.[TransType] = 13 THEN T9.[SlpName] ELSE T8.[SlpCode] END) as 'Bill-To Slp', <= this is the part where I'm failing. CONVERT/CAST don't work
I'm a total amateur, sorry... 🙂
Again, at least for me, it would help to see the column definitions.
July 18, 2018 at 9:20 am
dinaalesin - Wednesday, July 18, 2018 9:07 AMThe query above works, I just need to modify it a little and show: instead of codes T7.SlpCode the actual names from table T9.
(CASE WHEN T0.[TransType] = 13 THEN T9.[SlpName] ELSE T8.[SlpCode] END) as 'Bill-To Slp', <= this is the part where I'm failing. CONVERT/CAST don't work
I'm a total amateur, sorry... 🙂
It T9.SlpName is a string and T8.SlpCode is a numeric type, then you need to convert the numeric type into a string. All the possible results of a case expression should be using the same data type to keep the column consistent. If you don't use an explicit conversion, SQL Server will try to do an implicit conversion based on the data type precedence that I referenced before.
July 18, 2018 at 9:22 am
Luis Cazares - Wednesday, July 18, 2018 9:20 AMdinaalesin - Wednesday, July 18, 2018 9:07 AMThe query above works, I just need to modify it a little and show: instead of codes T7.SlpCode the actual names from table T9.
(CASE WHEN T0.[TransType] = 13 THEN T9.[SlpName] ELSE T8.[SlpCode] END) as 'Bill-To Slp', <= this is the part where I'm failing. CONVERT/CAST don't work
I'm a total amateur, sorry... 🙂It T9.SlpName is a string and T8.SlpCode is a numeric type, then you need to convert the numeric type into a string. All the possible results of a case expression should be using the same data type to keep the column consistent. If you don't use an explicit conversion, SQL Server will try to do an implicit conversion based on the data type precedence that I referenced before.
Which means character strings are implicitly converted to integers.
July 18, 2018 at 11:30 am
In case it helps others, here is a formatted version of the original code:
SELECT DISTINCT
Num = T0.TransId
, T0.Ref1
, T0.RefDate
, [BP Code] = (CASE
WHEN T0.TransType = 13 THEN
T3.CardCode
ELSE
T5.CardCode
END
)
, [BP Name] = (CASE
WHEN T0.TransType = 13 THEN
T3.CardName
ELSE
T5.CardName
END
)
, T2.Segment_0
, T2.AcctName
, [Line Total] = T1.Debit * -1 + T1.Credit
, [Bill-To State] = (CASE
WHEN T0.TransType = 13 THEN
T4.StateB
ELSE
T6.StateB
END
)
, [Bill-To Slp] = (CASE
WHEN T0.TransType = 13 THEN
T7.SlpCode
ELSE
T8.SlpCode
END
)
, [Ship-To State] = (CASE
WHEN T0.TransType = 13 THEN
T4.StateS
ELSE
T6.StateS
END
)
, [Ship-To Slp] = (CASE
WHEN T0.TransType = 13 THEN
T7.U_LBSISlpName
ELSE
T8.U_LBSISlpName
END
)
FROM
OJDT T0
INNER JOIN JDT1 T1 ON T0.TransId = T1.TransId
INNER JOIN OACT T2 ON T1.Account = T2.AcctCode
LEFT JOIN OINV T3 ON T0.TransId = T3.TransId
LEFT JOIN INV12 T4 ON T3.DocEntry = T4.DocEntry
LEFT JOIN INV1 T7 ON T3.DocEntry = T7.DocEntry
LEFT JOIN ORIN T5 ON T0.TransId = T5.TransId
LEFT JOIN RIN12 T6 ON T5.DocEntry = T6.DocEntry
LEFT JOIN RIN1 T8 ON T5.DocEntry = T8.DocEntry
LEFT JOIN OSLP T9 ON T3.SlpCode = T9.SlpCode
WHERE
T0.RefDate BETWEEN [%0] AND [%1]
AND T2.Segment_0 BETWEEN '40000' AND '49999'
AND T1.ContraAct <> '_SYS0000000000830'
AND T0.TransType <> -3;
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply