Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2012
    Posts
    188

    Unanswered: Multiple Case Statements In One Select

    I know I should know the answer to this, but I just can't quite get the syntax down
    Code:
    Select case when zipCode = '10185' Then 'Deliver'
    Else when zipCode = '2309' And paid = 'Yes' Then 'Deliver'
    Else When zipCode = '1291' And paid = 'Yes' Then 'Deliver'
    Else When zipCode = '88221' And paid = 'No' Then 'Hold'
    Else when zipCode = '34123' Then 'Deliver'
    End
    From postalDeliveryDatabase

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    SELECT
       CASE
          WHEN zipCode = '10185'                  THEN 'Deliver'
          WHEN zipCode = '2309'  And paid = 'Yes' THEN 'Deliver'
          WHEN zipCode = '1291'  And paid = 'Yes' THEN 'Deliver'
          WHEN zipCode = '88221' And paid = 'No'  THEN 'Hold'
          WHEN zipCode = '34123'                  THEN 'Deliver'
       END
       FROM postalDeliveryDatabase
    ...looks like syntactically correct SQL syntax to me.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Feb 2012
    Posts
    188
    Quote Originally Posted by Pat Phelan View Post
    Code:
    SELECT
       CASE
          WHEN zipCode = '10185'                  THEN 'Deliver'
          WHEN zipCode = '2309'  And paid = 'Yes' THEN 'Deliver'
          WHEN zipCode = '1291'  And paid = 'Yes' THEN 'Deliver'
          WHEN zipCode = '88221' And paid = 'No'  THEN 'Hold'
          WHEN zipCode = '34123'                  THEN 'Deliver'
       END
       FROM postalDeliveryDatabase
    ...looks like syntactically correct SQL syntax to me.

    -PatP
    You are correct, sorry I hit the submit button to quick before I finished typing. Here is what it should have said at the end.

    And if one of those parameters are not met then exclude from my result set...Don't display null, don't return at all.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    SELECT *
       FROM (SELECT
          CASE
             WHEN zipCode = '10185'                  THEN 'Deliver'
             WHEN zipCode = '2309'  And paid = 'Yes' THEN 'Deliver'
             WHEN zipCode = '1291'  And paid = 'Yes' THEN 'Deliver'
             WHEN zipCode = '88221' And paid = 'No'  THEN 'Hold'
             WHEN zipCode = '34123'                  THEN 'Deliver'
          END AS status
          FROM postalDeliveryDatabase) AS z
       WHERE  z.status IN ('Deliver', 'Hold')
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Feb 2012
    Posts
    188
    Quote Originally Posted by Pat Phelan View Post
    Code:
    SELECT *
       FROM (SELECT
          CASE
             WHEN zipCode = '10185'                  THEN 'Deliver'
             WHEN zipCode = '2309'  And paid = 'Yes' THEN 'Deliver'
             WHEN zipCode = '1291'  And paid = 'Yes' THEN 'Deliver'
             WHEN zipCode = '88221' And paid = 'No'  THEN 'Hold'
             WHEN zipCode = '34123'                  THEN 'Deliver'
          END AS status
          FROM postalDeliveryDatabase) AS z
       WHERE  z.status IN ('Deliver', 'Hold')
    -PatP
    This now produces an individual result for each entry (my query is bigger than just this case statement) is there a way to have it only show 1 instance of each case?
    Last edited by jo15765; 12-16-14 at 21:11.

  6. #6
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Quote Originally Posted by jo15765 View Post
    (my query is bigger than just this case statement)
    Do you mean the CASE statement is longer than what you have shown?

    If so, you may start considering to put all this logic in the database, instead of in the program.
    Code:
    CREATE TABLE postalDeliveryDatabase(
    	zipCode	VARCHAR(10)	NOT NULL,
    	paid	VARCHAR(3)	NOT NULL
    		CONSTRAINT CC_postalDeliveryDatabase_Paid_Y_N CHECK (paid in ('Yes', 'No')),
    )
    
    INSERT INTO postalDeliveryDatabase(zipCode, paid) VALUES
    ('10', 'No'),
    ('10185', 'No'),
    ('10185', 'Yes'),
    ('1291', 'Yes'),
    ('88221', 'Yes')
    
    CREATE TABLE Delivery_Combinations(
    	zipCode	VARCHAR(10)	NOT NULL,
    	paid	VARCHAR(3)	NULL,
    	DeliveryStatus	VARCHAR(10)	NOT NULL
    )
    
    INSERT INTO Delivery_Combinations(zipCode, paid, DeliveryStatus) VALUES
    ('10185', NULL, 'Deliver'),
    ('2309', 'Yes', 'Deliver'),
    ('1291', 'Yes', 'Deliver'),
    ('88221', 'No', 'Hold'),
    ('34123', NULL, 'Deliver')
    The Delivery_Combinations table contains all possible combinations of zip code and the paid status, with the resulting delivery status. When paid is NULL, it is treated as a "don't care" (it could be 'Yes' or 'No', the delivery status will still be the same).

    The query will be a lot easier to write and the maintenance of the decision table can be done by someone from the users.
    And other systems, like a data warehouse could easily reproduce the results.

    This query gives you the resulting delivery status:
    Code:
    SELECT PD.zipCode, PD.paid, DC.DeliveryStatus, DC.paid as DC_paid
    FROM postalDeliveryDatabase AS PD
    	INNER JOIN Delivery_Combinations as DC ON
    		PD.zipCode = DC.zipCode
    		AND PD.paid = COALESCE(DC.paid, PD.paid)
    Compare it's result set with this one, derived from your query:
    Code:
    SELECT *
       FROM (SELECT	zipCode, paid,
          CASE
             WHEN zipCode = '10185'                  THEN 'Deliver'
             WHEN zipCode = '2309'  And paid = 'Yes' THEN 'Deliver'
             WHEN zipCode = '1291'  And paid = 'Yes' THEN 'Deliver'
             WHEN zipCode = '88221' And paid = 'No'  THEN 'Hold'
             WHEN zipCode = '34123'                  THEN 'Deliver'
          END AS status
          FROM postalDeliveryDatabase) AS z
       WHERE  z.status IN ('Deliver', 'Hold')
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  7. #7
    Join Date
    Feb 2012
    Posts
    188
    This is full query but it gives one line for each zip instead of aggregating (one line per zip) sorry for nto being clear from the start.
    Code:
    SELECT *
       FROM (SELECT
       CASE
          WHEN zipCode = '10185'                  THEN 'Deliver'
          WHEN zipCode = '2309'  And paid = 'Yes' THEN 'Deliver'
          WHEN zipCode = '1291'  And paid = 'Yes' THEN 'Deliver'
          WHEN zipCode = '88221' And paid = 'No'  THEN 'Hold'
          WHEN zipCode = '34123'                  THEN 'Deliver'
       END As zip,
    COUNT(deliveryDriverName) As [Number of Drivers],
    deliveryVehicle,
    SUM(outSick) As [# of callins]) d
    FROM postalDeliveryDatabase
    WHERE d.zip in ('deliver', 'hold')
    GROUP BY d.zip

  8. #8
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    This is not the query, I got "Incorrect syntax near the keyword 'FROM'."
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •