Remedy Smart Reporting - Creating Week Day Number Using Calculated Fields With Oracle Database

Version 1
    Share This:

    This document contains official content from the BMC Software Knowledge Base. It is automatically updated when the knowledge article is modified.


    PRODUCT:

    Remedy AR System Server


    COMPONENT:

    Smart Reporting Remedy


    APPLIES TO:

    Remedy Smart Reporting 1805 and Below versions



    PROBLEM:

    Week Day Number Function is not available in Smart Reporting. Created Case Statement to display Week Day Number, but output is blank:
     

    (CASE  WHEN datename('wd',`HPD:Help Desk`.`Reported Date`) LIKE 'MONDAY' THEN 1 WHEN datename('wd',`HPD:Help Desk`.`Reported Date`) LIKE 'TUESDAY' THEN 2 WHEN datename('wd',`HPD:Help Desk`.`Reported Date`) LIKE 'WEDNESDAY' THEN 3 WHEN datename('wd',`HPD:Help Desk`.`Reported Date`) LIKE 'THURSDAY' THEN 4 WHEN datename('wd',`HPD:Help Desk`.`Reported Date`) LIKE 'FRIDAY' THEN 5 WHEN datename('wd',`HPD:Help Desk`.`Reported Date`) LIKE 'SATURDAY' THEN 6 WHEN datename('wd',`HPD:Help Desk`.`Reported Date`) LIKE 'SUNDAY' THEN 7  END)
      

      

     


    CAUSE:

    Issue is related to Oracle case sensitivity.


    SOLUTION:

    The issue is essentially related to case sensitivity. It looks like Oracle returns the day name in the upper case. So it is not possible to specify literal values to be compared with, in the lower case.

    In this case, modify the ARJDBC SQL or the Case Statement as below:
     

    (CASE WHEN datename('wd',`HPD:Help Desk`.`Reported Date`) LIKE '%MONDAY%' THEN 1 WHEN datename('wd',`HPD:Help Desk`.`Reported Date`) LIKE '%TUESDAY%' THEN 2 WHEN datename('wd',`HPD:Help Desk`.`Reported Date`) LIKE '%WEDNESDAY%' THEN 3 WHEN datename('wd',`HPD:Help Desk`.`Reported Date`) LIKE '%THURSDAY%' THEN 4 WHEN datename('wd',`HPD:Help Desk`.`Reported Date`) LIKE '%FRIDAY%' THEN 5 WHEN datename('wd',`HPD:Help Desk`.`Reported Date`) LIKE '%SATURDAY%' THEN 6 WHEN datename('wd',`HPD:Help Desk`.`Reported Date`) LIKE '%SUNDAY%' THEN 7 END)
      
    The leading and trailing wildcard needs to be introduced in the names of the days as per the above. 

     


    Article Number:

    000370193


    Article Type:

    Solutions to a Product Problem



      Looking for additional information?    Search BMC Support  or  Browse Knowledge Articles