RE: SQL NULL return

[<SRamanujan@xxxxxxxxxxxxxxxxxx>]

Sorry, I missed the whole point on typecasting, again.. I could, of
course, define the fields on the functions as varying to avoid this.  
On the default, I would have to work on some logic since I have to use
the native dds.  Also since I cannot change them since they affect the
rest of the applications that are not java.

Thanks,
Sudha

Sudha Ramanujan
SunGard Futures Systems
sramanujan@xxxxxxxxxxxxxxxxxx
(312) 577 6179
(312) 577 6101 - Fax


-----Original Message-----
From: Elvis Budimlic [mailto:ebudimlic@xxxxxxxxxxxxxxxxxxxxxxxxx] 
Sent: Monday, April 19, 2004 11:56 AM
To: 'Midrange Systems Technical Discussion'
Subject: RE: SQL NULL return

Sudha, we've discussed the "not found" issue in the past:

http://archive.midrange.com/midrange-l/200403/msg01124.html

Perhaps the solution is same/similar as the one found in that thread?
If not check this one out, though I doubt it'll help:

http://www.iseriesnetwork.com/Forums/Thread.cfm?CFApp=59&&Message_ID=134
324&
_#Message134324

If you have interactive SQL feature installed on your iSeries you should
have STRSQL command available (if not, license program is called SQL
Development Kit).
Type STRSQL -> CREATE TABLE -> F4 -> put the cursor under column "Nulls"
->
F1

This should give you help as to how NULLS are defined in SQL.  You'll
need
to adjust your UDTF to create the field null capable, or change your
program
logic not to generate nulls for those fields.

I'll paste the excerpt of the online help you'll see by doing the steps
above:

                              Nulls - Help                             
                                                                       
Select the null attribute to be used for the specified column.  The    
possible choices are:                                                  
                                                                       
1=NULL                                                                 
    Allows null values in the specified column (field).                
                                                                       
2=NOT NULL                                                             
    Does not allow null values in the specified column (field), and no 
    default is provided.                                               
                                                                       
3=NOT NULL WITH DEFAULT                                                
    Does not allow null values in the specified column (field), but    
    provides the default for the data types shown below:               

As for default values question, default value for DATE field is current
date.  If you allow nulls in the field, then you can pass in NULL.
Using
DDS (native file creation interface) you can specify other default
values,
but I am not sure SQL will let you do that.

Elvis

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of
SRamanujan@xxxxxxxxxxxxxxxxxx
Sent: Sunday, April 18, 2004 11:01 PM
To: midrange-l@xxxxxxxxxxxx
Subject: SQL NULL return

Hi All,
I have servlets accessing UDTF written in SQLRPGLE.  I encounter the
following problems that I need help with.

-       I wrote another UDF that is part of a declare/select of the UDTF
that is accessed by Java.  But I get this function not found in the
*LIBL error.  I am also not able to qualify my UDF with the library.  Is
there a solution to this?  My select statement looks like this:
        UDF func1(field2,field3) - returns calculated number
        UDTF    Select field1, func1(field2,field3),field4 from file
        The java access to udtf - Select * from table(UDTF(inp1, inp2))
x
-       I have problems with some of the packed decimal fields when they
are null.  I get 'Java Number Invalid Expression error' Should I define
my UDTF as null capable or something?  
-       Dates/Times - when the date/time is null, I have to send back a
date/time info and not a 0 or null. Now I have to write a logic in Java
to look for those values and move a 0.  Can this be avoided?

Thanks for all the help,
Sudha

Sudha Ramanujan
SunGard Futures Systems
sramanujan@xxxxxxxxxxxxxxxxxx
(312) 577 6179
(312) 577 6101 - Fax 



_______________________________________________
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.