Is there an easier way to write this...

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)

Is there an easier way to write this...

Author

Topic

JJ297
Aged Yak Warrior

940 Posts

Posted - 2009-03-03 : 13:58:50

SELECT *

FROM Diary02_13

WHERE (DryCd1 = '5w') OR
(DryCd1 = '7w') OR
(DryCd1 = '9w') OR
(DryCd2 = '5w') OR
(DryCd2 = '7w') OR
(DryCd2 = '9w')

I have to search DryCd1 - DryCd8 to see if a 5w, 7w, or 9w are listed in the DryCd... fields and if it is list them. This way works but is there an easier/efficient way to set this up?

A row of data looks like this:


Drycd1   Drydte     Drycd2     Drydte         DryCd3
MR       1/1/2008    5W       2/2/3008         7W

I would want this row to be listed twice since 5W and 7W are both listed there. Does this make sense? I am doing this to get a count.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-03-03 : 14:42:52

could this work?
SELECT *

FROM Diary02_13

WHERE DryCd1 IN ('5w', '7w', '9w') OR DryCd2 IN ('5w','7w', '9w')

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-03-03 : 14:44:05

I could think of this....but maybe there is a better way

select * from Diary02_13 where charindex('5w',DryCd1+DryCd2+DryCd3+...DryCd8) > 0
union all
select * from Diary02_13 where charindex('7w',DryCd1+DryCd2+DryCd3+...DryCd8) > 0
union all
select * from Diary02_13 where charindex('9w',DryCd1+DryCd2+DryCd3+...DryCd8) > 0

JJ297
Aged Yak Warrior

940 Posts

Posted - 2009-03-03 : 15:16:06

This way works fine.

SELECT *

FROM Diary02_13

WHERE DryCd1 IN ('5w', '7w', '9w') OR DryCd2 IN ('5w','7w', '9w')

Thanks!

JJ297
Aged Yak Warrior

940 Posts

Posted - 2009-03-03 : 15:32:43

Oop's spoke too soon

It's actually not working...


Claimno      Drycd2    DryDte      Drycd3      DryDte
622           5W       2/2/2008	    7W         1/2/2008

It only list this column once but it should list it twice because there's a 5W and a 7W in Drycd2 and Drycd3. Is there something called occurrences that I could use?

How could I get it to produce this?


Claimno      Drycd2    DryDte      Drycd3      DryDte
622           5W       2/2/2008	    7W         1/2/2008 
622           5W       2/2/2008	    7W         1/2/2008

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-03-03 : 16:18:41

[code]
SELECT *
FROM Diary02_13
WHERE DryCd1 IN ('5w', '7w', '9w')
UNION ALL
SELECT *
FROM Diary02_13
WHERE DryCd2 IN ('5w','7w', '9w')[/code]

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-03-03 : 16:32:16

Did u try my solution. I think that should work? Just curious.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-03 : 16:44:59

SELECT * FROM Diary02_13
WHERE '5w' IN (DryCd1, DryCd2, DryCd3, DryCd4, DryCd5, DryCd6, DryCd7, DryCd8)
OR '7w' IN (DryCd1, DryCd2, DryCd3, DryCd4, DryCd5, DryCd6, DryCd7, DryCd8)
OR '9w' IN (DryCd1, DryCd2, DryCd3, DryCd4, DryCd5, DryCd6, DryCd7, DryCd8)

SELECT * FROM Diary02_13
WHERE DryCd1 LIKE '[579]w'
OR DryCd2 LIKE '[579]w'
OR DryCd3 LIKE '[579]w'
OR DryCd4 LIKE '[579]w'
OR DryCd5 LIKE '[579]w'
OR DryCd6 LIKE '[579]w'
OR DryCd7 LIKE '[579]w'
OR DryCd8 LIKE '[579]w'

E 12°55'05.63"
N 56°04'39.26"

JJ297
Aged Yak Warrior

940 Posts

Posted - 2009-03-03 : 18:21:08

Thanks guys didn't get a chance to try it out. Will try it out in the morning and get back to you.

JJ297
Aged Yak Warrior

940 Posts

Posted - 2009-03-03 : 18:25:50

Vijayisonly I am sorry I will try yours in the morning too. Thanks again!

JJ297
Aged Yak Warrior

940 Posts

Posted - 2009-03-04 : 10:05:35

THANKS vijayisonly yours does work as well as the other one's too that were listed. Now I can move forward I was stuck on this for a while. I'm sure I'll be back again. Thanks again everyone!

JJ297
Aged Yak Warrior

940 Posts

Posted - 2009-03-05 : 13:15:17

I'm back

How do I get all of these results to go into a new table? I want to use this in SSIS

select * from Diary02_13 where charindex('5w',DryCd1+DryCd2+DryCd3+...DryCd8) > 0
union all
select * from Diary02_13 where charindex('7w',DryCd1+DryCd2+DryCd3+...DryCd8) > 0
union all
select * from Diary02_13 where charindex('9w',DryCd1+DryCd2+DryCd3+...DryCd8) > 0

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-05 : 13:18:56

[code]
insert into targettable
select * from Diary02_13 where charindex('5w',DryCd1+DryCd2+DryCd3+...DryCd8) > 0
union all
select * from Diary02_13 where charindex('7w',DryCd1+DryCd2+DryCd3+...DryCd8) > 0
union all
select * from Diary02_13 where charindex('9w',DryCd1+DryCd2+DryCd3+...DryCd8) > 0
[/code]

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-03-05 : 13:19:57

Use a SQL task and have this..

insert into NEWTABLE
select * from Diary02_13 where charindex('5w',DryCd1+DryCd2+DryCd3+...DryCd8) > 0
union all
select * from Diary02_13 where charindex('7w',DryCd1+DryCd2+DryCd3+...DryCd8) > 0
union all
select * from Diary02_13 where charindex('9w',DryCd1+DryCd2+DryCd3+...DryCd8) > 0

Your NEWTABLE and Diary02_13 should have the same structure and definition.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-03-05 : 13:20:39

Sorry Visakh...dint realize u were also on this....a timing issue..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-05 : 13:23:58

No problem

JJ297
Aged Yak Warrior

940 Posts

Posted - 2009-03-05 : 13:29:46

Thanks to you both. I posted this at the SSIS Forum but I guess you guys didn't see it so I posted it back here.

I added an Execute SQL Task to the Control Flow then added this

Insert into Test
select * from Diary02_13 where charindex('5w',DryCd1+DryCd2+DryCd3+...DryCd8) > 0
union all
select * from Diary02_13 where charindex('7w',DryCd1+DryCd2+DryCd3+...DryCd8) > 0
union all
select * from Diary02_13 where charindex('9w',DryCd1+DryCd2+DryCd3+...DryCd8) > 0

It didn't run it turned red do I have to have a table in the DB called Test or will this create table Test for me?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-05 : 13:31:33

you have to have table test in db. if you want to create table dynamically, then you need to use SELECT..INTO

JJ297
Aged Yak Warrior

940 Posts

Posted - 2009-03-05 : 13:34:44

That's what I want it to do set it up dynamically.

Is this correct?

Select * into Test
FROM Diary02_13
WHERE (CHARINDEX('5w', DryCd1 + DryCd2 + DryCd3 + Drycd4 + Drycd5 + DryCd6 + DryCd7 + DryCd8) > 0)

UNION ALL
SELECT * (what do I put here, test?)
FROM Diary02_13 AS Diary02_13_2
WHERE (CHARINDEX('7w', DryCd1 + DryCd2 + DryCd3 + Drycd4 + Drycd5 + DryCd6 + DryCd7 + DryCd8) > 0)
UNION ALL

SELECT * same for here?
FROM Diary02_13 AS Diary02_13_1
WHERE (CHARINDEX('9w', DryCd1 + DryCd2 + DryCd3 + Drycd4 + Drycd5 + DryCd6 + DryCd7 + DryCd8) > 0)
ORDER BY LNAME

I tried to put test there but it didn't work for all three select statements. I would like for all of the results to go into one table.

JJ297
Aged Yak Warrior

940 Posts

Posted - 2009-03-05 : 13:37:25

Another question to go with the other one I sent.

I tried to place the code into a conditional split but it didn't like CharIndex. I tried to use FindString but it still didn't work so that's why I decided to go with the Execute SQL Task. Which is efficient?

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-03-05 : 13:50:02

Can you try this? I haven't tried this before...so not sure if it will work.

Select * into Test
FROM(
select * from Diary02_13 where charindex('5w',DryCd1+DryCd2+DryCd3+...DryCd8) > 0
union all
select * from Diary02_13 where charindex('7w',DryCd1+DryCd2+DryCd3+...DryCd8) > 0
union all
select * from Diary02_13 where charindex('9w',DryCd1+DryCd2+DryCd3+...DryCd8) > 0
)

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources