CODEWITHSUNDEEP
sqlnetezza
Will
Will

Reputation: 8631

Selecting multiple columns from multiple tables

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.

Upvotes: 2

Views: 3391

Answers (1)

StuartLC
StuartLC

Reputation: 107247

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'

Upvotes: 1

Related Questions