Solved

Problem with query

Posted on 2014-07-30
12
305 Views
Last Modified: 2014-08-04
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

0
Comment
Question by:HuaMinChen
  • 5
  • 3
  • 2
  • +1
12 Comments
 
LVL 24

Expert Comment

by:chaau
ID: 40230972
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
 
LVL 10

Author Comment

by:HuaMinChen
ID: 40230982
Many thanks.
Which close bracket did you remove there?
0
 
LVL 24

Expert Comment

by:chaau
ID: 40230986
all of them. They are not required and were probably inserted as a cut-n-paste typo
0
 
LVL 10

Author Comment

by:HuaMinChen
ID: 40230991
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
 
LVL 10

Author Comment

by:HuaMinChen
ID: 40230996
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
 
LVL 24

Accepted Solution

by:
chaau earned 150 total points
ID: 40231000
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 10

Author Comment

by:HuaMinChen
ID: 40231069
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
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40231085
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
 
LVL 10

Author Comment

by:HuaMinChen
ID: 40231115
Many thanks.
Can I know what other changes you've applied to the original query, in addition to using "inner join"?
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40231330
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
 
LVL 37

Assisted Solution

by:Gerwin Jansen
Gerwin Jansen earned 80 total points
ID: 40231355
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
 
LVL 37

Expert Comment

by:Gerwin Jansen
ID: 40238584
@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

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now