concatenate problem

Hi All,

I am facing problem when i try to concatenate two columns. I have text in one column and numeric data in other field, and in want to update field with text datatype and wants to put '-field2'(field with numeic data) as suffix in text data field.

Result should be "field1-field2"

Any help will be appericiated

ThanksOriginally posted by Devinder Gera
Hi All,

I am facing problem when i try to concatenate two columns. I have text in one column and numeric data in other field, and in want to update field with text datatype and wants to put '-field2'(field with numeic data) as suffix in text data field.

Result should be "field1-field2"

Any help will be appericiated

Thanks
If I read that right you want:

Select field1 + convert(varchar, field2)

You can use that in an INSERT or UPDATE statement.

HTH, Saint|||If I read that right you want:

Select field1 + convert(varchar, field2)

You can use that in an INSERT or UPDATE statement.

HTH, Saint [/SIZE][/QUOTE]

Hi Saint,

Thanks for your reply. I tried this but it works fine in select statement but in update it gives different results.

Following is result in select statement and thats what i want after update
07535494187886-1
07535494187886-2
07535494187886-3
07535494187886-4
30834281804606-1
09462976809022-1
09462976809022-2
37882735916006-1

But actually after update i am getting following result

07535494187886-1-1-1-1-1-1-1-1
07535494187886-2-2-2-2-2-2-2
07535494187886-3-3-3-3-3-3
07535494187886-4-4-4-4-4
30834281804606-1-1-1-1
09462976809022-1-1-1
09462976809022-2-2
37882735916006-1

Any idea why its so.

Thanks|||Originally posted by Devinder Gera
If I read that right you want:

Select field1 + convert(varchar, field2)

You can use that in an INSERT or UPDATE statement.

HTH, Saint

Hi Saint,

Thanks for your reply. I tried this but it works fine in select statement but in update it gives different results.

Following is result in select statement and thats what i want after update
07535494187886-1
07535494187886-2
07535494187886-3
07535494187886-4
30834281804606-1
09462976809022-1
09462976809022-2
37882735916006-1

But actually after update i am getting following result

07535494187886-1-1-1-1-1-1-1-1
07535494187886-2-2-2-2-2-2-2
07535494187886-3-3-3-3-3-3
07535494187886-4-4-4-4-4
30834281804606-1-1-1-1
09462976809022-1-1-1
09462976809022-2-2
37882735916006-1

Any idea why its so.

Thanks [/SIZE][/QUOTE]

No worries guys it started working. I was just updating at wrong time. I changed the location of update now its working fantastic

Thanks a million Saint

Complex SQL Query

Hi all,

I am developing an application using SQL Server as Back-end. I am facing a problem in creating a SQL Query. The details are as follows:

There are three tables in the Database, Data Type of all Columns is Numeric in all three tables:

1. T1

(column names and sample data)

en
==

1
2
3

2) T2

(column names and sample data)

en gn
== ==

1 10
1 11
2 10
2 12
2 13

3) T3

(column names and sample data)

en pn
== ==

1 20
1 21
1 22
2 20

Now I have to create a SQL Query, whereby I can get the following result:

en gn pn
== == ==
1 10 20
1 11 21
1 NULL 22
2 10 20
2 12 NULL
2 13 NULL

I have tried various combination of Joins, but unable to get the desired result as the tables have many-to-many relationships, therefore I get many duplicate rows in the result. UNION will not solve the problem, as that will add the additional rows for the third table. Although I can achieve this by writing few lines of code, but I have to create a SQL Query for getting this result. Kindly tell me the way for creating the required Query for this. Many Thanks for your help.there does not seem to be any join criterion

how do you know gn=10 matches pn=20?

try stating the join criterion on english, and i don't think you can do it

you may have to do your "matching" with application code|||I agree with rudy, tried playing with the query but could not come up with anything.|||I think this is what you search for:

create table ##tmp1 (en Int,pn Int,ref Int);
create table ##tmp2 (en Int,gn Int,ref Int);

insert into ##tmp1
select
a.en,
a.pn,
count(b.en) ref
from t3 a,t3 b
where a.en=b.en and a.pn>=b.pn
group by a.en,a.pn;

insert into ##tmp2
select
a.en,
a.gn,
count(b.en) ref
from t2 a,t2 b
where a.en=b.en and a.gn>=b.gn
group by a.en,a.gn;

select
coalesce(##tmp1.en,##tmp2.en) en,
##tmp1.pn,
##tmp2.gn
from ##tmp1
full join ##tmp2 on ##tmp1.en=##tmp2.en and ##tmp1.ref=##tmp2.ref
order by en