• 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
  • Paul Clapham
  • Ron McLeod
  • paul wheaton
  • Devaka Cooray
Sheriffs:
  • Jeanne Boyarsky
  • Tim Cooke
  • Liutauras Vilda
Saloon Keepers:
  • Tim Moores
  • Tim Holloway
  • Stephan van Hulst
  • Carey Brown
  • Piet Souris
Bartenders:
  • salvin francis
  • Mikalai Zaikin
  • Himai Minh

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: 23544
161
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: 23544
161
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: 23544
161
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: 23544
161
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: 23544
161
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
 
if you think brussel sprouts are yummy, you should try any other food. And this tiny ad:
Thread Boost feature
https://coderanch.com/t/674455/Thread-Boost-feature
reply
    Bookmark Topic Watch Topic
  • New Topic