Solved

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

Posted on 2010-08-19
5
494 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 69

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

778 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