Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

ORA-00933: SQL command not properly ended

Posted on 2013-06-27
2
Medium Priority
?
580 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 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

877 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