IF and SELECT statement newbie question

Hi, I'm having trouble selecting a field from different table if a field in the first table is null. My SQL goes something like this: IF soldtable.price IS NULL THEN SELECT warehousetable.price FROM soldtable, warehousetableELSE SELECT soldtable.price FROM soldtable END IF I always end up with syntax error, I've spent a few hours on the web searching for a solution but I can't find one... I've tried enclosing different parts of this SQL in brackets, tried putting BEGIN and END and all sorts of stuff but it doesn't do much ... Can anyone help me, please ? Thanks ! -- Pozdrav Revenger 04.01.2007 10:28:17 Jednom su proizveli Chuck Norris toalet papir, ali papir nije dopustao da itko sere po njemu.

On Jan 4, 9:34 am, Revenger <nest.ne...@nesto.com> wrote: > Hi, > I'm having trouble selecting a field from different table if a field in > the first table is null. > My SQL goes something like this: > > IF soldtable.price IS NULL THEN > SELECT warehousetable.price > FROM soldtable, warehousetable> ELSE > SELECT soldtable.price FROM soldtable > END IF> > I always end up with syntax error, I've spent a few hours on the web > searching for a solution but I can't find one... > I've tried enclosing different parts of this SQL in brackets, tried putting > BEGIN and END and all sorts of stuff but it doesn't do much ... > Can anyone help me, please ? > > Thanks ! > -- > Pozdrav > Revenger > 04.01.2007 10:28:17 > Jednom su proizveli Chuck Norris toalet papir, ali papir nije dopustao da > itko sere po njemu. what you probably want to do is..... DEFINE price_var DECIMAL(7,2) SELECT soldtable.price INTO price_var FROM soldtable WHERE... IF price_var IS NULL THEN SELECT warehousetable.price INTO price_var FROM soldtable, warehousetable WHERE soldtable.some-col = warehousetable.someother-col AND ... END IF HTH

Revenger schrieb: > Hi, > I'm having trouble selecting a field from different table if a field in > the first table is null. > My SQL goes something like this: > > IF soldtable.price IS NULL THEN > SELECT warehousetable.price > FROM soldtable, warehousetable> ELSE > SELECT soldtable.price FROM soldtable > END IF> > I always end up with syntax error, I've spent a few hours on the web > searching for a solution but I can't find one... > I've tried enclosing different parts of this SQL in brackets, tried putting > BEGIN and END and all sorts of stuff but it doesn't do much ... > Can anyone help me, please ? > > Thanks ! Did you try using the NVL function? Or you could use DECODE() or CASE(). Checkout the manuals at http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp -- Roland Wintgen (Systemadministrator) EVG Elektro-Vertriebs-Gesellschaft Martens GmbH & Co KG Trompeterallee 244-246, D-41189 Moenchengladbach Tel. +49 21 66 / 55 08 23, Fax +49 21 66 / 55 08 90 www.evg.de rw@evg.de

On 4 Jan 2007 02:00:48 -0800, Nog wrote: > On Jan 4, 9:34 am, Revenger <nest.ne...@nesto.com> wrote: >> Hi, >> I'm having trouble selecting a field from different table if a field in >> the first table is null. >> My SQL goes something like this: >> >> IF soldtable.price IS NULL THEN >> SELECT warehousetable.price >> FROM soldtable, warehousetable>> ELSE >> SELECT soldtable.price FROM soldtable >> END IF>> >> I always end up with syntax error, I've spent a few hours on the web >> searching for a solution but I can't find one... >> I've tried enclosing different parts of this SQL in brackets, tried putting >> BEGIN and END and all sorts of stuff but it doesn't do much ... >> Can anyone help me, please ? >> >> Thanks ! >> -- >> Pozdrav >> Revenger >> 04.01.2007 10:28:17 >> Jednom su proizveli Chuck Norris toalet papir, ali papir nije dopustao da >> itko sere po njemu. > > what you probably want to do is..... > > DEFINE price_var DECIMAL(7,2) > > SELECT soldtable.price > INTO price_var > FROM soldtable > WHERE...> > IF price_var IS NULL THEN > SELECT warehousetable.price > INTO price_var > FROM soldtable, warehousetable > WHERE soldtable.some-col = warehousetable.someother-col > AND ... > END IF> > HTH Yep, that was just what I needed ! Thanks ! ALOT ! -- Pozdrav Revenger 04.01.2007 12:40:35 Jednom su proizveli Chuck Norris toalet papir, ali papir nije dopustao da itko sere po njemu.