• 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 Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Tim Cooke
  • paul wheaton
  • Jeanne Boyarsky
  • Ron McLeod
Sheriffs:
  • Paul Clapham
  • Liutauras Vilda
  • Devaka Cooray
Saloon Keepers:
  • Tim Holloway
  • Roland Mueller
Bartenders:

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: 28480
210
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: 28480
210
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?
 
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: 28480
210
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: 28480
210
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: 28480
210
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
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
 
Those who dance are thought mad by those who hear not the music. This tiny ad plays the bagpipes:
Smokeless wood heat with a rocket mass heater
https://woodheat.net
reply
    Bookmark Topic Watch Topic
  • New Topic