asked on

Oracle SQL PLUS - Syntax

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

Muhammad Noman Iqbal

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

Raja Jegan R

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

dawber39

ASKER

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

SOLUTION

Muhammad Noman Iqbal

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dawber39

ASKER

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

dawber39

ASKER

This is becoming more difficult than I anticipated - I am increasing this to 500 points

Muhammad Noman Iqbal

Hi,

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

ASKER CERTIFIED SOLUTION

Raja Jegan R

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Muhammad Noman Iqbal

Hi dawber39,

rrjegan17 is right, he has identified the problem.

dawber39

ASKER

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

SOLUTION

Sharath S

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dawber39

ASKER

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

Sharath S

Can you provide some sample set for this query?

SELECT TO_CHAR(SLS_DTE,'ddd')
  FROM BASE_LOAD

Open in new window

dawber39

ASKER

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

Raja Jegan R

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.

dawber39

ASKER

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.

SOLUTION

awking00

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

awking00

I said of varchar2(9) when it should have been char(9), but the issue is the same.

dawber39

ASKER

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