0

I have an optional date prompt , on my cognos report. And I want to pass the value into an SQL query, but if I won't give a value I want the macro to take a default value. I am trying the following format , but it seems that it is not working

Where ypd.ACTIVATION_DATE >= TO_DATE(#prompt('DateFrom','varchar','2000/01/01')# ,'rrrr-mm-dd' )

As it seems the query does not recognize the '2000/01/01'as a default value.

Any suggestions

eMazarakis
  • 122
  • 1
  • 13
  • What errors do you get? Why are you using rrrr? What context is this? where is not a valid operator in a filter. This suggests that you are doing this in a SQL statement. Have you tried to break the problem into smaller chunks? What happens when you don't have the prompt in? – C'est Moi Jun 03 '20 at 17:43
  • I do not get any errors.Because my dbData might have values such as /20 or /2020. So I want both of them to display in the right way. Yes , I am using an sql query as I said in the question "And I want to pass the value into an SQL query". Why to break it i a smaller chunks. My problem is this, when I have prompt date object, the prompt() macro into my sql query cannot understand that I did not chose anyting from the prompt in order to take the default value. That's why I am asking if the format of the defualt value is wrong 2000/01/01. – eMazarakis Jun 08 '20 at 11:31
  • Break the problem into smaller chunks = identify the elements of the problem and remove some of them in order to verify that the remaining elements are or are not the source of the problem. For example remove the prompt. Is the sql query executing? Is the default value added to the sql statement if you do not enter a value in the prompt? Do you get a prompt? I use default values in tons of my prompt filters. They work. The default value will be used if no value is entered into the prompt. I model this in my business layer, where they ought to be. – C'est Moi Jun 09 '20 at 20:51
  • Did you ever use date prompt and default value ? If yes, how did you do it ? – eMazarakis Jun 10 '20 at 07:46
  • In the business layer. Here's a simple example. When executed, produces a prompt but the OK button is enabled even though no value has been entered. If you press the OK button the query will use the default value [Business View].[time].[DAY_DATE] >= ( #prompt('Starting date','Date','2012-02-15') # ) – C'est Moi Jun 10 '20 at 20:02

1 Answers1

0

There is a sample in the IBM webpage. Reference Link: https://www.ibm.com/support/pages/using-prompt-macro-optional-andor-required. You only have to add three single quotes at the beginning of the string and three single quotes at the end. I'm using string instead of varchar. Also, please note that the date format should be consistent.

Please try this:

Where ypd.ACTIVATION_DATE >= TO_DATE(#prompt('DateFrom','string','''2000/01/01''')# ,'yyyy/mm/dd' )

Alberto Cervantes
  • 1
  • 1