1

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.stack.imgur.com/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;
Rodrigo A.
  • 63
  • 8
  • I wouldn't be surprised if that was a bug/problem in Oracle's SQL Developer. There are two JDBC API calls to get the name and the alias of a result set column. Some drivers do return different names, some don't. Oracle's driver doesn't (it returns the same value for the name and the alias) and I guess SQL Developer doesn't bother with the other alternatives (being an Oracle tool not a general purpose JDBC tool) –  Sep 05 '17 at 14:57
  • 1
    Have you tried setting a connection attribute for the DB2 connection - useJDBC4ColumnNameAndLabelSemantics=No (or False) ? – mao Sep 05 '17 at 16:03
  • Hello Mao, I haven't tried. I reviewed the DB2 connection but I cannot see that attribute. Could you help me clarifying where do I find that option? Thanks! – Rodrigo A. Sep 05 '17 at 18:23

1 Answers1

3

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

mao
  • 11,321
  • 2
  • 13
  • 29