May 14, 2008 at 6:39 am
select * from scan
where boxid = 'RET0018438'
and
(((client in ('MCP','JNJ','PCP','PCR','HOS')) and
(client in (select clientcode from policy where deduct_ship = 'N') or (reason = 'RCL'))))
Can somebody change this query so that it is readable?
May 14, 2008 at 7:09 am
[font="Verdana"]Not clear what exactly readable means. Though if I am not wrong do you need something like this ... ?
select * from scan
where boxid = 'RET0018438'
and (client in ('MCP','JNJ','PCP','PCR','HOS')
or client in (select clientcode from policy where deduct_ship = 'N' or reason = 'RCL'))
confirm on this.
Mahesh[/font]
MH-09-AM-8694
May 14, 2008 at 7:15 am
"Readable" means easier to understand. More logical.
The query is supposed to return 1 record.
Your version returns 23 records.
You changed the criteria.
My goal is to re-write it so that it is easier to follow the logic
without breaking the criteria logic.
May 14, 2008 at 7:24 am
[font="Verdana"]Then you should post some sample data and all the conditions you needs to apply as well. don't expect the answers on the fly.
Mahesh[/font]
MH-09-AM-8694
May 14, 2008 at 7:30 am
riga1966 (5/14/2008)
select * from scan
where boxid = 'RET0018438'
and
(((client in ('MCP','JNJ','PCP','PCR','HOS')) and
(client in (select clientcode from policy where deduct_ship = 'N') or (reason = 'RCL'))))
Can somebody change this query so that it is readable?
Readable is very subjective and encompasses logic and formatting. There are still people who think the old join syntax where the criteria is in the WHERE clause is more readable than standard ANSI INNER and OUTER join syntax. Without knowing the data and schemas I would just reformat the query to look like this (I only use parentheses when necessary):
[font="Courier New"]SELECT
*
FROM
SCHEMA.scan
WHERE
boxid = 'RET0018438' AND
client IN ('MCP','JNJ','PCP','PCR','HOS') AND
(
client IN (SELECT clientcode FROM SCHEMA.policy WHERE deduct_ship = 'N') OR
reason = 'RCL'
)
[/font]
or you could try this which may not match the logic correctly, but if it does I would find this much more readable:
[font="Courier New"]SELECT
*
FROM
SCHEMA.scan S INNER JOIN
SCHEMA.policy P ON
S.client = P.clientcode
WHERE
boxid = 'RET0018438' AND
client IN ('MCP','JNJ','PCP','PCR','HOS') AND
(P.deduct_ship = 'N' OR reason = 'RCL' )
[/font]
Note that I schema qualified the tables and I removed what I consider extraneous parentheses.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 14, 2008 at 9:12 am
Hi Jack,
I liked the second one
SELECT
*
FROM
SCHEMA.scan S INNER JOIN
SCHEMA.policy P ON
S.client = P.clientcode
WHERE
boxid = 'RET0018438' AND
client IN ('MCP','JNJ','PCP','PCR','HOS') AND
(P.deduct_ship = 'N' OR reason = 'RCL' )
I just hate too many paranthesis and "where in"
How do you post colorful code, Jack?
May 14, 2008 at 9:34 am
I use the SQL Prettifier. Someone mentioned it in another forum thread and I have used it since. It is an extra step, but I find it makes the code much easier to read. You could also do it all by hand if you wanted to using the IFCodes.
If the join query works logically then that is how I would go also. Pretty much anytime you have "where x in (Select y from z)" you can convert to a join (sometimes on a derived table) that is easier to read and understand.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply