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
Solved

ORA-00933: SQL command not properly ended

Posted on 2013-06-27
2
541 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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
pivot rows to columns 1 34
capture vmstat info and insert it into an oracle table 31 35
Row_number in SQL 6 33
Fill Null values 5 28
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
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 information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

856 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