sqldba2013
asked on
help required on tsql
Query 1:
select [Customer PO] from dbo.SAP_ZVBAK
Output:
[Customer PO]
5209741 10098475
5275291 10087977
Query 2:
select [PO Number] from dbo.CPOA
output:
PO Number
5209741
5275291
5275298
Main query:
select '$' + ' ' + CAST((SELECT SUM([Net Value]) FROM dbo.SAP_ZVBAK
WHERE SAP_ZVBAK.[Customer PO] = CPOA.[PO Number]) AS VARCHAR(50)) AS [X] from .....
Please guide me how to add LIKE condition for [Customer PO] column in above script in WHERE clause and I have to return two common rows (5209741,5275291) from above script using LIKE operator.
Thanks in advance.
select [Customer PO] from dbo.SAP_ZVBAK
Output:
[Customer PO]
5209741 10098475
5275291 10087977
Query 2:
select [PO Number] from dbo.CPOA
output:
PO Number
5209741
5275291
5275298
Main query:
select '$' + ' ' + CAST((SELECT SUM([Net Value]) FROM dbo.SAP_ZVBAK
WHERE SAP_ZVBAK.[Customer PO] = CPOA.[PO Number]) AS VARCHAR(50)) AS [X] from .....
Please guide me how to add LIKE condition for [Customer PO] column in above script in WHERE clause and I have to return two common rows (5209741,5275291) from above script using LIKE operator.
Thanks in advance.
If the match string is always in the start, you may try this:
SELECT A.[Customer PO], B.[PO Number], CHARINDEX(B.[PO Number],A.[Customer PO],0) FROM dbo.SAP_ZVBAK A
JOIN dbo.CPOA B ON CHARINDEX( B.[PO Number], A.[Customer PO], 0 ) = 1
If the match string occurs anywhere, then
SELECT A.[Customer PO], B.[PO Number], CHARINDEX(B.[PO Number],A.[Customer PO],0) FROM dbo.SAP_ZVBAK A
JOIN dbo.CPOA B ON CHARINDEX( B.[PO Number], A.[Customer PO], 0 ) > 0
should work.
SELECT A.[Customer PO], B.[PO Number], CHARINDEX(B.[PO Number],A.[Customer PO],0) FROM dbo.SAP_ZVBAK A
JOIN dbo.CPOA B ON CHARINDEX( B.[PO Number], A.[Customer PO], 0 ) = 1
If the match string occurs anywhere, then
SELECT A.[Customer PO], B.[PO Number], CHARINDEX(B.[PO Number],A.[Customer PO],0) FROM dbo.SAP_ZVBAK A
JOIN dbo.CPOA B ON CHARINDEX( B.[PO Number], A.[Customer PO], 0 ) > 0
should work.
You can also go with danny's comments, when i tested both, using LIKE seem to be faster, compared to CharIndex, may be when the correct indexes are setup for the tables.
if you want to match only with the start of the PO numbers:
select '$' + ' ' + CAST((SELECT SUM([Net Value])
FROM dbo.SAP_ZVBAK
JOIN dbo.CPOA ON SAP_ZVBAK.[Customer PO] LIKE CAST(CPOA.[PO Number] AS VARCHAR(50)) + '%'
HTH.
if you want to match only with the start of the PO numbers:
select '$' + ' ' + CAST((SELECT SUM([Net Value])
FROM dbo.SAP_ZVBAK
JOIN dbo.CPOA ON SAP_ZVBAK.[Customer PO] LIKE CAST(CPOA.[PO Number] AS VARCHAR(50)) + '%'
HTH.
" using LIKE seem to be faster"
Of course it is because that query can use indexes if there are any. Wrapping columns in SQL functions denies the SQL engine to use indexes. Query:
Of course it is because that query can use indexes if there are any. Wrapping columns in SQL functions denies the SQL engine to use indexes. Query:
SELECT
c.[PO Number],
'$ ' + CAST(SUM([Net Value]) as varchar(25)) as Total_Net
FROM
dbo.SAP_ZVBAK z
inner join dbo.CPOA c
ON z.[Customer PO] LIKE LTRIM(CAST(c.[PO Number] as varchar(25)))+'%'
GROUP BY
c.[PO Number]
ORDER BY
c.[PO Number]
ASKER
Thanks to all for your suggestions.
I have executed below query and the output (amount) is not showing in correct format. Please suggest how to modify below query for correct output format (ex; $1234.56)
select
'$' + ' ' + cast((select SUM([SO Net Value]) FROM dbo.SAP_ZVBAK z with(nolock)
inner join dbo.CPOA c with(nolock)
ON z.[Customer PO] LIKE LTRIM(CAST(c.[PO Number] as varchar(50)))+'%') as nvarchar(50))AS [Amount]
Output:
Amount
$ 5.41957e+008
I have executed below query and the output (amount) is not showing in correct format. Please suggest how to modify below query for correct output format (ex; $1234.56)
select
'$' + ' ' + cast((select SUM([SO Net Value]) FROM dbo.SAP_ZVBAK z with(nolock)
inner join dbo.CPOA c with(nolock)
ON z.[Customer PO] LIKE LTRIM(CAST(c.[PO Number] as varchar(50)))+'%') as nvarchar(50))AS [Amount]
Output:
Amount
$ 5.41957e+008
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
got the below error, pls suggest
Msg 8115, Level 16, State 6, Line 1
Arithmetic overflow error converting float to data type numeric.
Msg 8115, Level 16, State 6, Line 1
Arithmetic overflow error converting float to data type numeric.
Increase decimal (10,2) to decimal (18,2)
ASKER
--
select '$' + ' ' + CAST((SELECT SUM([Net Value]) FROM dbo.SAP_ZVBAK SAP
INNER JOIN dbo.CPOA
ON SAP.[Customer PO] LIKE '%'+CONVERT(VARCHAR(50),CP