We help IT Professionals succeed at work.
Private
Troubleshooting Question

when i am trying to select from this query i get the output in sqldeveloperbut when i export it it is giving me invalid number error

32 Views
Last Modified: 2020-10-06
SELECT
     amount,
     ( ( substr(amount,1,instr(amount,':') - 1) * 60 * 60 ) + ( substr(amount,instr(amount,':') + 1,length(amount) ) * 60 ) ) / 3600
     new_amount
FROM
paycateditimport

I tried this too
((nvl(regexp_substr(amount,'[^:]+[^:]', 1,1 ),0) )*60) +
(nvl(regexp_substr(amount,'[^:]+[^:]', 1,2 ),0) ) ) /3600,2))) amount_,
&
to_number(trim(round(((nvl(REGEXP_SUBSTR (amount, '([a-zA-Z0-9]+):', 1, 1, NULL, 1), amount)*60*60 )+

in place of new_amount in the first query

Comment
Watch Question

Author

Commented:
my real query is

select empid Person_Number,
to_char(to_date(editdate,'MM/DD/YY'), 'YYYY-MM-DD') Effective_Date,
null Start_Time,
paycat Pay_Code_Name,
--to_number(to_char(( (substr( amount,1,  instr(amount , ':')-1 )*60*60)+ (substr( amount,  instr(amount , ':')+1, length(amount) )*60))/3600, '99.99')) Amount,
to_number(trim(round(((nvl(REGEXP_SUBSTR (amount, '([a-zA-Z0-9]+):', 1, 1, NULL, 1), amount)*60*60 )+
((nvl(regexp_substr(amount,'[^:]+[^:]', 1,1 ),0) )*60) +
(nvl(regexp_substr(amount,'[^:]+[^:]', 1,2 ),0) ) ) /3600,2))) amount_,
null Business_Structure_Job,
null Labor_Company_Level_1,
null Labor_Company_Level_2,
null Labor_Company_Level_3,
null Labor_Company_Level_4,
null Labor_Company_Level_5,
null Labor_Company_Level_6,
null Work_Rule,
null Comment_name,
null Comment_Note1,
null Comment_Note2,
null Comment_Note3
from  PAYCATEDITIMPORT
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
SQL Developer buffers the data so it doesn't return ALL the rows by default.

I don't know what you mean be 'export' but my guess is there are rows somewhere that cause the substrings to return a non-number.

You will need to double check that ALL the substrings return numbers.

Author

Commented:
when I run the query in sql*developer I am getting the output but when I am exporting the output to .csv format or excel format I am getting invalid number error

Author

Commented:
Hi you can just look at this query it is erroring out with invalid number when I am trying to export the output in excel or .csv format

SELECT
     amount,
     ( ( substr(amount,1,instr(amount,':') - 1) * 60 * 60 ) + ( substr(amount,instr(amount,':') + 1,length(amount) ) * 60 ) ) / 3600
     new_amount--,
      FROM
    paycateditimport
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
When you run in SQL Developer are you going to the end of the results?  Again, SQL Developer doesn't return the entire result set.  By default it only returns the first 50 rows and waits for you to page/scroll down before it returns more.

If you don't have any "bad" data in the first 50 rows, then you likely won't see the invalid number.

When you go to export to csv, it returns ALL the rows.

You have data somewhere in the table that causes one of your substr calls to return a non-number.

I don't have your data so I cannot help you find the "bad" data.

Author

Commented:
Hi

This is very critical issue

Thanks,

Author

Commented:
the data for amount like 8:30,  5:10

which I have to convert in the a decimal format
that's why I am using

to_number(to_char(( (substr( amount,1,  instr(amount , ':')-1 )*60*60)+ (substr( amount,  instr(amount , ':')+1, length(amount) )*60))/3600, '99.99')) Amount,

in my query
to convert the amount in the decimal format

I have stripped down the query to

select to_number(to_char(( (substr( amount,1,  instr(amount , ':')-1 )*60*60)+ (substr( amount,  instr(amount , ':')+1, length(amount) )*60))/3600, '99.99')) Amount
from  PAYCATEDITIMPORT

and I am getting invalid number error when trying to export the output is .csv or excel format
the query gives  7000 rows
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
>>the data for amount like 8:30,  5:10

"some" but NOT all.
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
all the data is in this format like  I have to covert it into decimals that's why I am using this query

select to_number(to_char(( (substr( amount,1,  instr(amount , ':')-1 )*60*60)+ (substr( amount,  instr(amount , ':')+1, length(amount) )*60))/3600, '99.99')) Amount
from  PAYCATEDITIMPORT


8:30
8:30
8:30
8:30
4:00
0:45
8:30
4:00
0:04
8:00
3:19
AMOUNT                        
------------------------------
0:04
8:00
3:19
0:04
8:00
8:00
8:00
8:00
8:00
8:00
8:00


CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
This should also work and is a little simpler:
( ( substr(amount,1,instr(amount,':') - 1) ) + ( substr(amount,instr(amount,':') + 1,length(amount) ) / 60 ) )

Open in new window


Again, not ALL your data has that format.

You need to run the SQL I provided to find the ones that aren't "valid".
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
After you find the "bad" data, this should also produce the same results:
to_date(amount,'HH24:MI') - trunc(sysdate,'mm') ) * 24

Open in new window

Author

Commented:
Thanks a lot for your help
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.