Hi All,



Up until a few seconds ago I thought the only if then else statement
available was used only within a database procedure. However I've just
been shown the following...which works in 9.2.0.



select p.centre_id, pw.participant_id,



if(wd.withdrawal_date is not null, wd.withdrawal_date,
wd.withdrawal_changed_date) as withdrawal_date



from participant_wthdrw pw join participants p on
pw.participant_id=p.participant_id

left join withdrawal_date_view wd on pw.participant_id=wd.participant_id
and pw.withdrawal_id=wd.withdrawal_id

where pw.active=1 and pw.participant_id in (select participant_id from
visits v where visit_status=1 and form=1)

and pw.participant_id not in (select participant_id from
randomized_participants_view)



I can't find this described in the SQL Reference guide for 9.2.0. Anyone
got any ideas.



Martin Bowes

Marty,

Its documented in 9.3 but as if by magic it works in 9.2

Cheers

Peter

2009/11/25 Martin Bowes <martin.bowes (AT) ctsu (DOT) ox.ac.uk>


--
Peter Gale
pgale61 (AT) gmail (DOT) com

It is in the 9.2 (and 9.3) SQL Ref Guide under:



o Expressions in SQL

§ IF, NULLIF, and COALESCE Functions







From: info-ingres-bounces (AT) kettleriver...ting (DOT) com [mailto:info-ingres-bounces (AT) kettleriverconsulting (DOT) com] On Behalf Of Peter Gale
Sent: 25 November 2009 11:47
To: Ingres and related product discussion forum
Subject: Re: [Info-Ingres] if() with normal SQL statement



Marty,

Its documented in 9.3 but as if by magic it works in 9.2

Cheers

Peter

2009/11/25 Martin Bowes <martin.bowes (AT) ctsu (DOT) ox.ac.uk>

Hi All,



Up until a few seconds ago I thought the only if then else statement available was used only within a database procedure. However I've just been shown the following...which works in 9.2.0.



select p.centre_id, pw.participant_id,



if(wd.withdrawal_date is not null, wd.withdrawal_date, wd.withdrawal_changed_date) as withdrawal_date



from participant_wthdrw pw join participants p on pw.participant_id=p.participant_id

left join withdrawal_date_view wd on pw.participant_id=wd.participant_id and pw.withdrawal_id=wd.withdrawal_id

where pw.active=1 and pw.participant_id in (select participant_id from visits v where visit_status=1 and form=1)

and pw.participant_id not in (select participant_id from randomized_participants_view)



I can't find this described in the SQL Reference guide for 9.2.0. Anyone got any ideas.



Martin Bowes


_______________________________________________
Info-Ingres mailing list
Info-Ingres (AT) kettleriverconsulting (DOT) com
http://ext-cando.kettleriverconsulti...fo/info-ingres




--
Peter Gale
pgale61 (AT) gmail (DOT) com

Hi John



Thanks for that. My SQL manual is out of date, I only have the heading: NULLIF, and COALESCE Functions



I just tried an experiment and I see that I can embed the if expressions just like the case expression from which it derives. Neat.



select if(a=b, if(a>c, 1, 0), 2) as d from x\g





Marty





From: info-ingres-bounces (AT) kettleriver...ting (DOT) com [mailto:info-ingres-bounces (AT) kettleriverconsulting (DOT) com] On Behalf Of John Smedley
Sent: 25 November 2009 11:56
To: Ingres and related product discussion forum
Subject: Re: [Info-Ingres] if() with normal SQL statement



It is in the 9.2 (and 9.3) SQL Ref Guide under:



o Expressions in SQL

§ IF, NULLIF, and COALESCE Functions







From: info-ingres-bounces (AT) kettleriver...ting (DOT) com [mailto:info-ingres-bounces (AT) kettleriverconsulting (DOT) com] On Behalf Of Peter Gale
Sent: 25 November 2009 11:47
To: Ingres and related product discussion forum
Subject: Re: [Info-Ingres] if() with normal SQL statement



Marty,

Its documented in 9.3 but as if by magic it works in 9.2

Cheers

Peter

2009/11/25 Martin Bowes <martin.bowes (AT) ctsu (DOT) ox.ac.uk>

Hi All,



Up until a few seconds ago I thought the only if then else statement available was used only within a database procedure. However I've just been shown the following...which works in 9.2.0.



select p.centre_id, pw.participant_id,



if(wd.withdrawal_date is not null, wd.withdrawal_date, wd.withdrawal_changed_date) as withdrawal_date



from participant_wthdrw pw join participants p on pw.participant_id=p.participant_id

left join withdrawal_date_view wd on pw.participant_id=wd.participant_id and pw.withdrawal_id=wd.withdrawal_id

where pw.active=1 and pw.participant_id in (select participant_id from visits v where visit_status=1 and form=1)

and pw.participant_id not in (select participant_id from randomized_participants_view)



I can't find this described in the SQL Reference guide for 9.2.0. Anyone got any ideas.



Martin Bowes


_______________________________________________
Info-Ingres mailing list
Info-Ingres (AT) kettleriverconsulting (DOT) com
http://ext-cando.kettleriverconsulti...fo/info-ingres




--
Peter Gale
pgale61 (AT) gmail (DOT) com

Hi Pete,



Thanks for the intel.



I'll have to refresh my 9.2 manual.



Given the recent discussion on 9.3 I won't be upgrading to it. It looks
like a better option to get everything I've got to 9.2 and then wait for
10.0.



Marty



From: info-ingres-bounces (AT) kettleriver...ting (DOT) com
[mailto:info-ingres-bounces (AT) kettleriverconsulting (DOT) com] On Behalf Of
Peter Gale
Sent: 25 November 2009 11:47
To: Ingres and related product discussion forum
Subject: Re: [Info-Ingres] if() with normal SQL statement



Marty,

Its documented in 9.3 but as if by magic it works in 9.2

Cheers

Peter

2009/11/25 Martin Bowes <martin.bowes (AT) ctsu (DOT) ox.ac.uk>

Hi All,



Up until a few seconds ago I thought the only if then else statement
available was used only within a database procedure. However I've just
been shown the following...which works in 9.2.0.



select p.centre_id, pw.participant_id,



if(wd.withdrawal_date is not null, wd.withdrawal_date,
wd.withdrawal_changed_date) as withdrawal_date



from participant_wthdrw pw join participants p on
pw.participant_id=p.participant_id

left join withdrawal_date_view wd on pw.participant_id=wd.participant_id
and pw.withdrawal_id=wd.withdrawal_id

where pw.active=1 and pw.participant_id in (select participant_id from
visits v where visit_status=1 and form=1)

and pw.participant_id not in (select participant_id from
randomized_participants_view)



I can't find this described in the SQL Reference guide for 9.2.0. Anyone
got any ideas.



Martin Bowes


_______________________________________________
Info-Ingres mailing list
Info-Ingres (AT) kettleriverconsulting (DOT) com
http://ext-cando.kettleriverconsulti...fo/info-ingres




--
Peter Gale
pgale61 (AT) gmail (DOT) com