Prompt macro for date prompt in time-dependent table / query subject

[Michael75]

Environment is 10.2.1 running against Netezza.

I'm modelling a new, very wide DM fact table that's been created to satisfy a self-service reporting requirement. This table is built from five EDW tables. User req is to be able to visualise the situation either as of today, or as at a specified date in the past.

To this end, the table includes as columns five pairs of table_START_DATE & table_END_DATE, and contains many version records, reflecting the changes in the underlying tables. These date fields have data type timestamp. In order to pull out a coherent result set, a date ('Business Date') needs to be supplied, and filters applied to the query so that the requested date falls within each pair of START_DATE & END_DATE. Finally, I'd like the Business Date to default to today's date.

It seems to me that I need an embedded filter with prompt macro in the MQS to do this, and this is what I've been trying to put into practice. But I keep hitting syntax errors (QE-DEF-0260 Parsing error before or near position: nn) which I don't understand. Maybe this is due to my lack of experience with Netezza...

I'd like to know if my general approach seems sound, and if anybody can help me with the syntax. For the time being, I'm only working with one pair of dates. Below are some examples of constructs I've used for the embedded filter. I've put in bold the point at which FM's syntax checking gives the squiggly red underline.

S_POLICYVERSION_START_DATE <= #prompt('Business Date : ', 'date','current_date')# AND S_POLICYVERSION_END_DATE   >= #prompt('Business Date : ', 'date','current_date')#

#prompt('Business Date : ', 'date','current_date')# between S_POLICYVERSION_START_DATE and S_POLICYVERSION_END_DATE

#prompt('Business Date : ', 'date','current_date')# between cast (S_POLICYVERSION_START_DATE, date) and cast (S_POLICYVERSION_END_DATE, date)

#prompt('Business Date : ', 'date','current_date')# between date_trunc ('date', S_POLICYVERSION_START_DATE) and date_trunc ('date', S_POLICYVERSION_END_DATE)

#prompt('Business Date : ', 'date','getdate()')# between date_trunc ('date', S_POLICYVERSION_START_DATE) and date_trunc ('date', S_POLICYVERSION_END_DATE)

Any assistance will be gratefully received

Thx, Michael

[MFGF]

Environment is 10.2.1 running against Netezza.

I'm modelling a new, very wide DM fact table that's been created to satisfy a self-service reporting requirement. This table is built from five EDW tables. User req is to be able to visualise the situation either as of today, or as at a specified date in the past.

To this end, the table includes as columns five pairs of table_START_DATE & table_END_DATE, and contains many version records, reflecting the changes in the underlying tables. These date fields have data type timestamp. In order to pull out a coherent result set, a date ('Business Date') needs to be supplied, and filters applied to the query so that the requested date falls within each pair of START_DATE & END_DATE. Finally, I'd like the Business Date to default to today's date.

It seems to me that I need an embedded filter with prompt macro in the MQS to do this, and this is what I've been trying to put into practice. But I keep hitting syntax errors (QE-DEF-0260 Parsing error before or near position: nn) which I don't understand. Maybe this is due to my lack of experience with Netezza...

I'd like to know if my general approach seems sound, and if anybody can help me with the syntax. For the time being, I'm only working with one pair of dates. Below are some examples of constructs I've used for the embedded filter. I've put in bold the point at which FM's syntax checking gives the squiggly red underline.

S_POLICYVERSION_START_DATE <= #prompt('Business Date : ', 'date','current_date')# AND S_POLICYVERSION_END_DATE   >= #prompt('Business Date : ', 'date','current_date')#

#prompt('Business Date : ', 'date','current_date')# between S_POLICYVERSION_START_DATE and S_POLICYVERSION_END_DATE

#prompt('Business Date : ', 'date','current_date')# between cast (S_POLICYVERSION_START_DATE, date) and cast (S_POLICYVERSION_END_DATE, date)

#prompt('Business Date : ', 'date','current_date')# between date_trunc ('date', S_POLICYVERSION_START_DATE) and date_trunc ('date', S_POLICYVERSION_END_DATE)

#prompt('Business Date : ', 'date','getdate()')# between date_trunc ('date', S_POLICYVERSION_START_DATE) and date_trunc ('date', S_POLICYVERSION_END_DATE)

Any assistance will be gratefully received

Thx, Michael

Hi,

Is this the exact syntax you are using? Wouldn't it be

[namespace].[query subject].[S_POLICYCONVERSION_START_DATE] <= #prompt('Business Date : ', 'date','current_date')# AND [namespace].[query subject].[S_POLICYCONVERSION_END_DATE] >= #prompt('Business Date : ', 'date','current_date')#

MF.

[dougp]

Are you sure about the data type that's being returned by the prompt macro?  I am often surprised by what a prompt macro returns.  It may be including or omitting single quotes or returning the date in a format you don't expect.  Try looking at the prompt macro all by itself.

[Michael75]

Is this the exact syntax you are using? Wouldn't it be

[namespace].[query subject].[S_POLICYCONVERSION_START_DATE] <= #prompt('Business Date : ', 'date','current_date')# AND [namespace].[query subject].[S_POLICYCONVERSION_END_DATE] >= #prompt('Business Date : ', 'date','current_date')#

MF.

Ahem, I'm sure you're right. What happened is that my DBA sent me some suggested code for the filter, and I just adapted it in a text editor and copy/pasted it into the FM expression editor, without thinking that I had to drag and drop the different components. A newbie error, if ever there was one

@ dougp
Thanks for your suggestion, I'll bear that in mind when I take this up again tomorrow.

And to quickly change the subject to something less embarrassing... Does my general approach seem correct for the scenario I described?

[Michael75]

Many hours later in my development...

@ MF

Of course your analysis of the cause of my syntax errors was spot on. Couldn't see the forest for the trees. I like to think that I'd have quickly spotted the error myself if one of these conditions were true:

1. The squiggly red underline were situated anywhere near the actual cause of the error
2. The column number nn in the (QE-DEF-0260 Parsing error before or near position: nn) pointed anywhere vaguely near the element causing the error
3. There were any correlation between 1. & 2.

Of course, none of these conditions apply, which is just one of the many reasons why we love Cognos, isn't it? 

So, according to the original criteria (cf. my first mail), i.e. using a date prompt and checking if it falls between [namespace].[query subject].[S_POLICYCONVERSION_START_DATE] and the corresponding _END_DATE, I got my filter working correctly. So far, so good.

But then, talking with the DBA, we realised that there could be several "versions" within a given day, given the likelihood that at one or more of the five underlying tables would be updated during the day. We decided that a datetime prompt, rather than just a date prompt, would be needed to retrieve coherent results.

@ dougp

I said that I'd bear in mind your suggestion to carefully check data types & formats. I just didn't know at the time just how important that would turn out to be


So now, I want to compare my prompt input in datetime format against my actual DB fields. No more CAST (S_POLICYCONVERSION_START_DATE, date) etc. So what the DB fields contain is a value which (in my DB mgmt tool 'Aginity') shows up in the format:

Code: [Select]

2016-05-09 20:42:43
And so far, in several attempts to provide a datetime prompt in a format which correctly compares with the DB format, I've gone from the original:

Code: [Select]

(#prompt('Business Date : ', 'date','current_date')#
and tried both:

Code: [Select]

(#prompt('Business Date : ', 'timestamp','current_timestamp')#
and:

Code: [Select]

(#prompt('Business Date : ', 'datetime','current_time')#
I'm not totally surprised that none of these work. Of course, I could play at combining and reformatting the output of one or more of the above, but time is a bit short, and I'm sure that someone has already had to do exactly this.

So, for those who, understandably, didn't have the patience to read through all the stuff above, here's the executive résumé of my requirement:

1. I have DB fields in format 2016-05-09 20:42:43
2. I want to prompt the user for a value which correctly compares with this format
3. I would like the prompt box to default to current datetime


TIA
Michael

[MFGF]

Many hours later in my development...

@ MF

Of course your analysis of the cause of my syntax errors was spot on. Couldn't see the forest for the trees. I like to think that I'd have quickly spotted the error myself if one of these conditions were true:

1. The squiggly red underline were situated anywhere near the actual cause of the error
2. The column number nn in the (QE-DEF-0260 Parsing error before or near position: nn) pointed anywhere vaguely near the element causing the error
3. There were any correlation between 1. & 2.

Of course, none of these conditions apply, which is just one of the many reasons why we love Cognos, isn't it? 

So, according to the original criteria (cf. my first mail), i.e. using a date prompt and checking if it falls between [namespace].[query subject].[S_POLICYCONVERSION_START_DATE] and the corresponding _END_DATE, I got my filter working correctly. So far, so good.

But then, talking with the DBA, we realised that there could be several "versions" within a given day, given the likelihood that at one or more of the five underlying tables would be updated during the day. We decided that a datetime prompt, rather than just a date prompt, would be needed to retrieve coherent results.

@ dougp

I said that I'd bear in mind your suggestion to carefully check data types & formats. I just didn't know at the time just how important that would turn out to be


So now, I want to compare my prompt input in datetime format against my actual DB fields. No more CAST (S_POLICYCONVERSION_START_DATE, date) etc. So what the DB fields contain is a value which (in my DB mgmt tool 'Aginity') shows up in the format:

Code: [Select]

2016-05-09 20:42:43
And so far, in several attempts to provide a datetime prompt in a format which correctly compares with the DB format, I've gone from the original:

Code: [Select]

(#prompt('Business Date : ', 'date','current_date')#
and tried both:

Code: [Select]

(#prompt('Business Date : ', 'timestamp','current_timestamp')#
and:

Code: [Select]

(#prompt('Business Date : ', 'datetime','current_time')#
I'm not totally surprised that none of these work. Of course, I could play at combining and reformatting the output of one or more of the above, but time is a bit short, and I'm sure that someone has already had to do exactly this.

So, for those who, understandably, didn't have the patience to read through all the stuff above, here's the executive résumé of my requirement:

1. I have DB fields in format 2016-05-09 20:42:43
2. I want to prompt the user for a value which correctly compares with this format
3. I would like the prompt box to default to current datetime


TIA
Michael

Hi Michael,

All the code snippets you posted here have a superfluous open parenthesis at the beginning. Is it this easy?

Assuming you need the default value formatting as yyyy-mm-dd hh:mm:ss you can use the timestampMask() macro function for this:

#prompt('Business Date : ', 'timestamp',timestampMask($current_timestamp,'yyyy-mm-dd hh:mm:ss'))#

Cheers!

MF.

[Michael75]

All the code snippets you posted here have a superfluous open parenthesis at the beginning. Is it this easy?

Indeed they do. But that's just an oversight on my part in editing my code for the purposes of this post. I now have logic for all my five pairs of start / end dates, and I put brackets around the logic for each pair, i.e. (pair1 logic) AND (pair2 logic) AND etc. In my hasty editing, I simply truncated the code, and forgot to remove the (now) unmatched opening bracket. As we saw yesterday, I'm not above making newbie errors, but surprisingly this wasn't one of them.

Your suggestion looks good, and I'll try it on Monday. But tomorrow (21st July) we have a national holiday, and hence a long weekend.

Thanks for your continued support!

Michael

[srmoure]

Data ordering does't work. I couldn't make the format apply to the value entered in the date prompt (CA11.1R5)