I have a procedure with a drilldown that passes a datetime range as two HYYMDS field values to the next procedure. The latter procedure then displays detailed data between those datetimes. Should be pretty straightforward, no?

What I get is as follows (output of &ECHO=ALL):

 -DEFAULTH &STARTDT = DT(2012/05/29 07:00:00)
 -DEFAULTH &ENDDT = DT(2012/05/30 07:00:00)
 -TYPE From: DT(24 May 2012 07:00:00) to: DT(25 May 2012 07:00:00)
 From: DT(24 May 2012 07:00:00) to: DT(25 May 2012 07:00:00)
 -SET &D1	= HDATE(DT(24 May 2012 07:00:00), 'YYMD');
 0 ERROR AT OR NEAR LINE     36  IN PROCEDURE _OEE00005R1_dataFO
 (FOC36355)  INVALID TYPE OF ARGUMENT #1 FOR USER FUNCTION HDATE

So, that doesn't work, obviously...

How do I extract the dates from those DT-values using dialog manager? DM clearly interprets the DT(...) part as a string value, which is not a valid value for HDATE!

The above code works when implemented in a DEFINE, but as these values are part of my query constraints, the result of that approach is dog-slow: it first fetches all the data in the (largish) table and then applies the filter. We're currently near 10 mln. records in that table, that takes a while!

Is it possible to read DT-values in a useful way into DM variables?

A few things to note here:
-DEFAULT and -DEFAULTH do not do any function (or any computation) at all. Even &-substitution is not performed, unless you specify .EVAL. So the statement -DEFAULTH &STARTDT = DT('2012/05/29 18:18:19') will just equate &STARTDT to whatever string is at the right hand side, it will NOT evaluate the expression.
Then, the DT function does not work well inside another function. If you need a date-time field, use HINPUT. And if that is successful, the result of the expression would not have any real meaning in DM. You just get a number. This number represents the number of days since the WebFOCUS base date of dec 31, 1900. Example:

-DEFAULTH &STARTDT = '2012/05/29 18:18:19'
-DEFAULTH &ENDDT   = '2012/05/30 07:00:00'

-TYPE From: &STARTDT to: &ENDDT
-SET &D1 = HDATE(HINPUT(&STARTDT.LENGTH, '&STARTDT.EVAL', 8, 'HYYMDS'), 'YYMD');
-TYPE &D1
-SET &D2 = HDATE(HINPUT(&ENDDT.LENGTH, '&ENDDT.EVAL', 8, 'HYYMDS'), 'YYMD');
-TYPE &D2

This will result in &D1 having a value of 40692 and &D2 of 40693. Which are the correct values for the dates, if you count the days.

The problem is that the DT expression is what comes in from the other procedure, because the fields that are passed in the drilldown are defined as HYYMDS there.

So HINPUT isn't going to help me, unless HINPUT does understand the DT expression?

quote:

DT(24 May 2012 07:00:00)

So the calling procedure is actually passing a value such as "DT(24 May 2012 07:00:00)"?

Well, I don't think you can use HDATE in that context under Dialogue Manager.

If all you need is to convert your &variable value from 'DT(DD MMM YYYY HH:MI:SS)' to 'DD MM YYYY') so you can use it in your queries, why not just the EDIT function or any fancy string function to strip the characters you don't need and give you a "date only" string?

-* This should give you "24 May 2012" out of the received parameter which looks like a valid SmartDate
-SET &D1 = EDIT(&STARTDT, '$$$99$99$9999');  

TABLE FILE BLAH
SUM THIS AND THAT
WHERE MY_DATE GE '&D1'
END

Wouldn't that work or am I not understanding your need correctly?

Ah, if it were that simple! EDIT can't deal with variable length month names to mask contents.

I ended up converting the HYYMDS values in the parent report to I8YYMD so that I could safely pass them on in the drilldown.

Silly Dialog Manager... Ah well, writing language parsers is hard and in my experience the guys at IBI aren't particularly good at it. I'm expecting too much again.

quote:

So the calling procedure is actually passing a value such as "DT(24 May 2012 07:00:00)"?

.. and the answer to this is?

Knowing that will help others to understand a little more of your issue and potentially give you a workable slution.

T

quote:

Originally posted by Wep5622:

-DEFAULTH &STARTDT = DT(2012/05/29 07:00:00)
-DEFAULTH &ENDDT = DT(2012/05/30 07:00:00)
-TYPE From: DT(24 May 2012 07:00:00) to: DT(25 May 2012 07:00:00)

If the report fex has to be prepared to handle the date part in "dd Month yyyy" format, and perhaps also yyyy/mm/dd, life will certainly be simpler if you arrange for the drilldown to pass the date and the time separately.

quote:

The above code works when implemented in a DEFINE, but as these values are part of my query constraints, the result of that approach is dog-slow

I don't know exactly what makes it "dog-slow". Still, since DEFINE does work, how about something like this:

  
-* File wep03.fex
DEFINE FILE GGSALES
DT/HDMtYYS=HINPUT(8, EDIT(DATE), 8, 'HDMtYYS');
END
TABLE FILE GGSALES
PRINT
     SEQ_NO
     DATE
     DT

ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = endeflt,
$
TYPE=DATA,
     COLUMN=N3,
     FOCEXEC=WEP03DD( \
     DTPASS=N3 \
     ),
$
ENDSTYLE
END


-* File wep03dd.fex
DEFINE FILE SYSTABLE
DTYMD/YYMD=HDATE(&DTPASS, 'YYMD')
END
TABLE FILE SYSTABLE
PRINT NAME NOPRINT DTYMD
IF RECORDLIMIT EQ 1
ON TABLE HOLD AS WEP FORMAT ALPHA
END
-RUN
-READFILE WEP
-CLOSE WEP
-TYPE &DTYMD

Doesn't take very long.

I am guessing (reading between the lines) that the value(s) being passed are the ones being TYPEd out as a result of the &ECHO=ALL; and that your problem is being able to discern a YYMD value for utilisation further on in your code?

If that is the case, and please advise if I am way off key here, you could achieve the value as within this DM code

-DEFAULTH &BEGDT = 'DT(29 May 2012 07:00:00)'
-DEFAULTH &ENDDT = 'DT(30 May 2012 07:00:00)'

-SET &DT0    = GETTOK(&BEGDT.QUOTEDSTRING, &BEGDT.LENGTH, -1, '(', &BEGDT.LENGTH, 'A&BEGDT.LENGTH');
-SET &DT0    = GETTOK(&DT0.QUOTEDSTRING, &DT0.LENGTH, 1, ')', &DT0.LENGTH, 'A&DT0.LENGTH');
-SET &DT1    = HDATE(HINPUT(&DT0.LENGTH, &DT0.QUOTEDSTRING, 10, 'HYYMDS'), 'YYMD');

I know that you have made a change within the master file to help you over this hurdle, but it is always likely to be a recurring problem that just continues to frustrate. So, if we can progress this for you then everone wins. You get a method you can use onwards in other processes and others learn a new method of manipulating dates and "interesting" input values.

T

quote:

Originally posted by Tony A:

quote:

So the calling procedure is actually passing a value such as "DT(24 May 2012 07:00:00)"?

.. and the answer to this is?

Knowing that will help others to understand a little more of your issue and potentially give you a workable slution.

T

Apparently.
It is the result of supplying a HYYMDS field as a parameter to a FOCEXEC. WebFOCUS turned that into "DT(24 May 2012 07:00:00)", so I expected it to know how to handle that.

Interesting! I have seen some of the date functions accept DT(...) input but it appears that the one that you are interested in is not one of them!!

The DM that I gave above will deal with both date formats - 24 May 2012 00.00.00 and 24/05/2012 00.00.00 (and other derivations) so may give you a method from which to move forward without having to rely upon making changes to your synonyms?

T

quote:

Originally posted by Danny-SRL:

quote:

The above code works when implemented in a DEFINE, but as these values are part of my query constraints, the result of that approach is dog-slow

I don't know exactly what makes it "dog-slow". Still, since DEFINE does work, how about something like this:
[code]
-* File wep03.fex
DEFINE FILE GGSALES
DT/HDMtYYS=HINPUT(8, EDIT(DATE), 8, 'HDMtYYS');
END
TABLE FILE GGSALES

It's dog-slow because it first fetches all 10 mln records from the database and then applies the filter.
Instead, if I pass those dates as fixed values to the WHERE constraint it only fetches the relevant records. That's usually less than 1000.

However, to do the latter I need to convert the DT-expressions to integer dates, because that's how they're stored in the database. And that's where I run into a problem.

quote:

Originally posted by Tony A:
The DM that I gave above will deal with both date formats - 24 May 2012 00.00.00 and 24/05/2012 00.00.00 (and other derivations) so may give you a method from which to move forward without having to rely upon making changes to your synonyms?

No risk for that, thankfully. The datetimes passed on are constant values that are more convenient to pass along than just dates.

To explain the 'why' a little: The facility here is running 24-h shifts, where the first shift starts at 7:00 am (and the last one ends at it, of course). That causes a 7-hour skew over the data in the database that we need to look at when dealing with production data.

It's easy enough to hard-code that skew in our reports, but I prefer to do that as little as possible as it's bound to cause problems when something changes in the shift schedules.

Meanwhile, your solution appears to be as elegant as it can get under the circumstances (ideally, DM would just be able to parse it natively). So, thanks for that.

Wep,

quote:

it first fetches all 10 mln records from the database and then applies the filter

As often, I was a bit elliptic. So I'll explain.
I understand well that you drill down taking with you a date-time field (in fact 2) and WF provides a DT string which stumps DM.

In my example the wep03.fex is just a construct to create a date-time field to paraphrase your calling fex.
wep03dd.fex receives the string and instead of trying to parse it with DM, I use a DEFINE on the system file SYSTABLE in order to perform the translation from DT to YYMD. Then a TABLE command with a SAVE prints out the translated date which you can the -READ. Then use the &variable against your 10M records database.
It won't take much more time than the DM and allows for any date-time format.

quote:

To explain the 'why' a little:

Ah! It becomes clear.

Good luck

T

quote:

Originally posted by Danny-SRL:
Wep,

quote:

it first fetches all 10 mln records from the database and then applies the filter

As often, I was a bit elliptic. So I'll explain.
I understand well that you drill down taking with you a date-time field (in fact 2) and WF provides a DT string which stumps DM.

Ah, I missed the important detail (probably because it was in the 2nd half of the example code)!

I suppose that would work, but wouldn't that be rather vulnerable to concurrency issues?

I know, there are some things in WebFOCUS that you can't really get around without writing things to an ALPHA-format file and reading it back in using DM. The trouble is that such files end up in the same global location for every user requesting the report, so hilarity (read angry phone calls) ensues when two users get to that part of the code at the same time. We haven't found a good way around that yet...

We've tried using the foccache for example, but that didn't work as well as we hoped for: Writing the file there works just fine, but for some reason DM can't seem to read files from the foccache.

We've also experimented with adding user-id based suffixes to the filename of the ALPHA file, but WF tends to complain about such HOLD-file names and it creates tons of garbage from left-over files.

The best we manage to do is to keep the required lifetime of such ALPHA files as short as possible and to create them only when absolutely necessary.

There's got to be a proper solution, right? Frankly, we don't really want to have to deal with concurrency issues!

Hmm, perhaps we ought to move this discussion to another topic...

While it's preferable to do the date transformations in DEFINE, and then get the string into a &var (either as a passed parameter or via -READ), extracting a YYMD string from a DT(...) string with alphabetic month can be done in DM without resorting to -READ. Here's one solution.

-DEFAULT &DT1='  DT(1 September 2012 07:00:00)  '
-* STRREP (inlength, instring, searchlength, searchstring, replength, repstring, outlength, outstring)
-* GETTOK(infield, inlen, token_number, 'delim', outlen, outfield)
-* HINPUT(inputlength, 'inputstring', length, outfield)
-* HDATE(value, {'YYMD'|outfield})
-* DATECVT(date, 'infmt', {'outfmt'|outfield})

-SET &X= &DT1;
-* Remove 'dt(' prefix, and ')' suffix, if present:
-SET &X= STRREP(&X.LENGTH, &X, 3, 'DT(', 0, 'XXX', &X.LENGTH, &X); 
-SET &X= STRREP(&X.LENGTH, &X, 1, ')', 0, 'XXX', &X.LENGTH, &X); 
-* Extract raw components (note that GETTOK ignores leading consecutive delimiters):
-SET &XDAY  =GETTOK(&X, &X.LENGTH, 1, ' X', 2, 'A2');
-SET &XMONTH=GETTOK(&X, &X.LENGTH, 2, ' X', 9, 'A9');
-SET &XYEAR =GETTOK(&X, &X.LENGTH, 3, ' X', 4, 'A4');
-* Obtain date values (undecorated and hypenated)
-SET &XDATE =&XYEAR || (' '  | &XMONTH) || (' '  | &XDAY);
-SET &XADATE=DATECVT(HDATE(HINPUT(&XDATE.LENGTH, &XDATE, 8, 'HYYMDS'), 'YYMD'), 'YYMD', 'A8YYMD');
-SET &XBDATE=EDIT(&XADATE,'9999-99-99');

-? &X

Wep,

Unless, for some reason you direct all temporary files (HOLD/SAVE) to the same directory , WF creates for each task a separate temp directory. So, there shouldn't ever be any concurrency problem.

It runs slow??? that's interesting. Does the drill run slow or the initial request? If it's the initial request, try screening some records out if this is a large DB also try changing your TABLE command to TABLEF

If it's the drill, try this.
-DEFAULT &NAME = '';
-DEFAULT &DTYMD = '';
DEFINE FILE SYSTABLE
DTYMD/YYMD=HDATE(&DTPASS, 'YYMD');
END
TABLE FILE SYSTABLE
PRINT NAME NOPRINT DTYMD
IF RECORDLIMIT EQ 1
ON TABLE SAVE AS WEP
END
-RUN
-READ WEP &NAME.A64. &DTYMD.A8.
-CLOSE WEP
-TYPE &DTYMD