Re: [SQL] i need solution to this problem
On mið, 2006-06-28 at 11:15 +0530, Penchalaiah P. wrote: > I have tables like 1) emp_table (personal_no integer (foreign key), > cdacno varchar (primary key),name varchar); > 2) Rank_table (rank_id varchar (primary key), rank_name varchar); > 3) Rank_date_table (rank_id (foreign key), rank_date date); > 4) Unit_table (unit_id varchar (primarykey), unit_name varchar); > 5) Personal_table (per_no varchar (primary key), pername varchar); > My query is ….if I give cdacno I have to get per_no from > personal_table.. With this I have to display rank_name from > rank_table ,name from emp_table, unit_name from unit_master.. it is not clear what the relationships are between the tables. for example what is the foreign key to unit_table? how does the rank connect to emp_table or personal_table? gnari > ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] i need solution to this problem
> > I have tables like 1) emp_table (personal_no integer (foreign key), > > cdacno varchar (primary key),name varchar); > > > 2) Rank_table (rank_id varchar (primary key), rank_name varchar); > > > 3) Rank_date_table (rank_id (foreign key), rank_date date); > > > 4) Unit_table (unit_id varchar (primarykey), unit_name varchar); > > > 5) Personal_table (per_no varchar (primary key), pername varchar); > > > My query is â¦.if I give cdacno I have to get per_no from > > personal_table.. With this I have to display rank_name from > > rank_table ,name from emp_table, unit_name from unit_master.. > > it is not clear what the relationships are between the tables. for > example what is the foreign key to unit_table? > > how does the rank connect to emp_table or personal_table? yes. in addition to this, it seems that emp_table references personal_table on personal_no = per_no. But it is not clear how this is the case when personal_no is an integer and per_no is a varchar. Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
RES: [SQL] Joins between int and int[]
Thanks Mantzios, your answer helped a lot! But I have a lot of multi column foreign keys! Any other ideia?? Thanks in advance! -Mensagem original- De: Achilleus Mantzios [mailto:[EMAIL PROTECTED] Enviada em: quarta-feira, 28 de junho de 2006 04:01 Para: Rodrigo Sakai Assunto: Re: [SQL] Joins between int and int[] O Rodrigo Sakai Ýãñáøå óôéò Jun 27, 2006 : > Hi, > > > > I want to use the system catalog tables/views to query some things, and > one of the queries I have to do is joining pg_attribute and pg_constraint to > know what constraint belongs to which table.attribute. My question is how > can I write the join clause between a int (pg_attribute.attnum) and int[ ] > (pg_constraint.conkey). Are you having tables with multi column foreign keys? e.g. (a,b) REFERENCES partable(para,parb). If not then join with pg_constraint.conkey[1], if yes then the problem becomes a little less trivial. > > > > > > The query is: > > > > select relname, attname, attnotnull, atthasdef > > from pg_class as pc > > inner join pg_attribute as pa > > on pc.oid=pa.attrelid > > inner join pg_constraint pcons > > on pc.oid=pcons.conrelid > > and pa.attnum = pcons.conkey ---> the problem > > > > > > Thanks!!! > > > > -- -Achilleus ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: RES: [SQL] Joins between int and int[]
O Rodrigo Sakai έγραψε στις Jun 28, 2006 : > Thanks Mantzios, your answer helped a lot! But I have a lot of multi column > foreign keys! Any other ideia?? > You must use/write a function that takes as an argument an array and returns the elements of this array as a set. I dont recall if some function like that exists in contrib but you could search in the archives. Also if my memory serves well there was a similar talk in th -sql list some months ago. Also keep in mind that if your queries are standardized then you can write a program in C/perl/java/php/... that does what you want. If your queries are ad-hoc then go the first approach. > Thanks in advance! > > > -Mensagem original- > De: Achilleus Mantzios [mailto:[EMAIL PROTECTED] > Enviada em: quarta-feira, 28 de junho de 2006 04:01 > Para: Rodrigo Sakai > Assunto: Re: [SQL] Joins between int and int[] > > O Rodrigo Sakai έγραψε στις Jun 27, 2006 : > > > Hi, > > > > > > > > I want to use the system catalog tables/views to query some things, and > > one of the queries I have to do is joining pg_attribute and pg_constraint > to > > know what constraint belongs to which table.attribute. My question is how > > can I write the join clause between a int (pg_attribute.attnum) and int[ ] > > (pg_constraint.conkey). > > Are you having tables with multi column foreign keys? > e.g. (a,b) REFERENCES partable(para,parb). > > If not then join with pg_constraint.conkey[1], > if yes then the problem becomes a little less trivial. > > > > > > > > > > > > > The query is: > > > > > > > > select relname, attname, attnotnull, atthasdef > > > > from pg_class as pc > > > > inner join pg_attribute as pa > > > > on pc.oid=pa.attrelid > > > > inner join pg_constraint pcons > > > > on pc.oid=pcons.conrelid > > > > and pa.attnum = pcons.conkey ---> the problem > > > > > > > > > > > > Thanks!!! > > > > > > > > > > -- -Achilleus ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[SQL] "CASE" is not a variable
Hi All, The following is a section of code inside an SQL function. When I attempt to run it I get the error message '"CASE" is not a variable'. If I split this into two queries (one for each variable) it works fine. Obviously I have a work around but I would like to understand what I am doing wrong. TIA SELECT tbl_item_bom.so_subline INTO v_so_subline, CASE WHEN tbl_mesh.mesh_type = 'square' THEN ( CASE WHEN tbl_mesh.unit_of_measure = 'in' THEN tbl_mesh.mesh_size WHEN tbl_mesh.unit_of_measure = 'ft' THEN 12.0* tbl_mesh.mesh_size WHEN tbl_mesh.unit_of_measure = 'mm' THEN 25.4* tbl_mesh.mesh_size WHEN tbl_mesh.unit_of_measure = 'cm' THEN 2.54 * tbl_mesh.mesh_size WHEN tbl_mesh.unit_of_measure = 'm' THEN 0.0254 * tbl_mesh.mesh_size ELSE 0 END ) WHEN tbl_mesh.mesh_type = 'diamond' THEN ( CASE WHEN tbl_mesh.unit_of_measure = 'in' THEN tbl_mesh.mesh_size / 2.0 WHEN tbl_mesh.unit_of_measure = 'ft' THEN 12.0* tbl_mesh.mesh_size / 2.0 WHEN tbl_mesh.unit_of_measure = 'mm' THEN 25.4* tbl_mesh.mesh_size / 2.0 WHEN tbl_mesh.unit_of_measure = 'cm' THEN 2.54 * tbl_mesh.mesh_size / 2.0 WHEN tbl_mesh.unit_of_measure = 'm' THEN 0.0254 * tbl_mesh.mesh_size / 2.0 ELSE 0 END ) ELSE 0 END INTO v_mesh_size FROM sales_order.tbl_item_bom LEFT JOIN peachtree.tbl_mesh ON tbl_item_bom.item_id = tbl_mesh.item_id WHERE tbl_item_bom.so_number = rcrd_line.so_number AND tbl_item_bom.so_line = rcrd_line.so_line AND tbl_item_bom.component_type = 'net'; Kind Regards, Keith ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] "CASE" is not a variable
"Keith Worthington" <[EMAIL PROTECTED]> writes: > The following is a section of code inside an SQL function. SQL, or plpgsql? It looks to me like misuse of the plpgsql INTO clause (there can be only one). regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] generate_series with left join
Greetings. I'm having some difficulties with my first use of the generate_series function. Situation: cause| integer date | timestamp(2) without time zone cause | date --++---+ 1 | 2006-03-23 15:07:53.63 | 2 | 2006-02-02 12:13:23.11 | 2 | 2006-11-12 16:43:11.45 | 1 | 2005-03-13 18:34:44.13 | 3 | 2006-01-23 11:24:41.31 | (etc) What i need to do, is to count the 'cause' column for the values '1' and '2', and group them by year, using left joins in order to also have the serialized years with empty values in the output. My needed output for a series of (2005,2007) would be: year | one | two --+--+-- 2005 |1 |0 2006 |1 |2 2007 |0 |0 I have tried something like #select s, (select count(cause) from mytable where cause=1 ) as one, COUNT (cause) as two from generate_series(2006,2009) AS s(d) left JOIN mytable o ON (substr(o.date,1,4) = s.d and cause=2) GROUP BY s.d ORDER BY 1; which obviously is wrong, because of the results: s | one | two --+--+-- 2006 | 3769 | 1658 2007 | 3769 |0 2008 | 3769 |0 2009 | 3769 |0 As far as the 'two', the left join was successful, however i can not find a way to join the 'one'. The output value is correct, but the result shown should be only for the year 2006, not for all the values of the series. Maybe i've looked at it TOO much or maybe i'm completely failing to find a working logic. Any suggestions? Any and all help is humbly appreciated. \\pb -- This message has been scanned for viruses and dangerous content at MsgLab.com and is believed to be clean. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] "CASE" is not a variable
> > "Keith Worthington" <[EMAIL PROTECTED]> writes: > > The following is a section of code inside an SQL function. > > On Wed, 28 Jun 2006 12:16:29 -0400, Tom Lane wrote > SQL, or plpgsql? It looks to me like misuse of the plpgsql INTO clause > (there can be only one). > > regards, tom lane plpgsql This is part of a function inside a v8.0.2 database. I didn't realize that the INTO clause was only limited to one instance. I was trying to accomplish SELECT col_a INTO var1, col_b INTO var2, col_c INTO var3, ... FROM foo WHERE fobar; Kind Regards, Keith ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] "CASE" is not a variable
Keith Worthington wrote: "Keith Worthington" <[EMAIL PROTECTED]> writes: The following is a section of code inside an SQL function. On Wed, 28 Jun 2006 12:16:29 -0400, Tom Lane wrote SQL, or plpgsql? It looks to me like misuse of the plpgsql INTO clause (there can be only one). regards, tom lane plpgsql This is part of a function inside a v8.0.2 database. I didn't realize that the INTO clause was only limited to one instance. I was trying to accomplish SELECT col_a INTO var1, col_b INTO var2, col_c INTO var3, ... FROM foo WHERE fobar; Kind Regards, Keith try it like select col_a,col_b,col_c INTO va1,var2,var3 not sure if 8.0.2 allows you to do that, however. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] generate_series with left join
How about one of these two:select year_list.year, count(one.*), count(two.*)from ( select years from generate_series(2006,2009) as years) year_listleft outer join mytable as one on (
date_part('year', one.date) = year_list.years and one.cause = 1)left outer join mytable as two on ( date_part('year', two.date) = year_list.years and two.cause = 2)group by
year_list.year;select year_list.year, mytable.cause, count(mytable.*)from ( select years from generate_series(2006,2009) as years) year_listleft outer join mytable on (
date_part('year', mytable.date) = year_list.years)group by year_list.year, mytable.cause;I think one of the problems many people have is the writing of their SQL in paragraph form. It makes the SQL really hard to read and even harder to understand and debug. Formatting your SQL like I did above may make it easier to see what is wrong.
-Aaron BonoOn 6/28/06, Pedro B. <[EMAIL PROTECTED]> wrote:
Greetings.I'm having some difficulties with my first use of the generate_seriesfunction.Situation: cause| integer date | timestamp(2) without time zonecause | date
--++---+1 | 2006-03-23 15:07:53.63 |2 | 2006-02-02 12:13:23.11 |2 | 2006-11-12 16:43:11.45 |1 | 2005-03-13 18:34:44.13 |3 | 2006-01-23 11:24:41.31 |(etc)
What i need to do, is to count the 'cause' column for the values '1' and'2', and group them by year, using left joins in order to also have theserialized years with empty values in the output.My needed output for a series of (2005,2007) would be:
year | one | two--+--+-- 2005 |1 |0 2006 |1 |2 2007 |0 |0I have tried something like#select s, (select count(cause) from mytable where cause=1 ) as one,
COUNT (cause) as two from generate_series(2006,2009) AS s(d) left JOINmytable o ON (substr(o.date,1,4) = s.d and cause=2) GROUP BY s.d ORDERBY 1;which obviously is wrong, because of the results: s | one | two
--+--+-- 2006 | 3769 | 1658 2007 | 3769 |0 2008 | 3769 |0 2009 | 3769 |0As far as the 'two', the left join was successful, however i can notfind a way to join the 'one'. The output value is correct, but the
result shown should be only for the year 2006, not for all the values ofthe series.Maybe i've looked at it TOO much or maybe i'm completely failing to finda working logic.Any suggestions?Any and all help is humbly appreciated.
\\pb
Re: [SQL] generate_series with left join
On Wed, 2006-06-28 at 13:34 -0500, Aaron Bono wrote:
> select
> year_list.year,
> count(one.*),
> count(two.*)
> from (
> select years
> from generate_series(2006,2009) as years
> ) year_list
> left outer join mytable as one on (
> date_part('year', one.date) = year_list.years
> and one.cause = 1
> )
> left outer join mytable as two on (
> date_part('year', two.date) = year_list.years
> and two.cause = 2
> )
> group by
> year_list.year
> ;
>
>
> select
> year_list.year,
> mytable.cause,
> count(mytable.*)
> from (
> select years
> from generate_series(2006,2009) as years
> ) year_list
> left outer join mytable on (
> date_part('year', mytable.date) = year_list.years
> )
> group by
> year_list.year,
> mytable.cause
> ;
>
Aaron,
Thank you so much for your reply.
However, the 2 examples you provided have "weird" outputs:
The first:
years | count | count
---+-+-
2009 | 0 | 0
2008 | 0 | 0
2007 | 0 | 0
2006 | 7802080 | 7802080
(4 rows)
Time: 87110.753 ms << yay.
The second:
years | cause | count
---+-+---
2009 | | 0
2008 | | 0
2007 | | 0
2006 | 6 | 1
2006 | 1 | 4030
2006 | 2 | 1936
2006 | 3 | 4078
2006 | 100 | 3159
2006 | 98 | 2659
2006 | 99 | 2549
My need is really to only group the counts of where cause=1 and cause=2
for each year, none of the others.
> I think one of the problems many people have is the writing of their
> SQL in paragraph form. It makes the SQL really hard to read and even
> harder to understand and debug. Formatting your SQL like I did above
> may make it easier to see what is wrong.
Indeed. Note taken, i'll improve my formatting.
\\pb
--
This message has been scanned for viruses and
dangerous content at MsgLab.com and is
believed to be clean.
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [SQL] generate_series with left join
Sorry, I think I see the mistake - it is getting all the rows for 1 and all the rows for 2 and joining them. Try splitting up the query into two inner queries like so:select one_list.year, one_list.one_count,
two_list.two_countFROM( select year_list.year, count(one.*) as one_count from ( select years from generate_series(2006,2009) as years ) year_list
left outer join mytable as one on ( date_part('year', one.date) = year_list.years and one.cause = 1 ) group by year_list.year) one_list,( select year_list.year,
count(two.*) as two_count from ( select years from generate_series(2006,2009) as years ) year_list left outer join mytable as two on ( date_part('year', two.date
) = year_list.years and two.cause = 2 ) group by year_list.year) two_listWHERE one_list.year = two_list.year;On 6/28/06,
Pedro B. <[EMAIL PROTECTED]> wrote:
On Wed, 2006-06-28 at 13:34 -0500, Aaron Bono wrote:> select> year_list.year,> count(one.*),> count(two.*)> from (> select years> from generate_series(2006,2009) as years
> ) year_list> left outer join mytable as one on (> date_part('year', one.date) = year_list.years> and one.cause = 1> )> left outer join mytable as two on (> date_part('year',
two.date) = year_list.years> and two.cause = 2> )> group by> year_list.year> ;>>> select> year_list.year,> mytable.cause,> count(mytable.*)
> from (> select years> from generate_series(2006,2009) as years> ) year_list> left outer join mytable on (> date_part('year', mytable.date) = year_list.years> )
> group by> year_list.year,> mytable.cause> ;>Aaron,Thank you so much for your reply.However, the 2 examples you provided have "weird" outputs:The first:
years | count | count---+-+- 2009 | 0 | 0 2008 | 0 | 0 2007 | 0 | 0 2006 | 7802080 | 7802080(4 rows)Time: 87110.753 ms << yay.
The second: years | cause | count---+-+--- 2009 | | 0 2008 | | 0 2007 | | 0 2006 | 6 | 1 2006 | 1 | 4030
2006 | 2 | 1936 2006 | 3 | 4078 2006 | 100 | 3159 2006 | 98 | 2659 2006 | 99 | 2549My need is really to only group the counts of where cause=1 and cause=2
for each year, none of the others.> I think one of the problems many people have is the writing of their> SQL in paragraph form. It makes the SQL really hard to read and even> harder to understand and debug. Formatting your SQL like I did above
> may make it easier to see what is wrong.Indeed. Note taken, i'll improve my formatting.\\pb
Re: [SQL] generate_series with left join
This should work too:select year_list.year, one_list.one_count, two_list.two_countFROM ( select years from generate_series(2006,2009) as years) year_listleft outer join (
select date_part('year', one.date) as one_year, count(one.*) as one_count from mytable as one where one.cause = 1 group by date_part('year', one.date)) one_list on (year_list.years = one_year)
left outer join ( select date_part('year', two.date) as two_year, count(two.*) as two_count from mytable as two where two.cause = 2 group by date_part('year',
two.date)) two_list on (year_list.years = two_year);On 6/28/06, Aaron Bono <[EMAIL PROTECTED]
> wrote:Sorry, I think I see the mistake - it is getting all the rows for 1 and all the rows for 2 and joining them. Try splitting up the query into two inner queries like so:
select one_list.year, one_list.one_count,
two_list.two_countFROM( select year_list.year, count(one.*) as one_count from ( select years from generate_series(2006,2009) as years
) year_list
left outer join mytable as one on ( date_part('year', one.date) = year_list.years and one.cause = 1 ) group by year_list.year
) one_list,( select year_list.year,
count(two.*) as two_count from ( select years from generate_series(2006,2009) as years ) year_list left outer join mytable as two on (
date_part('year', two.date
) = year_list.years and two.cause = 2 ) group by year_list.year) two_listWHERE one_list.year = two_list.year;
On 6/28/06,
Pedro B. <[EMAIL PROTECTED]> wrote:
On Wed, 2006-06-28 at 13:34 -0500, Aaron Bono wrote:> select> year_list.year,> count(one.*),> count(two.*)> from (> select years> from generate_series(2006,2009) as years
> ) year_list> left outer join mytable as one on (> date_part('year', one.date) = year_list.years> and one.cause = 1> )> left outer join mytable as two on (> date_part('year',
two.date) = year_list.years> and two.cause = 2> )> group by> year_list.year> ;>>> select> year_list.year,> mytable.cause,> count(mytable.*)
> from (> select years> from generate_series(2006,2009) as years> ) year_list> left outer join mytable on (> date_part('year', mytable.date) = year_list.years> )
> group by> year_list.year,> mytable.cause> ;>Aaron,Thank you so much for your reply.However, the 2 examples you provided have "weird" outputs:The first:
years | count | count---+-+- 2009 | 0 | 0 2008 | 0 | 0 2007 | 0 | 0 2006 | 7802080 | 7802080(4 rows)Time: 87110.753 ms << yay.
The second: years | cause | count---+-+--- 2009 | | 0 2008 | | 0 2007 | | 0 2006 | 6 | 1 2006 | 1 | 4030
2006 | 2 | 1936 2006 | 3 | 4078 2006 | 100 | 3159 2006 | 98 | 2659 2006 | 99 | 2549My need is really to only group the counts of where cause=1 and cause=2
for each year, none of the others.> I think one of the problems many people have is the writing of their> SQL in paragraph form. It makes the SQL really hard to read and even> harder to understand and debug. Formatting your SQL like I did above
> may make it easier to see what is wrong.Indeed. Note taken, i'll improve my formatting.\\pb
Re: [SQL] generate_series with left join
On Wed, 2006-06-28 at 15:16 -0500, Aaron Bono wrote:
> This should work too:
>
> select
> year_list.year,
> one_list.one_count,
> two_list.two_count
> FROM (
> select years
> from generate_series(2006,2009) as years
> ) year_list
> left outer join (
> select
> date_part('year', one.date) as one_year,
> count(one.*) as one_count
> from mytable as one
> where one.cause = 1
> group by
> date_part('year', one.date)
> ) one_list on (year_list.years = one_year)
> left outer join (
> select
> date_part('year', two.date) as two_year,
> count(two.*) as two_count
> from mytable as two
> where two.cause = 2
> group by
> date_part('year', two.date)
> ) two_list on (year_list.years = two_year)
> ;
Aaron,
I confess i will take some time to digest the amazing code you just
sent, but in the meantime, let me tell you right away that both work
just as i needed.
I will stop pulling my hairs now.
Thank you so much.
\\pb
--
This message has been scanned for viruses and
dangerous content at MsgLab.com and is
believed to be clean.
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
[SQL] SELECT Aggregate
Hi all,
I have two tables which are storing all our sales orders /
invoices as below. sales_order.trans_no and soh_product.soh_num are the common
columns. This is PostgreSQL 8.1.4 (ie, the latest release)
We have some issues that I’ve been able to identify
using this SELECT:
SELECT trans_no,
customer,
date_placed,
date_complete,
date_printed,
ord_type,
ord_status,
customer_reference,
salesman,
parent_order,
child_order,
order_number
FROM sales_orders
WHERE (trans_no Like
'8%' AND order_number Like '8%')
OR (trans_no
Like '9%' AND order_number Like '8%')
OR (trans_no
Like '8%' AND order_number Like '9%')
OR (trans_no
Like '9%' AND order_number Like '9%')
AND (warehouse='M')
AND (date_placed
> (current_date + ('12 months ago'::interval)))
ORDER BY trans_no DESC
But I want to add in a wholesale value of each order –
SUM(soh_product.sell_price) – How would be best to do this? Would it be
easiest to create a function to accept the trans_no then do a SELECT on
soh_product and return that value?
Thanks,
-p
I’ve tried to do this but Postgres complains about
having to include all the other columns in either an aggregate or the GROUP BY.
SELECT trans_no,
customer,
date_placed,
date_complete,
date_printed,
ord_type,
ord_status,
SUM(soh_product.sell_price),
customer_reference,
salesman,
parent_order,
child_order,
order_number
FROM sales_orders,
soh_product
WHERE (trans_no Like
'8%' AND order_number Like '8%')
OR (trans_no
Like '9%' AND order_number Like '8%')
OR (trans_no
Like '8%' AND order_number Like '9%')
OR (trans_no
Like '9%' AND order_number Like '9%')
AND (warehouse='M')
AND (sales_orders.trans_no
= soh_product.soh_num)
AND (date_placed
> (current_date + ('12 months ago'::interval)))
GROUP BY soh_product.soh_num
ORDER BY trans_no DESC
CREATE TABLE sales_orders
(
trans_no varchar(6) NOT NULL,
customer varchar(6),
date_placed date,
date_complete date,
date_printed date,
ord_type varchar(1),
ord_status varchar(1),
discount float8,
customer_reference text,
warehouse varchar(3),
salesman varchar(3),
username text,
ordered_value float8 DEFAULT 0,
supplied_value float8 DEFAULT 0,
ordered_qty int8,
supplied_qty int8 DEFAULT 0,
frieght float8 DEFAULT 0,
delivery_instructions text,
parent_order varchar(6),
child_order varchar(6),
apply_to_order varchar(6),
fo_release date,
order_number varchar(6),
orig_fo_number varchar(6),
CONSTRAINT soh_pkey PRIMARY KEY (trans_no)
)
CREATE TABLE soh_product
(
soh_num varchar(6) NOT NULL,
prod_code varchar(6) NOT NULL,
qty_ordered numeric(8),
qty_supplied numeric(8),
cost_price numeric(10,2),
sell_price numeric(10,2),
sales_tax numeric(10,2),
discount numeric(10,2),
cost_gl varchar(5),
if_committed varchar(1)
)
***Confidentiality and Privilege Notice***
The material contained in this message is privileged and confidential to
the addressee. If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.
Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments
Re: [SQL] SELECT Aggregate
I would recommend against using a function. If you are selecting a large number of rows, the function will run for each row returned and will have to do a select for each row. So if you get 1000 rows returned from your query, you will end up with 1001 select statements for your one query.
Assuming trans_no is your primary key (or at least unique) then a group by on all columns in the select EXCEPT sale_price should do the trick:
SELECT trans_no,
customer,
date_placed,
date_complete,
date_printed,
ord_type,
ord_status,
SUM(soh_product.sell_price),
customer_reference,
salesman,
parent_order,
child_order,
order_number
FROM sales_orders,
soh_product
WHERE (trans_no Like
'8%' AND order_number Like '8%')
OR (trans_no
Like '9%' AND order_number Like '8%')
OR (trans_no
Like '8%' AND order_number Like '9%')
OR (trans_no
Like '9%' AND order_number Like '9%')
AND (warehouse='M')
AND (sales_orders.trans_no
= soh_product.soh_num)
AND (date_placed
> (current_date + ('12 months ago'::interval)))
GROUP BY trans_no,
customer,
date_placed,
date_complete,
date_printed,
ord_type,
ord_status,
customer_reference,
salesman,
parent_order,
child_order,
order_number
ORDER BY trans_no DESCOn 6/28/06, Phillip Smith <
[EMAIL PROTECTED]> wrote:
Hi all,
I have two tables which are storing all our sales orders /
invoices as below. sales_order.trans_no and soh_product.soh_num are the common
columns. This is PostgreSQL 8.1.4 (ie, the latest release)
We have some issues that I've been able to identify
using this SELECT:
SELECT trans_no,
customer,
date_placed,
date_complete,
date_printed,
ord_type,
ord_status,
customer_reference,
salesman,
parent_order,
child_order,
order_number
FROM sales_orders
WHERE (trans_no Like
'8%' AND order_number Like '8%')
OR (trans_no
Like '9%' AND order_number Like '8%')
OR (trans_no
Like '8%' AND order_number Like '9%')
OR (trans_no
Like '9%' AND order_number Like '9%')
AND (warehouse='M')
AND (date_placed
> (current_date + ('12 months ago'::interval)))
ORDER BY trans_no DESC
But I want to add in a wholesale value of each order –
SUM(soh_product.sell_price) – How would be best to do this? Would it be
easiest to create a function to accept the trans_no then do a SELECT on
soh_product and return that value?
Thanks,
-p
I've tried to do this but Postgres complains about
having to include all the other columns in either an aggregate or the GROUP BY.
SELECT trans_no,
customer,
date_placed,
date_complete,
date_printed,
ord_type,
ord_status,
SUM(soh_product.sell_price),
customer_reference,
salesman,
parent_order,
child_order,
order_number
FROM sales_orders,
soh_product
WHERE (trans_no Like
'8%' AND order_number Like '8%')
OR (trans_no
Like '9%' AND order_number Like '8%')
OR (trans_no
Like '8%' AND order_number Like '9%')
OR (trans_no
Like '9%' AND order_number Like '9%')
AND (warehouse='M')
AND (sales_orders.trans_no
= soh_product.soh_num)
AND (date_placed
> (current_date + ('12 months ago'::interval)))
GROUP BY soh_product.soh_num
ORDER BY trans_no DESC
CREATE TABLE sales_orders
(
trans_no varchar(6) NOT NULL,
customer varchar(6),
date_placed date,
date_complete date,
date_printed date,
ord_type varchar(1),
ord_status varchar(1),
discount float8,
customer_reference text,
warehouse varchar(3),
salesman varchar(3),
username text,
ordered_value float8 DEFAULT 0,
supplied_value float8 DEFAULT 0,
ordered_qty int8,
supplied_qty int8 DEFAULT 0,
frieght float8 DEFAULT 0,
delivery_instructions text,
parent_order varchar(6),
child_order varchar(6),
apply_to_order varchar(6),
fo_release date,
order_number varchar(6),
orig_fo_number varchar(6),
CONSTRAINT soh_pkey PRIMARY KEY (trans_no)
)
CREATE TABLE soh_product
(
soh_num varchar(6) NOT NULL,
prod_code varchar(6) NOT NULL,
qty_ordered numeric(8),
qty_supplied numeric(8),
cost_price numeric(10,2),
sell_price numeric(10,2),
sales_tax numeric(10,2),
discount numeric(10,2),
cost_gl varchar(5),
if_committed varchar(1)
)
Re: [SQL] SELECT Aggregate
> SELECT trans_no,
> customer,
> date_placed,
> date_complete,
> date_printed,
> ord_type,
> ord_status,
select (
SUM(sell_price)
from soh_product
where sales_orders.trans_no = soh_product.soh_num
) as transact_sum,
> customer_reference,
> salesman,
> parent_order,
> child_order,
> order_number
> FROMsales_orders
> WHERE (trans_no Like '8%' AND order_number Like '8%')
> OR (trans_no Like '9%' AND order_number Like '8%')
> OR (trans_no Like '8%' AND order_number Like '9%')
> OR (trans_no Like '9%' AND order_number Like '9%')
> AND(warehouse='M')
> AND(date_placed > (current_date + ('12 months ago'::interval)))
> ORDER BY trans_no DESC
I am pretty new to SQL. But while reading a book written by an author
recommended on this list,I
can suggest a possible solution that I've seen. It might work for your
problem. Of course, I
haven't tested anything like this and don't know if PostgreSQL supports it.
Just be sure that trans_no is unique in the returned query.
Regards,
Richard Broersma Jr.
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [SQL] SELECT Aggregate
> > SELECT trans_no,
> > customer,
> > date_placed,
> > date_complete,
> > date_printed,
> > ord_type,
> > ord_status,
(select SUM(sell_price) -- this syntax working for me. see
below
fromsoh_product
where sales_orders.trans_no = soh_product.soh_num
) as transact_sum,
> > customer_reference,
> > salesman,
> > parent_order,
> > child_order,
> > order_number
> > FROMsales_orders
> > WHERE (trans_no Like '8%' AND order_number Like '8%')
> > OR (trans_no Like '9%' AND order_number Like '8%')
> > OR (trans_no Like '8%' AND order_number Like '9%')
> > OR (trans_no Like '9%' AND order_number Like '9%')
> > AND(warehouse='M')
> > AND(date_placed > (current_date + ('12 months ago'::interval)))
> > ORDER BY trans_no DESC
>
>
> I am pretty new to SQL. But while reading a book written by an author
> recommended on this
> list,I
> can suggest a possible solution that I've seen. It might work for your
> problem. Of course, I
> haven't tested anything like this and don't know if PostgreSQL supports it.
>
> Just be sure that trans_no is unique in the returned query.
select
f1.fiscalyear,
(select f2.startdate
from fiscalyeartable2 as f2
where f1.fiscalyear = f2.fiscalyear
) as start2date,
f1.enddate
from
fiscalyeartable1 as f1;
fiscalyear | start2date | enddate
++
1995 | 1994-10-01 | 1995-09-30
1996 | 1995-10-01 | 1996-08-30
1997 | 1996-10-01 | 1997-09-30
1998 | 1997-10-01 | 1998-09-30
QUERY PLAN
--
Seq Scan on fiscalyeartable1 f1 (cost=0.00..6.83 rows=1 width=6)
(actual time=0.044..0.067 rows=4 loops=1)
SubPlan
-> Index Scan using fiscalyeartable2_pkey on fiscalyeartable2 f2
(cost=0.00..5.82 rows=1 width=4)
(actual time=0.008..0.009 rows=1 loops=4)
Index Cond: ($0 = fiscalyear)
Total runtime: 0.138 ms
(5 rows)
it works, and check out the nifty query plan.
Regards,
Richard Broersma Jr.
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
[SQL] can any one solve this problem
emp_table( Cdacno varchar (7) (primary key), Personal_No varchar (10)(foreign key), Name varchar (40)); personal_table ( Personal_No varchar (10) (primary key), Cdacno varchar (7), Date_Of_Birth date); unit_master ( Unit id varchar (10) (primary key), Unit_Name varchar(25), Unit_Location varchar(25)); Unit_Details_table ( Unit_id varchar (foreign key) CDA_No varchar(7) foreign key); rank_table( Rank_ID numeric(2)(primary key), Rank_Code numeric(2), Rank_Name varchar (25)); Rank_Date_table ( Rank_Date__ID numeric NOT NULL, CDA_No varchar(7) (foreign key), Rank_ID numeric(2)); My query is ….if I give cdacno I have to get per_no from personal_table.. With this I have to display rank_name from rank_table ,name from emp_table, unit_name from unit_master.. Like that if I give per_no I have to get cdacno from emp_table.. .. With this I have to display rank_name from rank_table ,name from emp_table, unit_name from unit_master.. And here unit_name is depends on unit_details_table ..and rank_name is depends on rank_date_table.. Doing these things first it has to check when we r giving cdacno.. whether per_no is null or not.. like this if I give per_no it has to check cdacno is null or not. Let me know the solution.. But I written one function to this to get per_no if I give cdacno………. Thanks & Regards Penchal reddy | Software Engineer Infinite Computer Solutions | Exciting Times…Infinite Possibilities... SEI-CMMI level 5 | ISO 9001:2000 IT SERVICES | BPO Telecom | Finance | Healthcare | Manufacturing | Energy & Utilities | Retail & Distribution | Government Tel +91-80-5193-(Ext:503)| Fax +91-80-51930009 | Cell No +91-9980012376|www.infics.com Information transmitted by this e-mail is proprietary to Infinite Computer Solutions and/ or its Customers and is intended for use only by the individual or entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient or it appears that this mail has been forwarded to you without proper authority, you are notified that any use or dissemination of this information in any manner is strictly prohibited. In such cases, please notify us immediately at [EMAIL PROTECTED] and delete this mail from your records. Information transmitted by this e-mail is proprietary to Infinite Computer Solutions and / or its Customers and is intended for use only by the individual or the entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient or it appears that this mail has been forwarded to you without proper authority, you are notified that any use or dissemination of this information in any manner is strictly prohibited. In such cases, please notify us immediately at [EMAIL PROTECTED] and delete this email from your records.
