Problem with query

Hi,
I get this error

Error at line 136
ORA-00933: SQL command not properly ended

Open in new window

to this query. Any advice?

select
NVL((SELECT 
TRIM(stk.PICKUP_place||stk.PICKUP_DEPOT)
FROM 
booking_stock stk,
prt2_details bci, 
bl_units bu,
booking_prt2_desc bcd,
booking_prt2_remarks bcr,
booking_prt2_marks bcm,
prt2_type_table ct,
bl_header bkh,
prt_selection es,
units_on_vessel uov
WHERE rownum=1
and bci.rc_id = stk.rc_id(+) 
and bci.bl_prt2_item_id= stk.bl_prt2_item_id(+) 
and bci.rc_id = bu.rc_id(+) 
and bci.bl_prt2_item_id= bu.fcl_booked_prt2_item_id(+) 
and bci.rc_id = bcd.rc_id(+) 
and bci.bl_prt2_item_id= bcd.bl_prt2_item_id(+) 
and bci.rc_id = bcr.rc_id(+) 
and bci.bl_prt2_item_id= bcr.bl_prt2_item_id(+) 
and bci.rc_id = bcm.rc_id(+) 
and bci.bl_prt2_item_id= bcm.bl_prt2_item_id(+) 
and bci.rc_id = bkh.rc_id 
and bci.prt2_type = ct.prt2_type 
and bu.uov_id=uov.uov_id
and bkh.status_cd in ('C','S')
and bkh.bl_correction_version=f_get_last_bkg_version(bkh.booking_reference)
and 
(not exists(select null from booking_prt_event bkee where                  bkee.rc_id=bkh.rc_id)
  or es.prt_retransmit = 'Y'
)
and es.prt_act_id = ee.prt_act_ID --jack
and 
(  (es.prt_selection = 'B'  and
      bkh.vessel = (select vp.vessel from feat3_places vp 
                            WHERE vp.feat3_place_id = es.prt_item_id) AND
      bkh.feat3 = (select vp.feat3 from feat3_places vp
                              WHERE vp.feat3_place_id = es.prt_item_id) AND
      bkh.leg    = (select vp.leg from feat3_places vp 
                         WHERE vp.feat3_place_id = es.prt_item_id) AND
      bkh.pol    = (select vp.place_of_call from feat3_places vp 
                         WHERE vp.feat3_place_id = es.prt_item_id) 
     )
 or (es.prt_selection = 'S'
        and bkh.rc_id = es.prt_item_id)
)),'_')  as mtypick,
NVL((SELECT 
TRIM(stk.RETURN_place||stk.RETURN_DEPOT)
FROM 
booking_stock stk,
prt2_details bci, 
bl_units bu,
booking_prt2_desc bcd,
booking_prt2_remarks bcr,
booking_prt2_marks bcm,
prt2_type_table ct,
bl_header bkh,
prt_selection es,
units_on_vessel uov
WHERE rownum=1
and bci.rc_id = stk.rc_id(+) 
and bci.bl_prt2_item_id= stk.bl_prt2_item_id(+) 
and bci.rc_id = bu.rc_id(+) 
and bci.bl_prt2_item_id= bu.fcl_booked_prt2_item_id(+) 
and bci.rc_id = bcd.rc_id(+) 
and bci.bl_prt2_item_id= bcd.bl_prt2_item_id(+) 
and bci.rc_id = bcr.rc_id(+) 
and bci.bl_prt2_item_id= bcr.bl_prt2_item_id(+) 
and bci.rc_id = bcm.rc_id(+) 
and bci.bl_prt2_item_id= bcm.bl_prt2_item_id(+) 
and bci.rc_id = bkh.rc_id 
and bci.prt2_type = ct.prt2_type 
and bu.uov_id=uov.uov_id
and bkh.status_cd in ('C','S')
and bkh.bl_correction_version=f_get_last_bkg_version(bkh.booking_reference)
and (not exists(select null from booking_prt_event bkee where                  bkee.rc_id=bkh.rc_id)
  or es.prt_retransmit = 'Y'
)
and es.prt_act_id = ee.prt_act_ID --jack
and 
(  (es.prt_selection = 'B'  and
      bkh.vessel = (select vp.vessel from feat3_places vp 
                            WHERE vp.feat3_place_id = es.prt_item_id) AND
      bkh.feat3 = (select vp.feat3 from feat3_places vp
                              WHERE vp.feat3_place_id = es.prt_item_id) AND
      bkh.leg    = (select vp.leg from feat3_places vp 
                         WHERE vp.feat3_place_id = es.prt_item_id) AND
      bkh.pol    = (select vp.place_of_call from feat3_places vp 
                         WHERE vp.feat3_place_id = es.prt_item_id) 
     )
 or (es.prt_selection = 'S'
        and bkh.rc_id = es.prt_item_id)
)),'_')  as fullreturn,
ee.prt_act_ID                 ,
ee.prt_act_TYPE               ,
ee.EVENT_LEVEL_cd               ,
ee.prt_feat2_cd               ,
ee.prt_EVENT_cd           ,
ee.prt_feat_cd               ,
ee.prt_feat_VERSION,
ee.prt_act_DIRECTION          ,
ee.prt_act_OWNER              ,
ee.prt_act_QUEUED             ,
ee.prt_SENDER_RECEIVER_ID         ,
ee.prt_MAP                        ,
ee.prt_MAP_LOCATION               ,
ee.prt_act_FILENAME           ,
ee.prt_act_START              ,
ee.prt_act_END                ,
ee.prt_act_STATUS_cd             ,
ee.prt_act_PROCESSED          ,
ee.prt_act_TRANSMITTED     ,   
es.prt_SELECTION                  ,
es.prt_act_REFERENCE          ,
es.prt_ITEM_ID                    ,
es.prt_RETRANSMIT                 ,
es.prt_SELECT_DATE_FROM           ,
es.prt_SELECT_DATE_TO             ,
es.prt_SELECT_PLACE_FROM          ,
es.prt_SELECT_PLACE_TO            ,
es.prt_SELECT_DEPOT_FROM          ,
es.prt_SELECT_DEPOT_TO            ,
es.prt_SELECT_EVENT               ,
es.prt_SELECT_ACCOUNT             ,
es.prt_SELECT_TRANSplace           ,
es.prt_act_RULE               ,
es.LAST_UPDATE_USER               ,
es.LAST_UPDATE_DATE               ,
ep.OUTBOUND_FILE_LOCATION		  ,
ep.FILENAME_PREFIX				  ,
ep.FILENAME_SUFFIX
FROM prt_act ee, prt_selection es,prt_act_status exs, prt_feat2_feat ep
 WHERE bci.rc_id = stk.rc_id(+) AND bci.bl_prt2_item_id= stk.bl_prt2_item_id(+) AND bci.rc_id = bu.rc_id(+) AND bci.bl_prt2_item_id= bu.fcl_booked_prt2_item_id(+) AND bci.rc_id = bcd.rc_id(+) AND bci.bl_prt2_item_id= bcd.bl_prt2_item_id(+) AND bci.rc_id = bcr.rc_id(+) AND bci.bl_prt2_item_id= bcr.bl_prt2_item_id(+) AND bci.rc_id = bcm.rc_id(+) AND bci.bl_prt2_item_id= bcm.bl_prt2_item_id(+) AND bci.rc_id = bkh.rc_id AND bci.prt2_type = ct.prt2_type AND bu.uov_id=uov.uov_id AND bkee.rc_id=bkh.rc_id) AND es.prt_act_id = ee.prt_act_ID AND vp.feat3_place_id = es.prt_item_id) AND vp.feat3_place_id = es.prt_item_id) AND vp.feat3_place_id = es.prt_item_id) AND vp.feat3_place_id = es.prt_item_id) AND bkh.rc_id = es.prt_item_id) AND bci.rc_id = stk.rc_id(+) AND bci.bl_prt2_item_id= stk.bl_prt2_item_id(+) AND bci.rc_id = bu.rc_id(+) AND bci.bl_prt2_item_id= bu.fcl_booked_prt2_item_id(+) AND bci.rc_id = bcd.rc_id(+) AND bci.bl_prt2_item_id= bcd.bl_prt2_item_id(+) AND bci.rc_id = bcr.rc_id(+) AND bci.bl_prt2_item_id= bcr.bl_prt2_item_id(+) AND bci.rc_id = bcm.rc_id(+) AND bci.bl_prt2_item_id= bcm.bl_prt2_item_id(+) AND bci.rc_id = bkh.rc_id AND bci.prt2_type = ct.prt2_type AND bu.uov_id=uov.uov_id AND bkee.rc_id=bkh.rc_id) AND es.prt_act_id = ee.prt_act_ID AND vp.feat3_place_id = es.prt_item_id) AND vp.feat3_place_id = es.prt_item_id) AND vp.feat3_place_id = es.prt_item_id) AND vp.feat3_place_id = es.prt_item_id) AND bkh.rc_id = es.prt_item_id) AND ee.prt_act_id = es.prt_act_id AND ee.prt_act_STATUS_cd = exs.prt_act_STATUS_cd AND ee.prt_feat2_cd = ep.prt_feat2_cd AND ee.prt_act_ID = 452650173;

Open in new window

LVL 11
HuaMin ChenSystem AnalystAsked:
Who is Participating?
 
chaauConnect With a Mentor Commented:
This is actually what I have expected, but could not vouch for it. The thing is, in the very last WHERE clause you really only need to refer to the tables that you use in the previous line, i.e.
FROM prt_act ee, prt_selection es,prt_act_status exs, prt_feat2_feat ep

Open in new window

Therefore the last line should be as simple as this one:
WHERE es.prt_act_id = ee.prt_act_ID AND es.prt_act_id = ee.prt_act_ID 
 AND ee.prt_act_id = es.prt_act_id AND ee.prt_act_STATUS_cd = exs.prt_act_STATUS_cd AND ee.prt_feat2_cd = ep.prt_feat2_cd 
 AND ee.prt_act_ID = 452650173;

Open in new window

0
 
chaauCommented:
There are quite a few closing brackets in the last line (line #136). Please replace the last line with this one and try again:
WHERE bci.rc_id = stk.rc_id(+) AND bci.bl_prt2_item_id= stk.bl_prt2_item_id(+) AND bci.rc_id = bu.rc_id(+) AND bci.bl_prt2_item_id= bu.fcl_booked_prt2_item_id(+) 
 AND bci.rc_id = bcd.rc_id(+) AND bci.bl_prt2_item_id= bcd.bl_prt2_item_id(+) AND bci.rc_id = bcr.rc_id(+) AND bci.bl_prt2_item_id= bcr.bl_prt2_item_id(+) AND bci.rc_id = bcm.rc_id(+) 
 AND bci.bl_prt2_item_id= bcm.bl_prt2_item_id(+) AND bci.rc_id = bkh.rc_id AND bci.prt2_type = ct.prt2_type AND bu.uov_id=uov.uov_id AND bkee.rc_id=bkh.rc_id 
 AND es.prt_act_id = ee.prt_act_ID AND vp.feat3_place_id = es.prt_item_id AND vp.feat3_place_id = es.prt_item_id AND vp.feat3_place_id = es.prt_item_id 
 AND vp.feat3_place_id = es.prt_item_id AND bkh.rc_id = es.prt_item_id AND bci.rc_id = stk.rc_id(+) AND bci.bl_prt2_item_id= stk.bl_prt2_item_id(+) 
 AND bci.rc_id = bu.rc_id(+) AND bci.bl_prt2_item_id= bu.fcl_booked_prt2_item_id(+) AND bci.rc_id = bcd.rc_id(+) AND bci.bl_prt2_item_id= bcd.bl_prt2_item_id(+) 
 AND bci.rc_id = bcr.rc_id(+) AND bci.bl_prt2_item_id= bcr.bl_prt2_item_id(+) AND bci.rc_id = bcm.rc_id(+) AND bci.bl_prt2_item_id= bcm.bl_prt2_item_id(+) 
 AND bci.rc_id = bkh.rc_id AND bci.prt2_type = ct.prt2_type AND bu.uov_id=uov.uov_id AND bkee.rc_id=bkh.rc_id AND es.prt_act_id = ee.prt_act_ID 
 AND vp.feat3_place_id = es.prt_item_id AND vp.feat3_place_id = es.prt_item_id AND vp.feat3_place_id = es.prt_item_id AND vp.feat3_place_id = es.prt_item_id 
 AND bkh.rc_id = es.prt_item_id AND ee.prt_act_id = es.prt_act_id AND ee.prt_act_STATUS_cd = exs.prt_act_STATUS_cd AND ee.prt_feat2_cd = ep.prt_feat2_cd 
 AND ee.prt_act_ID = 452650173;

Open in new window

0
 
HuaMin ChenSystem AnalystAuthor Commented:
Many thanks.
Which close bracket did you remove there?
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
chaauCommented:
all of them. They are not required and were probably inserted as a cut-n-paste typo
0
 
HuaMin ChenSystem AnalystAuthor Commented:
Sorry, I still get this

Error at line 136
ORA-00933: SQL command not properly ended

after I've removed all close bracket on that line.
0
 
HuaMin ChenSystem AnalystAuthor Commented:
Sorry, please omit my previous reply.

After I've removed all close bracket on that line. I do encounter this

Error at line 136
ORA-00904: "BKH"."RC_ID": invalid identifier

Open in new window

0
 
HuaMin ChenSystem AnalystAuthor Commented:
Sorry, I really cannot remove all close bracket like what you showed above, as then I would have the problem I currently showed above. thanks
0
 
PortletPaulfreelancerCommented:
no points please.

Nice work chaau!

an alternative would be to use more modern join syntax for the FROM clause which simplifies the WHERE clause even further:
FROM prt_act ee
   INNER JOIN prt_selection es   ON ee.prt_act_id = es.prt_act_id
   INNER JOIN prt_act_status exs ON ee.prt_act_STATUS_cd = exs.prt_act_STATUS_cd
   INNER JOIN prt_feat2_feat ep  ON ee.prt_feat2_cd = ep.prt_feat2_cd
WHERE ee.prt_act_ID = 452650173;

Open in new window

The 2 correlated subqueries now appear to be the most complex:
SELECT NVL (
              (SELECT TRIM (stk.PICKUP_place
                    || stk.PICKUP_DEPOT)
              FROM booking_stock stk
                 , prt2_details bci
                 , bl_units bu
                 , booking_prt2_desc bcd
                 , booking_prt2_remarks bcr
                 , booking_prt2_marks bcm
                 , prt2_type_table ct
                 , bl_header bkh
                 , prt_selection es
                 , units_on_vessel uov
              WHERE rownum = 1
              AND bci.rc_id = stk.rc_id(+)
              AND bci.bl_prt2_item_id = stk.bl_prt2_item_id(+)
              AND bci.rc_id = bu.rc_id(+)
              AND bci.bl_prt2_item_id = bu.fcl_booked_prt2_item_id(+)
              AND bci.rc_id = bcd.rc_id(+)
              AND bci.bl_prt2_item_id = bcd.bl_prt2_item_id(+)
              AND bci.rc_id = bcr.rc_id(+)
              AND bci.bl_prt2_item_id = bcr.bl_prt2_item_id(+)
              AND bci.rc_id = bcm.rc_id(+)
              AND bci.bl_prt2_item_id = bcm.bl_prt2_item_id(+)
              AND bci.rc_id = bkh.rc_id
              AND bci.prt2_type = ct.prt2_type
              AND bu.uov_id = uov.uov_id
              AND bkh.status_cd IN ('C'
                                    , 'S')
              AND bkh.bl_correction_version = f_get_last_bkg_version (bkh.booking_reference)
              AND
                 (
                 NOT EXISTS
                 (SELECT NULL
                 FROM booking_prt_event bkee
                 WHERE bkee.rc_id = bkh.rc_id
                 )
              OR es.prt_retransmit = 'Y'
                 )
              AND es.prt_act_id = ee.prt_act_ID --jack
              AND
                 (
                 (
                 es.prt_selection = 'B'
              AND bkh.vessel =
                 (SELECT vp.vessel
                 FROM feat3_places vp
                 WHERE vp.feat3_place_id = es.prt_item_id
                 )
              AND bkh.feat3 =
                 (SELECT vp.feat3
                 FROM feat3_places vp
                 WHERE vp.feat3_place_id = es.prt_item_id
                 )
              AND bkh.leg =
                 (SELECT vp.leg
                 FROM feat3_places vp
                 WHERE vp.feat3_place_id = es.prt_item_id
                 )
              AND bkh.pol =
                 (SELECT vp.place_of_call
                 FROM feat3_places vp
                 WHERE vp.feat3_place_id = es.prt_item_id
                 )
                 )
              OR
                 (
                 es.prt_selection = 'S'
              AND bkh.rc_id = es.prt_item_id
                 )
                 )
              )
            , '_') AS mtypick
   , NVL (
            (SELECT TRIM (stk.RETURN_place
                  || stk.RETURN_DEPOT)
            FROM booking_stock stk
               , prt2_details bci
               , bl_units bu
               , booking_prt2_desc bcd
               , booking_prt2_remarks bcr
               , booking_prt2_marks bcm
               , prt2_type_table ct
               , bl_header bkh
               , prt_selection es
               , units_on_vessel uov
            WHERE rownum = 1
            AND bci.rc_id = stk.rc_id(+)
            AND bci.bl_prt2_item_id = stk.bl_prt2_item_id(+)
            AND bci.rc_id = bu.rc_id(+)
            AND bci.bl_prt2_item_id = bu.fcl_booked_prt2_item_id(+)
            AND bci.rc_id = bcd.rc_id(+)
            AND bci.bl_prt2_item_id = bcd.bl_prt2_item_id(+)
            AND bci.rc_id = bcr.rc_id(+)
            AND bci.bl_prt2_item_id = bcr.bl_prt2_item_id(+)
            AND bci.rc_id = bcm.rc_id(+)
            AND bci.bl_prt2_item_id = bcm.bl_prt2_item_id(+)
            AND bci.rc_id = bkh.rc_id
            AND bci.prt2_type = ct.prt2_type
            AND bu.uov_id = uov.uov_id
            AND bkh.status_cd IN ('C'
                                  , 'S')
            AND bkh.bl_correction_version = f_get_last_bkg_version (bkh.booking_reference)
            AND
               (
               NOT EXISTS
               (SELECT NULL
               FROM booking_prt_event bkee
               WHERE bkee.rc_id = bkh.rc_id
               )
            OR es.prt_retransmit = 'Y'
               )
            AND es.prt_act_id = ee.prt_act_ID --jack
            AND
               (
               (
               es.prt_selection = 'B'
            AND bkh.vessel =
               (SELECT vp.vessel
               FROM feat3_places vp
               WHERE vp.feat3_place_id = es.prt_item_id
               )
            AND bkh.feat3 =
               (SELECT vp.feat3
               FROM feat3_places vp
               WHERE vp.feat3_place_id = es.prt_item_id
               )
            AND bkh.leg =
               (SELECT vp.leg
               FROM feat3_places vp
               WHERE vp.feat3_place_id = es.prt_item_id
               )
            AND bkh.pol =
               (SELECT vp.place_of_call
               FROM feat3_places vp
               WHERE vp.feat3_place_id = es.prt_item_id
               )
               )
            OR
               (
               es.prt_selection = 'S'
            AND bkh.rc_id = es.prt_item_id
               )
               )
            )
          , '_') AS fullreturn
   , ee.prt_act_ID
   , ee.prt_act_TYPE
   , ee.EVENT_LEVEL_cd
   , ee.prt_feat2_cd
   , ee.prt_EVENT_cd
   , ee.prt_feat_cd
   , ee.prt_feat_VERSION
   , ee.prt_act_DIRECTION
   , ee.prt_act_OWNER
   , ee.prt_act_QUEUED
   , ee.prt_SENDER_RECEIVER_ID
   , ee.prt_MAP
   , ee.prt_MAP_LOCATION
   , ee.prt_act_FILENAME
   , ee.prt_act_START
   , ee.prt_act_END
   , ee.prt_act_STATUS_cd
   , ee.prt_act_PROCESSED
   , ee.prt_act_TRANSMITTED
   , es.prt_SELECTION
   , es.prt_act_REFERENCE
   , es.prt_ITEM_ID
   , es.prt_RETRANSMIT
   , es.prt_SELECT_DATE_FROM
   , es.prt_SELECT_DATE_TO
   , es.prt_SELECT_PLACE_FROM
   , es.prt_SELECT_PLACE_TO
   , es.prt_SELECT_DEPOT_FROM
   , es.prt_SELECT_DEPOT_TO
   , es.prt_SELECT_EVENT
   , es.prt_SELECT_ACCOUNT
   , es.prt_SELECT_TRANSplace
   , es.prt_act_RULE
   , es.LAST_UPDATE_USER
   , es.LAST_UPDATE_DATE
   , ep.OUTBOUND_FILE_LOCATION
   , ep.FILENAME_PREFIX
   , ep.FILENAME_SUFFIX
FROM prt_act ee
   INNER JOIN prt_selection es   ON ee.prt_act_id = es.prt_act_id
   INNER JOIN prt_act_status exs ON ee.prt_act_STATUS_cd = exs.prt_act_STATUS_cd
   INNER JOIN prt_feat2_feat ep  ON ee.prt_feat2_cd = ep.prt_feat2_cd
WHERE ee.prt_act_ID = 452650173;

Open in new window

0
 
HuaMin ChenSystem AnalystAuthor Commented:
Many thanks.
Can I know what other changes you've applied to the original query, in addition to using "inner join"?
0
 
PortletPaulfreelancerCommented:
No other changes. The adjustments were to the where clause (which had redundant information in it) and as a consequence I introduced INNER JOINS which further simplified the where clause (to one condition).

Although the rest might look different that is only due to the way I have formatted it.
0
 
Gerwin Jansen, EE MVEConnect With a Mentor Topic Advisor Commented:
Just a remark: if you have a query that is not properly constructed, put in in a tool that can format sql like SQL Developer.

Paste the query, right-click and choose 'Format', gave me this result:
formatting a query(pointing to the first single ) character)
0
 
Gerwin Jansen, EE MVETopic Advisor Commented:
@HuaMinChen - Why the B-grade if I may ask?

http://support.experts-exchange.com/customer/portal/articles/481419 -
"When closing the question, the asker should explain why a B grade was awarded."
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.