CODEWITHSUNDEEP
db2oracle-sqldeveloper
Rodrigo A.
Rodrigo A.

Reputation: 73

Column alias querying IBM DB2 using Oracle SQL developer

I'm connected to an IBM DB2 database using Oracle SQL Developer and I'm querying several tables in order to perform an automated extraction of data. The issue here is that I can't set aliases for my results. I tried a lot of variants like adding quotes ("") ([]) ('') and it's not working. I saw several tutorials and everyone uses "AS" only, but for me it's not working. Any recommendations? Thanks!

Image as example here: https://i.sstatic.net/5NrED.png

My code is:

    SELECT 
        "A"."TC_SHIPMENT_ID" AS SHIPMENT_ID,
        "A"."CLAIM_ID",
        B.DESCRIPTION CLAIM_CLASSIFICATION,
        C.DESCRIPTION CLAIM_CATEGORY,
        D.DESCRIPTION CLAIM_TYPE,
        F.DESCRIPTION CLAIM_STATUS
    FROM CLAIMS A
        INNER JOIN CLAIM_CLASSIFICATION B ON A.CLAIM_CLASSIFICATION = B.CLAIM_CLASSIFICATION
        INNER JOIN CLAIM_CATEGORY C ON A.CLAIM_CATEGORY = C.CLAIM_CATEGORY
        INNER JOIN CLAIM_TYPE D ON A.CLAIM_TYPE = D.CLAIM_TYPE
        INNER JOIN CLAIM_STATUS F ON A.CLAIM_STATUS = F.CLAIM_STATUS;

Upvotes: 2

Views: 1550

Answers (1)

mao
mao

Reputation: 12267

TLDR: append the connection-attribute(s) to the database name bounded by : and ;

When creating a new DB2-connection: On the dialog box for 'New /Select Database Connection', click the DB2 tab, and on the field labelled 'Database' you enter your database-name followed by a colon, followed by your property=value (connection attribute), followed by a semicolon.

When you want to alter the properties of an existing DB2 connection, right click that DB2-connection icon and choose properties, and adjust the database name in the same pattern as above, then test and save.

For example, in my case the database name is SAMPLE and if I want the application to show the correlation-ID names from my queries then I use for the database-name:

SAMPLE:useJDBC4ColumnNameAndLabelSemantics=No;

The same labels for result-sets as given in my queries then appear on the Query-Result pane on Oracle SQL Developer.

Tested with DB2 v11.1.2.2 with db2jcc4.jar and Oracle SQL Developer 17.2.0.188

Upvotes: 4

Related Questions