Last Day Scalar function



IBM's flagship relational database management system

Last Day Scalar function

Postby sandhya.budhi » Thu Mar 10, 2011 1:34 am

My requirement is I need to get the 2 day of the current month.

I am using the below code to retrieve the 2nd day of the current month

LAST_DAY(TIMESTAMP(:WS-CUR-TIMESTAMP) - 1 MONTH) + :WS-VAR-MON-DAY DAYS)

The variable WS-CUR-TIMESTAMP has the timestamp value retrived from a table. The value WS-VAR-MON-DAYS keeps on changing. It can be 2,3 or 4 depending on the data retrieved from the table.

I populate WS-VAR-MON-DAY as

MOVE WS-VAR-VALUE to WS-VAR-MON-DAY

Where WS-VAR-VALUE is X(10).

I declared WS-CUR-TIMESTAMP as X(26) and WS-VAR-MON-DAYS as S9(10) COMP.

Because I am declaring the variable WS-VAR-MON-DAYS as COMP the data is not moving properly. Instead of value 2 it has 10245 some garbage value.

I tried decalring the varaible as numeric and alphanumeric but my program failed while compiling itself. I errored with undefined or unusable Host variable WS-VAR-MON-DAYS.

Can you please help me in this issue. I tried for the whole day and I was not able to identify the exact problem. Your help will be highly appreciated.
sandhya.budhi
 
Posts: 2
Joined: Thu Mar 10, 2011 1:18 am
Has thanked: 0 time
Been thanked: 0 time

Re: Last Day Scalar function

Postby dick scherrer » Thu Mar 10, 2011 4:02 am

Hello and welcome to the forum,

Suggest you add a bit of code and show the values in all of these WS fields and make sure the content is what you want.

How does a value get into WS-VAR-VALUE?

For one thing, you (most likely) do not want to move the x(10) field to a comp field. . .
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6268
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 93 times

Re: Last Day Scalar function

Postby sandhya.budhi » Thu Mar 10, 2011 10:02 am

Hi dick,

I tried not moving the value X(10) to comp by using different options like moving the x(10) value to a temp variable x(10) and I redefined a new variable as temp1 redefines temp pic 9(10) and tried moving the value to the comp variable. When i tried this it is moving some garbage value 10245. Actually the value should be moved as 4.

I am using a query to retrieve the value of WS-VAR-VALUE. In the table the field is declared as x(10) so i used WS-VAR-VALUE as x(10)

I have an other doubt also. Whether we can define WS-VAR-MON-DAY as numeric 9() or alphanumeric x() instead of comp.

LAST_DAY(TIMESTAMP(:WS-CUR-TIMESTAMP) - 1 MONTH) + :WS-VAR-MON-DAY DAYS)

When i tried using this my program is failing. Undefined or unusable host varaible. It is that I should used only comp variable in the above code.

Please find the code below which is tried using:


WS-VAR-VALUE              pic x(10)
WS-VAR-MON-DAY          pic s9(10) comp
WS-CUR-TIMESTAMP      pic x(26)

select start_day
into : WS-VAR-VALUE
from table1
where employee id = 1

MOVE WS-VAR-VALUE to WS-VAR-MON-DAY

LAST_DAY(TIMESTAMP(:WS-CUR-TIMESTAMP) - 1 MONTH) + :WS-VAR-MON-DAY DAYS)


I also tried like this:

WS-VAR-VALUE      PIC X(10)
TEMP1 REDEFINES WS-VAR-VALUE PIC 9(10)

MOVE  WS-VAR-VALUE to TEMP1
MOVE TEMP1 TO WS-VAR-MON-DAY

LAST_DAY(TIMESTAMP(:WS-CUR-TIMESTAMP) - 1 MONTH) + :WS-VAR-MON-DAY DAYS)
sandhya.budhi
 
Posts: 2
Joined: Thu Mar 10, 2011 1:18 am
Has thanked: 0 time
Been thanked: 0 time

Re: Last Day Scalar function

Postby Quasar » Sun Mar 13, 2011 11:58 am

sandhya.budhi wrote:My requirement is I need to get the 2 day of the current month.
I populate WS-VAR-MON-DAY as

MOVE WS-VAR-VALUE to WS-VAR-MON-DAY
Where WS-VAR-VALUE is X(10).
Because I am declaring the variable WS-VAR-MON-DAYS as COMP the data is not moving properly. Instead of value 2 it has 10245 some garbage value.


I was wondering, why can't you try using the numeric intrinsic function NUMVAL. Please refer to this code snippet -

Code Snippet :-
WORKING-STORAGE SECTION.                                 
01  ALPHANUM                         PIC X(03).           
01  NUM-DISPLAY                      PIC S9(03).         
01  NUM-COMP                         PIC S9(03) COMP.     
01  NUM-COMP-3                       PIC S9(03) COMP-3.   
                                                         
PROCEDURE DIVISION.                                       
    MOVE '123' TO ALPHANUM                               
    COMPUTE NUM-DISPLAY = FUNCTION NUMVAL(ALPHANUM)       
    COMPUTE NUM-COMP    = FUNCTION NUMVAL(ALPHANUM)       
    COMPUTE NUM-COMP-3  = FUNCTION NUMVAL(ALPHANUM)       
    DISPLAY 'ALPHANUM    : ' ALPHANUM                     
    DISPLAY 'NUM-DISPLAY : ' NUM-DISPLAY                 
    DISPLAY 'NUM-COMP    : ' NUM-COMP                     
    DISPLAY 'NUM-COMP3   : ' NUM-COMP-3                   
    STOP RUN.                                             


Output :-
 SDSF OUTPUT DISPLAY SYSADMD  JOB02620  DSID   101 LINE 0       COLUMNS 02- 81 
 COMMAND INPUT ===>                                            SCROLL ===> CSR 
********************************* TOP OF DATA **********************************
ALPHANUM    : 123                                                               
NUM-DISPLAY : 12C                                                               
NUM-COMP    : 123                                                               
NUM-COMP3   : 123                                                               
******************************** BOTTOM OF DATA ********************************
Quasar Chunawala,
Software Engineer, Lives at Borivali, Mumbai
User avatar
Quasar
 
Posts: 102
Joined: Wed Nov 10, 2010 7:11 pm
Location: Borivali, Mumbai
Has thanked: 13 times
Been thanked: 2 times

Re: Last Day Scalar function

Postby Quasar » Sun Mar 13, 2011 12:08 pm

Sandhya -

You've got a post a lot more information, if you wish to seek help. A Question or Query, should be well articulated, put across in simple and lucid language.

You haven't posted the DB2 DECLARE Statement, which you've coded in the Program. You also haven't posted the COBOL Host Variables corresponding to this field. "Undefined or unusable host variables" is what happens, when the pre-compiler complains that you haven't correctly defined a host variable.

Thank you very much.
Quasar Chunawala,
Software Engineer, Lives at Borivali, Mumbai
User avatar
Quasar
 
Posts: 102
Joined: Wed Nov 10, 2010 7:11 pm
Location: Borivali, Mumbai
Has thanked: 13 times
Been thanked: 2 times

Re: Last Day Scalar function

Postby BillyBoyo » Sun Mar 13, 2011 4:36 pm

sandhya.budhi wrote:
My requirement is I need to get the 2 day of the current month.



Are you saying that this month, you'd need 2nd March, next month, 2nd April, then 2nd May etc?

Get hold of you current business date, from wherever in you database. Stick it in WS. Define the "DD" part of the date (I'd suggest PIC XX). Move a variable called 2nd-day-of-month (or similar), again PIC XX, which has an initial value of "02" to the DD field you have defined.

This is too simple. You must mean something else?

Also you define a data-item

WS-VAR-MON-DAYS as S9(10) COMP.


Do you know what the maximum value you can get in that field is? 9,999,999,999 (depending on compile option could be much bigger). Or, if this is something to do with "DAYS", like your data-name is implying, a little over 2.7m years.

My general advice is to make data-names as descriptive (and I mean truly descriptive, not just long) as you need to for someone else to be able to "read" your "program", look at the specification/documentation and know that the two are, or aren't, the same thing. Also, for numeric USAGEs, use what is appropriate to the way you are going to use the item.

Also, you refer to WS-VAR-MON-DAYS and "DAY" - confusing.
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 265 times

Re: Last Day Scalar function

Postby BillyBoyo » Mon Mar 14, 2011 5:55 am

sandhya.budhi wrote:
WS-VAR-VALUE      PIC X(10)
TEMP1 REDEFINES WS-VAR-VALUE PIC 9(10)

MOVE  WS-VAR-VALUE to TEMP1
MOVE TEMP1 TO WS-VAR-MON-DAY



Sandhya, I always found it a good idea to take breaks from concentrating on one problem. Often, when returning to the problem, I would see straight away something which would lead to the solution. We have an expression "can't see the woods for the trees".

Looking further at your code snippets, I have a couple of further comments. Above, where you have done the redefine, you end up moving the same piece of storage to itself. If you do the redefine, you don't need to do the move of MS-VAR-VALUE to TEMP1, just the move of TEMP1. I don't think that is your problem anyway.

Next, in the following, you have "unbalanced parentheses". Where this is possible, it is more "left" than "right" that work (say, REXX). But you have more "right" parenthesis than "left".

Finally, as I previously mentioned, you should use the business date from your database. You seem to be using some "timestamp", which smacks of some actual date/time not some business date. If you use an actual date, you program might behave differently if run before midnight and after midnight. That is not a good thing. Even if, as here, you were using a system date to just (maybe, still not clear to me) get the 2nd day of the month, it is not good enough. What about the circumstance where you have to "re-run" a previous day's run? Using the system date, you can (and eventually will for sure) calculate an inccorrect date.

You can use the system date for reporting/tracking and things like that, alongside the business date. In my book, you should never use it for data selection of any sort.

dick scherrer wrote:Suggest you add a bit of code and show the values in all of these WS fields and make sure the content is what you want.


I think, Sandhya, you should take this advice. You don't understand the value 10245. It is a computer. If you understand the inputs and the process you understand the output. I think you'll find one of the inputs has a value that is not what you think before you use it to get 10245. Or your "process" is wrong. Putting in the displays will tell you which, and from there you might be able to make further progress.
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 265 times


Return to DB2

 


  • Related topics
    Replies
    Views
    Last post