PL/SQL calculation

[Hugo Nilsson]

So i have to make a calculation in pl/sql and i dont think im that far off. The code im trying is:

declare
v_pnr varchar2(13);
v_fnamn varchar2(20);
v_enamn varchar2(20);

begin
select to_char(sysdate,'YYYY-MM-DD') - substr(pnr,1,8),fnamn,enamn
into v_pnr,v_fnamn,v_enamn
from bilägare;
dbms_output.put_line('Namn: '||v_fnamn||', '||'Efternamn: '||v_enamn||', '||'Ålder: '||v_pnr);
end;

The first 8 characters of pnr should be subtracted with sysdate. Maybe i need some form of for loop of cursor loop? Any help would be appreciated

[Tim Holloway]

I have not the slightest idea of what you are trying to do there. My best guess is that you are attempting to obtain part of the system date into a string, but you are doing it all with character string operations when it would be much simpler to use date arithmetic and/or format the date directly instead of formatting the entire date and then attempting to cut stuff out of it.

So could you give us some examples of what you are expecting the inputs and results of that expression to look like?

[Hugo Nilsson]

Yes sorry. The first 8 characters of pnr should be substracted with sysdate and then printed

[Tim Holloway]

I still do not understand and it's partly language translation, I'm sure, but I would not "subtract characters" from either a date or a character string. So if you could should what actual data looks like it would help.

[Hugo Nilsson]

My tables look like this:

CREATE TABLE bilägare(
pnr VARCHAR2(13) PRIMARY KEY,
fnamn VARCHAR2(20),
enamn VARCHAR2(20),
bor_i VARCHAR2(20),
jobbar_i VARCHAR2(20));

CREATE TABLE fordon(
regnr VARCHAR2(6) PRIMARY KEY,
pnr REFERENCES bilägare(pnr),
tillverkare VARCHAR2(20),
modell VARCHAR2(20),
årsmodell NUMBER(4),
hk NUMBER(4),
datum DATE);

I need to subtract the first 8 numbers in the pnr strings(which are birthdays like this:YYYYMMDD) with sysdate to get the age with one decimal like this:

Hans, Rosenboll, 65,6 år.
Tomas, Kvist, 60,7 år.
Roger, Nyberg, 49,1 år.
Lena, Malm, 43,4 år.
Ollas, Bullas, 45,6 år.
Tåmmy, Dåmert, 55,5 år.
Rollf, Ekengren, 53,6 år.
Maria, Stjärnkvist, 33,5 år.
Leyla, Errstraid, 42,2 år.
Arne, Möller, 76,6 år.

Does that make it clearer?

[Dave Tolls]

I would say that's "how do I extract the date stamp that is the first 8 characters of my PNR value"?

The time subtraction is just a case of using the available date functions in Oracle.

The code you have at the moment:

 to_char(sysdate,'YYYY-MM-DD') - substr(pnr,1,8)

is actually backwards.
You want to to_date the first 8 characters of the PNR, and then subtract them from sysdate, to get a duration.

[Hugo Nilsson]

Im sorry i didnt really understand that. Could you give a code example please?

[Tim Holloway]

OK, that makes much more sense.

I think probably this formula:

SELECT ROUND(MONTHS_BETWEEN(SYSDATE, pnr)/12.0, 1) AS age_5, ...

... ought to be a good start.

[Hugo Nilsson]

Thanks for your help guys but i cant get it to work

[Tim Holloway]

Although Oracle error messages are often pretty worthless, still it would help if you could post the error message anyway.

As we say in our FAQ, '"It doesn't work" is useless.'

[Hugo Nilsson]

When i used the suggested line: select to_date(substr(pnr,1,8)) - sysdate
I get:  literal does not match format string ORA-06512.
And when i use the suggested line: select ROUND(MONTHS_BETWEEN(SYSDATE, pnr)/12.0, 1) AS age_5
I get the same error

[Tim Holloway]

Yep, Oracle messages are awful.

Your problem is that pnr isn't a date, it's a character string. And it's a very strange thing to use as a primary key, as it allows only one person to be born per day.

Date/time values are bad for primary keys anyway since they have many of the same precision problems that floating-point numbers do, In particular, Java Date classes have diffferent granularities than Oracle's DATE data types.

To make my sample work, you'd have to use the TO_DATE() function to convert pnr from a string (VARCHAR) to a DATE object.

[Dave Tolls]

to_date needs to be told what format the string you want to convert is in.
See:
https://www.techonthenet.com/oracle/functions/to_date.php