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,
               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.


  1. Bro.. even this is handy (only in oracle)
    SELECT supplier_name,
    decode(supplier_id, 10000, 'IBM',
    10001, 'Microsoft',
    10002, 'Hewlett Packard',
    'Gateway') result
    FROM suppliers;

    The above decode statement is equivalent to the following IF-THEN-ELSE statement:

    IF supplier_id = 10000 THEN
    result := 'IBM';

    ELSIF supplier_id = 10001 THEN
    result := 'Microsoft';

    ELSIF supplier_id = 10002 THEN
    result := 'Hewlett Packard';

    result := 'Gateway';

    END IF;

  2. Ah yes the decode funtion .. One of our DBA's was saying that the CASE statments are optimized that the decode function.