3
\$\begingroup\$

I have a pretty straightforward query that I am essentially hard-coding. I have a table with 3 keys and a description field.

The first key has to do with the type of compensation for an employee (Award, Hourly, NonHourly, Staff, Other, and Commission).

The next key has to do with Payment Frequency. O refers to One-Time Payment, M to Multiple Payments.

The third key has to do with the Compensation Rate Code. It's like another frequency. P means Per Project/Assignment, H means Hourly, and M means Monthly.

Certain Employee Compensations types are restricted to different types of compensation. In the below query, I've hard-coded every possibility. Is there a better way to write this query than just hard-coding every combination and unioning them together?

SELECT 'Award' 
 , 'O' 
 , 'P' 
 , 'Per Project or Assignment' 
  FROM DUAL 
  UNION 
 SELECT 'Hourly' 
 , 'M' 
 , 'H' 
 , 'Per Hour' 
  FROM DUAL 
  UNION 
 SELECT 'Hourly' 
 , 'M' 
 , 'P' 
 , 'Per Project or Assignment' 
  FROM DUAL 
  UNION 
 SELECT 'Hourly' 
 , 'M' 
 , 'M' 
 , 'Per Month' 
  FROM DUAL 
  UNION 
 SELECT 'Hourly' 
 , 'O' 
 , 'H' 
 , 'Per Hour' 
  FROM DUAL 
  UNION 
 SELECT 'Hourly' 
 , 'O' 
 , 'P' 
 , 'Per Project/Assignment or Month' 
  FROM DUAL 
  UNION 
 SELECT 'NonHourly' 
 , 'M' 
 , 'P' 
 , 'Per Project or Assignment' 
  FROM DUAL 
  UNION 
 SELECT 'NonHourly' 
 , 'M' 
 , 'M' 
 , 'Per Month' 
  FROM DUAL 
  UNION 
 SELECT 'NonHourly' 
 , 'O' 
 , 'P' 
 , 'Per Project/Assignment or Month' 
  FROM DUAL 
  UNION 
 SELECT 'Staff' 
 , 'M' 
 , 'H' 
 , 'Per Hour' 
  FROM DUAL 
  UNION 
 SELECT 'Staff' 
 , 'O' 
 , 'H' 
 , 'Per Hour' 
  FROM DUAL 
  UNION 
 SELECT 'Other' 
 , 'M' 
 , 'H' 
 , 'Per Hour' 
  FROM DUAL 
  UNION 
 SELECT 'Other' 
 , 'M' 
 , 'P' 
 , 'Per Project or Assignment' 
  FROM DUAL 
  UNION 
 SELECT 'Other' 
 , 'M' 
 , 'M' 
 , 'Per Month' 
  FROM DUAL 
  UNION 
 SELECT 'Other' 
 , 'O' 
 , 'H' 
 , 'Per Hour' 
  FROM DUAL 
  UNION 
 SELECT 'Other' 
 , 'O' 
 , 'P' 
 , 'Per Project or Assignment' 
  FROM DUAL 
  UNION 
 SELECT 'Other' 
 , 'O' 
 , 'M' 
 , 'Per Month' 
  FROM DUAL 
  UNION 
 SELECT 'Commission' 
 , 'O' 
 , 'P' 
 , 'Commission/Tips' 
  FROM DUAL

Note that DUAL is just a dummy table. I'm not actually selecting anything from any tables.

EDIT:

This is the SQL behind a View. I wouldn't be opposed to creating new views to "extract" out pieces of the query if that could be used to simplify it. It creates a view that has the following rows in it:
SQL View results

Essentially, my query takes the results of 18 SQL statements and unions them together to get 18 rows in the newly created view. Surely, there's a better way to do it?

The second and third keys are represented in a table that maps their character values to a description (e.g. O -> One-Time Payment). The first key is only used for this view.

\$\endgroup\$
7
  • \$\begingroup\$ Do these values exist in a table? For example could you get each key values with a SELECT DISTINCT key FROM table query? \$\endgroup\$
    – Tasos K.
    Commented Mar 31, 2017 at 19:05
  • \$\begingroup\$ @TasosK. Not really. The first key is used in this context only. The latter two keys do exist in two separate tables that map their values from a character to a description. \$\endgroup\$
    – Cache Staheli
    Commented Mar 31, 2017 at 20:02
  • \$\begingroup\$ I have prepared a demo, but I assumed that all data are in the database. Since this is not the case, you could always keep the query results to a table and simply SELECT * that table. \$\endgroup\$
    – Tasos K.
    Commented Mar 31, 2017 at 20:13
  • \$\begingroup\$ @TasosK. I added an edit to my post to clarify this. The SQL is the SQL behind a View. It results in 18 rows. I'll edit a picture of the expected rows into the post. \$\endgroup\$
    – Cache Staheli
    Commented Mar 31, 2017 at 20:18
  • 3
    \$\begingroup\$ As Tasos said, why don't you just add this as a table (f.e. named AwardDescription) to your database? \$\endgroup\$
    – Gert Arnold
    Commented Apr 1, 2017 at 10:03

2 Answers 2

Reset to default
2
\$\begingroup\$

This really should be put into a table, if possible.

If such a table already exists, you can take the SELECT for your existing view and simply wrap an INSERT INTO TargetTable statement around it.

INSERT INTO TargetTable
SELECT key1,key2,key3,key4 FROM YourView;
COMMIT;

Otherwise, you can CREATE TABLE TargetTable AS to generate a new table from your return query.

CREATE TABLE TargetTable AS
SELECT key1,key2,key3,key4 FROM YourView;
COMMIT;
\$\endgroup\$
0
\$\begingroup\$

Another option is to turn the statement into a view, as in:

CREATE OR REPLACE VIEW COMPENSATION_TERMS_VIEW AS
  SELECT 'Award' AS COMPENSATION_TYPE
       , 'O' AS PAYMENT_FREQUENCY
       , 'P' AS COMPENSATION_RATE_CODE
       , 'Per Project or Assignment' AS DESCRIPTION
    FROM DUAL 
  UNION 
  SELECT 'Hourly' AS COMPENSATION_TYPE
       , 'M' AS PAYMENT_FREQUENCY
       , 'H' AS COMPENSATION_RATE_CODE
       , 'Per Hour' AS DESCRIPTION
    FROM DUAL 
  UNION 
 SELECT 'Hourly' AS COMPENSATION_TYPE
       , 'M' AS PAYMENT_FREQUENCY
       , 'P' AS COMPENSATION_RATE_CODE
       , 'Per Project or Assignment' AS DESCRIPTION
    FROM DUAL 
  UNION 
 SELECT 'Hourly' AS COMPENSATION_TYPE
       , 'M' AS PAYMENT_FREQUENCY
       , 'M' AS COMPENSATION_RATE_CODE
       , 'Per Month' AS DESCRIPTION
    FROM DUAL 
  UNION 
 SELECT 'Hourly' AS COMPENSATION_TYPE
       , 'O' AS PAYMENT_FREQUENCY
       , 'H' AS COMPENSATION_RATE_CODE
       , 'Per Hour' AS DESCRIPTION
    FROM DUAL 
  UNION 
 SELECT 'Hourly' AS COMPENSATION_TYPE
       , 'O' AS PAYMENT_FREQUENCY
       , 'P' AS COMPENSATION_RATE_CODE
       , 'Per Project/Assignment or Month' AS DESCRIPTION
    FROM DUAL 
  UNION 
 SELECT 'NonHourly' AS COMPENSATION_TYPE
       , 'M' AS PAYMENT_FREQUENCY
       , 'P' AS COMPENSATION_RATE_CODE
       , 'Per Project or Assignment' AS DESCRIPTION
    FROM DUAL 
  UNION 
 SELECT 'NonHourly' AS COMPENSATION_TYPE
       , 'M' AS PAYMENT_FREQUENCY
       , 'M' AS COMPENSATION_RATE_CODE
       , 'Per Month' AS DESCRIPTION
    FROM DUAL 
  UNION 
 SELECT 'NonHourly' AS COMPENSATION_TYPE
       , 'O' AS PAYMENT_FREQUENCY
       , 'P' AS COMPENSATION_RATE_CODE
       , 'Per Project/Assignment or Month' AS DESCRIPTION
    FROM DUAL 
  UNION 
 SELECT 'Staff' AS COMPENSATION_TYPE
       , 'M' AS PAYMENT_FREQUENCY
       , 'H' AS COMPENSATION_RATE_CODE
       , 'Per Hour' AS DESCRIPTION
    FROM DUAL 
  UNION 
 SELECT 'Staff' AS COMPENSATION_TYPE
       , 'O' AS PAYMENT_FREQUENCY
       , 'H' AS COMPENSATION_RATE_CODE
       , 'Per Hour' AS DESCRIPTION
    FROM DUAL 
  UNION 
 SELECT 'Other' AS COMPENSATION_TYPE
       , 'M' AS PAYMENT_FREQUENCY
       , 'H' AS COMPENSATION_RATE_CODE
       , 'Per Hour' AS DESCRIPTION
    FROM DUAL 
  UNION 
 SELECT 'Other' AS COMPENSATION_TYPE
       , 'M' AS PAYMENT_FREQUENCY
       , 'P' AS COMPENSATION_RATE_CODE
       , 'Per Project or Assignment' AS DESCRIPTION
    FROM DUAL 
  UNION 
 SELECT 'Other' AS COMPENSATION_TYPE
       , 'M' AS PAYMENT_FREQUENCY
       , 'M' AS COMPENSATION_RATE_CODE
       , 'Per Month' AS DESCRIPTION
    FROM DUAL 
  UNION 
 SELECT 'Other' AS COMPENSATION_TYPE
       , 'O' AS PAYMENT_FREQUENCY
       , 'H' AS COMPENSATION_RATE_CODE
       , 'Per Hour' AS DESCRIPTION
    FROM DUAL 
  UNION 
 SELECT 'Other' AS COMPENSATION_TYPE
       , 'O' AS PAYMENT_FREQUENCY
       , 'P' AS COMPENSATION_RATE_CODE
       , 'Per Project or Assignment' AS DESCRIPTION
    FROM DUAL 
  UNION 
 SELECT 'Other' AS COMPENSATION_TYPE
       , 'O' AS PAYMENT_FREQUENCY
       , 'M' AS COMPENSATION_RATE_CODE
       , 'Per Month' AS DESCRIPTION
    FROM DUAL 
  UNION 
 SELECT 'Commission' AS COMPENSATION_TYPE
       , 'O' AS PAYMENT_FREQUENCY
       , 'P' AS COMPENSATION_RATE_CODE
       , 'Commission/Tips' AS DESCRIPTION
    FROM DUAL

Best of luck.

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.