Solved

ORA-00933: SQL command not properly ended

Posted on 2013-06-27
2
530 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
2 Comments
 
LVL 76

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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
The System Center Operations Manager 2012, known as SCOM, is a part of the Microsoft system center product that provides the user with infrastructure monitoring and application performance monitoring. SCOM monitors:   Windows or UNIX/LinuxNetwo…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

744 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now