使用Oracle SQL Developer查询IBM DB2的列别名 [英] Column alias querying IBM DB2 using Oracle SQL developer

问题描述

我已使用Oracle SQL Developer连接到IBM DB2数据库，并且正在查询多个表以便执行数据的自动提取.这里的问题是我无法为结果设置别名.我尝试了很多变体，例如添加引号(")([])('')，但它不起作用.我看了几本教程，每个人都只使用"AS"，但是对我来说，它不起作用.有什么建议吗?谢谢！

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!

此处以图片为例: https://i.stack .imgur.com/5NrED.png

我的代码是:

    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;

推荐答案

TLDR:将连接属性附加到以:和;界定的数据库名称中.

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

在创建新的DB2连接时:在新建/选择数据库连接"对话框中，单击"DB2"选项卡，然后在标有数据库"的字段中输入数据库名称，后跟冒号，然后输入冒号.您的property = value(连接属性)，后跟一个分号.

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.

如果要更改现有DB2连接的属性，请右键单击该DB2连接图标并选择属性，然后以与上述相同的方式调整数据库名称，然后进行测试并保存.

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.

例如，在我的情况下，数据库名称为SAMPLE，如果我希望应用程序从查询中显示相关ID名称，则使用数据库名称:

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;

与我的查询中给定的结果集相同的标签出现在 Oracle SQL Developer上的查询结果"窗格.

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

在带有db2jcc4.jar的DB2 v11.1.2.2和Oracle SQL Developer 17.2.0.188中进行了测试

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

这篇关于使用Oracle SQL Developer查询IBM DB2的列别名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！