Login Register Actian.com  

Actian Community Forum


Go Back   Actian Community Forums > Ingres Forums > Comp.Databases.Ingres
 

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
Old 2009-11-25   #1 (permalink)
Martin Bowes
Guest
 
Posts: n/a
Default Re: [Info-Ingres] if() with normal SQL statement

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@kettleriverconsulting.com [mailto:info-ingres-bounces@kettleriverconsulting.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@kettleriverconsulting.com [mailto:info-ingres-bounces@kettleriverconsulting.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@ctsu.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@kettleriverconsulting.com
http://ext-cando.kettleriverconsulti...fo/info-ingres




--
Peter Gale
pgale61@gmail.com


  Reply With Quote

Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


© 2011 Actian Corporation. All Rights Reserved