Solved

ORA-00933: SQL command not properly ended

Posted on 2013-06-27
2
546 Views
Last Modified: 2013-06-28
Hi,

I have this current sequel statement:

select cust_cd, ivc_cd, csh_dwr_cd, origin_store, trn_tp_cd, amt, emp_cd_o
p from ar_trn
  2  where cust_cd = '&cust'
  3  /
Enter value for cust: 00133667
old   2: where cust_cd = '&cust'
new   2: where cust_cd = '00133667'
 
CUST_CD    IVC_CD         CSH OR TRN     AMT EMP_CD_OP
---------- -------------- --- -- --- ------- ----------
00133667   ON ACCOUNT         01 FCR     200 JEREMY
00133667   TCBLDGMAINT        08 PCR     430 DSR
00133667   CADBLDGMAINT       01 PCR     120 DSR
00133667   0815101MPMF        01 SAL  360.01 TJG
00133667   ON ACCOUNT         01 GRC     424 JEREMY
00133667   ON ACCOUNT         01 FCR   434.8 JEREMY
00133667   0208101LBZJ        01 SAL     250 TJG
00133667   ON ACCOUNT         01 DTD     200 TJG
00133667   0208101LBZJ        01 DTC     200 TJG
00133667   0815101MPMF        01 DTD  324.99 TJG
00133667   0428201OQRO        01 DTC  324.99 TJG
00133667   0808201PLUS        01 SAL       0 BLB
00133667   ON ACCOUNT         01 FCR     221 DMB
00133667   0815101MPMF        08 FCR     385 JEREMY
00133667   LATE CHARGE        01 LC        1
00133667   ON ACCOUNT         01 GRC     299 JEREMY
00133667   LATE CHARGE        01 FC        1 TJG
00133667   ON ACCOUNT         01 ACR     100 TJG
00133667   VANDEPOL #4869     01 DTD     200 TJG
00133667   0815101MPMF        01 DTD     200 TJG
00133667   VANDEPOL #4869     01 DTC     200 TJG
 
CUST_CD    IVC_CD         CSH OR TRN     AMT EMP_CD_OP
---------- -------------- --- -- --- ------- ----------
00133667   0720201PIED    01  01 PMT  649.81 BLB
00133667   0720201PIED        01 SAL    1500 KRR
00133667   0428201OQRO        01 SAL     415 TJG
00133667   0910001JXFX        01 SAL 2384.22 BLB
00133667   CADBLDGMAINT       01 DTD     120 TJG
00133667   0815101MPMF        01 DTC     120 TJG
00133667   TCBLDGMAINT        01 DTD     430 TJG
00133667   0815101MPMF        01 DTC     430 TJG
00133667   0815101MPMF        01 DTD      50 TJG
00133667   0208101LBZJ        01 DTC      50 TJG
00133667   0910001JXFX    01  01 PMT 1561.22 TJG
00133667   ON ACCOUNT         01 DTD     823 TJG
00133667   0910001JXFX        01 DTC     823 TJG
00133667   LATE CHARGE        01 LC        1
00133667   LATE CHARGE        01 FC        1 BLB
00133667   0428201OQRO        01 DTD  324.99 TJG
00133667   0720201PIED        01 DTC  324.99 TJG
00133667   0720201PIED        01 FCR   525.2 JEREMY
00133667   0428201OQRO    01  01 PMT     415 DSR
00133667   ON ACCOUNT         01 ACR     100 DMB
00133667   0619900GKRX        00 SAL     965 KRR
 
CUST_CD    IVC_CD         CSH OR TRN     AMT EMP_CD_OP
---------- -------------- --- -- --- ------- ----------
00133667   0619900GKRX    01  00 PMT     644 DMB
00133667   ON ACCOUNT         01 DTD     321 DMB
00133667   0619900GKRX        00 DTC     321 DMB
 
45 rows selected




I'm trying to CONVERT the AMT column to currency.

This is what I have tried:

      '$' + CONVERT(varchar(12), amt, 1) AS amt

and

     CONVERT (varchar(12), amt, 1) AS amt


This is what I have now:

live> select cust_cd, ivc_cd, csh_dwr_cd, origin_store, trn_tp_cd,
  2  amt, emp_cd from ar_trn
  3  CONVERT (varchar(12), amt, 1) AS amt,
  4  where cust_cd = '&cust'
  5  /
Enter value for cust: 00133667
old   4: where cust_cd = '&cust'
new   4: where cust_cd = '00133667'
CONVERT (varchar(12), amt, 1) AS amt,
        *
ERROR at line 3:
ORA-00933: SQL command not properly ended

I know I'm close....hopefully someone can tell me what I'm missing.
0
Comment
Question by:drozeveld
[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
2 Comments
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 39282713
Several things wrong.  First CONVERT needs to be BEFORE the FROM.

Also, per the docs, CONVERT converts strings between character sets:
http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions034.htm#SQLRF00620

Try to_char:
select cust_cd, ivc_cd, csh_dwr_cd, origin_store, trn_tp_cd, to_char(amt,'$999,999.00') amt, emp_cd_op
from ar_trn
where cust_cd = '&cust'
/

You just need a format mask large enough to hold the largest value.
0
 
LVL 1

Author Closing Comment

by:drozeveld
ID: 39284509
WORKS PERFECTLY !!
Thank you!!
0

Featured Post

Comparison of Amazon Drive, Google Drive, OneDrive

What is Best for Backup: Amazon Drive, Google Drive or MS OneDrive? In this free whitepaper we look at their performance, pricing, and platform availability to help you decide which cloud drive is right for your situation. Download and read the results of our testing for free!

Question has a verified solution.

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

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

734 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