SQLRPGLE producing invalid output

6895 pts.
Tags:
iSeries AS/400
SQLRPGLE
V6R1
I've got an SQLRPGLE program used to determine, among other things, whether data in one file member does or does not exist - if data exists in the current version but not in the previous a record is written to a transaction file. Thing is, I'm getting multiple records being written even though the data (by type & account) is unique.
exec sql
create alias c_Hsp0060 for Hsp0060(Current);

exec sql
create alias p_Hsp0060 for Hsp0060(Previous);


exec sql
declare Deletes_0060 cursor for
select *
from p_Hsp0060 a exception join c_Hsp0060 b
on a.rtyp60 = b.rtyp60 and
a.acct60 = b.acct60;

exec sql
open Deletes_0060;
Dou sqlcod = 100;
exec sql
fetch Deletes_0060 into :p_hsp0060r;
If sqlcod = 100;
exec sql
close Deletes_0060;
Leave;
EndIf;

Acd60 = 'D';

Write(e) Hsp0160r;
EndDo;


Well, I'm having no luck with this text editor (the link 'How to use this text editor' produces 'page not found') and I can't see any of the buttons referenced in the faq. I hope someone can make out the above.

Software/Hardware used:
V6R1, SQLRPGLE

Answer Wiki

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

Discuss This Question: 14  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
    What happens if you replace the DECLARE, the OPEN and the DO-group with this single statement:  
    exec sql insert into Hsp0160r select * from p_Hsp0060 a exception join c_Hsp0060 b on a.rtyp60 = b.rtyp60 and a.acct60 = b.acct60;
     I would expect the exact same result, but it might help determine where the problem is. Also, what happens when you run this statement by itself in interactive SQL:  
    select * from p_Hsp0060 a exception join c_Hsp0060 b on a.rtyp60 = b.rtyp60 and a.acct60 = b.acct60;
     You would need to run the CREATE ALIAS statements first and DROP ALIAS after. But do you only get single rows the way that you intend?   Tom
    125,585 pointsBadges:
    report
  • TomLiotta
    BTW, thanks for coming up with a way to show code in a semi-readable way. It's not perfect, but I might be able to refine it.   Tom
    125,585 pointsBadges:
    report
  • philpl1jb
    If SQLCODE < 0, execution was not successful.Perhaps you're getting error codes on some fetches
    49,600 pointsBadges:
    report
  • Splat
    Tom, I hadn't tried the single statement though I'll give it a shot.  I lose the value placed in Acd60 but that's a minor issue at the moment.  I've run the statement interactively, creating the aliases and everything, and the data shows up like it should.Phil, I'll toss in an additional check to look for SQLCODE < 0.  I'm game to try anything.I used blockquote, code, and br tags in HTML format to try and get the display readable (I just tossed in more br tags but the result wasn't quite what I was trying for).  The buttons that show under 'Discuss This Question:' aren't available on the 'Ask a Question:' in either Firefox or IE.
    6,895 pointsBadges:
    report
  • TomLiotta
    I used blockquote, code, and br tags in HTML format...   I saw the blockquote in the source of your post and tried it with code. It helps the code to come across better, though I haven't ried a significant snippet yet.   One important item is that a copy/paste direct from a 5250 screen will not find any line-feed characters. The screen doesn't have any. I always paste multiple lines into Notepad before copy/pasting into a browser to ensure that line breaks are included.   Phil's thought is valid. You shouldn't do a WRITE without verifying that the FETCH was successful. But then the question would be why was the FETCH failing. The cursor should supply a row each time.   Since you're using a multi-member file (why?), it's much more likely to run into data errors. That would seem to be a reasonable cause of failures. If you were using real SQL tables, data errors would be reduced for FETCH.   Tom
    125,585 pointsBadges:
    report
  • Splat
    Found the bloody problem. And yes, I deserve to be kicked.
    fetch Deletes_0060 into :p_hsp0060r;
    should read
    fetch Deletes_0060 into :c_hsp0060r;
    Wrong target data structure so what was being written out was the last valid value in the data structure. Sometimes it helps to read the code I wrote.
    6,895 pointsBadges:
    report
  • Splat
    Tom, I hadn't thought about the lack of line feeds from a 5250 display.  I'll try using notepad next time.Short version of the why of the multiple member files.  We isolate adds/changes/deletes for selected data compiled from multiple sources - load a new (current) member with the most recent compilation then compare it to the old (previous) member.  It's may not be the most elegant solution but it serves its purpose and (excepting when I'm being denser than usual) is stable.  This was my first attempt at implementing the process without using multiple member logical files.
    6,895 pointsBadges:
    report
  • TomLiotta
    Wrong target data structure...   Not much chance of us seeing that given what we were shown. And from what we were shown, it wouldn't seem to matter. By name, it looks like  :p_hsp0060r is just a structure that describes Hsp0060(Previous) . Since that's a member from the same file as Hsp0060(Current) , and :c_hsp0060r would seem to be a structure that describes that other member, there would be no reason for them to be different. And since only Hsp0060(Previous) is input into the program by SQL, there seems to be no reason for the other structure to exist in the program at all. You are, after, using a [LEFT] EXCEPTION JOIN.   Or is there additional SQL that we weren't shown?   Tom
    125,585 pointsBadges:
    report
  • TomLiotta
    Your description, of course, says that you apparently want the opposite of the join that you coded. -- Tom
    125,585 pointsBadges:
    report
  • Splat
    There are other parts to the program that use both data structures and no, I wouldn't expect you to have been able to spot that.  That I didn't think it relevant to the problem at hand is my mistake.
    6,895 pointsBadges:
    report
  • Splat
    My description is insufficiently descriptive.  I want those items existing in Hsp0060(Previous) but not in Hsp0060(Current).  The p_hsp0060r data structure is prefixed (for purposes elsewhere) but the fields in the output file are named the same as the file named in the data structures' EXTNAME keyword.
    6,895 pointsBadges:
    report
  • TomLiotta
    So your original was incorrect:  
    if data exists in the current version but not in the previous...
      And your join was correct, i.e., you wanted rows from "previous".   But even the you were writing from the data structure for "previous"... it was the wrong data structure???   Since the code (that we see) never input anything into the "current" data structure and you were fetching the rows that you wanted into the "previous" data structure, the "current" data structure was populated somewhere else in the code. Also, because the "current" data structure wasn't prefixed, the output came from "current due to matching names.   Phew! Assuming it makes sense, it could be an argument for single-purpose functions.   Still, the single INSERT should avoid all of it. The data structures shouldn't even be needed if the file formats match.   Tom
    125,585 pointsBadges:
    report
  • TomLiotta
    @Splat: It would be nice if I could edit a couple spots in my last comment to make some sense. But it was mostly just trying to clarify for myself what was intended. Don't think to hard about most of it. -- Tom
    125,585 pointsBadges:
    report
  • Splat
    Tom, you are correct.  The original description should have read 'if data exists in the current version but not in the previous'.I'll be trying the single INSERT when I've the time to do the necessary testing.  Thank you for that by the way.As always, I appreciate the assistance and the insights.
    6,895 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