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

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 (

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: [SQ

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 fu

[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

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)---

[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

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). > >

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).

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

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.y

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

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 on

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

[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   

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. Assumin

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

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 f

[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),