Solved

Oracle SQL PLUS - Syntax

Posted on 2009-03-29
19
1,935 Views
Last Modified: 2013-12-18
I am trying to load the data from one table to another - The base table contains all the sales history - and I am trying to break it down into several tables Sale, Sale detail, and so on.  All the data types match. However,  - In the Detail table I have a DAY_ID field - and I am trying to load the table so that the day_id reflects the numbered day of the year. So far I have gotten several errors - and just when I think I have it I got this error:
ERROR at line 5:
ORA-00933: SQL command not properly ended

And I cannot figure it out any help would be appreciated - here is the layout No keys assigned yet -

SQL> desc base_load;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 SLS_DTE                                            CHAR(9)
 STORE_NUM                                          CHAR(4)
 REG_NUM                                            CHAR(1)
 TRANS_NUM                                          CHAR(4)
 SLS_TME                                            CHAR(4)
 CODE                                               CHAR(6)
 QTY                                                NUMBER(6)
 UNT_PRC                                            NUMBER(9,2)
 CATGRY                                             VARCHAR2(11)
 RGN                                                CHAR(1)
 ITM_DES                                            VARCHAR2(52)
 COUNTY_NAME                                        VARCHAR2(50)
 CAL_QTR                                            CHAR(2)
 REC_PRCS_DTE                                       CHAR(9)

SQL> desc sale_detail;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 STOR_NUM                                           CHAR(4)
 REG_NUM                                            CHAR(1)
 TRANS_NUM                                          CHAR(4)
 DAY_ID                                             NUMBER(3)
 CODE                                               CHAR(6)
 QTY_SOLD                                           NUMBER(6)
INSERT INTO SALE_DETAIL
SELECT TO_NUMBER(TO_CHAR(SLS_DTE, 'ddd')) DAY_ID, STORE_NUM, REG_NUM, 
TRANS_NUM, CODE, QTY_SOLD 
FROM BASE_LOAD
GROUP BY TO_NUMBER(TO_CHAR(SLS_DTE,'ddd')) STORE_NUM, REG_NUM, TRANS_NUM, CODE
ORDER BY DAY_ID,STORE_NUM,REG_NUM,TRANS_NUM,CODE;

Open in new window

0
Comment
Question by:dawber39
[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
  • 8
  • 4
  • 3
  • +2
19 Comments
 
LVL 4

Expert Comment

by:Nouman
ID: 24012628
Hi,

when writing insert with select statement you have to follow the column order In your Select as it is in Database Table.


INSERT INTO SALE_DETAIL
    SELECT STORE_NUM, REG_NUM, TRANS_NUM, TO_NUMBER(TO_CHAR(SLS_DTE, 'ddd')) DAY_ID, CODE, QTY_SOLD
      FROM BASE_LOAD
     GROUP BY TO_NUMBER(TO_CHAR(SLS_DTE, 'ddd')) STORE_NUM, REG_NUM, TRANS_NUM, CODE
     ORDER BY DAY_ID, STORE_NUM, REG_NUM, TRANS_NUM, CODE;

Open in new window

0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24012650
Hope this is what you require:
Do you need to find SUM or Count of QTY_SOLD, I have placed it as SUM use COUNT if required.
INSERT INTO SALE_DETAIL (DAY_ID, STORE_NUM, REG_NUM, TRANS_NUM, CODE, QTY_SOLD )
SELECT TO_NUMBER(TO_CHAR(SLS_DTE, 'ddd')) DAY_ID, STORE_NUM, REG_NUM, TRANS_NUM, CODE, SUM(QTY_SOLD )
FROM BASE_LOAD
GROUP BY TO_NUMBER(TO_CHAR(SLS_DTE,'ddd')), STORE_NUM, REG_NUM, TRANS_NUM, CODE
ORDER BY DAY_ID,STORE_NUM,REG_NUM,TRANS_NUM,CODE;

Open in new window

0
 

Author Comment

by:dawber39
ID: 24013390
Nouman  and rrjegan - I tries both of your statements - and they were returned as below with the error below it.

The only change I made in youre regan was from STTORE to STOR for the detail table
SQL> INSERT INTO SALE_DETAIL
  2      SELECT STORE_NUM, REG_NUM, TRANS_NUM, TO_NUMBER(TO_CHAR(SLS_DTE, 'ddd')) DAY_ID, CODE, QTY_
SOLD
  3        FROM BASE_LOAD
  4       GROUP BY TO_NUMBER(TO_CHAR(SLS_DTE, 'ddd')) STORE_NUM, REG_NUM, TRANS_NUM, CODE
  5       ORDER BY DAY_ID, STORE_NUM, REG_NUM, TRANS_NUM, CODE;
     GROUP BY TO_NUMBER(TO_CHAR(SLS_DTE, 'ddd')) STORE_NUM, REG_NUM, TRANS_NUM, CODE
                                                 *
ERROR at line 4:
ORA-00933: SQL command not properly ended
 
 
 
 
 
 
 
 
 
SQL> INSERT INTO SALE_DETAIL (DAY_ID, STOR_NUM, REG_NUM, TRANS_NUM, CODE, QTY_SOLD )
  2  SELECT TO_NUMBER(TO_CHAR(SLS_DTE, 'ddd')) DAY_ID, STORE_NUM, REG_NUM, TRANS_NUM, CODE, SUM(QTY)
 
  3  FROM BASE_LOAD
  4  GROUP BY TO_NUMBER(TO_CHAR(SLS_DTE,'ddd')), STORE_NUM, REG_NUM, TRANS_NUM, CODE
  5  ORDER BY DAY_ID,STORE_NUM,REG_NUM,TRANS_NUM,CODE;
GROUP BY TO_NUMBER(TO_CHAR(SLS_DTE,'ddd')), STORE_NUM, REG_NUM, TRANS_NUM, CODE
                           *
ERROR at line 4:
ORA-01722: invalid number

Open in new window

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 4

Assisted Solution

by:Nouman
Nouman earned 175 total points
ID: 24013496
Hi,

there is a comma missing,


INSERT INTO SALE_DETAIL
    SELECT STORE_NUM, REG_NUM, TRANS_NUM, TO_NUMBER(TO_CHAR(SLS_DTE, 'ddd')) DAY_ID, CODE, QTY_SOLD
      FROM BASE_LOAD
     GROUP BY TO_NUMBER(TO_CHAR(SLS_DTE, 'ddd')), STORE_NUM, REG_NUM, TRANS_NUM, CODE
     ORDER BY DAY_ID, STORE_NUM, REG_NUM, TRANS_NUM, CODE;

Open in new window

0
 

Author Comment

by:dawber39
ID: 24013541
I think we are getting close - I did have to change Qty_Sold to QTY (named as such in base_load)

it has something to do with the group by expression now - as noted below
SQL> INSERT INTO SALE_DETAIL
  2      SELECT STORE_NUM, REG_NUM, TRANS_NUM, TO_NUMBER(TO_CHAR(SLS_DTE, 'ddd')) DAY_ID, CODE, QTY
  3        FROM BASE_LOAD
  4       GROUP BY TO_NUMBER(TO_CHAR(SLS_DTE, 'ddd')), STORE_NUM, REG_NUM, TRANS_NUM, CODE
  5       ORDER BY DAY_ID, STORE_NUM, REG_NUM, TRANS_NUM, CODE;
    SELECT STORE_NUM, REG_NUM, TRANS_NUM, TO_NUMBER(TO_CHAR(SLS_DTE, 'ddd')) DAY_ID, CODE, QTY
                                                                                           *
ERROR at line 2:
ORA-00979: not a GROUP BY expression

Open in new window

0
 

Author Comment

by:dawber39
ID: 24013677
This is becoming more difficult than I anticipated - I am increasing this to 500 points
0
 
LVL 4

Expert Comment

by:Nouman
ID: 24013705
Hi,

do you want to sum the quantity, if yes then in select clause instead of qty write sum(qty)
0
 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 200 total points
ID: 24013740
FYI, I have fixed the Comma and Group by issue in my earlier query itself.

does your SALE_DETAIL table contains these columns (DAY_ID, STORE_NUM, REG_NUM, TRANS_NUM, CODE, QTY_SOLD )

Slight change to my earlier query;

<< ORA-01722: invalid number >>

Does your SLS_DTE column contains valid DATE values.
INSERT INTO SALE_DETAIL (DAY_ID, STORE_NUM, REG_NUM, TRANS_NUM, CODE, QTY_SOLD )
SELECT TO_NUMBER(TO_CHAR(SLS_DTE, 'ddd')) DAY_ID, STORE_NUM, REG_NUM, TRANS_NUM, CODE, SUM(QTY)
FROM BASE_LOAD
GROUP BY TO_NUMBER(TO_CHAR(SLS_DTE,'ddd')), STORE_NUM, REG_NUM, TRANS_NUM, CODE
ORDER BY DAY_ID,STORE_NUM,REG_NUM,TRANS_NUM,CODE;

Open in new window

0
 
LVL 4

Expert Comment

by:Nouman
ID: 24013785
Hi dawber39,

rrjegan17 is right, he has identified the problem.

0
 

Author Comment

by:dawber39
ID: 24013970
I tried it - at first I got an invalid Identifier message referring to the STORE_NUM - inthe detail table it is labeled STOR_NUM - but after I changed that the result was as follows.


I really appreciate you efforts here in helping me with this
SQL> INSERT INTO SALE_DETAIL (DAY_ID, STOR_NUM, REG_NUM, TRANS_NUM, CODE, QTY_SOLD )
  2  SELECT TO_NUMBER(TO_CHAR(SLS_DTE, 'ddd')) DAY_ID, STORE_NUM, REG_NUM, TRANS_NUM, CODE, SUM(QTY)
 
  3  FROM BASE_LOAD
  4  GROUP BY TO_NUMBER(TO_CHAR(SLS_DTE,'ddd')), STORE_NUM, REG_NUM, TRANS_NUM, CODE
  5  ORDER BY DAY_ID,STORE_NUM,REG_NUM,TRANS_NUM,CODE;
GROUP BY TO_NUMBER(TO_CHAR(SLS_DTE,'ddd')), STORE_NUM, REG_NUM, TRANS_NUM, CODE
                           *
ERROR at line 4:
ORA-01722: invalid number

Open in new window

0
 
LVL 41

Assisted Solution

by:Sharath
Sharath earned 75 total points
ID: 24014001
Can you run this only and check whether you are getting any error.
SELECT TO_NUMBER(TO_CHAR(SLS_DTE,'ddd')) 
  FROM BASE_LOAD

Open in new window

0
 

Author Comment

by:dawber39
ID: 24014021
I believe that is where the problem is  - as noted below
SQL> SELECT TO_NUMBER(TO_CHAR(SLS_DTE,'ddd')) 
  2    FROM BASE_LOAD;
SELECT TO_NUMBER(TO_CHAR(SLS_DTE,'ddd'))
                         *
ERROR at line 1:
ORA-01722: invalid number

Open in new window

0
 
LVL 41

Expert Comment

by:Sharath
ID: 24014032
Can you provide some sample set for this query?
SELECT TO_CHAR(SLS_DTE,'ddd')
  FROM BASE_LOAD

Open in new window

0
 

Author Comment

by:dawber39
ID: 24014056
That query returned a similar error -
SQL> SELECT TO_CHAR(SLS_DTE,'ddd')
  2    FROM BASE_LOAD;
SELECT TO_CHAR(SLS_DTE,'ddd')
               *
ERROR at line 1:
ORA-01722: invalid number

Open in new window

0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24014073
Check whether SLS_DTE contains Valid date Values as mentioned in my comment 24013740
Also check whether it is NULL or not.

And try to use NVL function to handle those NULL values to some valid date so that you can resolve your problem.
0
 

Author Comment

by:dawber39
ID: 24014118
There aren't any NULL SLS_DTE - and I am not sure how to verify that the rest are valid.

There are over 13 million records in the BASE_LOAD table - This has been done before using the same data - so I am almost sure that the positive in the SLS_DTE field is correct.
0
 
LVL 32

Assisted Solution

by:awking00
awking00 earned 50 total points
ID: 24019404
I think the problem may be that SLS_DTE is of varchar2(9) datatype. I have no idea what the format is, but assuming DD-MON-YY, you need to modify the TO_CHAR(SLS_DTE,'ddd') to TO_CHAR(TO_DATE(SLS_DTE,'DD-MON-YY'),'ddd').
0
 
LVL 32

Expert Comment

by:awking00
ID: 24019418
I said of varchar2(9) when it should have been char(9), but the issue is the same.
0
 

Author Comment

by:dawber39
ID: 24024487
Unfortunately my system suffered the wrath of a lightning storm last night while I was in the process of trying to populate the sale_detail table - At the time  - I still had not created a backup - needless to say the db was fried. Thank god it was only test data

I will split the points where I think appropriate and I will be back -

Thanks for all your help
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
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…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

726 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