1

I'm trying to put into my select a date parameter. I'm not sure how really to do this. Right now I have fixed date value in it and now I want to have a parameter like: StartDate and EndDate. I'll be really grateful for any help. My select:

select laborcode,
(select sum(workhours) from workperiod where calnum='...'and workdate between to_date('01.02.2020','DD.MM.YYYY') and to_date('01.03.2020','DD.MM.YYYY')) -  
(select count(calnum) from workperiod where calnum='...'and workdate between to_date('01.02.2020','DD.MM.YYYY') and to_date('01.03.2020','DD.MM.YYYY') and shiftnum='HOLIDAY') * 8 as alias,
(select sum(lt.regularhrs) from labtrans lt where lt.laborcode = l.laborcode and lt.orgid = l.orgid and startdate between to_date('01.02.2020','DD.MM.YYYY') and to_date('01.03.2020','DD.MM.YYYY')) as alias,
(select sum(ass.laborhrs) from assignment ass where ass.laborcode = l.laborcode and ass.scheduledate between to_date('01.02.2020','DD.MM.YYYY') and to_date('01.03.2020','DD.MM.YYYY')) as alias
from labor l
George
  • 124
  • 1
  • 12

1 Answers1

3

The macro prompt syntax is #prompt('StartDate','date')# Which would change your SQL to:

select laborcode, (select sum(workhours) from workperiod where calnum='KOPA'and workdate between to_date(#prompt('StartDate','date')#,'DD.MM.YYYY') and to_date(#prompt('EndDate','date')#,'DD.MM.YYYY')) -
(select count(calnum) from workperiod where calnum='KOPA'and workdate between to_date(#prompt('StartDate','date')#,'DD.MM.YYYY') and to_date(#prompt('EndDate','date')#,'DD.MM.YYYY') and shiftnum='HOLIDAY') * 8 as stevilo_praznikov_ure, (select sum(lt.regularhrs) from labtrans lt where lt.laborcode = l.laborcode and lt.orgid = l.orgid and startdate between to_date(#prompt('StartDate','date')#,'DD.MM.YYYY') and to_date(#prompt('EndDate','date')#,'DD.MM.YYYY')) as delovne_ure, (select sum(ass.laborhrs) from assignment ass where ass.laborcode = l.laborcode and ass.scheduledate between to_date(#prompt('StartDate','date')#,'DD.MM.YYYY') and to_date(#prompt('EndDate','date')#,'DD.MM.YYYY')) as assignment_ure from labor l

Daniel Wagemann
  • 741
  • 4
  • 6
  • thanks for your answer Daniel :) so if I u understand this right...i need just this select now and then use it in Cognos SQL and create parameters StartDate and EndDate? – George Feb 21 '20 at 13:08
  • It'll probably be native SQL but the approach would be the same. – Daniel Wagemann Feb 21 '20 at 13:30
  • Hey @DanielWagemann it's me again...hope you can help me here with my problem. I added the SQL and those two parameters and then when I run the report I get a error: sqlScrollBulkFetch' status='-51 UDA-SQL-0144 Odkrito je bilo aritmetično izjemno stanje.[IBM][CLI Driver][DB2/NT64] SQL20448N "2007" cannot be interpreted using format string "DD.MM.YYYY" for the TIMESTAMP_FORMAT function. SQLSTATE=22007 Is it something wrong with the format of the parameter? A fully arithmetic state was detected...that's the translation above. – George Feb 24 '20 at 07:14
  • The driver probably does not like the date to date manipulation, does it work if you take the to_date out? Or you could try changing the the prompt macro data type from date to timestamp. – Daniel Wagemann Feb 24 '20 at 11:59