Hey

I been trying to wrap my head around whether this is possible.

I am using an SQL pass through and have a query that pulls the last day of February (trying to account for leap years)

What I wanted to do, is that this query pulls one column "LAST_DAY" with one value (either 28 or 29)

I then want to take this value and put it into a variable and let the rest of the report use this determining date ranges.

How would I go about doing this? Or is this even possible to just pull "one value" from a query?

Thanks in advance

This message has been edited. Last edited by: Kerry,

Easy does it:

1.) sql here...
2.) TABLE FILE SQLOUT
PRINT ...
ON TABLE HOLD
3.) -READ HOLD &LASTDAY

Now you can use &LASTDAY for the rest of the report.

Just a minor comment to Anatess' code.

If after using -READ, your &LASTDAY variable shows funny characters that would be due to a binary representation of numbers in the HOLD file. If that's the case, make sure the value your SQL passthru returns is "seen" as a character (i.e. select to_char(lastday) as lastday from blah ) -OR- when creating the HOLD file, use ALPHA format. Just something to keep in mind.

Also make sure you have a -RUN before the -READ.

If the sole purpose of the SQL pass through is to determine if the last day of February in a given year is the 28th or the 29th, then this can be accomplished using a DEFINE FUNCTION. The defined function would include the logic for determining if a year is a leap year. If you are interested I'll look through my notes and post the code.

Ira, you make a very valid point. I don't think I read Rodney's requirement in detail but focused on his need to "capture SQL output in an &variable" .

In following your line of thought, it is very easy to determine the last day of any month in any year (leap or otherwise) in "pure" WebFOCUS style using DATEMOV:

-* "Regular" year
SET TESTDATE=20110201
-RUN
-SET &LASTDAY=EDIT(DATECVT(DATEMOV(DATECVT(&YYMD, 'I8YYMD', 'YYMD'), 'EOM'), 'YYMD', 'A8YYMD'), '$$$$$$99');
-TYPE &LASTDAY

-* Leap year
SET TESTDATE=20080201
-RUN
-SET &LASTDAY=EDIT(DATECVT(DATEMOV(DATECVT(&YYMD, 'I8YYMD', 'YYMD'), 'EOM'), 'YYMD', 'A8YYMD'), '$$$$$$99');
-TYPE &LASTDAY

Ira,

I posted a function for exactly that back in February - link

One thing to remember is that century years are not leap years.

T

Wow!

To add on to the very helpful suggestions here I just wanted to note some things I noticed for any future readers of this thread.

At first I was using this code

SQL
SELECT LAST_DAY FROM XYZ
;
TABLE ON TABLE HOLD AS TMP_XYZ
END
-RUN
TABLE FILE TMP_XYZ
PRINT LAST_DAY
ON TABLE HOLD
END
-RUN
-READ HOLD &TMP.A6
-TYPE TEST: &TMP;
-EXIT

When testing this, my output for &TMP was actually blank, or it came out to

TEST:

Thanks to the suggestion from njsden I changed it so it would then set the value pulled to a CHAR.

SQL
SELECT TO_CHAR(LAST_DAY) LAST FROM XYZ
;
TABLE ON TABLE HOLD AS TMP_XYZ
END
-RUN
TABLE FILE TMP_XYZ
PRINT LAST
ON TABLE HOLD
END
-RUN
-READ HOLD &TMP.A6
-TYPE TEST: &TMP;
-EXIT

Which gave me what I wanted, though I had to use a TRUNCATE to make sure there were no extra spaces lurking around

That define function is awesome too, I was mainly trying to do the -READ to play with it but I will try that define function too.

Thanks so much for the replies, I am much more encouraged to learn Webfocus now with such a great community!

quote:

I am much more encouraged to learn Webfocus now

Great! Keep it on, Rodney. You'll encountered a few challenges along the way (just as many of us have) but the experience may be quite rewarding.

Rodney, glad u manage to solve it. Here's another approach, using the AYMD function.

DEFINE FILE EMPLOYEE
MARCH_FIRST/I8YYMD = IF EMP_ID EQ '071382660' THEN 20080301 ELSE 20110301;
FEBRUARY_LAST/I8YYMD = AYMD(MARCH_FIRST, -1, FEBRUARY_LAST);
END

There are similar functions for the various date-time columns.

Håkan

Rodney, all in light of contributing to your learning I'd like to make a few comments on your sample code.

1) Avoid using LAST as a field name as it is a WebFOCUS reserved word which allows to get the value of a field from the previous record (or previous entry in the internal matrix in case of SUM requests). Though it is working now in your example it *might* not do so in future versions of the product. So:

SELECT TO_CHAR(LAST_DAY) LAST_DAY FROM XYZ

is a better choice.

2) Whenever possible, give your HOLD files a name. It is not required but it is a practice I've embraced to save me from headaches especially when many HOLD files are involved in a report process.

ON TABLE HOLD AS HLASTDAY

3) The syntax of -READ suggests to use a dot (.) before and after the type/length declaration of the &variable:

-READ HLASTDAY &TMP.A6.

4) Finally, -TYPE does not require a semicolon at the end of the line.


Though those suggestions may look silly or unnecessary, I share them just because after our recent upgrade from WF 5.3 to WF 7.7 we found a few lines of code that stopped running due to code tightening (stricter syntax rules) in the newer version.

Hope that helps somehow