Solved

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

Posted on 2010-08-19
5
492 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
5 Comments
 
LVL 58

Accepted Solution

by:
cyberkiwi earned 125 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 125 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 68

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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
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 setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

758 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now