Paradigm Patterns

To follow is an example of an anonymous procedure using a nested case statement in SQL. The cursor declaration is 'ad hoc' for use in Toad. BEGIN OPEN :pResultSet FOR SELECT pc.cdbcustomerid, CASE WHEN (pc.typeid = 1 OR pc.typeid = 2) THEN (CASE WHEN ( pc.motor_passenger_carrier IS NULL OR pc.motor_passenger_carrier = 'Not Specified') OR ( pc.inter_intra_state IS NULL OR pc.inter_intra_state = 'Not Specified') OR ( pc.discovered_psp_via IS NULL OR pc.discovered_psp_via = 'No Information Provided') THEN 'No' ELSE 'Yes...

Conditional expressions in where clause: procedure get_demo_by_carrier_scope (pIsInterstateCarrier in char,pResultSet out sys_refcursor) AS BEGIN OPEN pResultSet FOR SELECT pc.dba_name, pc.cdbcustomerid, pt.type_name, pc.inter_intra_state FROM psp_customer pc JOIN psp_customer_types pt ON pt.typeid = pc.typeid WHERE (pIsInterstateCarrier ='A' OR pc.inter_intra_state = CASE WHEN pIsInterstateCarrier = 'Y' THEN 'Interstate' WHEN pIsInterstateCarrier = 'N' THEN 'Intrastate' END) AND (PC.TYPEID = 1 or pc.typeid = 2); -- MC and IAH only END get_demo_by_carrier_scope;;

I recently added an ad hoc column to a heavily used query with paging and sorting. To get the sorting to work on the new aliased column it was necessary to pull the "row_number() over" clause in to an outer query. I just wanted to document how this was done for future reference. SELECT * FROM (SELECT my_table.*, ROW_NUMBER () OVER ( ORDER BY UPPER ( DECODE ( pSortDir, 'asc', DECODE (pSortOrder, 'HasDemographicData', has_demographic_data pc.legal_name))), UPPER ( DECODE ( pSortDir, 'desc', DECODE (pSortOrder, 'HasDemographicData', has_demographic_data ...