Skip to main content

SQL Conditional expressions in where clause

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;;

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
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

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...
Post a Comment
Read more