Not able to assign values of select to variables in oracle-CodePudding

I have defined to nvachar variables and I am assigning like below

select sap_id, (CAST(TO_TIMESTAMP_TZ(bill_end_date, 'YYYY-MM-DD"T"HH24:MI:SSTZH:TZM') as date)- CAST(TO_TIMESTAMP_TZ(bill_start_date, 'YYYY-MM-DD"T"HH24:MI:SSTZH:TZM') as date)) as noofdays  
into V_SAPID_NOOFDAYS, V_NOOFDAYS 
from IPCOLO_IPFEE_CALC_BIL;

But it's giving me error as

ORA-01422: exact fetch returns more than requested number of rows

how to assign it and use it?

CodePudding user response：

Your query is returning multiple rows, and as you are including the ID in the results and aren't filtering (via a where clause) that seems to be what you expect and want.

You could loop over the results, which doesn't need any PL/SQL variables to be defined as the cursor does that internally:

for r in (
  select sap_id, (CAST(TO_TIMESTAMP_TZ(bill_end_date, 'YYYY-MM-DD"T"HH24:MI:SSTZH:TZM') as date)- CAST(TO_TIMESTAMP_TZ(bill_start_date, 'YYYY-MM-DD"T"HH24:MI:SSTZH:TZM') as date)) as noofdays  
  from IPCOLO_IPFEE_CALC_BIL
)
loop
  -- do something with each pair of values
  dbms_output.put_line(r.sap_id || ': ' || r.noofdays);
end loop;

Or you could define a record type and collection type, have a collection variable, and bulk collect into the collection. (Or separate collections for the two values, but then you have to keep track of them separately).

It depends what you need to do with the data once you've queried it.

As an aside, your calculation will give you fractions of days, so you might want to trunc or floor it to get an integer, again depending on what you are doing. It also ignores the time zones of the original strings - you are converting the strings to timestamps with time zones, but when you cast to date you're losing that. It's possible that could affect the result even if both times are in the same region, of they cross a DST boundary. But perhaps they're all UTC, so it wouldn't matter anyway. Still, you can subtract the timestamps to get an interval, and then extract the days from that.