Solved

Oracle SQL PLUS - Syntax

Posted on 2009-03-29
19
1,923 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
  • 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
 
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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 40

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 40

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 31

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 31

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
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…
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…
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.

708 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

19 Experts available now in Live!

Get 1:1 Help Now