Can I add a column instantly at select with a fixed value that is not existing in the table?-CodePudding

SELECT 
    NULL AS Scope10Key, 
    NULL AS TrackingID, 
    AddressId AS AddressID, CountryCode AS CountryCode, 
    NULL AS Modei, work_date AS WorkDate, 
    NULL AS WeekNo, Casetype AS SubMode, del_edit_type AS nakissue

..as the above can I add any column instantly with a fixed value in it???

Please help me here Thanking You

CodePudding user response：

Yes, you can add additional column as per you requirement, below is sample with the same mentioned query:

SELECT  
NULL AS Scope10Key,
NULL AS TrackingID, 
AddressId AS AddressID,
CountryCode AS CountryCode, 
NULL AS Modei, 
work_date AS WorkDate, 
NULL AS WeekNo, 
Casetype AS SubMode, 
del_edit_type AS nakissue,
'value_1' AS fixed_value, --If fixed value is varchar
99999 AS fixed_value2 ---If fixed value is numeric
From <Table_name> ;

CodePudding user response：

Yes. Instead of specifying a column name to be SELECTed, you specify a literal.

As others have pointed out, you are doing that already by saying SELECT NULL as SomeName, ....

I would caution you to be explicit about the type of your literals, however. Consider:

SELECT NULL AS FirstElement,
         57 AS SecondElement,
       ...

In your result set, what SQL type will FirstElement be? Will SecondElement be an INTEGER? A BIGINT? Etc. Often this doesn't matter until it matters a lot — for example, when you UNION your query with another SELECT and the types your RDMBS engine has chosen don't align with the other query. Or, perhaps, if you pass these values to a function expecting certain types. Not all RDBMS engines allow fluid, implicit type conversions, and even those that do don't always do what you want.

Better to say:

SELECT CAST(NULL AS VARCHAR(10)) AS FirstElement,
       CAST(55 As INTEGER)       AS SecondElement,
       ...

if that's what you really want.