Re: DB2 SQL confusion

[Charles Wilt <charles.wilt@xxxxxxxxx>]

Darrell,

First off, sysibm/sysdummy1 is a IBM provided table with a single row.
 You'll find such a table often comes in handy.  Oracle and MS SQL
Server provide a similar 1 row table.

Now as for as getting the date into a variable, are you actually
writing a stored procedure or user defined function in SQL?  Or are
you using SQL embedded in RPG.  If the later, simply use RPG op-codes
to get the current date.

Assuming you are writing an SQL stored procedure or user defined
function, then all you really need is a simple set:

create function test ()
returns date
language SQL
begin
  declare wDate date;
  set wDate = current_date;
  return wDate;
end

The catch is of course that CURRENT_DATE returns a date data type and
you need a numeric.

create function ctsc_w/test ()
returns numeric (8,0)
returns NULL on NULL input
deterministic
no external action
contains SQL
language SQL
begin
  declare wDate date;
  declare numDate numeric(8,0);
  set wDate = current_date;
  set numDate = year(wDate) * 10000
              + month(wDate) * 100 + Day(wDate);
  return numDate;
end

Note that I only reference CURRENT_DATE once, this protects against
the slim possibility that the set numDate statement is running across
midnight when the date changes.

I recommend placing your date conversion routine in it's own user
defined function, then you can simply have

set myVar = DateToNumeric(current_Date);

create function ctsc_w/DateToNumeruc (inDate date)
returns numeric (8,0)
returns NULL on NULL input
deterministic
no external action
contains SQL
language SQL
begin
  declare numDate numeric(8,0);
  set numDate = year(inDate) * 10000
              + month(inDate) * 100 + Day(inDate);
  return numDate;
end

HTH,
Charles



On Tue, May 12, 2009 at 9:59 AM,  <DLee@xxxxxxxx> wrote:
> Hi
>
> I'm new working with SQL, and looking thru various manuals, it seems hard
> to gleen out how to get the system date into a variable.
> I specifically need to get it into a numeric (8,0) variable for later
> comparison to a numeric (8,0) loan expiration date existing in our loan
> file.
> I need to check the expiration date against the current date to determine
> if the loan has expired.
>
> The only expamples I find are like: "select year(curdate()) from
> sysibm/sysdummy1"  or "select current date from sysibm/sysdummy1"
> And I definetely don't understand the concept of the "sysibm/sysdummy1" to
> get the date.
> And how can I get this stored in a variable?
>
> I know I sound like a novice wit;h these questions,  I have done a lot of
> embedded sql, but now I'm working with a sql stored procedure, and it
> seems like a different world.
>
> I'm hoping somebody can help me understand how to get what I'm looking
> for.
>
> Appreciate your help.
>
> Darrell Lee
> Information Technology
> Extension 17127
> --
> This is the RPG programming on the IBM i / System i (RPG400-L) mailing list
> To post a message email: RPG400-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
> or email: RPG400-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/rpg400-l.