Skip to main content

Oracle SQL - Nested Case statements

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'
                    END)
                ELSE
                   (CASE
                       WHEN    (   pc.discovered_psp_via IS NULL
                                OR pc.discovered_psp_via =
                                      'No Information Provided')
                            OR (pc.isp_estimated_customers = 0)
                            OR (pc.isp_annual_screenings = 0)
                       THEN
                          'No'
                       ELSE
                          'Yes'
                    END)
             END
                AS has_demographic_data
        FROM psp_customer pc
       WHERE pc.cdbcustomerid = 10668;
END;

Comments

Post a Comment

Popular posts from this blog

Sorting an ICollection

Have you ever wanted to sort an ICollection? It took me awhile to figure this one out so I thought I should blog it. I originally posted this quite awhile ago. Since then I have discovered a much easier way to sort a collection. Here's an update on sorting collections using LINQ. Much simpler: var orderedList = customer.Users .OrderBy(x => x.UserType) .ToList(); And here is my original post on the subject. You decide which one looks easier. private IList<EventReqFormSection> GetSections() { ICollection<EventReqFormSection> sections = EventReqFormSection.GetByEventReqFormId(_businessObject.Id, ObjectManager); // sort by Display Sequence List<EventReqFormSection> sortedValues = new List<EventReqFormSection>(sections); sortedValues.Sort(new EventReqFormSection.DisplaySequenceSort()); return sortedValues; } The above function retrieves an ICollection puts it into a List object and calls the sort method usin...
2 comments
Read more

Oracle: sorting by an aliased column

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 ...
Post a Comment
Read more
This blog is complete rubbish. Who knew the blogger release was going to fubar my blog so badly? Sigh... Well. I did kind of ignore this for about the last 4 years so it's my fault. I don't know if I have the energy to try repair my previous posts, or if I should just delete them all and start fresh?
Post a Comment
Read more