arputh Contributor.
Contributor.
280 views

SQL Query return invalid number

Jump to solution

Hi,

 

We have sql query on a parameter field which is populated with a number. Due to the fact that the validation stores a varchar we have to convert the field to a number field.

 

In the specific query we use the IN Condition to include only specific number but the query return "ORA-01722: invalid number"

 

We have even created a view by converting the varchar to number and the view indicate that it is a "NUMBER" data type in the view but still getting same error.

 

Here is the query we used:

 

SELECT *
FROM
  (SELECT khd.request_id PROJECT_ID,
    khd.visible_parameter21 SEGMENT_TOWER,
    trunc(CAST(khd.parameter22 AS NUMBER),0) COMMERCIAL
  FROM kcrt_request_types kt ,
    kcrt_request_details kd,
    kcrt_req_header_details khd,
    kcrt_fg_pfm_project pfm,
    kcrt_requests k,
    pm_projects pm,
    pm_project_rollup proll
  WHERE kt.request_type_name ='CPMG - Project'
  AND kt.request_type_id     = kd.request_type_id
  AND kd.batch_number        = 1
  AND kd.request_id          = khd.request_id
  AND khd.request_id         = pfm.request_id
  AND pfm.request_id         = k.request_id
  AND k.status_code          = 'IN_PROGRESS'
  AND k.request_id           = pm.pfm_request_id
  AND pfm.request_id         = pm.pfm_request_id
  AND pm.rollup_id           = proll.rollup_id
  )sub
WHERE sub.commercial in (2,3,1)

 

Your assistance are greatly appreciated.

 

Regards,

 

Alfred

Alfred Puth
PPM Consultant
EOH Application Management
0 Likes
1 Solution

Accepted Solutions
Absent Member.. AlexSavencu Absent Member..
Absent Member..

Re: SQL Query return invalid number

Jump to solution
Hello, Alfred,

This error is caused by the native behaviour of Oracle data processing during query execution. In more details, Oracle will try to convert all values in the table regardless of your filters.

The solution is to a) replace the selection of parameter22 with a subquery or b) replace the selection parameter22 with a custom Oracle function based on request id.

Both options above will solve your issue.

Let me know if the above suffices.

Cheers
Alex

--remember to kudos people who helped solve your problem
6 Replies
Absent Member.. randull Absent Member..
Absent Member..

Re: SQL Query return invalid number

Jump to solution

Hi Alfred,

 

Please give it a try using the function to_number instead of the casting, maybe that is what is causing you the problem.

 

SELECT *
FROM
  (SELECT khd.request_id PROJECT_ID,
    khd.visible_parameter21 SEGMENT_TOWER,
    to_number(khd.parameter22) COMMERCIAL
  FROM kcrt_request_types kt ,
    kcrt_request_details kd,
    kcrt_req_header_details khd,
    kcrt_fg_pfm_project pfm,
    kcrt_requests k,
    pm_projects pm,
    pm_project_rollup proll
  WHERE kt.request_type_name ='CPMG - Project'
  AND kt.request_type_id     = kd.request_type_id
  AND kd.batch_number        = 1
  AND kd.request_id          = khd.request_id
  AND khd.request_id         = pfm.request_id
  AND pfm.request_id         = k.request_id
  AND k.status_code          = 'IN_PROGRESS'
  AND k.request_id           = pm.pfm_request_id
  AND pfm.request_id         = pm.pfm_request_id
  AND pm.rollup_id           = proll.rollup_id
  )sub
WHERE sub.commercial in (2,3,1)

 

Best regards,
Randall

-- Remember to give Kudos to answers! (click the KUDOS star)
"If you find that this or any post resolves your issue, please be sure to mark it as an accepted solution.”
0 Likes
arputh Contributor.
Contributor.

Re: SQL Query return invalid number

Jump to solution

Hi Randull,

 

Still the same error message:

 

ORA-01722: invalid number
01722. 00000 -  "invalid number"
*Cause:    
*Action:

Alfred Puth
PPM Consultant
EOH Application Management
0 Likes
Absent Member.. randull Absent Member..
Absent Member..

Re: SQL Query return invalid number

Jump to solution

Alfred,

 

I would recommend you to go little by little on this query to see where is it failing.

 

Best regards,
Randall

-- Remember to give Kudos to answers! (click the KUDOS star)
"If you find that this or any post resolves your issue, please be sure to mark it as an accepted solution.”
0 Likes
mike_se Contributor.
Contributor.

Re: SQL Query return invalid number

Jump to solution

Yes, I agree with Randall.

 

In the DEV environment, first hardcode the values to make sure everything works as expected. Then 1-by-1 replace each value using the select to see which exact one is causing the issue.

 

This way, will be able to tell the difference between the hardcoaded value and the value being returned by the database (may be an unexpected null?)

 

-Mike

“HP Support
If you find that this or any post resolves your issue, please be sure to mark it as an accepted solution.”
0 Likes
Absent Member.. AlexSavencu Absent Member..
Absent Member..

Re: SQL Query return invalid number

Jump to solution
Hello, Alfred,

This error is caused by the native behaviour of Oracle data processing during query execution. In more details, Oracle will try to convert all values in the table regardless of your filters.

The solution is to a) replace the selection of parameter22 with a subquery or b) replace the selection parameter22 with a custom Oracle function based on request id.

Both options above will solve your issue.

Let me know if the above suffices.

Cheers
Alex

--remember to kudos people who helped solve your problem
arputh Contributor.
Contributor.

Re: SQL Query return invalid number

Jump to solution

Hi Alex,

 

Thanks, works 100%

 

The sql statement used:

SELECT *
FROM
  (SELECT khd.request_id PROJECT_ID,
    khd.visible_parameter21 SEGMENT_TOWER,
    nvl((select to_number(kl.lookup_code) from knta_lookups kl where kl.lookup_type like 'Phils Priority' and kl.lookup_code = khd.parameter22),0) as comm_code
  FROM kcrt_request_types kt ,
    kcrt_request_details kd,
    kcrt_req_header_details khd,
    kcrt_fg_pfm_project pfm,
    kcrt_requests k,
    pm_projects pm,
    pm_project_rollup proll
  WHERE kt.request_type_name ='CPMG - Project'
  AND kt.request_type_id     = kd.request_type_id
  AND kd.batch_number        = 1
  AND kd.request_id          = khd.request_id
  AND khd.request_id         = pfm.request_id
  AND pfm.request_id         = k.request_id
  AND k.status_code          = 'IN_PROGRESS'
  AND k.request_id           = pm.pfm_request_id
  AND pfm.request_id         = pm.pfm_request_id
  AND pm.rollup_id           = proll.rollup_id
  )sub
WHERE sub.comm_code in (1,10,3)

Alfred Puth
PPM Consultant
EOH Application Management
0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.