dslreports logo
 
    All Forums Hot Topics Gallery
spc

spacer




how-to block ads


Search Topic:
uniqs
420
share rss forum feed


Nordy

join:2007-10-20
kudos:1

How to do a if or case after a where in an sql statement

I'm trying to use if or case in a select statement after the where, but my little knowledge of sql is preventing me from doing it.

What I'm trying to achieve is like this:
$i = 10;//This is arrived at using php
 
//The sql in pseudo code
select count(id) from products where parent = $foo and 
type = 
(if $i = 2 then type = 1, 
else if $i = 3 then type = 2 or 1, 
else if $i = 10 then type = 3)
 

How can something like this be done? Is is possible to this? I've used ifs before the where, but is using it after the where doable and also doing it with a php variable?

Please help.


cowboyro
Premium
join:2000-10-11
Shelton, CT
Of course you'll be a good boy and use a parametrized stored proc...
select count(id) from products where parent = @parent and
type = CASE @param
  WHEN 2 THEN 1
  WHEN 3 THEN 2
  WHEN 10 THEN 5
  ELSE 17 END
 
Since you have an OR in your pseudocode (don't know if intended or not), your selection logic should be a bit more advanced, you may want to use a values list such as:

DECLARE TBL_TYPES TABLE (TYPE INT);
 
IF @param = 2 THEN INSERT INTO TBL_TYPES (TYPE) VALUES (1); 
IF @param = 3 THEN INSERT INTO TBL_TYPES (TYPE) VALUES (1),(2),(3);
IF @param = 4 THEN INSERT INTO TBL_TYPES (TYPE) VALUES (2),(4);
 
select count(id) from products where parent = @parent and
type IN (SELECT TYPE FROM TBL_TYPES);
 

pablo
MVM
join:2003-06-23
kudos:1

1 edit
reply to Nordy
Hi,

If it were me, I'd convert the logic so it's data driven and this way avoid hard coding the information in code.

For example, you might have a new table named [product_type]:

CREATE TABLE product_type ...
id NUMBER,
code NUMBER,
screen_identifier NUMBER -- probably a cruddy column name but ...

--
-- Emulate:
-- (if $i = 2 then type = 1,
-- else if $i = 3 then type = 2 or 1,
-- else if $i = 10 then type = 3)
--
INSERT INTO product_type (id, code, screen_identifier)
VALUES (1, 1, 2);
INSERT INTO product_type (id, code, screen_identifier)
VALUES (2, 2, 3);
INSERT INTO product_type (id, code, screen_identifier)
VALUES (3, 1, 3);
INSERT INTO product_type (id, code, screen_identifier)
VALUES (4, 3, 10);

With the above, you can now codify it as follows:

SELECT COUNT(*)
FROM products p,
product_type pt
WHERE p.parent = $foo
AND p.type = pt.code
AND pt.screen_identifier = $i;

As I mentioned earlier, by making it table-driven, any changes are now data-driven. No recoding required (or re-QA'ing, etc).

Cheers,
-pablo

:: Edit ::

Fixed the surrogate key setting.

--
openSUSE 12.2/KDE 4.x
ISP: TekSavvy Bonded DSL; backhauled via a 6KM wireless link
Assorted goodies: »pablo.blog.blueoakdb.com


cowboyro
Premium
join:2000-10-11
Shelton, CT
Not very efficient, creates multiple copies of the same data

pablo
MVM
join:2003-06-23
kudos:1
Hi,

Where in particular are you seeing multiple copies?

The development mantra should always be, avoid writing code.

Cheers,
-pablo
--
openSUSE 12.2/KDE 4.x
ISP: TekSavvy Bonded DSL; backhauled via a 6KM wireless link
Assorted goodies: »pablo.blog.blueoakdb.com


cowboyro
Premium
join:2000-10-11
Shelton, CT
said by pablo:

Where in particular are you seeing multiple copies?

You are creating a potentially huge table to use in a join on another potentially huge table. Drastically increasing the complexity of a relatively simple problem. Wait until you have mathematical operations involved (ex if param = 1 select types 1 or 2, if param=2 select types less than 5 or greater than 19, if param = 3 select types 11 to 13, otherwise select type=99.
The KISS principle should always apply.

pablo
MVM
join:2003-06-23
kudos:1

1 edit
said by cowboyro:

said by pablo:

Where in particular are you seeing multiple copies?

You are creating a potentially huge table to use in a join on another potentially huge table. Drastically increasing the complexity of a relatively simple problem. Wait until you have mathematical operations involved (ex if param = 1 select types 1 or 2, if param=2 select types less than 5 or greater than 19, if param = 3 select types 11 to 13, otherwise select type=99.
The KISS principle should always apply.

Hi,

Joining between two or more "large" tables shouldn't be feared. As long as there are supporting indexes, the queries will be performant.

For this particular case, I'm not suggesting to use a table to emulate Boolean logic. I think the comment I originally posted was a bit misleading though in that it appears to suggest it. All I intended with it was to indicate how the requirement translated into values in the table.

Ideally, what we should have is a requirement on what the business is intending to do so that a proper solution can be proposed.

Cheers,
-pablo
--
openSUSE 12.2/KDE 4.x
ISP: TekSavvy Bonded DSL; backhauled via a 6KM wireless link
Assorted goodies: »pablo.blog.blueoakdb.com

pablo
MVM
join:2003-06-23
kudos:1
reply to cowboyro
Hi,

btw, on the large table thing, I routinely work with tables in the 600-800 GiB range. This does not include the space for the index(es).

For one particular client, they had a table that was 4 TiB (sans the indexes). They requested I lop off 3.2 TiB. It was quite cool to work with some honk'n iron to get it done in a "safe" (transactional) and minimally log set of operations.

The final table was 800 GiB and the indexes were roughly the same; it's a sales log table used for reporting (although it's mainly an OLTP system).

It took 26 hours to create the new table, create all its new indexes and gather statistics on all of the objects.

Cheers,
-pablo
--
openSUSE 12.2/KDE 4.x
ISP: TekSavvy Bonded DSL; backhauled via a 6KM wireless link
Assorted goodies: »pablo.blog.blueoakdb.com


Nordy

join:2007-10-20
kudos:1
reply to Nordy
Sorry. No progress on this. But once we do something, I'll post how we did it.