Re: sql query wanted

From: Gary <gary_at_moneysuite.com>
Date: 22 Feb 2002 18:01:17 -0800
Message-ID: <e7778a4a.0202221801.45e5e3d6@posting.google.com>

I have two solutions for you. Both require that you string together the desired sku values into one string, punctuated as shown:

1. The following will work if you are willing to have another input besides the set of desired values: The count of how many are in the set.

select thiscount.id from
(select id, count(*) c from foobar group by id) totcount,
(select id, count(*) c from foobar where sku in (&set.) group by id)
thiscount
where totcount.id=thiscount.id and totcount.c=thiscount.c and totcount.c=&cnt;

now with

define set="'a','b'"
define cnt=2

we get

old 3: (select id, count(*) c from foobar where sku in (&set.) group by id) thiscount
new 3: (select id, count(*) c from foobar where sku in ('a','b') group by id) thiscount
old 5: and totcount.c=&cnt
new 5: and totcount.c=2

ID

---

3
6

2 rows selected.

And so on.

2. The second figures out the count itself. Here, you use a punctuation character between and bounding the set of desired values that is not found in any sku -- say, the "!". Thus:

define set='!a!b!'

Then

select thiscount.id from
(select id, count(*) c from foobar group by id) totcount,
(select id, count(*) c from foobar where '&set' like '%!'||sku||'!%'
 group by id) thiscount
where totcount.id=thiscount.id and totcount.c=thiscount.c and totcount.c=length(replace('&set','!','##'))-length('&set')-1;

yields

old 3: (select id, count(*) c from foobar where '&set' like '%!'||sku||'!%'
new 3: (select id, count(*) c from foobar where '!a!b!' like '%!'||sku||'!%'
old 6: and totcount.c=length(replace('&set','!','##'))-length('&set')-1 new 6: and totcount.c=length(replace('!a!b!','!','##'))-length('!a!b!')-1

ID

---

3
6

2 rows selected.

Now, if someone can help me with the hints problem I just posted . . .
(it hasn't appeared yet, give a few hours)

Gary Marquart
CTO, The Moneysuite Company Received on Fri Feb 22 2002 - 20:01:17 CST