pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedConditional SQL query
10+ messages / 6 participants
[nested] [flat]
* Conditional SQL query
@ 2000-11-05 19:03 Indraneel Majumdar <indraneel@www.cdfd.org.in>
0 siblings, 0 replies; 10+ messages in thread
From: Indraneel Majumdar @ 2000-11-05 19:03 UTC (permalink / raw)
To: pgsql-sql
Hi,
how can I insert a record after checking whether one of the fields has a
certain value or not? eg:
if table(col1)='value' insert into table values (x,x,x)
I am using perl interface. Is there any way to define a function to take
table name as an argument? (eg: select * from $table) I tried but this
gave an error (so I'm using perl to pass the real tablename for each
query)
Any help will be great,
Indraneel
/************************************************************************.
# Indraneel Majumdar ¡ E-mail: indraneel@123india.com #
# Bioinformatics Unit (EMBNET node), ¡ URL: http://scorpius.iwarp.com #
# Centre for DNA Fingerprinting and Diagnostics, #
# Hyderabad, India - 500076 #
`************************************************************************/
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Conditional SQL query
@ 2000-11-06 09:07 Indraneel Majumdar <indraneel@www.cdfd.org.in>
2000-11-05 21:46 ` Re: Conditional SQL query Cristóvão B. B. Dalla Costa <cdallacosta@bigfoot.com>
0 siblings, 1 reply; 10+ messages in thread
From: Indraneel Majumdar @ 2000-11-06 09:07 UTC (permalink / raw)
To: marten@toppoint.de; +Cc: pgsql-sql
On Sun, 5 Nov 2000, Marten Feldtmann wrote:
> Indraneel Majumdar schrieb:
> >
> > Hi,
> >
> > how can I insert a record after checking whether one of the fields has a
> > certain value or not? eg:
> >
> > if table(col1)='value' insert into table values (x,x,x)
> >
> > I am using perl interface. Is there any way to define a function to take
> > table name as an argument? (eg: select * from $table) I tried but this
> > gave an error (so I'm using perl to pass the real tablename for each
> > query)
> >
>
> insert into table select ... where col1 = 'value'
>
this is working but I am inserting values directly and not by select. eg:
insert into table values(x,x,x)
and not
insert into table select * from table2
the former is not working.
\Indraneel
>
> MF
>
/************************************************************************.
# Indraneel Majumdar ¡ E-mail: indraneel@123india.com #
# Bioinformatics Unit (EMBNET node), ¡ URL: http://scorpius.iwarp.com #
# Centre for DNA Fingerprinting and Diagnostics, #
# Hyderabad, India - 500076 #
`************************************************************************/
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Conditional SQL query
2000-11-06 09:07 Re: Conditional SQL query Indraneel Majumdar <indraneel@www.cdfd.org.in>
@ 2000-11-05 21:46 ` Cristóvão B. B. Dalla Costa <cdallacosta@bigfoot.com>
2000-11-06 10:00 ` Re: Conditional SQL query Indraneel Majumdar <indraneel@www.cdfd.org.in>
0 siblings, 1 reply; 10+ messages in thread
From: Cristóvão B. B. Dalla Costa @ 2000-11-05 21:46 UTC (permalink / raw)
To: pgsql-sql
> > insert into table select ... where col1 = 'value'
> >
>
> this is working but I am inserting values directly and not by select. eg:
>
> insert into table values(x,x,x)
> and not
> insert into table select * from table2
>
insert into table select (x, x, x) where col1 = 'value'
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Conditional SQL query
2000-11-06 09:07 Re: Conditional SQL query Indraneel Majumdar <indraneel@www.cdfd.org.in>
2000-11-05 21:46 ` Re: Conditional SQL query Cristóvão B. B. Dalla Costa <cdallacosta@bigfoot.com>
@ 2000-11-06 10:00 ` Indraneel Majumdar <indraneel@www.cdfd.org.in>
2000-11-05 22:44 ` Re: Conditional SQL query Cristóvão B. B. Dalla Costa <cdallacosta@bigfoot.com>
2000-11-06 10:23 ` Re: Conditional SQL query Indraneel Majumdar <indraneel@www.cdfd.org.in>
0 siblings, 2 replies; 10+ messages in thread
From: Indraneel Majumdar @ 2000-11-06 10:00 UTC (permalink / raw)
To: Cristóvão B. B. Dalla Costa <cdallacosta@bigfoot.com>; +Cc: pgsql-sql
I finally got it to work. you can't use the parentheses. eg:
insert into table select x,x,x where col1 = 'value';
and not
insert into table select (x,x,x) where col1 = 'value';
the latter gives a
ERROR: parser: parse error at or near "where"
I am using PostgreSQL-7.0.2
Now I have one more problem. How do I insert only once if value does not
exist? eg:
insert into table once select x,x,x where not col1='value'
Thanks,
Indraneel
On Sun, 5 Nov 2000, Cristóvão B. B. Dalla Costa wrote:
> > > insert into table select ... where col1 = 'value'
> > >
> >
> > this is working but I am inserting values directly and not by select. eg:
> >
> > insert into table values(x,x,x)
> > and not
> > insert into table select * from table2
> >
>
> insert into table select (x, x, x) where col1 = 'value'
>
/************************************************************************.
# Indraneel Majumdar ¡ E-mail: indraneel@123india.com #
# Bioinformatics Unit (EMBNET node), ¡ URL: http://scorpius.iwarp.com #
# Centre for DNA Fingerprinting and Diagnostics, #
# Hyderabad, India - 500076 #
`************************************************************************/
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Conditional SQL query
2000-11-06 09:07 Re: Conditional SQL query Indraneel Majumdar <indraneel@www.cdfd.org.in>
2000-11-05 21:46 ` Re: Conditional SQL query Cristóvão B. B. Dalla Costa <cdallacosta@bigfoot.com>
2000-11-06 10:00 ` Re: Conditional SQL query Indraneel Majumdar <indraneel@www.cdfd.org.in>
@ 2000-11-05 22:44 ` Cristóvão B. B. Dalla Costa <cdallacosta@bigfoot.com>
1 sibling, 0 replies; 10+ messages in thread
From: Cristóvão B. B. Dalla Costa @ 2000-11-05 22:44 UTC (permalink / raw)
To: pgsql-sql
>
> Now I have one more problem. How do I insert only once if value does not
> exist? eg:
>
You should create a unique index on the appropriate fields. For example:
CREATE UNIQUE INDEX indexname ON table (field1, field2)
Then the insert will fail if this particular combination of the values of
(field1, field2) is already in the table.
Be careful if the insert is inside a transaction, tough. If it fails due to
non-uniqueness your transaction will be aborted (all further queries ignored
untill a COMMIT or ROLLBACK).
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Conditional SQL query
2000-11-06 09:07 Re: Conditional SQL query Indraneel Majumdar <indraneel@www.cdfd.org.in>
2000-11-05 21:46 ` Re: Conditional SQL query Cristóvão B. B. Dalla Costa <cdallacosta@bigfoot.com>
2000-11-06 10:00 ` Re: Conditional SQL query Indraneel Majumdar <indraneel@www.cdfd.org.in>
@ 2000-11-06 10:23 ` Indraneel Majumdar <indraneel@www.cdfd.org.in>
1 sibling, 0 replies; 10+ messages in thread
From: Indraneel Majumdar @ 2000-11-06 10:23 UTC (permalink / raw)
To: Cristóvão B. B. Dalla Costa <cdallacosta@bigfoot.com>; +Cc: pgsql-sql
OK, I solved my own problems (Congrats ;-) Sorry for bothering the list.
This is what I'm doing:
INSERT INTO table SELECT x,x,x where (select count(*) from table where
col1='value')=0;
any suggestions?
thanks,
Indraneel
On Mon, 6 Nov 2000, Indraneel Majumdar wrote:
> Now I have one more problem. How do I insert only once if value does not
> exist? eg:
>
> insert into table once select x,x,x where not col1='value'
>
/************************************************************************.
# Indraneel Majumdar ¡ E-mail: indraneel@123india.com #
# Bioinformatics Unit (EMBNET node), ¡ URL: http://scorpius.iwarp.com #
# Centre for DNA Fingerprinting and Diagnostics, #
# Hyderabad, India - 500076 #
`************************************************************************/
^ permalink raw reply [nested|flat] 10+ messages in thread
* Conditional SQL Query
@ 2007-01-11 21:34 devil live <definite_ocean@hotmail.com>
2007-01-11 22:01 ` Re: Conditional SQL Query M.P.Dankoor <m.p.dankoor@gmail.com>
2007-01-11 22:03 ` Re: Conditional SQL Query Chad Wagner <chad.wagner@gmail.com>
0 siblings, 2 replies; 10+ messages in thread
From: devil live @ 2007-01-11 21:34 UTC (permalink / raw)
To: pgsql-sql
I want to write sql statement about product, product_tree_special,
product_tree_template
product has
product_code varchar(20) PRIMARY KEY,
product_name varchar(20)
product_tree_template has
product_code varchar(20) PK,
stock_code_to_make_product(20) PK,
amout float4
product_tree_spcial has
production_no serial,
product_code varchar(20) PK,
stock_code_to_make_product(20) PK,
amout float4
NOW : product_tree_template table is the default table for production
ingredients of the PRODUCT
but sometimes my customer using special product_tree table for some
production_no
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?
PS: I do not wanna write PL functions for this...
Thanks
_________________________________________________________________
Hava durumunu bizden ögrenin ve evden öyle çikin!
http://www.msn.com.tr/havadurumu/
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Conditional SQL Query
2007-01-11 21:34 Conditional SQL Query devil live <definite_ocean@hotmail.com>
@ 2007-01-11 22:01 ` M.P.Dankoor <m.p.dankoor@gmail.com>
2007-01-11 23:35 ` Re: Conditional SQL Query Shane Ambler <pgsql@007Marketing.com>
1 sibling, 1 reply; 10+ messages in thread
From: M.P.Dankoor @ 2007-01-11 22:01 UTC (permalink / raw)
To: devil live <definite_ocean@hotmail.com>; +Cc: pgsql-sql
I think that the simplest solution is to use an union e.g:
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 1 = 1
AND PRD.product_code = PTT.product_code
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 1 = 1
AND PRD.product_code = PTS.product_code
ORDER BY 1,2,4
I've added an order by the columns 1,2 and 4 (just guessing),
note that this particular solution uses positional ordering, i.e no
column names.
Mario
devil live wrote:
>
> I want to write sql statement about product, product_tree_special,
> product_tree_template
>
> product has
> product_code varchar(20) PRIMARY KEY,
> product_name varchar(20)
>
> product_tree_template has
> product_code varchar(20) PK,
> stock_code_to_make_product(20) PK,
> amout float4
>
> product_tree_spcial has
> production_no serial,
> product_code varchar(20) PK,
> stock_code_to_make_product(20) PK,
> amout float4
>
>
>
> NOW : product_tree_template table is the default table for production
> ingredients of the PRODUCT
>
> but sometimes my customer using special product_tree table for some
> production_no
>
> 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?
>
>
>
> PS: I do not wanna write PL functions for this...
>
> Thanks
>
> _________________________________________________________________
> Hava durumunu bizden ögrenin ve evden öyle çikin!
> http://www.msn.com.tr/havadurumu/
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Conditional SQL Query
2007-01-11 21:34 Conditional SQL Query devil live <definite_ocean@hotmail.com>
2007-01-11 22:01 ` Re: Conditional SQL Query M.P.Dankoor <m.p.dankoor@gmail.com>
@ 2007-01-11 23:35 ` Shane Ambler <pgsql@007Marketing.com>
0 siblings, 0 replies; 10+ messages in thread
From: Shane Ambler @ 2007-01-11 23:35 UTC (permalink / raw)
To: M.P.Dankoor <m.p.dankoor@gmail.com>; +Cc: devil live <definite_ocean@hotmail.com>; 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@007Marketing.com
Get Sheeky @ http://Sheeky.Biz
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Conditional SQL Query
2007-01-11 21:34 Conditional SQL Query devil live <definite_ocean@hotmail.com>
@ 2007-01-11 22:03 ` Chad Wagner <chad.wagner@gmail.com>
1 sibling, 0 replies; 10+ messages in thread
From: Chad Wagner @ 2007-01-11 22:03 UTC (permalink / raw)
To: devil live <definite_ocean@hotmail.com>; +Cc: pgsql-sql
On 1/11/07, devil live <definite_ocean@hotmail.com> wrote:
>
> NOW : product_tree_template table is the default table for production
> ingredients of the PRODUCT
>
> but sometimes my customer using special product_tree table for some
> production_no
>
> 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?
It would be helpful if you could show the create table's, sample data, and
expected results for each case.
One thing to point out here is you should define your constraints, if a
column is not meant to be nullable then it should be NOT NULL.
One of the ways to achieve this is to outer join your "optional" data table,
and use the CASE (or perhaps NULLIF) to conditionally swap the result
columns from the optional or default data tables. For example:
select p.product_code
,pt.stock_code_to_make_product
,case when ps.product_code is not null and
ps.stock_code_to_make_product is not null
then ps.amount
else pt.amount
end AS amount
from product p
join product_tree_template pt on p.product_code = pt.product_code
left join product_tree_special ps on pt.product_code = ps.product_code
and pt.stock_code_to_make_product = ps.stock_code_to_make_product;
--
Chad
http://www.postgresqlforums.com/
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2007-01-11 23:35 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-11-05 19:03 Conditional SQL query Indraneel Majumdar <indraneel@www.cdfd.org.in>
2000-11-06 09:07 Re: Conditional SQL query Indraneel Majumdar <indraneel@www.cdfd.org.in>
2000-11-05 21:46 ` Cristóvão B. B. Dalla Costa <cdallacosta@bigfoot.com>
2000-11-06 10:00 ` Indraneel Majumdar <indraneel@www.cdfd.org.in>
2000-11-05 22:44 ` Cristóvão B. B. Dalla Costa <cdallacosta@bigfoot.com>
2000-11-06 10:23 ` Indraneel Majumdar <indraneel@www.cdfd.org.in>
2007-01-11 21:34 Conditional SQL Query devil live <definite_ocean@hotmail.com>
2007-01-11 22:01 ` Re: Conditional SQL Query M.P.Dankoor <m.p.dankoor@gmail.com>
2007-01-11 23:35 ` Re: Conditional SQL Query Shane Ambler <pgsql@007Marketing.com>
2007-01-11 22:03 ` Re: Conditional SQL Query Chad Wagner <chad.wagner@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox