Oracle - Syntax

1. Decode
This works as if-then-else flow control

Here i want to display the relevant application form depending on the licensename

SELECT
decode(vwclientlicenses.licensename,'Land Mobile Service','RF 1','Aircraft Station','RF 14','Maritime Station','RF 14B','Port Operations(Coast) Radio','RF 14B','Amateur Band Radio','RF 2','Citizen Band Radio','RF 1B','Terrestrial Point to Point Fixed Links','RF 3','Fixed Wireless Access Network','RF 3','Terrestrial Point to Multipoint Fixed Links','RF 3','Cellular Network','RF 3','Broadcasting (Radio) - Commercial Free To Air','RF 4','Broadcasting (TV) - Commercial Free To Air','RF 5','RF 1') as applicationform
FROM vwclientlicenses;

2. Coalesce
This allows us to trap NULL values and handle them accordingly

Example

SELECT
correspondenceaction.correspondenceactionid,
coalesce(esc.fullname,us.fullname) as forwardedby,
('' || correspondence.cckreference || '
' || correspondence.correspondencesource || '
' || correspondence.subject || '') as corrrespondencedetails
FROM correspondenceaction
INNER JOIN correspondence ON correspondenceaction.correspondenceid=correspondence.correspondenceid
INNER JOIN users ac ON correspondenceaction.actorid = ac.userid
LEFT JOIN users esc ON correspondenceaction.escalatedby = esc.userid
LEFT JOIN users us ON correspondenceaction.userid = us.userid;

3. Database links

CREATE DATABASE LINK "ERP_LINK.CCK"
CONNECT TO "OPENBR" IDENTIFIED BY VALUES '058C24497EA0E9171D374EA0A3A6D411E09B5CA906AFC989CA' --hash of password
USING '172.100.3.22:1530/imiserp';