Embedded SQL in rpg for subfile build SQLCOD

1380 pts.
Tags:
RPG
SQL
SQLCOD
SQLRPGLE
My fetch next in sql is getting sqlcod = -000000181 and sqlstt = '22007'.  I looked this up.  It says invalid date or time. Pointing to date data types in the file I am trying to read.

The records contain two date fields and two time fields.

How can I get around this?

 

Thanks in advance.

Nick



Software/Hardware used:
as/400 sqlrpgle

Answer Wiki

Thanks. We'll let you know when a new response is added.

The correct answer is…… null date fields. I loaded a valid date in all the records in the file. Works like a champ.

Discuss This Question: 5  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • TomLiotta
    How can I get around this? Does any error appear if you simply use SELECT * FROM... in interactive SQL to select all rows and output them to a new temporary file? Tom
    125,585 pointsBadges:
    report
  • TomLiotta
    You might know that null date fields shouldn't be the problem. The problem should either be the appropriate programming to handle null date fields or the definition of the date fields to disallow null values. If null values are to be allowed, then the programming needs to be changed to accept them when they happen. Tom
    125,585 pointsBadges:
    report
  • NickHutcheson1
    Tom, tell me more. "If null values are to be allowed, then the programming needs to be changed to accept them when they happen." I am trying to evolve, I just need some nudging now and then... Also, in another case, I am trying to join a header and a detail file and select all fields into data structures defined as external(filename).
    D dswtchdr      E DS                  EXTNAME(wtchdr) 
    D dswtcdet      E DS                  EXTNAME(wtcdet) 
    C     $CreateCsr    Begsr                                                     
    C/EXEC SQL                                                                    
    C+ prepare S1 from :sqlstmt                                                   
    C/end-exec                                                                    
     *                                                                            
    C/EXEC SQL                                                                    
    C+ declare C1 scroll cursor for S1                                            
    C/end-exec                                                                    
    C                   EndSr                                                     
     *================================================================            
    C     $OpenCsr      Begsr                                                     
    C/exec sql                                                                    
    C+ open C1                                                                    
    C/end-exec                                                                    
    C                   EndSr                           
       sqlbase = 'SELECT * '                                              + 
                  'from wtchdr inner join wtchdet '                      +  
                  ' on wtchdr.whcnum = wtchdet.wdcnum '                  +  
                  %trim(whereclause)                                      + 
                       (orderbyclause)                                    ; 
    sqlstmt =%trim(sqlbase)                                               ; 
    
    C/exec sql                                          
    C+ fetch next from C1 into :dswtchdr                
    C/end-exec                                          
    On the fetch next, the header data structure receives all data from the row selected. How do I include the detail?
    1,380 pointsBadges:
    report
  • TomLiotta
    Database null values don't exist in HLLs. They only "exist" in the database and are only referenced through a database language such as SQL. If you want columns to be transferred from the database into a HLL program's variables, the program must be coded appropriately. When a null-capable value is FETCHed into a host variable (a program variable), a null value can't be transferred. So, you provide additional definitions for each such column to act as "indicators" of null values. If you FETCH Cust_Name into a host variable named :CustName, you also need to FETCH an associated indicator value, perhaps named :CN_ind, e.g., FETCH ... INTO :CustName:CN_ind. Before you can make any use of the value in :CustName, you always must test :CN_ind first to see if its value is negative. If it is, then there is no valid value in :CustName that your program can use. Null-indicator variables are just small integers, defined in RPG perhaps as 5i 0. You can have an array of smallint variables that you FETCH right along with an array of column values if you aren't declaring separate variables for each column; you can DIM() the array to fit the number of columns. You need to have enough indicator variables to match up with each null-capable column. The related null-indicator must be tested for each null-capable column before that column value can be used. The V7R1 Info Center has a lot more topics on-line than earlier releases. A simple example is shown in Using indicator variables in ILE RPG applications that use SQL, though you should download the various embedded programming .PDF manuals for your VRM. As for your second problem, it really should be in a new thread. It has the potential to confuse this thread a lot. The only way that I can think of quickly that might make it work is to CREATE VIEW over a JOIN of the two files. Then reference the VIEW name in EXTNAME() for a single E DS. You don't actually need to use the VIEW for anything other than that reference. That is, you don't need to SELECT from it if you don't want to. The pre-compiler will only detect one DS for one SELECT. There is only a single set of columns in the returned result set. Tom
    125,585 pointsBadges:
    report
  • NickHutcheson1
    Thanks Tom. I'll be trying that today. Nick
    1,380 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following