Solved

help required on tsql

Posted on 2013-11-08
9
289 Views
Last Modified: 2013-11-11
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.
0
Comment
Question by:sqldba2013
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 5

Expert Comment

by:dannygonzalez09
ID: 39633792
Try this

select '$' + ' ' + CAST((SELECT SUM([Net Value]) FROM dbo.SAP_ZVBAK SAP
INNER JOIN dbo.CPOA
ON SAP.[Customer PO] LIKE '%'+CONVERT(VARCHAR(50),CPOA.[PO Number])  + '%'
0
 
LVL 9

Expert Comment

by:Valliappan AN
ID: 39633849
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.
0
 
LVL 9

Expert Comment

by:Valliappan AN
ID: 39633898
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.
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 27

Expert Comment

by:Zberteoc
ID: 39634143
" 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:
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]

Open in new window

0
 

Author Comment

by:sqldba2013
ID: 39635638
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
0
 
LVL 27

Accepted Solution

by:
Zberteoc earned 250 total points
ID: 39635654
If you use that query you will not have the result on each [PO Number]. You will have one SUM per all [PO Number] that satisfy the condition together.

In order to get the format you want you will have to cast to decimal(my query):

SELECT
	c.[PO Number],
	'$ ' + CAST(cast(SUM([Net Value]) as decimal(18,2)) 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]

Open in new window

In the query you tried:
select
'$' + ' ' + cast(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 decimal (10,2)) as nvarchar(50))AS [Amount]

Open in new window

0
 

Author Comment

by:sqldba2013
ID: 39635675
got the below error, pls suggest

Msg 8115, Level 16, State 6, Line 1
Arithmetic overflow error converting float to data type numeric.
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 39635681
Increase decimal (10,2) to decimal (18,2)
0
 

Author Closing Comment

by:sqldba2013
ID: 39638572
--
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question