• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 603
  • Last Modified:

ORA-00933: SQL command not properly ended

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
drozeveld
Asked:
drozeveld
1 Solution
 
slightwv (䄆 Netminder) Commented:
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
 
drozeveldAuthor Commented:
WORKS PERFECTLY !!
Thank you!!
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now