RE: Retrieve JOB (not SYSTEM) date in SQL

[MKirkpatrick@xxxxxxxxxxxxxxxxx]

GENERAL style parameters worked also.


midrange-l-bounces@xxxxxxxxxxxx wrote on 06/05/2008 12:16:13 PM:

> Ok Elvis,
>
> How does this work?
>
> How can you return a date from a CL program???
>
> Aahh, I see DB2SQL style parameters work that way.  Cool, learned 
> something new.
>
> Charles Wilt 
> --
> Software Engineer
> CINTAS Corporation - IT 92B
> 513.701.1307
>
> wiltc@xxxxxxxxxx
>
>
> > -----Original Message-----
> > From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-
> > bounces@xxxxxxxxxxxx] On Behalf Of Elvis Budimlic
> > Sent: Thursday, June 05, 2008 1:54 PM
> > To: 'Midrange Systems Technical Discussion'
> > Subject: RE: Retrieve JOB (not SYSTEM) date in SQL
> >
> > I see you figured it out already, but here is what came up in case it
> > helps.
> > It has some extraneous error handling logic that you can ignore or 
chuck
> > altogether.
> > Also, I opted to use DB2SQL parameter style instead of more common 
GENERAL
> > style used by CL (old habits die hard).
> >
> >
> > CREATE FUNCTION QGPL/getJobDate()
> > RETURNS DATE
> > EXTERNAL NAME 'QGPL/GETJOBDATE'
> > LANGUAGE CL
> > PARAMETER STYLE DB2SQL
> > DETERMINISTIC
> > NO SQL
> > RETURNS NULL ON NULL INPUT
> > DBINFO
> > NO EXTERNAL ACTION
> > DISALLOW PARALLEL
> > NO SCRATCHPAD
> > NOT FENCED
> >
> >
> > /* CL code */
> >
> > pgm parm(&jobDate &outind                             +
> >          &sqlstate &funcname &specname &msgtext &dbinfo)
> >
> > dcl &jobDate   *char 10
> > dcl &outInd    *int  2
> > dcl &localdate *char 6
> >
> > dcl &sqlstate *char   5
> > dcl &funcname *char 517
> > dcl &specname *char 128
> > dcl &msgtext  *char  70
> > dcl &dbinfo   *char 816
> >
> > dcl &dummy    *char   2 x'0000'
> >
> > monmsg (cpf0000 mch0000 sql0000) exec(goto exception)
> >
> > RTVJOBA    DATE(&LOCALDATE)
> > CVTDAT     DATE(&LOCALDATE) TOVAR(&JOBDATE) TOFMT(*ISO) TOSEP(-)
> > chgvar &outind 0
> > goto exit
> >
> > exception:
> >   chgvar &outind -1
> >   chgvar &sqlstate 'XC999'
> >   chgvar &msgtext (&dummy *tcat 'Unexpected error occurred, +
> >                                please check the joblog')
> >   sndpgmmsg  msgid(CPF9898) msgf(QCPFMSG) +
> >            msgdta('Unexpected exception') msgtype(*DIAG)
> >
> > exit:
> >
> > endpgm
> >
> >
> > HTH, Elvis
> >
> > Celebrating 11-Years of SQL Performance Excellence on IBM i, i5/OS and
> > OS/400
> > www.centerfieldtechnology.com
> >
> >
> > -----Original Message-----
> > Subject: Re: Retrieve JOB (not SYSTEM) date in SQL
> >
> > Thanks.  I had coded a TOSEP(*NONE) on my CVTDAT cmd instead of 
TOSEP('-')
> > .  It seems to work now.
> >
> > CHGJOB DATE(010108)
> >
> > STRSQL
> >
> > SELECT getjobdate()
> >   FROM SYSIBM/SYSDUMMY1
> >
> >  ....+....1....
> >  GETJOBDATE ( )
> >     01/01/08
> >
> > Is this OK or am I fooling myself.
> >
> >
> > --
> > This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
> > list
> > To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> > To subscribe, unsubscribe, or change list options,
> > visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> > or email: MIDRANGE-L-request@xxxxxxxxxxxx
> > Before posting, please take a moment to review the archives
> > at http://archive.midrange.com/midrange-l.
>
>
>
> This e-mail transmission contains information that is intended to be
> confidential and privileged.  If you receive this e-mail and you are
> not a named addressee you are hereby notified that you are not 
> authorized to read, print, retain, copy or disseminate this 
> communication without the consent of the sender and that doing so is
> prohibited and may be unlawful.  Please reply to the message 
> immediately by informing the sender that the message was 
> misdirected.  After replying, please delete and otherwise erase it 
> and any attachments from your computer system.  Your assistance in 
> correcting this error is appreciated.
> -- 
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing 
list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.





This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed.  Unless you are the addressee (or authorized to receive for the addressee), you may not use, copy or disclose to anyone this email or any information contained in this email.  If you have received this email in error, please advise the sender by replying to this email, and delete this email immediately.  Please note that any views or opinions presented in this email are solely those of the author and do not necessarily represent those of Western Dental Services, Inc.  Finally, the recipient should check this email and any attachments for the presence of viruses. Western Dental Services, Inc. accepts no liability for any damage caused by any virus transmitted by this email.