Select sub-queries - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Select sub-queries

Author

Topic

deniseaddy
Starting Member

12 Posts

Posted - 2009-03-23 : 11:44:03

Hi,

I have a table called "payments" that contains a column called "orders" which contains a comma-delimited list of all order numbers associated with this particular payment record. i.e. 3,5,8,10. The payments table also has a column called "status" which would either be pending, completed or failed etc.

What I need to do is select all of the completed orders (without duplication) from all of the payment records that match the particular status I specify, so I can then parse these results in the main SELECT query using "IN" or "NOT IN".

For instance, if payment record one has "1,2,3" orders value, and payment record 2 has "4,5,6" orders value then I want to return:

1
2
3
4
5
6

.. as results.

Then I can say SELECT * from dbo.orders_table where id NOT IN @results etc.

Any ideas?

Denise

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-03-23 : 11:49:34

I think Visakh has a function that'll do this for you. Check this thread.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=111722

deniseaddy
Starting Member

12 Posts

Posted - 2009-03-23 : 14:49:50

Thanks. I've tried the code below but it's coming up with "function ParseValues has too many arguments specified"

[CODE]
CREATE FUNCTION dbo.ParseValues
(@String varchar(8000))
RETURNS @RESULTS TABLE
(val int)
AS
BEGIN
DECLARE @Value varchar(100)

WHILE @String is not null
BEGIN
SELECT @Value=CASE WHEN CHARINDEX(',',@String) >0 THEN LEFT(@String,CHARINDEX(',',@String)-1) ELSE @String END,
@String=CASE WHEN CHARINDEX(',',@String) >0 THEN SUBSTRING(@String,CHARINDEX(',',@String)+1,LEN(@String)) ELSE NULL END
INSERT INTO @RESULTS (val)
SELECT @Value
END
RETURN
END

Declare @tbl as table(orders int)

Insert into @tbl
SELECT DISTINCT b.val
from dbo.payments a
CROSS APPLY ParseValues(a.orders) b
WHERE (a.status = 'PENDING' or a.status = 'completed')
[/CODE]

To clarify, each orders record has value such as "5,32,45" etc and some may repeat such as "5,67,88".. where 5 is duplicated.

deniseaddy
Starting Member

12 Posts

Posted - 2009-03-23 : 16:49:25

It's okay. It was an error elsewhere in my code. This works fine. Thanks again.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources