Co-related sub-qry - 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 2000 Forums

Transact-SQL (2000)

Co-related sub-qry

Author

Topic

frank.svs
Constraint Violating Yak Guru

368 Posts

Posted - 2007-06-13 : 08:49:58

Hi,

I have one doubt.

create table customer
(cid int primary key,
cname varchar(2000)
)

insert into customer values(101,'ram')
insert into customer values(102,'manu')

create table customer_installments
(
cid int references customer(cid),
installement_no int
)

insert into customer_installments values(101,1)
insert into customer_installments values(101,2)
insert into customer_installments values(101,3)

insert into customer_installments values(102,1)
insert into customer_installments values(102,2)
insert into customer_installments values(102,3)
insert into customer_installments values(102,4)
insert into customer_installments values(102,5)

-----------------------------
--My 0utput should look like
-----------------------------
101 ram 1,2,3
102 manu 1,2,3,4,5

My Solution for this
====================

ALTER FUNCTION ConcatInstallmentsnos(@p_cid numeric) RETURNS VARCHAR(4000)
AS
BEGIN
DECLARE @Output VARCHAR(8000)
SET @Output = ''

SELECT @Output =CASE @Output
WHEN '' THEN
ltrim(str(installement_no))
ELSE
@Output + ',' +ltrim(str(installement_no))
END
FROM customer_installments WHERE cid =@p_cid

RETURN (@Output)
END

GO
SELECT cid,cname,(SELECT DBO.ConcatInstallmentsnos(cid)) AS "InstallmentNos" FROM Customer
GO
I am getting the desired o/p

But tried it in another way using a correlated sub-query , but i am getting error. Why. Can u help me out
declare @strValues varchar(8000)
select c.cid,c.cname,(select @strValues=COALESCE(@strValues+',', '')+str(installement_no) from customer_installments where cid=c.cid select @strValues) from customer c

Error :

Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near '='.

Thanks in advance.
Franky

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-13 : 08:54:49

[code]
SELECT cid,cname, DBO.ConcatInstallmentsnos(cid) AS "InstallmentNos" FROM Customer
[/code]

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-06-14 : 00:55:02

Assignment to a variable can not be done in select list with columns..
means
you are getting cid,cname from customer table along with assignment to a variable which is not possible.

--------------------------------------------------
S.Ahamed

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources