Skip to Main Content
  • Questions

  • Guidance to assist Student to learn SQL

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Dorothy.

Asked: August 08, 2019 - 1:42 pm UTC

Last updated: August 09, 2019 - 7:23 am UTC

Version: 10.1.2

Viewed 1000+ times

You Asked

What is wrong with the code and why will the script not run ?
<Code
SELECT employee_id || first_name || last_name ||
'joined the company in' || hire_date || 'and has'
TRUNC(SYSDATE-hire_date/31) || 'years experience'
FROM employees;
Code>
Thanks in advance,
Dorothy.

and Connor said...

OK, let's work through the pieces one at a time

SQL> SELECT employee_id || first_name || last_name ||
  2  'joined the company in' || hire_date || 'and has'
  3  TRUNC(SYSDATE-hire_date/31) || 'years experience'
  4  FROM hr.employees;
TRUNC(SYSDATE-hire_date/31) || 'years experience'
     *
ERROR at line 3:
ORA-00923: FROM keyword not found where expected


At line 3 I was expecting FROM, ie, the end of the SELECT hence I must be missing something on line 2, ie, I want to keep concatenating. So I add ||

SQL>
SQL>
SQL> SELECT employee_id || first_name || last_name ||
  2  'joined the company in' || hire_date || 'and has' ||
  3  TRUNC(SYSDATE-hire_date/31) || 'years experience'
  4  FROM hr.employees;
TRUNC(SYSDATE-hire_date/31) || 'years experience'
              *
ERROR at line 3:
ORA-00932: inconsistent datatypes: expected NUMBER got DATE


This is a bracketing issue. I am trying to do hiredate/31 (ie, date divided by number) which is illegal. I need to do the subtracting first, then the division. So I add brackets

SQL>
SQL> SELECT employee_id || first_name || last_name ||
  2  'joined the company in' || hire_date || 'and has' ||
  3  TRUNC((SYSDATE-hire_date)/31) || 'years experience'
  4  FROM hr.employees;

EMPLOYEE_ID||FIRST_NAME||LAST_NAME||'JOINEDTHECOMPANYIN'||HIRE_DATE||'ANDHAS'||TRUNC((SYSDATE-HIRE_DATE)/31)||'YEARSEXPERIENCE'
-------------------------------------------------------------------------------------------------------------------------------------------
100StevenKingjoined the company in17-JUN-03and has190years experience
101NeenaKochharjoined the company in21-SEP-05and has163years experience
102LexDe Haanjoined the company in13-JAN-01and has218years experience
103AlexanderHunoldjoined the company in03-JAN-06and has160years experience
104BruceErnstjoined the company in21-MAY-07and has143years experience
105DavidAustinjoined the company in25-JUN-05and has166years experience
106ValliPataballajoined the company in05-FEB-06and has159years experience
107DianaLorentzjoined the company in07-FEB-07and has147years experience
108NancyGreenbergjoined the company in17-AUG-02and has200years experience
109DanielFavietjoined the company in16-AUG-02and has200years experience
110JohnChenjoined the company in28-SEP-05and has163years experience
111IsmaelSciarrajoined the company in30-SEP-05and has163years experience
112Jose ManuelUrmanjoined the company in07-MAR-06and has158years experience
113LuisPoppjoined the company in07-DEC-07and has137years experience
...
...

107 rows selected.


OK, now its working. I'm adding a 'txt' alias just for a nicer header

SQL>
SQL> SELECT employee_id || first_name || last_name ||
  2  'joined the company in' || hire_date || 'and has' ||
  3  TRUNC((SYSDATE-hire_date)/31) || 'years experience' txt
  4  FROM hr.employees;

TXT
-------------------------------------------------------------------------------------------------------------------------------------------
100StevenKingjoined the company in17-JUN-03and has190years experience
101NeenaKochharjoined the company in21-SEP-05and has163years experience
102LexDe Haanjoined the company in13-JAN-01and has218years experience
103AlexanderHunoldjoined the company in03-JAN-06and has160years experience
104BruceErnstjoined the company in21-MAY-07and has143years experience
105DavidAustinjoined the company in25-JUN-05and has166years experience
106ValliPataballajoined the company in05-FEB-06and has159years experience
107DianaLorentzjoined the company in07-FEB-07and has147years experience
108NancyGreenbergjoined the company in17-AUG-02and has200years experience
109DanielFavietjoined the company in16-AUG-02and has200years experience
110JohnChenjoined the company in28-SEP-05and has163years experience
111IsmaelSciarrajoined the company in30-SEP-05and has163years experience
112Jose ManuelUrmanjoined the company in07-MAR-06and has158years experience
113LuisPoppjoined the company in07-DEC-07and has137years experience
...
...

107 rows selected.


Now I want to fix up that spacing, so I add in some more single spaces via concatenation

SQL>
SQL>
SQL> SELECT employee_id || ' '|| first_name || ' '|| last_name ||
  2  ' joined the company in ' || hire_date || ' and has ' ||
  3  TRUNC((SYSDATE-hire_date)/31) || ' years experience' txt
  4  FROM hr.employees;

TXT
-------------------------------------------------------------------------------------------------------------------------------------------
100 Steven King joined the company in 17-JUN-03 and has 190 years experience
101 Neena Kochhar joined the company in 21-SEP-05 and has 163 years experience
102 Lex De Haan joined the company in 13-JAN-01 and has 218 years experience
103 Alexander Hunold joined the company in 03-JAN-06 and has 160 years experience
104 Bruce Ernst joined the company in 21-MAY-07 and has 143 years experience
105 David Austin joined the company in 25-JUN-05 and has 166 years experience
106 Valli Pataballa joined the company in 05-FEB-06 and has 159 years experience
107 Diana Lorentz joined the company in 07-FEB-07 and has 147 years experience
108 Nancy Greenberg joined the company in 17-AUG-02 and has 200 years experience
109 Daniel Faviet joined the company in 16-AUG-02 and has 200 years experience
110 John Chen joined the company in 28-SEP-05 and has 163 years experience
111 Ismael Sciarra joined the company in 30-SEP-05 and has 163 years experience
112 Jose Manuel Urman joined the company in 07-MAR-06 and has 158 years experience
113 Luis Popp joined the company in 07-DEC-07 and has 137 years experience
...
...

107 rows selected.


And finally, if I want *years* of experience, then I probably want to divide by 365 not 31.


SQL>
SQL>
SQL> SELECT employee_id || ' '|| first_name || ' '|| last_name ||
  2  ' joined the company in ' || hire_date || ' and has ' ||
  3  TRUNC((SYSDATE-hire_date)/365) || ' years experience' txt
  4  FROM hr.employees;

TXT
-------------------------------------------------------------------------------------------------------------------------------------------
100 Steven King joined the company in 17-JUN-03 and has 16 years experience
101 Neena Kochhar joined the company in 21-SEP-05 and has 13 years experience
102 Lex De Haan joined the company in 13-JAN-01 and has 18 years experience
103 Alexander Hunold joined the company in 03-JAN-06 and has 13 years experience
104 Bruce Ernst joined the company in 21-MAY-07 and has 12 years experience
105 David Austin joined the company in 25-JUN-05 and has 14 years experience
106 Valli Pataballa joined the company in 05-FEB-06 and has 13 years experience
107 Diana Lorentz joined the company in 07-FEB-07 and has 12 years experience
108 Nancy Greenberg joined the company in 17-AUG-02 and has 16 years experience
109 Daniel Faviet joined the company in 16-AUG-02 and has 16 years experience
110 John Chen joined the company in 28-SEP-05 and has 13 years experience
111 Ismael Sciarra joined the company in 30-SEP-05 and has 13 years experience
112 Jose Manuel Urman joined the company in 07-MAR-06 and has 13 years experience
113 Luis Popp joined the company in 07-DEC-07 and has 11 years experience
...
...

107 rows selected.

SQL>
SQL>


And there we go :-)

Is this answer out of date? If it is, please let us know via a Comment
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.