0

I have SQL that works perfectly in OracleSQLDeveloper.

basis of the SQL : pulls day and defines the day of the month and then provides the shift and hours for the day in question.

i am trying to make the 3 dates parameters so i can vary them dynamically.

SQL:

    select t.*, dsp.day_work_hours as SHIFT  from 
(select employee, decode( extract(day from date '2016-05-17'), 1, day_1, 2, day_2, 3, day_3, 4, day_4, 5, day_5, 6, day_6,7, day_7,8, day_8,9, day_9,10, day_10, 11, day_11, 12, day_12, 13,day_13, 14,day_14,15,day_15,16,day_16,17,day_17,18,day_18,19,day_19,20,day_20,21,day_21,22,day_22,23,day_23,24,day_24,25,day_25,26,day_26,27,day_27,28,day_28,day_29,30,day_30,31,day_31) as day_shift 
from odb.location_site_emp_schl_tmplt
where group_month = extract(month from date '2016-05-17')
and group_year = extract(year from date '2016-05-17')) T 
inner join odb.daily_shift_pattern dsp on dsp.day_pattern = t.day_shift

EXCEL Cell formula it's referencing:

=TEXT(today,"YYYY-MM-DD")

the excel cell displays perfectly as well but if i run the SQL with parameters it states "expression missing"

any ideas why this isn't working?

Improve this question

1 Answer 1

Reset to default
1

Not having Oracle handy to test this, I would think the issue is that Excel stores the date as an integer value. So, while the display seems to match your string in the SQL, the actual value passed to the parameterized query does not.

My suggestion is to use 3 parameters, one each for the day, month, and year. Aside from improving performance (no date extraction), it will also ensure that the value types match between Excel and the query parameter.

So the query would look like this:

select t.*, dsp.day_work_hours as SHIFT  from 
(select employee, decode( param1, 1, day_1, 2, day_2, 3, day_3, 4, day_4, 5, day_5, 6, day_6,7, day_7,8, day_8,9, day_9,10, day_10, 11, day_11, 12, day_12, 13,day_13, 14,day_14,15,day_15,16,day_16,17,day_17,18,day_18,19,day_19,20,day_20,21,day_21,22,day_22,23,day_23,24,day_24,25,day_25,26,day_26,27,day_27,28,day_28,day_29,30,day_30,31,day_31) as day_shift 
from odb.location_site_emp_schl_tmplt
where group_month = param2
and group_year = param3) T 
inner join odb.daily_shift_pattern dsp on dsp.day_pattern = t.day_shift
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.