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

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

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

ASP.NET Anti-pattern - redone in AngularJs

I came across this code today. This is the ultimate anti-pattern for ASP.NET and jQuery. The idea here is that when a user clicks on the cdbDriverConsent checkbox; if the customer account is "inProbation", the "probationBox" will slide down with further instructions. The problem is, when looking at strictly the markup. There is absolutely no way of knowing that this is what is happening. The markup looks like this: ' ToolTip="I attest" /> ... In the code behind the OnItemDatabind function grabs the checkbox and assigns a jQuery click handler: protected void dgDIR_OnItemDataBound(object sender, DataGridItemEventArgs e) { if (e.Item.ItemType == ListItemType.Item || e.Item.ItemType == ListItemType.AlternatingItem) { ... if (IsOnProbation()) { ...
Post a Comment
Read more