Thread: append fields for *where...*

I have the following tables:

TABLE A
month   |   year   |   item         |   num
1              2005      myitem         003

TABLE B
num    |   date             |      descr
003         02-01-2005          blahblah
003         01-01-2005          toratora

I am trying to come up with something like:
select date,item,descr from TABLEA a 
LEFT OUTER JOIN TABLEB b ON  b.num=a.num
WHERE date=month-01-year;

How can you make month (append) - 01 - (append) year? and
pass that as a condition to the field date? Is this possible?

On Thu, Feb 10, 2005 at 17:45:11 -0500, Johnny C <juandelacruz@gmail.com> wrote:
> 
> How can you make month (append) - 01 - (append) year? and
> pass that as a condition to the field date? Is this possible?

You can EXTRACT the day of the month from the date. If you need this
to use an index, you can create a functional index.

Johnny C wrote:
> I have the following tables:
> 
> TABLE A
> month   |   year   |   item         |   num
> 1              2005      myitem         003
> 
> TABLE B
> num    |   date             |      descr
> 003         02-01-2005          blahblah
> 003         01-01-2005          toratora
> 
> I am trying to come up with something like:
> select date,item,descr from TABLEA a 
> LEFT OUTER JOIN TABLEB b ON  b.num=a.num
> WHERE date=month-01-year;
> 
> How can you make month (append) - 01 - (append) year? and
> pass that as a condition to the field date? Is this possible?
> 

This works for me :  WHERE (month||'-01-'||year)::timestamp=date;

Here is my session :
galette=# create table a (month int4, year int4, item varchar(255), num 
int4);
CREATE TABLE
galette=# insert into a values (1,2005,'myitem',3);
INSERT 17296 1
galette=# create table b (num int4, date timestamp, descr varchar(255));
CREATE TABLE
galette=# insert into b values (3,'02-01-2005','blahblah');
INSERT 17299 1
galette=# insert into b values (3,'01-01-2005','toratora');
INSERT 17300 1
galette=# select date,item,descr from a
LEFT OUTER JOIN b ON  b.num=a.num
WHERE (month||'-01-'||year)::timestamp=date;        date         |  item  |  descr
---------------------+--------+---------- 2005-01-01 00:00:00 | myitem | toratora
(1 ligne)


-- 
Guillaume.
<!-- http://abs.traduc.org/     http://lfs.traduc.org/     http://traduc.postgresqlfr.org/ -->