Problem to query

Hi,
I run
 
spo c:\pe\1b
Select
   krn.obj0,
   krn.obj0_id,
   krn.p3_sequence,
   krn.par0,
   ptpar0.obj1_name As par0_name,
   krn.par1,
   ptpar1.obj1_name As par1_name,
   krn.p1,
   krn.p2,
   krn.p3,
   krn.obj1_of_call,
   krn.obj1_From,
   ptpf.obj1_name As obj1_From_Name,
   krn.obj1_To,
   ptpt.obj1_name As obj1_To_Name,
   krn.por,
   krn.sent_count,
   krn.prepaid_collect_ind,
   Coalesce(krn.par2_name,ptpor.obj1_name) As par2_name,
   krn.par3,
   Coalesce(krn.par3_name,ptpd.obj1_name) As par3_name,
   krn.TS_Flag,
   krn.original_obj0_id,
   krn.orig_obj0_id,
   krn.bill_type,
   krn.event_flag,
   krn.next_obj1_to,
   (Select vp0.obj1_call_ref From tw.p2_obj1s vp0,tw.t1_p3s brl0
    Where
    krn.event_flag = 'EM'
    And brl0.obj0_id = Decode(krn.bill_type,'M',Decode(krn.issue_place,krn.obj1_of_call,krn.original_obj0_id,krn.obj0_id),krn.obj0_id)
--Decode(krn.bill_type,'M',krn.original_obj0_id,krn.obj0_id)
    And brl0.obj1_cd_to = krn.obj1_of_call
    And vp0.obj1_of_call = brl0.obj1_cd_to          
    And vp0.p1 = brl0.imp_p1 And  vp0.p2 = brl0.imp_p2 And  vp0.p3 = brl0.imp_p3
    And brl0.obj1_cd_to = vp0.obj1_of_call
    And vp0.obj1_sequence  = 1
    And brl0.p3_sequence = (Select Max(p3_sequence) From tw.t1_p3s where obj0_id = brl0.obj0_id
                                  And p3_sequence < krn.p3_sequence
                                  And obj1_cd_to = krn.obj1_of_call)) As MRN_TS,
(Select Max(blr.ref_no) From tw.bl_reference blr Where
    --blr.obj0_id = Decode(krn.bill_type,'M',krn.original_obj0_id,krn.obj0_id)
    blr.obj0_id = Decode(krn.event_flag,'IM',krn.obj0_id,
    Decode(krn.bill_type,'M',Decode(krn.issue_place,krn.obj1_of_call,krn.original_obj0_id,krn.obj0_id),krn.obj0_id))
    And blr.p2_obj1_id = Decode(krn.event_flag,'IM' ,krn.p2_obj1_id,
         (Select vp0.p2_obj1_id From tw.p2_obj1s vp0,tw.t1_p3s brl0
         Where
         brl0.obj0_id = Decode(krn.bill_type,'M',Decode(krn.issue_place,krn.obj1_of_call,krn.original_obj0_id,krn.obj0_id),krn.obj0_id)
-- Decode(krn.bill_type,'M',krn.original_obj0_id,krn.obj0_id)
         And brl0.obj1_cd_to = krn.obj1_of_call
         And vp0.obj1_of_call = brl0.obj1_cd_to          
         And vp0.p1 = brl0.imp_p1 And  vp0.p2 = brl0.imp_p2 And  vp0.p3 = brl0.imp_p3
         And brl0.obj1_cd_to = vp0.obj1_of_call
         And vp0.obj1_sequence  = 1
         And brl0.p3_sequence = (Select Max(p3_sequence) From tw.t1_p3s where obj0_id = brl0.obj0_id
                                  And p3_sequence < krn.p3_sequence
                                  And obj1_cd_to = krn.obj1_of_call)
                                  ))
     And blr.ref_type = 'ACM' ) As MSN,    
   ctpor.country_name As origin_country_name,
   ctpar0.country_name As par0_country_name,
   ctpd.country_name As destination_country_name,
   ctpt.country_name As final_destination_country_name,
   ptpoc.country As obj1_of_call_country,
   ptnpt.country As next_obj1_to_country,
   etpar0.edi_translation_code As par0_unlocode,
   etpar1.edi_translation_code As par1_unlocode,
   etpf.edi_translation_code As obj1_from_unlocode,
   etpt.edi_translation_code As obj1_to_unlocode,
   etpd.edi_translation_code As par3_unlocode,
   etpor.edi_translation_code As par2_unlocode,
   etporc.edi_translation_code As origin_country_UN,
   etptc.edi_translation_code As destination_country_UN,
   etpdc.edi_translation_code As final_destination_country_UN,
(Select Count('*') From tw.bl_units bu,tw.units_on_p1 uov
               Where bu.obj0_id = krn.obj0_id
               And uov.uov_id = bu.uov_id
               And uov.unit_empty_full_ind = 'F') As Non_Empty_Container_Flag
From
((Select
    bh.obj0,
    bh.obj0_id,
    bh.par0,
    bh.par1,
    bf.prepaid_collect_ind,
    vp.p1,
    vp.p2,
    vp.p3,
    vp.p2_obj1_id,
    vp.obj1_of_call,
    bh.par2 As por,
    bh.par3,
    bh.par2_name,
    bh.par3_name,
    Nvl(brl.obj1_cd_from,bh.par0) As obj1_From,
    Nvl(brl.obj1_cd_to,bh.par1) As obj1_To,
    Decode(bh.bill_type,'M',(Select bh2.obj0_id From tw.bl_header bh2
           Where bh2.obj0 = (Select bh1.obj0 From tw.bl_header bh1 Where bh1.obj0_id = bh.orig_obj0_id)
           And bh2.bl_correction_version = 0),Null) As original_obj0_id,
    bh.orig_obj0_id,
    bh.bill_type,
    bh.issue_place,
(Select Rtrim(et_empcnt.edi_translation_code)
     From tw.edi_translation et_empcnt
     Where Rtrim(et_empcnt.edi_system_code)  = Rtrim(ee.edi_message_code)||Rtrim(ee.edi_message_version)
       And et_empcnt.edi_system_value        = 'EMPCNT'
       And et_empcnt.edi_translation_type    = 'EDIMSG'
       And Rtrim(et_empcnt.edi_partner_code) = Rtrim(vp.obj1_of_call)
   ) As Empty_Cont_Flag,
   ee.edi_o4_id,
   (Select Count(*) From tw.bl_edi_event bee, tw.edi_o4 eee
     Where bee.obj0_id In (Select obj0_id From tw.bl_header Where obj0 = bh.obj0)
      And eee.edi_o4_id      = bee.edi_o4_id
      And eee.edi_o4_id <> ee.edi_o4_id
      And eee.edi_partner_code    = ee.edi_partner_code
      And eee.edi_message_code    = ee.edi_message_code
      And eee.edi_message_version = ee.edi_message_version
      And eee.edi_event_code  = ee.edi_event_code
      And eee.edi_o4_status_code = 'EXTOK') As Sent_Count,
Decode(NVL(eve.event_status_code,Decode(ee.edi_event_code,'VSIMF','IM','EM')) ,
                'IM',Nvl ((Select Decode(brll.obj1_cd_from,bh.par1,'I',Null,Null,'T') From
         tw.t1_p3s brll
         Where brll.obj0_id = brl.obj0_id
         And brll.p3_sequence = (Select Min(p3_sequence) From tw.t1_p3s
                                    Where obj0_id = brll.obj0_id
                                    And p3_sequence > brl.p3_sequence)),
Decode(brl.obj1_cd_to,bh.par1,'I','T')),Decode(bh.bill_type ,'M','T',Decode(brl.obj1_cd_from,bh.par0,'E','T'))) As TS_Flag,
      (Select Decode(brl.obj1_cd_to,bh.par1,Null,Decode(brll.obj1_cd_from,bh.par1,Null,brll.obj1_cd_to)) From
         tw.t1_p3s brll
         Where
NVL(eve.event_status_code,Decode(ee.edi_event_code,'VSIMF','IM','EM')) = 'IM'
         And brll.obj0_id = brl.obj0_id
         And brll.p3_sequence = (Select Min(p3_sequence) From tw.t1_p3s
                                    Where obj0_id = brll.obj0_id
                                    And p3_sequence > brl.p3_sequence)) As next_obj1_to,
Nvl(eve.event_status_code,Decode(ee.edi_event_code,'VSIMF','IM','EM')) As event_flag,
         brl.p3_sequence
From
     tw.bl_header         bh,
     tw.edi_selection     es,
     tw.edi_o4       ee,
     tw.bl_edi_event      be,
     tw.p2_obj1s      vp,
     tw.bl_freight        bf,
     tw.edi_event         eve,
     tw.t1_p3s     brl
Where
     ee.edi_o4_id = 1544129587 --11714893--8933295--
     And eve.edi_event_code(+) = ee.edi_event_code
     And es.edi_o4_id = ee.edi_o4_id
     And vp.p2_obj1_id = es.edi_item_id
     And be.edi_o4_id = es.edi_o4_id
     And bh.obj0_id = be.obj0_id
     And bh.BK_BL = 'BL'
     And (bh.NVOCC ! =  'S' Or bh.NVOCC Is Null) 
     And ((bh.bill_type Not in ('Q','A','H')) Or bh.bill_type Is Null)
     And bh.bl_correction_version = 0
     And bf.obj0_id(+) = bh.obj0_id
     And bf.charge_code(+) = 'FRT'
     And bf.charge_group  = 1
     And brl.obj0_id = bh.obj0_id
     And (((eve.event_status_code = 'IM' Or (eve.event_status_code Is Null And ee.edi_event_code = 'VSIMF'))
         And brl.imp_p1 = vp.p1 And brl.imp_p2 = vp.p2 And brl.imp_p3 = vp.p3
         And brl.obj1_cd_to = vp.obj1_of_call)
         Or
         ((eve.event_status_code = 'EM' Or (eve.event_status_code Is Null And ee.edi_event_code = 'VSEMF'))
         And brl.p1 = vp.p1 And brl.p2 = vp.p2 And brl.p3 = vp.p3
         And brl.obj1_cd_from = vp.obj1_of_call))
     And Exists (select * from tw.bl_cargo_pack bcp where bcp.obj0_id = bh.obj0_id)
) 
Union
(Select
    bh.obj0,
    bh.obj0_id,
    bh.par0,
    bh.par1,
    bf.prepaid_collect_ind,
    vp.p1,
    vp.p2,
    vp.p3,
    vp.p2_obj1_id,
    vp.obj1_of_call,
    bh.par2 As por,
    bh.par3,
    bh.par2_name,
    bh.par3_name,
    bh.par0 As obj1_From,
    bh.par1 As obj1_To,
    Decode(bh.bill_type,'M',(Select bh2.obj0_id From tw.bl_header bh2
           Where bh2.obj0 = (Select bh1.obj0 From tw.bl_header bh1 Where bh1.obj0_id = bh.orig_obj0_id)
           And bh2.bl_correction_version = 0),Null) As original_obj0_id,
    bh.orig_obj0_id,
    bh.bill_type,
    bh.issue_place,
(Select Rtrim(et_empcnt.edi_translation_code)
     From tw.edi_translation et_empcnt
     Where Rtrim(et_empcnt.edi_system_code)  = Rtrim(ee.edi_message_code)||Rtrim(ee.edi_message_version)
       And et_empcnt.edi_system_value        = 'EMPCNT'
       And et_empcnt.edi_translation_type    = 'EDIMSG'
       And Rtrim(et_empcnt.edi_partner_code) = Rtrim(vp.obj1_of_call)
   ) As Empty_Cont_Flag,
   ee.edi_o4_id,
   (Select Count(*) From tw.bl_edi_event bee, tw.edi_o4 eee
     Where bee.obj0_id In (Select obj0_id From tw.bl_header Where obj0 = bh.obj0)
      And eee.edi_o4_id      = bee.edi_o4_id
      And eee.edi_o4_id <> ee.edi_o4_id
      And eee.edi_partner_code    = ee.edi_partner_code
      And eee.edi_message_code    = ee.edi_message_code
      And eee.edi_message_version = ee.edi_message_version
      And eee.edi_event_code  = ee.edi_event_code
      And eee.edi_o4_status_code = 'EXTOK') As Sent_Count,
Decode(NVL(eve.event_status_code,Decode(ee.edi_event_code,'VSIMF','IM','EM')),
'IM',Decode(vp.obj1_of_call,bh.par1,'I','T'),
'EM',Decode(bh.bill_type,'M','T',Decode(vp.obj1_of_call,bh.par0,'E','T'))) As TS_Flag,
Null As next_obj1_to,   
Nvl(eve.event_status_code,Decode(ee.edi_event_code,'VSIMF','IM','EM')) As event_flag,
Null As p3_seuence
From
     tw.bl_header         bh,
     tw.edi_selection     es,
     tw.edi_o4       ee,
     tw.bl_edi_event      be,
     tw.p2_obj1s      vp,
     tw.bl_freight        bf,
     tw.edi_event         eve
Where
     ee.edi_o4_id = 1544129587 --11714893--8933295--
     And eve.edi_event_code(+) = ee.edi_event_code
     And es.edi_o4_id = ee.edi_o4_id
     And vp.p2_obj1_id = es.edi_item_id
     And be.edi_o4_id = es.edi_o4_id
     And bh.obj0_id = be.obj0_id
     And bh.BK_BL = 'BL'
     And (bh.NVOCC ! =  'S' Or bh.NVOCC Is Null) 
     And ((bh.bill_type Not in ('Q','A','H')) Or bh.bill_type Is Null)
     And bh.bl_correction_version = 0
     And bf.obj0_id(+) = bh.obj0_id
And bf.charge_code(+) = 'FRT'
     And bf.charge_group  = 1
     And Exists (select * from tw.bl_cargo_pack bcp where bcp.obj0_id = bh.obj0_id)
     And Not Exists (Select '*' From tw.t1_p3s Where obj0_id =  bh.obj0_id)
))
krn,
tw.obj1_table            ptpar0,
tw.obj1_table            ptpar1,
tw.obj1_table            ptpf,
tw.obj1_table            ptpt,
tw.obj1_table            ptpor,
tw.obj1_table            ptpd,
tw.obj1_table            ptnpt,
tw.obj1_table            ptpoc,
tw.country_table     ctpd, --country_of_ultimate_destination
tw.country_table     ctpt,  --country of obj1_to
tw.country_table     ctpor,
tw.country_table     ctpar0,
tw.country_table     ctnpt,
tw.country_table     ctpoc,
tw.edi_translation   etpar0,
tw.edi_translation   etpar1,
tw.edi_translation   etpf,
tw.edi_translation   etpt,
tw.edi_translation   etpor,
tw.edi_translation   etpd,
tw.edi_translation   etporc,
tw.edi_translation   etptc,
tw.edi_translation   etpdc
Where 
   ptpar1.obj1_code(+) = krn.par1
And ptpar0.obj1_code(+) = krn.par0
And ptpor.obj1_code(+) = krn.por
And ptpf.obj1_code(+) = krn.obj1_from
And ptpt.obj1_code(+) = krn.obj1_to
And ptpd.obj1_code(+) = krn.par3
And ptnpt.obj1_code(+) = krn.next_obj1_to
And ptpoc.obj1_code(+) = krn.obj1_of_call
And ctpd.country(+) = ptpd.country
And ctpt.country(+) = ptpt.country
And ctpor.country(+) = ptpor.country
And ctpar0.country(+) = ptpar0.country
And ctnpt.country(+) =  ptnpt.country
And ctpoc.country(+) = ptpoc.country
And Rtrim(etpar0.edi_system_value(+)) = krn.par0
And etpar0.edi_system_code(+) = 'obj1'
And etpar0.edi_partner_code(+) = 'UNLOCOD'
And Rtrim(etpar1.edi_system_value(+)) = krn.par1
And etpar1.edi_system_code(+) = 'obj1'
And etpar1.edi_partner_code(+) = 'UNLOCOD'
And Rtrim(etpf.edi_system_value(+)) = krn.obj1_From
And etpf.edi_system_code(+) = 'obj1'
And etpf.edi_partner_code(+) = 'UNLOCOD'
And Rtrim(etpt.edi_system_value(+)) = krn.obj1_To
And etpt.edi_system_code(+) = 'obj1'
And etpt.edi_partner_code(+) = 'UNLOCOD'
And Rtrim(etpd.edi_system_value(+)) = krn.par3
And etpd.edi_system_code(+) = 'obj1'
And etpd.edi_partner_code(+) = 'UNLOCOD'
And Rtrim(etpor.edi_system_value(+)) = krn.por
And etpor.edi_system_code(+) = 'obj1'
And etpor.edi_partner_code(+) = 'UNLOCOD'
And Rtrim(etporc.edi_system_value(+)) = ptpor.country
And etporc.edi_system_code(+) = 'COUNTRY'
And etporc.edi_partner_code(+) = 'COUNTRY UN'
And Rtrim(etptc.edi_system_value(+)) = ptpt.country
And etptc.edi_system_code(+) = 'COUNTRY'
And etptc.edi_partner_code(+) = 'COUNTRY UN'
And Rtrim(etpdc.edi_system_value(+)) = ptpd.country
And etpdc.edi_system_code(+) = 'COUNTRY'
And etpdc.edi_partner_code(+) = 'COUNTRY UN'
And (Exists (Select '*' From tw.bl_units bu,tw.units_on_p1 uov
               Where bu.obj0_id = krn.obj0_id
               And uov.uov_id = bu.uov_id
               And ((krn.empty_cont_flag = 'N' And uov.unit_empty_full_ind = 'F')
                       Or Nvl(krn.empty_cont_flag,'Y') = 'Y'))
       Or
       Exists (Select '*' From tw.bl_cargo_pack bcp Where bcp.obj0_id = krn.obj0_id
               And Not Exists (Select '*' From tw.units_cargo uc Where uc.bl_cargo_pack_id = bcp.bl_cargo_pack_id)))    
Order By krn.obj0;
spo off
 

Open in new window

and have got this
 
09:27:22 SQL> @c:\pe\f2
Nvl(eve.event_status_code,Decode(ee.edi_event_code,'VSIMF','IM','EM')) As event_flag,
        *
ERROR at line 9:
ORA-00933: SQL command not properly ended

Why?
LVL 12
HuaMin ChenProblem resolverAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Geert GOracle dbaCommented:
yer calling a script with sql code.
you can only call a script with static parameters

sqlplus can do this via a column definition

sample:
column event_flag new_value event_flag_var

-- you'll need to limit the output to 1 line 
select Nvl(eve.event_status_code,Decode(ee.edi_event_code,'VSIMF','IM','EM')) As event_flag from yourtable

@c:\pe\f2 event_flag_var

Open in new window

0
Geert GOracle dbaCommented:
besides that ... for performance
sometimes it helps to move item = max(item) to a function
i see you have a few of those

   AND brl0.p3_sequence =
                     (SELECT MAX (p3_sequence)
                        FROM tw.t1_p3s
                       WHERE     obj0_id = brl0.obj0_id
                             AND p3_sequence < krn.p3_sequence
                             AND obj1_cd_to = krn.obj1_of_call))

Open in new window


-- you need to find the correct ordering 
create function maxp3seq (in_obj0_id in number, in_p3seq in number, in_obj1_of_call in number) returns number is 
  x number;
begin
  x := 0;
  begin
    SELECT p3_sequence into x 
    from (
      select p3_sequence
                        FROM tw.t1_p3s
                       WHERE     obj0_id = in_obj0_id
                             AND p3_sequence < in_p3seq
                             AND obj1_cd_to = in_obj1_of_call
      order by p3_sequence desc)
    where rownum < 2;
  exception 
    when no_data_found then 
      x := 0;
  end;
  return x;
end;
/

Open in new window


turns into :
   AND brl0.p3_sequence =maxp3seq(brl0.obj0_id, krn.p3_sequence, krn.obj1_of_call 

Open in new window

0
HuaMin ChenProblem resolverAuthor Commented:
How to resolve the current error encountered? As I cannot touch the DB, I would not create function to this query.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Geert GOracle dbaCommented:
for your error
that's odd ... your script has totally different text at line 9 ... are your sure you posted the correct script ?
0
Geert GOracle dbaCommented:
you are touching the DB with a query
so performance is not a problem then ?

my car doesn't have rubber tires ... but i can drive it
really ?

looool ! that's a good one :)
select '*' 

Open in new window

0
Geert GOracle dbaCommented:
that's a good one too :)
select count('*') 

Open in new window

0
Geert GOracle dbaCommented:
we don't have your database to verify everything ... a little more difficult for us
try the query on it's own in sqlplus without the spooling

sqlplus should give a line where it fails

and post the script as it is
posting an error line of the unformatted query doesn't help with a differently formatted query
0
HuaMin ChenProblem resolverAuthor Commented:
Yes, I am posting the current version and want to resolve the error encountered.
0
Geert GOracle dbaCommented:
the line 9 you posted :
   ptpar1.obj1_name As par1_name,

the error :
09:27:22 SQL> @c:\pe\f2 Nvl(eve.event_status_code,Decode(ee.edi_event_code,'VSIMF','IM','EM')) As event_flag,

looks like 2 very different lines to me
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Geert GOracle dbaCommented:
it's missing a )

Nvl(eve.event_status_code,Decode(ee.edi_event_code,'VSIMF','IM','EM')) ) As
0
johnsoneSenior Oracle DBACommented:
If you are running Oracle 12, you can use a function without creating one.  It can be defined and used in the SQL statement.  There is a simple example in the documentation here.
0
slightwv (䄆 Netminder) Commented:
I also tend to agree that the script you posted doesn't match the error.  In your script, I don't see where you are trying to use the command line parameter.

I also agree that the parameter you pare passing into the script will not work as you are using it.

If I take the script you provided and run it as-is, I get the error I expect since I don't have your tables:  
SQL> @myscript Nvl(eve.event_status_code,Decode(ee.edi_event_code,'VSIMF','IM','EM')) As event_flag,
tw.edi_translation   etpdc
   *
ERROR at line 267:
ORA-00942: table or view does not exist

Open in new window

0
HuaMin ChenProblem resolverAuthor Commented:
I did get problem to the query. What can be the reason?
0
Geert GOracle dbaCommented:
you mistyped ... ?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.