Solved

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

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
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 Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

920 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

16 Experts available now in Live!

Get 1:1 Help Now