Sprout new to oracle, the second time to write a temporary table, ora - 01722, invalid number, but for a long time did not find the reason, find, every brother please help, thank you
The create global temporary table TT_SALEDEPTTEMP
(
Shopid VARCHAR2 (4),
Shopname VARCHAR2 (32),
Deptid INTEGER,
The deptname VARCHAR2 (20),
Costvalue NUMBER (20, 2),
Salevalue NUMBER (20, 2),
Discvalue NUMBER (20, 2),
Resalevalue NUMBER (20, 2),
Hbcostvalue NUMBER (20, 2),
Hbsalevalue NUMBER (20, 2),
Hbdiscvalue NUMBER (20, 2),
Hbresalevalue NUMBER (20, 2)

)
On the commit delete rows;
- data preparation
Declare V_levelvalue number;
Begin the select levelvalue into V_levelvalue from deptlevel where deptlevelid=1;
Insert into TT_SALEDEPTTEMP
Select
A.s hopid, b.n ame shopname,
A. d. eptid, d.n ame the deptname,
A.c ostvalue, a.s alevalue, a. d. iscvalue, a.r esalevalue,
C. bcostvalue, c. bsalevalue, c. bdiscvalue, c. bresalevalue
The from
(select
Shopid,
Floor (deptid/v_levelvalue) deptid,
The sum (costvalue) costvalue,
The sum (salevalue) salevalue,
The sum (discvalue) discvalue,
The sum (salevalue - discvalue) resalevalue
The from
Rpt_saledept where 1=1 and to_char (sdate, '- DD YYYY - MM) between' 2020-06-07 'and' 2020-06-07 s'
Group by shopid, floor (deptid/v_levelvalue)) a,
Shop, b
(select shopid,
Floor (deptid/v_levelvalue) deptid,
The sum (costvalue) hbcostvalue,
The sum (salevalue) hbsalevalue,
The sum (discvalue) hbdiscvalue,
The sum (salevalue - discvalue) hbresalevalue
The from rpt_saledept where 1=1 and to_char (sdate, '- DD YYYY - MM) between' 2020-06-07 'and' 2020-06-07 s'
Group by shopid, floor (deptid/v_levelvalue)) c,
(select id, name from dept union select id, name from sgroup) d
Where a.s hopid=b.i d and a.s hopid=c.s. hopid and a. d. eptid=c.d eptid and a. d. eptid=d.i d;
end;
Error: General Error SQL.
ORA - 01722: invalid number
ORA - 06512: at line 1

CodePudding user response:

You see if there are data is not digital, and the sum computation operations,
Such as storage character of some columns, 'LKDJF' operation, will prompt invalid number

CodePudding user response:

Column sum arithmetic operations are number type, which saved data are Numbers, this I have already checked, I thought before the where the id and shopid and deptid type is not the same, then I check the table, for the number/type

CodePudding user response:

The select levelvalue into V_levelvalue from deptlevel
This table levelvalue data types?

CodePudding user response:

Change the block to the debug process

CodePudding user response:

Levelvalue type is number

CodePudding user response:

Rpt_saledept table structure also stick to create table in the form of,

CodePudding user response:

- Create table
The create table RPT_SALEDEPT
(
Sdate DATE not null,
Shopid VARCHAR2 (4) not null,
Deptid NUMBER (10) not null,
Saletaxrate NUMBER (20, 8) not null,
Salerate NUMBER (20, 8) not null,
Salevalue NUMBER (20, 8) not null,
Discvalue NUMBER (20, 8) not null,
Saletaxvalue NUMBER (20, 8) not null,
Costvalue NUMBER (20, 8) not null,
Msaletotal NUMBER (20, 8) not null,
Mdisctotal NUMBER (20, 8) not null,
Mtaxtotal NUMBER (20, 8) not null,
McOstvalue NUMBER (20, 8) not null
)
In tablespace DATA_SPC
Pctfree 10
Initrans 1
Maxtrans 255
Storage
(
Initial 64
Next 1
Minextents 1
The maxextents unlimited
);
- Add comments to the columns
Comment on the column RPT_SALEDEPT. Sdate
Is' date (date);
Comment on the column RPT_SALEDEPT. Shopid
Is the 'shop';
Comment on the column RPT_SALEDEPT. Deptid
Is' small class size;
Comment on the column RPT_SALEDEPT. Saletaxrate
Is' output tax rate;
Comment on the column RPT_SALEDEPT. Salerate
Is' the day sales accounted for;
Comment on the column RPT_SALEDEPT. Salevalue
Is the 'sales';
Comment on the column RPT_SALEDEPT. Discvalue
Is' discount ';
Comment on the column RPT_SALEDEPT. Saletaxvalue
Is' output taxes;
Comment on the column RPT_SALEDEPT. Costvalue
Is' cost of sales;
Comment on the column RPT_SALEDEPT. Msaletotal
Is' month accumulative total sales;
Comment on the column RPT_SALEDEPT. Mdisctotal
A total discount 'is';
Comment on the column RPT_SALEDEPT. Mtaxtotal
Output tax is' month cumulative ';
Comment on the column RPT_SALEDEPT. McOstvalue
The cost of sales total 'is';
- the Create/Recreate primary, unique and foreign key constraints
The alter table RPT_SALEDEPT
Add the constraint PK_RPT_SALEDEPT primary key (SDATE, SHOPID, DEPTID, SALETAXRATE)
Using the index
In tablespace INDX_SPC
Pctfree 10
Initrans 2
Maxtrans 255
Storage
(
Initial 64 k
Next 1 m
Minextents 1
The maxextents unlimited
);
This is rpt_saledept table architecture