Skip site navigation (1) Skip section navigation (2)

Re: Conditional SQL Query

From: Shane Ambler <pgsql(at)007Marketing(dot)com>
To: "M(dot)P(dot)Dankoor" <m(dot)p(dot)dankoor(at)gmail(dot)com>
Cc: devil live <definite_ocean(at)hotmail(dot)com>, pgsql-sql(at)postgresql(dot)org
Subject: Re: Conditional SQL Query
Date: 2007-01-11 23:35:25
Message-ID: 45A6C9BD.3020002@007Marketing.com (view raw or flat)
Thread:
Lists: pgsql-sql
M.P.Dankoor wrote:
 > devil live wrote:
 >> how can I write a query to get right ingredients of a product basis on
 >> production_no field
 >>
 >> such as;
 >>
 >> first check production_no if product_tree_special table if not found
 >> then look at template table...
 >>
 >> 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 = case
	when (select production_no from product_tree_special ts
		where ts.product_code=PRD.product_code) is null
	then 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 = case
	when (select production_no from product_tree_special ts
		where ts.product_code=PRD.product_code) is not null
	then 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(at)007Marketing(dot)com

Get Sheeky @ http://Sheeky.Biz

In response to

pgsql-sql by date

Next:From: Mario BehringDate: 2007-01-12 17:45:28
Subject: deleting records from a table
Previous:From: Chad WagnerDate: 2007-01-11 22:03:32
Subject: Re: Conditional SQL Query

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group