invalid number error in concatination 1

[chochoo]

hi all
i m doing a very simple query i don't know where is the problem. i tried this one
SELECT 'INSERT INTO TRIAL(AMOUNT) VALUES('||NVL(SUM(COL1),0)-NVL(SUM(COL2),0)||');'
FROM TAB1,TAB2,TAB3
WHERE COND1
AND COND2;
I GOT ERROR AT THIS POINT " VALUES('||NVL(SUM
*
ORA-01722 invalid number
I'VE TRIED THIS QUERY WITHOUT CONCATINATION , AND GOT TRUE RESULTS , BUT I WANT TO MAKE A SPOOL FILE OF THIS AND DON'T KNOW HOW TO REMOVE THIS ERROR. please guide me
thanks in advance

 

[SantaMufasa]

ChooChoo,

As you probably know, Oracle will not allow an invalid number to reside in a column defined as numeric. Therefore, to get this error, COL1 and/or COL2 are defined as something non-numeric. But, since you are doing a SUM() function on both COL1 and COL2, all data in each column must be numeric (which in and of itself is a bit puzzling from a design perspective). So, to find out the number of rows with which you are having problems, you can say:

select count(*)
FROM TAB1,TAB2,TAB3
WHERE COND1
AND COND2
AND (length(translate(COL1,'^0123456789','^')) is not null
  or length(translate(COL2,'^0123456789','^')) is not null);

The count(*) will be at least 1. Then to see the entries that are offending, you can say:

select col1, col2
FROM TAB1,TAB2,TAB3
WHERE COND1
AND COND2
AND (length(translate(COL1,'^0123456789','^')) is not null
  or length(translate(COL2,'^0123456789','^')) is not null);

Let us know your findings.

Mufasa
(aka Dave of Sandy, Utah, USA @ 17:18 (12Sep04) UTC (aka "GMT" and "Zulu"), 10:18 (12Sep04) Mountain Time)

 

[HimanB]

ChooChoo,
Probably the problem is not that you have a invalid no. but is of syntax.

Look at the following example:

SQL> select 'Insert into ABC values('||nvl(sal,0)-nvl(comm,0)||');' from emp;
select 'Insert into ABC values('||nvl(sal,0)-nvl(comm,0)||');' from emp
                                 *
ERROR at line 1:
ORA-01722: invalid number

Now I change this code to enclose nvl(sal,0)-nvl(comm,0) in parenthesis as follows:

  1* select 'Insert into ABC values('||(nvl(sal,0)-nvl(comm,0))||');' from emp
SQL> /

'INSERTINTOABCVALUES('||(NVL(SAL,0)-NVL(COMM,0))||');'
-----------------------------------------------------------------
Insert into ABC values(3400);
Insert into ABC values(1300);
Insert into ABC values(750);
Insert into ABC values(2975);
Insert into ABC values(-150);
Insert into ABC values(2850);
Insert into ABC values(2450);
Insert into ABC values(3000);
Insert into ABC values(5000);
Insert into ABC values(1500);
Insert into ABC values(1100);
Insert into ABC values(950);
Insert into ABC values(3000);
Insert into ABC values(1300);
Insert into ABC values(2490);
Insert into ABC values(3400);
Insert into ABC values(0);
Insert into ABC values(90);
Insert into ABC values(1300);
Insert into ABC values(0);

20 rows selected.

So, what you require is a minor change in your query as follows:

SELECT 'INSERT INTO TRIAL(AMOUNT) VALUES('||[b]([/b]NVL(SUM(COL1),0)-NVL(SUM(COL2),0)[b])[/b]||');'
FROM TAB1,TAB2,TAB3
WHERE COND1
AND COND2;

HTH
Regards
Himanshu

 

[chochoo]

thanks both of u for replying. i've checked my columsn they contain only number and the remedy of himan works and i got my desired results thanks Himan for that.