Selecting multiple columns from multiple tables

Question

I've constructed the flowing SQL for my netezza db:

select t.TRADE_OID, t.TRADE_ID, t.TRADE_VERSION, t.TRADE_SOURCE_SYSTEM, tl.LINK_PARENT_ID, 
tl.LINK_PARENT_VERSION, tc.CHARGE_AMOUNT FROM EQ_MO_TRADE (NOLOCK) t, EQ_MO_TRADE_CHARGE (NOLOCK) tc, EQ_MO_TRADE_LINKAGE (NOLOCK) tl 
WHERE t.Last_update_time >= '2013-01-09 00:00:00' 
AND t.last_update_time < '2013-01-10 00:00:00' 
AND t.TRADE_OID = tl.TRADE_OID
AND t.TRADE_OID = tc.TRADE_OID
AND tc.CHARGE_NAME = 'Commission'

However I'm given the general error:

 [SELECT - 0 row(s), 0.000 secs]  [Error Code: 1100, SQL State: HY000]  ERROR:  'select 

t.TRADE_OID, t.TRADE_ID, t.TRADE_VERSION, t.TRADE_SOURCE_SYSTEM, tl.LINK_PARENT_ID, tl.LINK_PARENT_VERSION, tc.CHARGE_AMOUNT FROM EQ_MO_TRADE (NOLOCK) t, EQ_MO_TRADE_CHARGE (NOLOCK) tc, EQ_MO_TRADE_LINKAGE (NOLOCK) tl 
WHERE t.Last_update_time >= '2013-01-09 00:00:00' 
AND t.last_update_time < '2013-01-10 00:00:00' 
AND t.TRADE_OID = tl.TRADE_OID
AND t.TRADE_OID = tc.TRADE_OID
AND tc.CHARGE_NAME = 'Commission' limit 1000'
error                                                                                                                                                         

^ found "(" (at char 150) expecting `EXCEPT' or `FOR' or `INTERSECT' or `ORDER

Character 150 is between these two:

Trade and (NOLOCK)

My syntax seems correct but DBVisualiser won't run the query.

The full syntax in for an optimizer hint like `NOLOCK` is `WITH (NOLOCK)`. As you no doubt are aware `READ UNCOMMITTED` isolation can cause issues like phantom reads etc. — StuartLC, Jan 17 '13 at 11:38

score 1 · Accepted Answer · edited May 23 '17 at 12:27

As far as I can tell from the Netezza Database User's Guide (5.0.x), Netezza does not support Optimizer hints like WITH (NOLOCK).

From page 3.36

Note that there is no need for user intervention, commands, or hints

In any event, NOLOCK is widely regarded as dubious practice.

However, Netezza does support INNER JOIN Syntax (p2.17)

I would rewrite the query as follows:

SELECT
    t.TRADE_OID, 
    t.TRADE_ID, 
    t.TRADE_VERSION, 
    t.TRADE_SOURCE_SYSTEM, 
    tl.LINK_PARENT_ID, 
    tl.LINK_PARENT_VERSION, 
    tc.CHARGE_AMOUNT 
FROM 
  EQ_MO_TRADE t 
    INNER JOIN EQ_MO_TRADE_CHARGE tc
            ON (t.TRADE_OID = tc.TRADE_OID)
    INNER JOIN EQ_MO_TRADE_LINKAGE tl 
        ON (t.TRADE_OID = tl.TRADE_OID)
WHERE 
  t.Last_update_time >= '2013-01-09 00:00:00' 
  AND t.last_update_time < '2013-01-10 00:00:00' 
  AND tc.CHARGE_NAME = 'Commission'

Selecting multiple columns from multiple tables

1 Answers1