SQL Statement

pts.
Tags:
i5
iSeries
OS/400
Column qualifier or table SLGRREP undefined. update yyyyy/slf1rep set F1_CAP_AT_END = 'C' where (xxxxx/slgrrep.grbss || ' ' || xxxxx/slgrrep.grggr) = in (select yyyyy/slf1rep.f1BSS || ' ' || yyyyy/slf1rep.F1GGR from xxxxx/slgrrep, yyyyy/slf1rep, xxxxx/slsyrep where SLF1REP.F1_END_DATE > SLSYREP.SY_PROCESSING_DATE and SLGRREP.GR_LOAN_TYPE = 'abcd' and F1_TYPE_OF_DEFER_FORB between 'z01' and 'z99' and F1_ACTIVE_INACTIVE_STS = 'A' and F1_CAP_AT_END = ' ' and SLF1REP.F1_CANCELED_FLAG = ' ') What is wrong with this As400 SQL statment?

Answer Wiki

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

Hi,

i’m not sure this is allowed to use UPDATE keyword with ‘Joined’ table. Seems like it does not like this second table (the one not to be updated).

=====================================================================
<pre>
where (xxxxx/slgrrep.grbss || ‘ ‘ || xxxxx/slgrrep.grggr) =
in ( subselect )
</pre>
I’ve never seen a library qualifier used as part of a correlation name. Instead of [xxxxx/slgrrep], try just [slgrrep].

And I don’t quite see the logic in the WHERE clause in the first place. You’re taking a couple columns that exist only within the subselect and looking for matches against columns that exist in both the outer UPDATE as well as within the subselect, without identifying which set they should be taken from.

It’s not clear what you’re actually trying to do. It looks like you should have <b>where (yyyyy/slf1rep.f1BSS || ‘ ‘ || yyyyy/slf1rep.F1GGR)</b> and <b>select xxxxx/slgrrep.grbss || ‘ ‘ || xxxxx/slgrrep.grggr
from</b>. I.e., reverse the two column specifications. Since they should have equal values, you shouldn’t care if they’re reversed.

In any case, a description of your intended logic would a long way towards determining what should go where.

Tom

Discuss This Question: 2  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
  • Lookin2learn
    In your code, SLF1REP.F1_END_DATE > SLSYREP.SY_PROCESSING_DATE and SLGRREP.GR_LOAN_TYPE = 'abcd' and F1_TYPE_OF_DEFER_FORB between 'z01' and 'z99' and F1_ACTIVE_INACTIVE_STS = 'A' and F1_CAP_AT_END = ' ' and SLF1REP.F1_CANCELED_FLAG = ' ') it appears that the end bracket at the end of your statement doesn't have a corresponding open bracket anywhere else in the code. Not sure if that's it or not since I don't have all of your code but that looks suspicious. Good luck with it...I'm sure someone in here will solve it for you, there are a lot of very smart people watching and replying to messages in here. Take care.
    0 pointsBadges:
    report
  • Josephnongbe
    As you notice, in the first WHERE statement, the '=' is followed by 'IN'. I don't think that possible. update yyyyy/slf1rep set F1_CAP_AT_END = 'C' where (xxxxx/slgrrep.grbss || ' ' || xxxxx/slgrrep.grggr) = in (select yyyyy/slf1rep.f1BSS || ' ' || yyyyy/slf1rep.F1GGR from xxxxx/slgrrep, yyyyy/slf1rep, xxxxx/slsyrep where
    0 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