Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Problem with query

Posted on 2014-07-30
12
Medium Priority
?
339 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
  • +1
12 Comments
 
LVL 25

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 11

Author Comment

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

Expert Comment

by:chaau
ID: 40230986
all of them. They are not required and were probably inserted as a cut-n-paste typo
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 11

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 11

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 25

Accepted Solution

by:
chaau earned 450 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
 
LVL 11

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 49

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 11

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 49

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 38

Assisted Solution

by:Gerwin Jansen, EE MVE
Gerwin Jansen, EE MVE earned 240 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 38

Expert Comment

by:Gerwin Jansen, EE MVE
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

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
Via a live example, show how to take different types of Oracle backups using RMAN.
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.

715 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