Monday, October 5, 2009

Few SQL Select Tips

Was working on a Jasper report and i was able to learn some new SQL select options which i didnt know of and thought to share it with  you guys.
 
1. To concatinate fields to one column use the following;
 
    SELECT a.last_name || ' ' || a.first_name || ' '  as pax_name from Passenger a
 
2. To put a condition within the select statement use the following;
    SELECT  a.checking_sequence_number as seq_no,a.seat_no,
               CASE
               WHEN booking_status = 'STNDBY'
               THEN booking_status
               ELSE passenger_checkin_type
               END as checkin_type
 
      from Passenger a
 
What this does is it checks for the column called booking_status and if it is STNDBY then it selects that value for the column else it gets the value of the column called passenger_checking_type.