Writing a single record from a single COLUMN exam

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

SQL Server Development (2000)

Writing a single record from a single COLUMN exam

Author

Topic

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-01-21 : 16:59:25

Here’s my dilemma.

I have a table tab_UU4 that has data in the 4 UUxID columns that I wish to create a single record for each. For each row in tab_UU I am examining each UUxID column. If the UUxID holds 1111, 2222, 7777, or 9999, I want to write a record to another table. In some case there will be 4 records written for a particular row, in other cases there’ll be no records written.

The problem is getting the records written and written to another table.

In my current code, I succeed only in generating a duplicate row for each instance I find.

Here’s the input:

R_SEQ Who UU1Name UU1Ck UUID UU2Name UU2Ck UU2ID UU3Name UU3Ck UU3ID UU4Name UU4Ck UU4ID
00001 PQ Probus 45X 1000 Aurelian 67W 1111 Gallus 44K 7777 Otho 54J 6809
00002 JK Galba 45X 9999 Tiberius 67W 1174 Philip 44K 4444 Alexius 54J 3333
00003 UO Gratian 45X 9799 Maximus 67W 0008 Jovian 44K 0005 Macrinus 54J 0055
00004 SD Valens 45X 6000 Gordian 67W 2222 Titus 44K 6600 Nerva 54J 7777
00005 GW Trajan 45X 7777 Pertinax 67W 9999 Hadrian 44K 0044 Majorian 54J 0006

Here’s what I want:

R_SEQ Who UUName UU1Ck UUID
00001 PQ Aurelian 67W 1111
00001 PQ Gallus 44K 7777
00002 JK Galba 45X 9999
00004 SD Gordian 67W 2222
00004 SD Nerva 54J 7777
00005 GW Trajan 45X 7777
00005 GW Pertinax 67W 9999

But this is what I’m getting:

R_SEQ Who UU1Name UU1Ck UUID UU2Name UU2Ck UU2ID UU3Name UU3Ck UU3ID UU4Name UU4Ck UU4ID
00001 PQ Probus 45X 1000 Aurelian 67W 1111 Gallus 44K 7777 Otho 54J 6809
00001 PQ Probus 45X 1000 Aurelian 67W 1111 Gallus 44K 7777 Otho 54J 6809
00002 JK Galba 45X 9999 Tiberius 67W 1174 Philip 44K 4444 Alexius 54J 3333
00004 SD Valens 45X 6000 Gordian 67W 2222 Titus 44K 6600 Nerva 54J 7777
00004 SD Valens 45X 6000 Gordian 67W 2222 Titus 44K 6600 Nerva 54J 7777
00005 GW Trajan 45X 7777 Pertinax 67W 9999 Hadrian 44K 0044 Majorian 54J 0006
00005 GW Trajan 45X 7777 Pertinax 67W 9999 Hadrian 44K 0044 Majorian 54J 0006

Here’s the code

SELECT r_SEQ, Who,
UU1Name, UU2Name, UU3Name, UU4Name,
UU1Ck, UU2Ck, UU3Ck, UU4Ck,
UU1ID, UU2ID, UU3ID, UU4ID,
FROM [Xerxes].[dbo].[tab_UU]
WHERE UU1ID in (‘1111’,’2222’,’7777’,’9999’)UNION ALL

SELECT r_SEQ, Who,
UU1Name, UU2Name, UU3Name, UU4Name,
UU1Ck, UU2Ck, UU3Ck, UU4Ck,
UU1ID, UU2ID, UU3ID, UU4ID,
FROM [Xerxes].[dbo].[tab_UU]
WHERE UU2ID in (‘1111’,’2222’,’7777’,’9999’)UNION ALL

SELECT r_SEQ, Who,
UU1Name, UU2Name, UU3Name, UU4Name,
UU1Ck, UU2Ck, UU3Ck, UU4Ck,
UU1ID, UU2ID, UU3ID, UU4ID,
FROM [Xerxes].[dbo].[tab_UU]
WHERE UU3ID in (‘1111’,’2222’,’7777’,’9999’)UNION ALL

SELECT r_SEQ, Who,
UU1Name, UU2Name, UU3Name, UU4Name,
UU1Ck, UU2Ck, UU3Ck, UU4Ck,
UU1ID, UU2ID, UU3ID, UU4ID,
FROM [Xerxes].[dbo].[tab_UU]
WHERE UU4ID in (‘1111’,’2222’,’7777’,’9999’)

And that’s where it ends…

Could anyone point this ole Marine in the right direction from here?

Thanks….

~~~~~~~~~~~~~
Semper fi,

Xerxes, USMC

epanther
Starting Member

8 Posts

Posted - 2005-01-21 : 23:18:34

Well you are asking for all of the columns, so you are going to get all of the columns. Here is my suggestion.

Create a temp or permanent table with R_SEQ, Who, UUName, UU1Ck, and UUID.

insert into new table
(select R_SEQ, Who, UU1Name, UU1Ck, UUID
FROM [Xerxes].[dbo].[tab_UU]
WHERE UU1ID in (‘1111’,’2222’,’7777’,’9999’))

and then three more inserts for 2, 3, and 4

That should do the trick. I really wonder why your tab_UU table is setup the way it is, but I guess there is a reason.

BTW, Thanks for serving our country!

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-01-24 : 14:02:15

Thanks epanther,

My 20 years ('75-'95) in the Marines was my greatest pleasure and privilege! (And I miss it every day!)

Back on SQL.....after reading your comment that in "asking for all the columns I was going to get all the columns"...thanks. I should have seen that, but I'd been banging my head too long at this problem.

In actuality I'm finding the INSERT statement is causing me as much grief as my original problem.

Thanks for trying, but I believe I've got to find another way to make this work.

~~~~~~~~~~~~~
Semper fi,

Xerxes, USMC

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-01-27 : 14:26:52

quote:
Originally posted by epanther

Well you are asking for all of the columns, so you are going to get all of the columns. Here is my suggestion.

Create a temp or permanent table with R_SEQ, Who, UUName, UU1Ck, and UUID.

insert into new table
(select R_SEQ, Who, UU1Name, UU1Ck, UUID
FROM [Xerxes].[dbo].[tab_UU]
WHERE UU1ID in (‘1111’,’2222’,’7777’,’9999’))

and then three more inserts for 2, 3, and 4

That should do the trick. I really wonder why your tab_UU table is setup the way it is, but I guess there is a reason.

BTW, Thanks for serving our country!

By the way....I really meant to thank you for your assistance----your suggestion made the difference!

~~~~~~~~~~~~~
Semper fi,

Xerxes, USMC

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources