If Then Else - 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)

If Then Else

Author

Topic

mufasa
Yak Posting Veteran

62 Posts

Posted - 2003-10-24 : 11:36:30

I have a database that I need to link to another database.

The problem I am having is that the field in database A, that I need to link to database B has some differences.

These differences are that some records start with an M in front and some do not. Some records are 9 digits some are 8. The linking database B does not start with an M. The field type is char(10)

I tried the following but did not work;
IF(left(MEXX.dbo.GCEFIL.gce_giftcer,1)='M' then substring(MEXX.dbo.GCEFIL.gce_giftcer,2,10) else MEXX.dbo.GCEFIL.gce_giftcer)

What I am trying to accomplish, is that if the record starts with 'M', drop the M and return the balance, if it does not start with an 'M' return the record as is.

Any help would be great
Thanks
Mufasa

BTW I can not change any data in Database 'A'

SamC
White Water Yakist

3467 Posts

Posted - 2003-10-24 : 11:46:58

There are lots of ways to do this. Here's a quick one.

I think you want to join on these columns?

SELECT * FROM A
INNER JOIN B on B.MyCol = RIGHT(A.Mycol, LEN(B.Mycol))

mufasa
Yak Posting Veteran

62 Posts

Posted - 2003-10-24 : 12:19:37

Tried that but did not return the results that I need, also took more than 2 minutes.
Below is an example of what I need returned
Table A ------ Table B
M100012345 = 100012345
M02512345 = 02512345
M002512345 = 002512345
10002512 = 10002512

I could if need be create a query first on table A dropping the M (when there is one) then join with table B.
Just need to know how to drop the M.

Thanks
Mufasa

MakeYourDaddyProud

184 Posts

Posted - 2003-10-24 : 12:20:23

Can you give some example data in table column A and B and some variations?

This will enable members to give more accurate SQL

Daniel Small MIAP
www.danielsmall.com IT Factoring

MakeYourDaddyProud

184 Posts

Posted - 2003-10-24 : 12:25:11

select * from tableA A
join tableB B
on B.col1 = A.col1
where left(a.col1,1) = 'M'
union all
select * from tableA A
join tableB B
on right(B.col1,9) = A.col1
where left(a.col1,1) <> 'M'

Daniel Small MIAP
www.danielsmall.com IT Factoring

MakeYourDaddyProud

184 Posts

Posted - 2003-10-24 : 12:26:28

Ooooppppssss more accurate here

select * from tableA A
join tableB B
on B.col1 = A.col1
where left(a.col1,1) = 'M'
union all
select * from tableA A
join tableB B
on right(A.col1,9) = B.col1
where left(a.col1,1) <> 'M'

Daniel Small MIAP
www.danielsmall.com IT Factoring

MakeYourDaddyProud

184 Posts

Posted - 2003-10-24 : 12:27:52

.... and reverse the = 'M' and <> 'M' bit ... I like to do things in stages. He he

Daniel Small MIAP
www.danielsmall.com IT Factoring

mufasa
Yak Posting Veteran

62 Posts

Posted - 2003-10-24 : 14:54:07

Thanks Daniel

I had to make a couple of changes for it to work for me, your way only returned records that started with 'M'. I also needed a new colunm for the truncated number.

Here is what I did;

SELECT SUBSTRING(gce_giftcer, 2, 9) AS GIFTC#, dbo.GCEFIL.*
FROM dbo.GCEFIL
WHERE (LEFT(gce_giftcer, 1) = 'M')

union all
SELECT gce_giftcer AS GIFTC#, dbo.GCEFIL.*
FROM dbo.GCEFIL
WHERE (LEFT(gce_giftcer, 1) <> 'M')

I use this outcome with table 'B' and works fine.

One thing I do not understand, I created the above query in views, but could not save it right away. It said that it did could not use UNION in views, though it did return the results and eventually saved it.

I also had to edit it a little as I found that it is case sensative.

Thanks again for the help

Mufasa

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources