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
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.
Posts
2 comments:
Bro.. even this is handy (only in oracle)
--cena7
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';
ELSE
result := 'Gateway';
END IF;
Ah yes the decode funtion .. One of our DBA's was saying that the CASE statments are optimized that the decode function.
Post a Comment