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 11
HuaMin ChenSystem AnalystAsked:
Who is Participating?
 
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
 
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
HuaMin ChenSystem AnalystAuthor Commented:
How to resolve the current error encountered? As I cannot touch the DB, I would not create function to this query.
0
 
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 ChenSystem AnalystAuthor Commented:
Yes, I am posting the current version and want to resolve the error encountered.
0
 
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 ChenSystem AnalystAuthor Commented:
I did get problem to the query. What can be the reason?
0
 
Geert GOracle dbaCommented:
you mistyped ... ?
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.