I am attempting to use some string variables to return data from an Oracle SQL DB. I am able to define the variables and then use them in my select, but then no results are returned. When I just type out an example select using the values I want in the variables then I get results.
Here is the script I've got so far:
define Team_Name = concat(concat('''','100.100.1112'),'''');
define not_tcode_name1 = concat(concat('''','PTO'),'''');
define not_tcode_name2 = concat(concat('''','BRV'),'''');
define start_date = to_date('28/11/2021','dd/mm/yyyy');
define end_date = to_date('11/12/2021','dd/mm/yyyy');
select wd.dept_name
, wd.tcode_name "Time Code"
, wd.htype_name "Hour Type"
, wd.wrkd_minutes "Minutes Worked"
from workbrain.VIEW_MOD_WORK_DETAIL wd
where wd.dept_name = &Team_Name
and wd.wrkd_work_date between &start_date and &end_date
and wd.tcode_name <> ¬_tcode_name1
and wd.tcode_name <> ¬_tcode_name2
;
When I run this the Query Results are all blank. And the Script Output looks like this:
old:select wd.dept_name
, wd.tcode_name "Time Code"
, wd.htype_name "Hour Type"
, wd.wrkd_minutes "Minutes Worked"
from workbrain.VIEW_MOD_WORK_DETAIL wd
where wd.dept_name = &Team_Name
and wd.wrkd_work_date between &start_date and &end_date
and wd.tcode_name <> ¬_tcode_name1
and wd.tcode_name <> ¬_tcode_name2
new:select wd.dept_name
, wd.tcode_name "Time Code"
, wd.htype_name "Hour Type"
, wd.wrkd_minutes "Minutes Worked"
from workbrain.VIEW_MOD_WORK_DETAIL wd
where wd.dept_name = concat(concat('''','100.100.1112'),'''')
and wd.wrkd_work_date between to_date('28/11/2021','dd/mm/yyyy') and to_date('11/12/2021','dd/mm/yyyy')
and wd.tcode_name <> concat(concat('''','PTO'),'''')
and wd.tcode_name <> concat(concat('''','BRV'),'''')
>>Query Run In:Query Result 1
So I try running the new code:
select wd.dept_name
, wd.tcode_name "Time Code"
, wd.htype_name "Hour Type"
, wd.wrkd_minutes "Minutes Worked"
from workbrain.VIEW_MOD_WORK_DETAIL wd
where wd.dept_name = concat(concat('''','100.100.1112'),'''')
and wd.wrkd_work_date between to_date('28/11/2021','dd/mm/yyyy') and to_date('11/12/2021','dd/mm/yyyy')
and wd.tcode_name <> concat(concat('''','PTO'),'''')
and wd.tcode_name <> concat(concat('''','BRV'),'''');
And that returns nothing for each column.
However if I remove the concat(concat('''', and ),'''') from around the 3 variables for dept_name, and tcode_name then it works and returns values. Looks like this:
select wd.dept_name
, wd.tcode_name "Time Code"
, wd.htype_name "Hour Type"
, wd.wrkd_minutes "Minutes Worked"
from workbrain.VIEW_MOD_WORK_DETAIL wd
where wd.dept_name = '100.100.1112'
and wd.wrkd_work_date between to_date('28/11/2021','dd/mm/yyyy') and to_date('11/12/2021','dd/mm/yyyy')
and wd.tcode_name <> 'PTO'
and wd.tcode_name <> 'BRV'
;
I have tried with out the concat but then I just get an error:
SQL command not properly ended
Error at line: 13 Column 29
The Script Output gives me this for the new:
select wd.dept_name
, wd.tcode_name "Time Code"
, wd.htype_name "Hour Type"
, wd.wrkd_minutes "Minutes Worked"
from workbrain.VIEW_MOD_WORK_DETAIL wd
where wd.dept_name = 100.100.1112
and wd.wrkd_work_date between to_date('28/11/2021','dd/mm/yyyy') and to_date('11/12/2021','dd/mm/yyyy')
and wd.tcode_name <> PTO
and wd.tcode_name <> BRV
So the variables, though strings, are not being treated as strings. They don't automatically get the single quotes around them. And using concat isn't working either as that just adds it to the string instead of concating to single quotes.
I have also tried putting the concat in the where clause around where I'm using them, but then I get a different error saying:
missing right parenthesis
Error at Line: 13 Column 28
And I see that the Script Output shows this for the new:
select wd.dept_name
, wd.tcode_name "Time Code"
, wd.htype_name "Hour Type"
, wd.wrkd_minutes "Minutes Worked"
from workbrain.VIEW_MOD_WORK_DETAIL wd
where wd.dept_name = concat(concat('''',100.100.1112),'''')
and wd.wrkd_work_date between to_date('28/11/2021','dd/mm/yyyy') and to_date('11/12/2021','dd/mm/yyyy')
and wd.tcode_name <> concat(concat('''',PTO),'''')
and wd.tcode_name <> concat(concat('''',BRV),'''')
Now the strings don't have single quotes around them, so the concat fails thinking the first one is a number with too many decimals.
So, my question is, how do I get the variables to work right?
There could be anywhere from a few dozen to several hundred thousand returned rows. And I need to be able to copy them out to Excel or Word so using declare and a cursor and outputting using the DBMS_OUTPUT.PUT_LINE isn't going to work for me I don't think.
True, you made it work, but some pieces of code don't make much sense. For example:
define not_tcode_name1 = to_char('PTO');
'PTO' already is a string, so converting it to one (with the help of to_char function) is as if you applied to_number to 1 (although it already is a number) or - what we often see - to_date to sysdate (although sysdate is a function that returns date datatype).
From my point of view, what you should have done in the first place is to simply enclose substitution variables - that represent strings - into single quotes.
SQL> help define
DEFINE
------
Specifies a substitution variable and assigns a CHAR value to it, or
lists the value and variable type of a single variable or all variables.
DEF[INE] [variable] | [variable = text]
I don't have your table so I'll use Scott's sample schema and its emp table that contains info about employees. I'm interested in someone called KING.
This is what you did:
SQL> define v_ename = 'KING'
SQL> select empno, job, sal from emp where ename = &v_ename;
select empno, job, sal from emp where ename = KING
*
ERROR at line 1:
ORA-00904: "KING": invalid identifier
This is what you should have done:
SQL> select empno, job, sal from emp where ename = '&v_ename';
^ ^
EMPNO JOB SAL | |
---------- --------- ---------- single quotes here
7839 PRESIDENT 5000
SQL>
What about dates? They aren't strings. This is King's full record:
SQL> select * From emp where ename = 'KING';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ----------- ---------- ---------- ----------
7839 KING PRESIDENT 17-Nov-1981 5000 10
OK, so he was hired on 17th of November 1981. Let's set substitution variable to that value:
SQL> define v_hiredate = '17-Nov-1981'
SQL> select ename, job, sal from emp where hiredate = '&v_hiredate';
ENAME JOB SAL
---------- --------- ----------
KING PRESIDENT 5000
That worked, but only because v_hiredate - which is a string (have a look at what help define says) - matches date format which is recognizable by Oracle. What if I modify default date format for current session?
SQL> alter session set nls_date_format = 'yyyy.mm.dd';
Session altered.
SQL> select ename, job, sal from emp where hiredate = '&v_hiredate';
select ename, job, sal from emp where hiredate = '17-Nov-1981'
*
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected
Right; it doesn't work any more because Oracle was incapable of converting string '17-Nov-1981' into a valid date value.
If I match new format model, it works:
SQL> define v_hiredate = '1981.11.17'
SQL> select ename, job, sal from emp where hiredate = '&v_hiredate';
ENAME JOB SAL
---------- --------- ----------
KING PRESIDENT 5000
SQL>
So, carefully with that you're doing.
If that particular query (you posted) is the ONLY query you're running, perhaps you'd rather simply put all values into where clause, applying appropriate function(s) when necessary. If there are two (or more) queries, then yes - it makes sense to define substitution variables at the beginning of the script and use them wherever needed.