Need help with a search query - 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)

Need help with a search query

Author

Topic

darthasshat
Starting Member

17 Posts

Posted - 2006-06-16 : 18:40:35

Hi, I'm trying to locate accounts that meet a certain criterion. My goal is to pull accounts that have a combination of services or perhaps have one service and not another. I've tried different ways to pull this using both In & Not In and even = & <>. Neither have been successful. Here is a sample of what I'm trying to do


 SELECT       	Distinct cunrov.cumstpf, cuco.cumstpf, cucno.cumstpf, cehed.convrpf, ceitm.convrpf, itdsc1.itmstpf, ceser.convrpf, svccode.srvstring
 FROM         	larlivfile/cumstpf,larlivfile/convrpf,larlivfile/cdo1cpp,larlivfile/srvrpf,larlivfile/itmstpf,larsql/srvstring
 PARTIAL OUTER JOIN  	o1syp8.cdo1cpp=aosyp8.srvrpf and o1cek6.cdo1cpp=aocek6.srvrpf and ceser.convrpf=o1txah.cdo1cpp and o1cnbr.cdo1cpp=aocnbr.srvrpf and cecus.convrpf=o1cnbr.cdo1cpp and cecus.convrpf=cucno.cumstpf
				 and o1nrov.cdo1cpp=aonrov.srvrpf and cunrov.cumstpf=cenrov.convrpf and ceitm.convrpf=itptno.itmstpf and cucno.cumstpf=aocnbr.srvstring
 WHERE       	custa='A' AND ceitm IN('DHCT012','DHCT014','MMCD001') AND
				(aosvcd IN('HDTVSVC') AND aosvcd IN('DVRSVC1')) AND aorccd='A'

The task the above should perform is to pull the account number of accounts that have both the HDTVSVC and DVRSVC1 codes active on the account. If anyone has any ideas they could share I would greatly appreciate the help. Thanks!

darthasshat
Starting Member

17 Posts

Posted - 2006-06-20 : 13:47:14

Anyone have any ideas?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-20 : 14:01:58

Hard to tell without DDL or test data of tables involved.

Peter Larsson
Helsingborg, Sweden

darthasshat
Starting Member

17 Posts

Posted - 2006-06-21 : 11:05:01

The data source is composed of 6 large tables so sample tables will be a bit hard to come by. Is what I explained even possible? Here is what is happening. Customer X has 9 services and I want to check if they have have both HDTVSVC and DVRSVC1 OR HDTVSVC and not DVRSVC1. There is a record for each service code on the account. Can a query be created that will search based on that criteria or should I use a stored procedure or temp tables?

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2006-06-21 : 11:25:50

Follow the link in my signature, and restate your question. It should help.

And just looking at this part of your WHERE clause, i dont see you ever getting any records back

(aosvcd IN('HDTVSVC') AND aosvcd IN('DVRSVC1'))

aosvcd cannot be both values at once, can it?

perhaps you mean to use OR

Help us help YOU!
Read this blog entry for more details: http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx

*need more coffee*
SELECT * FROM Users WHERE CLUE > 0
(0 row(s) affected)

darthasshat
Starting Member

17 Posts

Posted - 2006-06-21 : 13:27:02

You're right. The field can not be the both at the same time. Each will be its own record. Is it possible to say pull an account that has two specific service codes? I only want to pull the account if it has both the codes on the same outlet. I'm using SEQUEL Viewpoint but I'll try and get the sample data. Thank you for your help!

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2006-06-21 : 16:32:32

You probably wanted:


aosvcd in ('HDTVSVC', 'DVRSVC1')

For searching based on list of values see articles:
http://www.sql-server-performance.com/mm_list_random_values.asp
http://www.sommarskog.se/arrays-in-sql.html

darthasshat
Starting Member

17 Posts

Posted - 2006-06-21 : 16:52:19

I tried that but that returns a record if either of them are present. I need the record to be pulled only if both are present. Any ideas?

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2006-06-22 : 01:27:21

It looks like it is not t-sql syntax. What is database server we are discussing about?

darthasshat
Starting Member

17 Posts

Posted - 2006-06-30 : 16:20:39

I'm actually trying to pull this using sequel viewpoint. Any ideas?