SQL Queries for Different Types of Databases

The SQL syntax for creating custom fields depending on whether your database is Oracle, MySQL, or Microsoft SQL Server. Here are examples of queries for each type of database and each field datatype, that is string, numeric, or date.

Oracle

STRING

SELECT SCENARIO_FACTS_ID, 
STRING_VALUE AS cost_status 
FROM Reporting.USER_DEFINED_ATTRIBUTE 
WHERE NAME = 'costStatus'

NUMERIC

SELECT SCENARIO_FACTS_ID, 
NUMERIC_VALUE AS numericValue 
FROM Reporting.USER_DEFINED_ATTRIBUTE 
WHERE NAME = 'numericValue' 

DATE

SELECT SCENARIO_FACTS_ID, 
DATE_VALUE AS target_completion 
FROM Reporting.USER_DEFINED_ATTRIBUTE 
WHERE NAME = 'targetCompletion'

MS SQL:

STRING

SELECT SCENARIO_FACTS_ID, 
STRING_VALUE AS cost_status 
FROM Reporting.dbo.USER_DEFINED_ATTRIBUTE 
WHERE NAME = 'costStatus'

NUMERIC

SELECT SCENARIO_FACTS_ID, 
NUMERIC_VALUE AS numericValue 
FROM Reporting. dbo.USER_DEFINED_ATTRIBUTE 
WHERE NAME = 'numericValue' 

DATE

SELECT SCENARIO_FACTS_ID, 
DATE_VALUE AS target_completion 
FROM Reporting. dbo.USER_DEFINED_ATTRIBUTE 
WHERE NAME = 'targetCompletion'

SQLServer:

STRING

SELECT SCENARIO_FACTS_ID, 
STRING_VALUE AS cost_status 
FROM USER_DEFINED_ATTRIBUTE 
WHERE NAME = 'costStatus'

NUMERIC

SELECT SCENARIO_FACTS_ID, 
NUMERIC_VALUE AS numericValue 
FROM USER_DEFINED_ATTRIBUTE 
WHERE NAME = 'numericValue' 

DATE

SELECT SCENARIO_FACTS_ID, 
DATE_VALUE AS target_completion 
FROM USER_DEFINED_ATTRIBUTE 
WHERE NAME = 'targetCompletion'