Pls Help with ORA-00933: SQL 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

 

[gmmastros]

Looks like you are using an Oracle database. This forum is specifically for Microsoft SQL Server. I encourage you to post your questions in an Oracle specific forum.

Glancing at your code, with an towards SQL in general, it looks like you are missing a comma.

[tt][blue]
END AS MAILTYPE[!],[/!] <- Missing comma here
[/blue][/tt]

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[penndro]

Hi Gmmastros,

I included the comma however I am still getting the same error. The cursor comes to set near the second "Select" statement. Could something be wrong there?

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

 

[gmmastros]

Looks like you are also missing an AND in your where clause.

[tt][blue]
[!]And [/!]a.cust_id IN ('5315832')
[/blue][/tt]

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[penndro]

Hi George,

I also included the And statement but still get the same error. I am running this code in PL/SQL.

Thanks,
Penndro

 

[penndro]

Can someone else help out with this one? Stuck...

Thanks,
Penndro

 

[r937]

i guess you missed the earlier reply which reminded you that this is the Microsoft SQL Server forum

you should ask your question in an Oracle forum

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[fredericofonseca]

if you are issuing both SQL statements in one go you will need the ";" at the end of each one.

Add to that the missing commas you were told already about

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886