Re: Conditional SQL Query

M.P.Dankoor wrote:> devil live wrote:>> how can I write a query to get right ingredients of a product basis on>>
production_nofield>>>> such as;>>>> first check production_no if product_tree_special table if not found>> then look at
templatetable...>>>> What are your suggestions?>>
 

I think M.P. Dankoor's suggestion is close but I believe the conditional 
part you are looking for would make it into this -

select  PRD.product_code      ,PRD.product_name      ,NULL::int            AS production_no
,PTT.stock_code_to_make_product     ,PTT.amount
 
from  product PRD    ,product_tree_template PTT
where PRD.product_code='searchcode'
AND PRD.product_code = casewhen (select production_no from product_tree_special ts    where
ts.product_code=PRD.product_code)is nullthen PTT.product_code else '' end
 
UNION
select  PRD.product_code      ,PRD.product_name      ,PTS.production_no      ,PTS.stock_code_to_make_product
,PTS.amount
from  product PRD    ,product_tree_special  PTS
where PRD.product_code='searchcode'
AND PRD.product_code = casewhen (select production_no from product_tree_special ts    where
ts.product_code=PRD.product_code)is not nullthen PTS.product_code else '' end
 
ORDER BY 1,2,4


So if we entered the following -

INSERT INTO product VALUES ('one','test one');
INSERT INTO product VALUES ('two','test two');
INSERT INTO product_tree_special VALUES (1,'one','special list',1.1);
INSERT INTO product_tree_template VALUES ('two','template parts',2.2);


change both WHERE clauses to PRD.product_code='two' you will get -

two    test two    <null>    template parts    2.2    

then change both WHERE clauses to PRD.product_code='one' you will get -

one    test one    1    special list    1.1    




-- 

Shane Ambler
pgSQL@007Marketing.com

Get Sheeky @ http://Sheeky.Biz