Message-ID: <420C6BA7.20809@wanadoo.fr>
Date: Fri, 11 Feb 2005 09:24:07 +0100
From: Guillaume LELARGE <gleu@wanadoo.fr>
User-Agent: Mozilla Thunderbird 1.0 (X11/20041206)
MIME-Version: 1.0
To: Johnny C <juandelacruz@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: append fields for *where...*
References: <2bffcc33050210144524608491@mail.gmail.com>
In-Reply-To: <2bffcc33050210144524608491@mail.gmail.com>
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature";
	boundary="------------enigBADF2E0904FA9AFB5FC7AF91"

This is an OpenPGP/MIME signed message (RFC 2440 and 3156)
--------------enigBADF2E0904FA9AFB5FC7AF91
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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

--------------enigBADF2E0904FA9AFB5FC7AF91
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: OpenPGP digital signature
Content-Disposition: attachment; filename="signature.asc"

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.0 (GNU/Linux)

iD8DBQFCDGuqYmO3DB+cCkERAjNzAKDPuMsuhM+ZkrplEXp9q3rAI5nq1ACg47o8
KnKf9SdErCJuagCeUKRRGI0=
=rLAt
-----END PGP SIGNATURE-----

--------------enigBADF2E0904FA9AFB5FC7AF91--