Re: DB2 SQL confusion

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.