?
Solved

How do I create a column header name for a case statement in Oracle SQL

Posted on 2010-08-19
5
Medium Priority
?
498 Views
Last Modified: 2013-12-19
The following code pulls the data correctly put the name of the column header for the case statement is a concatation of all of the cases.  How do I assign 'PARTY' as the column header for the data pulled in the case statement?

SELECT DISTINCT
       ELECTIONS.ID,
       ELECTIONS.ELECTION_DATE,
       ELECTIONS.DESCRIPTION,
       JURISDICTION_DISTRICTS.NAME PRECINCTS,
       JURISDICTION_DISTRICTS.DISTRICT_CODE,
       JURISDICTIONS.NAME COUNTY,
       SPLITS.ID SPLIT,
       BALLOT_STYLES.CODE BALLOT_STYLE_NUMBER,
       ELECTION_RACES.BALLOT_ORDER,
      CANDIDATE_CONTESTS.BALLOT_POSITION CANDIDATE_ORDER_NUMBER,
      DISTRICT_TYPES.DESCRIPTION || ' ' || POSITIONS.POS_NAME "OFFICE TITLE",
      CANDIDATE_CONTESTS.BALLOT_NAME,
      POSITIONS.IS_WRITE_IN_ALLOWED,
      CASE  CANDIDATE_CONTESTS.POLPRT_ID
                        WHEN 2 THEN 'Democrat'
                     WHEN 3 THEN 'Republican'
                       WHEN 4 THEN 'Libertarian'
                   WHEN 5 THEN 'Reform'
                   WHEN 6 THEN 'Natural Law'
                   WHEN 8 THEN 'Constitution'
                   WHEN 9 THEN 'Green'
                   WHEN 10 THEN 'America First'
                   WHEN 11 THEN 'Independent'
                   WHEN 12 THEN 'Non Partisan'
                     ELSE ' '
                   END
         FROM ELECTIONS,
       SPLITS,
       SPLIT_DISTRICTS,
       DISTRICTS,
         DISTRICTS DISTRICTS_1,
       JURISDICTIONS,
         JURISDICTION_DISTRICTS,
       BALLOT_STYLE_SPLITS,
       BALLOT_STYLE_RACES,
       RACES,
       POSITIONS,
       CANDIDATE_CONTESTS,
       BALLOT_STYLES,
       DISTRICT_TYPES,
         ELECTION_RACES,
         POLITICAL_PARTIES
         WHERE     (ELECTIONS.ID = 575000643)
       AND (ELECTIONS.JS_CODE = '25')
       AND (SPLITS.IS_DELETED = 0)
         AND (SPLITS.ID) <16
       AND (SPLIT_DISTRICTS.IS_DELETED = 0)
       AND (DISTRICTS.IS_DELETED = 0)
       AND (DISTRICTS.DISTYP_ID = 3)
       AND (JURISDICTION_DISTRICTS.IS_DELETED = 0)
       AND (JURISDICTION_DISTRICTS.IS_FILING_OFF = 1)
       AND (ELECTIONS.IS_ARCHIVED = 0)
       AND (SPLIT_DISTRICTS.SPL_ID = SPLITS.ID)
       AND (JURISDICTION_DISTRICTS.DIS_ID = DISTRICTS.ID)
       AND (ELECTIONS.JS_CODE = JURISDICTIONS.CODE)
       AND (SPLIT_DISTRICTS.DIS_ID = DISTRICTS.ID)
       AND (JURISDICTIONS.CODE = JURISDICTION_DISTRICTS.JS_CODE)
       AND (BALLOT_STYLE_SPLITS.SPL_ID = SPLITS.ID)
       AND (BALLOT_STYLE_SPLITS.ELECBS_ID = BALLOT_STYLE_RACES.ELECBS_ID)
       AND (BALLOT_STYLE_RACES.RAC_ID = RACES.ID)
       AND (RACES.POS_ID = POSITIONS.ID)
       AND (CANDIDATE_CONTESTS.POS_ID = POSITIONS.ID)
       AND (CANDIDATE_CONTESTS.RAC_ID = RACES.ID)
       AND (BALLOT_STYLE_SPLITS.ELECBS_ID = BALLOT_STYLES.ID)
       AND (BALLOT_STYLE_RACES.ELECBS_ID = BALLOT_STYLES.ID)
       AND (BALLOT_STYLES.ELEC_ID = ELECTIONS.ID)
       AND (POSITIONS.DIS_ID = DISTRICTS_1.ID)
         AND (DISTRICTS_1.DISTYP_ID = DISTRICT_TYPES.ID)
         AND (ELECTION_RACES.ELEC_ID = ELECTIONS.ID)
       AND (ELECTION_RACES.JS_CODE = JURISDICTIONS.CODE)
       AND (ELECTION_RACES.RAC_ID = RACES.ID)
         ORDER BY JURISDICTION_DISTRICTS.NAME, ELECTION_RACES.BALLOT_ORDER,CANDIDATE_ORDER_NUMBER
        
Party.jpg
0
Comment
Question by:restockett
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 58

Accepted Solution

by:
cyberkiwi earned 500 total points
ID: 33480260
Just put it after the expression with or without the "AS" keyword, i.e.

      CASE  CANDIDATE_CONTESTS.POLPRT_ID
                        WHEN 2 THEN 'Democrat'
                     WHEN 3 THEN 'Republican'
                       WHEN 4 THEN 'Libertarian'
                   WHEN 5 THEN 'Reform'
                   WHEN 6 THEN 'Natural Law'
                   WHEN 8 THEN 'Constitution'
                   WHEN 9 THEN 'Green'
                   WHEN 10 THEN 'America First'
                   WHEN 11 THEN 'Independent'
                   WHEN 12 THEN 'Non Partisan'
                     ELSE ' '
                   END PartyName
         FROM ELECTIONS,
0
 
LVL 42

Assisted Solution

by:dqmq
dqmq earned 500 total points
ID: 33480261
CASE
...
END AS Party


And I believe the AS is optional
0
 
LVL 2

Expert Comment

by:thunderwonder
ID: 34041304
CASE  CANDIDATE_CONTESTS.POLPRT_ID
                        WHEN 2 THEN 'Democrat'
                     WHEN 3 THEN 'Republican'
                       WHEN 4 THEN 'Libertarian'
                   WHEN 5 THEN 'Reform'
                   WHEN 6 THEN 'Natural Law'
                   WHEN 8 THEN 'Constitution'
                   WHEN 9 THEN 'Green'
                   WHEN 10 THEN 'America First'
                   WHEN 11 THEN 'Independent'
                   WHEN 12 THEN 'Non Partisan'
                     ELSE ' '
                   END AS PARTY
0
 
LVL 70

Expert Comment

by:Qlemo
ID: 34328534
This question has been classified as abandoned and is being closed as part of the Cleanup Program.  See my comment at the end of the question for more details.
0

Featured Post

Certified OpenStack Administrator Course

We just refreshed our COA course based on the Newton exam.  With 14 labs, this course goes over the different OpenStack services that are part of the certification: Dashboard, Identity Service, Image Service, Networking, Compute, Object Storage, Block Storage, and Orchestration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question