• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Paul Clapham
  • Jeanne Boyarsky
  • Ron McLeod
  • Tim Cooke
Sheriffs:
  • Devaka Cooray
  • paul wheaton
  • Mark Herschberg
Saloon Keepers:
  • Tim Moores
  • Tim Holloway
  • Stephan van Hulst
  • Frits Walraven
  • Jj Roberts
Bartenders:
  • Carey Brown
  • salvin francis
  • Piet Souris

PL/SQL calculation

 
Ranch Hand
Posts: 34
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Saloon Keeper
Posts: 23259
158
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 34
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Yes sorry. The first 8 characters of pnr should be substracted with sysdate and then printed
 
Tim Holloway
Saloon Keeper
Posts: 23259
158
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 34
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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?
 
Master Rancher
Posts: 4801
50
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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:

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
Ranch Hand
Posts: 34
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Im sorry i didnt really understand that. Could you give a code example please?
 
Tim Holloway
Saloon Keeper
Posts: 23259
158
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
OK, that makes much more sense.

I think probably this formula:

... ought to be a good start.
 
Hugo Nilsson
Ranch Hand
Posts: 34
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thanks for your help guys but i cant get it to work
 
Tim Holloway
Saloon Keeper
Posts: 23259
158
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 34
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Saloon Keeper
Posts: 23259
158
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Master Rancher
Posts: 4801
50
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
The harder I work, the luckier I get. -Sam Goldwyn So tiny. - this ad:
the value of filler advertising in 2021
https://coderanch.com/t/730886/filler-advertising
reply
    Bookmark Topic Watch Topic
  • New Topic