Help with ORA-00933 Command Not Properly Ended Error

[penndro]

I am not that advanced in coding and trying to modify someone else's code but getting SQL Command Not properly Ended Error.

I would appreciate it if someone would help me by taking a look and see what I am overlooking.


Here is the SQL Code:

select customer_site_number,soldto_num,customer_number, customer_name

from ops_owner.sm_customer

where soldto_num in ('0005112839')

---------------------------------------

select

b.soldto_num as CUSTOMER_NUMBER,

b.customer_name as CUSTOMER_NAME,

a.CUST_ID as CUST_ID,

ceil(a.weight*16) as OZ_BREAK,

round(a.weight,2) as LB_BREAK,



CASE a.MAILTYPE

WHEN 1 THEN 'FLAT'

WHEN 2 THEN 'IP'

WHEN 3 THEN 'MP'

WHEN 5 THEN 'PMDS'

WHEN 6 THEN 'BPM'

WHEN 7 THEN 'PM'

WHEN 8 THEN 'PN'

WHEN 9 THEN 'MM'

WHEN 20 THEN 'NFM-IP'

WHEN 30 THEN 'NFM-MP'

ELSE 'NUL'

END AS MAILTYPE



CASE a.DELIVERYMETHOD

WHEN 1 THEN 'STDA'

WHEN 2 THEN 'FIRST'

WHEN 3 THEN 'EXPRESS'

WHEN 4 THEN 'PRIORITY'

WHEN 5 THEN 'STDB'

WHEN 7 THEN 'STDB_SINGLE'

WHEN 8 THEN 'STDA_NONAUTO'

WHEN 9 THEN 'PARCEL PLUS BGT'

WHEN 10 THEN 'PARCEL PLUS EXP'

ELSE 'NUL'

END AS METHOD,



CASE

WHEN substr(a.BAG_ID,1,1) in '0' THEN 'EXP'

WHEN substr(a.BAG_ID,1,1) in '1' THEN 'SEL'

WHEN substr(a.BAG_ID,1,1) in '2' THEN 'PLS'

WHEN substr(a.BAG_ID,1,1) in '3' THEN 'BGT'

WHEN substr(a.BAG_ID,1,1) in '4' THEN '@HS'

WHEN substr(a.BAG_ID,1,1) in '5' THEN '@HD'

ELSE 'NUL'

END AS BAG_ID,



a.zone_manifest,

count(trackingnumber) as PIECES



FROM ops_owner.mail_data a join ops_owner.sm_customer b

on a.cust_id = b.customer_site_number



WHERE
a.billingdate between '01-JUN-10' and '31-AUG-10'

a.cust_id IN ('5315832')

and a.billingweight is not null


GROUP BY

a.CUST_ID,

ceil(a.weight*16),

round(a.weight,2),

a.MAILTYPE,

a.DELIVERYMETHOD,

b.soldto_num,

b.customer_name,

a.BAG_ID,

a.zone_manifest


Thanks,
Penndro

 

[Dagon]

Is this meant to be two separate SQL statements? If so, I would start by putting a semi-colon at the end of both of them so that Oracle knows where one ends and the next one starts.

For Oracle-related work, contact me through Linked-In.

 

[penndro]

Hi Dragon,

it should only be one statement but it is pulling data from two tables.

putting in semicolons did not fix the issue.

 

[Turkbear]

Hi,
You cannot use 2 select statements in one query so how does this query:

select customer_site_number,soldto_num,customer_number, customer_name

from ops_owner.sm_customer

where  soldto_num in ('0005112839')

relate to this one:

---------------------------------------

select

b.soldto_num as CUSTOMER_NUMBER,

b.customer_name as CUSTOMER_NAME, 

a.CUST_ID as CUST_ID,
 
ceil(a.weight*16) as OZ_BREAK,

round(a.weight,2) as LB_BREAK, 

 

CASE a.MAILTYPE

WHEN 1 THEN 'FLAT'

WHEN 2 THEN 'IP'

WHEN 3 THEN 'MP'

WHEN 5 THEN 'PMDS'

WHEN 6 THEN 'BPM'

WHEN 7 THEN 'PM'

WHEN 8 THEN 'PN'

WHEN 9 THEN 'MM'

WHEN 20 THEN 'NFM-IP'

WHEN 30 THEN 'NFM-MP'

ELSE 'NUL' 

END AS MAILTYPE

 

CASE a.DELIVERYMETHOD

WHEN 1 THEN 'STDA'

WHEN 2 THEN 'FIRST'

WHEN 3 THEN 'EXPRESS'

WHEN 4 THEN 'PRIORITY'

WHEN 5 THEN 'STDB'

WHEN 7 THEN 'STDB_SINGLE'

WHEN 8 THEN 'STDA_NONAUTO'

WHEN 9 THEN 'PARCEL PLUS BGT'

WHEN 10 THEN 'PARCEL PLUS EXP'

ELSE 'NUL' 

END AS METHOD,

 

CASE

WHEN substr(a.BAG_ID,1,1) in '0' THEN 'EXP'

WHEN substr(a.BAG_ID,1,1) in '1' THEN 'SEL'

WHEN substr(a.BAG_ID,1,1) in '2' THEN 'PLS'

WHEN substr(a.BAG_ID,1,1) in '3' THEN 'BGT'

WHEN substr(a.BAG_ID,1,1) in '4' THEN '@HS'

WHEN substr(a.BAG_ID,1,1) in '5' THEN '@HD'

ELSE 'NUL' 

END AS BAG_ID,

 

a.zone_manifest,

count(trackingnumber) as PIECES

 

FROM ops_owner.mail_data a join ops_owner.sm_customer b 

on a.cust_id = b.customer_site_number

 

WHERE
a.billingdate between '01-JUN-10' and '31-AUG-10'

a.cust_id IN ('5315832')

and a.billingweight is not null

 
GROUP BY

a.CUST_ID,

ceil(a.weight*16),

round(a.weight,2), 

a.MAILTYPE,

a.DELIVERYMETHOD,

b.soldto_num,

b.customer_name,

a.BAG_ID,

a.zone_manifest

To Paraphrase:"The Help you get is proportional to the Help you give.."