Page 1 of 3 123 LastLast
Results 1 to 15 of 38
  1. #1
    Join Date
    Jan 2012
    Posts
    37

    Unanswered: DB2 AS400 ISeries - Update with inner join

    Now, I know update with inner joins have been asked before, but so far none of the suggested code have worked.

    Here is what it is meant to do:
    Code:
    	update CMUSREXP
    	set SECQUES=@SECQUES,
    		SECANS=@SECANS
    	from CMSUSRP a
    	inner join CMUSREXP b
    	on RTRIM(a.CSUSER)=RTRIM(b.CSUSER)
    	where RTRIM(a.CSUSER)=RTRIM(@CSUSER) and RTRIM(a.PM46bA)=RTRIM(@PM46bA);
    	
    	GET DIAGNOSTICS @resultInt = ROW_COUNT;
    Any help would be appreciated.
    I would like for you to stay off this thread or tell me something useful. It must be relative to the question, and get me closer to fixing the problem. Just by me posting, I am considering that there may be someone with more knowledge than myself. I hate having to ask other people for help, so this is my last resort. Don't make it worse, because it can get worse. -Zaryk

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by zaryk View Post
    tell me something useful
    Like, read the fine manual?

  3. #3
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,369
    Теперь жди ответного удара.

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    MERGE statement may be better.

    Code:
    Example 1: For activities whose description has changed,
     update the description in the archive table.
     For new activities, insert into the archive table.
     The archive and activities tables both have activity as a primary key.
    
    MERGE INTO archive ar
      USING (SELECT activity, description FROM activities) ac
      ON (ar.activity = ac.activity)
      WHEN MATCHED THEN 
        UPDATE SET description = ac.description
      WHEN NOT MATCHED THEN
        INSERT (activity, description) VALUES(ac.activity, ac.description)
    Please see Information Center for more detailed descriptions.
    MERGE

    Another issue is you need not RTRIM to compare strings.
    String comparisons
    String comparisons

    ...

    Character and graphic string comparisons

    ...

    If the strings have different lengths,
    a temporary copy of the shorter string is padded on the right with blanks before comparison.
    The padding makes each string the same length.
    ....

  5. #5
    Join Date
    Jan 2012
    Posts
    37
    Quote Originally Posted by n_i View Post
    Like, read the fine manual?
    and beer makes people stupid.
    I would like for you to stay off this thread or tell me something useful. It must be relative to the question, and get me closer to fixing the problem. Just by me posting, I am considering that there may be someone with more knowledge than myself. I hate having to ask other people for help, so this is my last resort. Don't make it worse, because it can get worse. -Zaryk

  6. #6
    Join Date
    Jan 2012
    Posts
    37
    Quote Originally Posted by db2girl View Post
    Теперь жди ответного удара.
    No comprendo.
    I would like for you to stay off this thread or tell me something useful. It must be relative to the question, and get me closer to fixing the problem. Just by me posting, I am considering that there may be someone with more knowledge than myself. I hate having to ask other people for help, so this is my last resort. Don't make it worse, because it can get worse. -Zaryk

  7. #7
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Google Translate

    Do you know this website?
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  8. #8
    Join Date
    Jan 2012
    Posts
    37
    Quote Originally Posted by sathyaram_s View Post
    Google Translate

    Do you know this website?



    Sure do, but if someone doesn't take the time to post in english, I shouldn't have to take the time to translate it into english.
    I would like for you to stay off this thread or tell me something useful. It must be relative to the question, and get me closer to fixing the problem. Just by me posting, I am considering that there may be someone with more knowledge than myself. I hate having to ask other people for help, so this is my last resort. Don't make it worse, because it can get worse. -Zaryk

  9. #9
    Join Date
    Jan 2012
    Posts
    37
    Quote Originally Posted by tonkuma View Post
    MERGE statement may be better.

    Code:
    Example 1: For activities whose description has changed,
     update the description in the archive table.
     For new activities, insert into the archive table.
     The archive and activities tables both have activity as a primary key.
    
    MERGE INTO archive ar
      USING (SELECT activity, description FROM activities) ac
      ON (ar.activity = ac.activity)
      WHEN MATCHED THEN 
        UPDATE SET description = ac.description
      WHEN NOT MATCHED THEN
        INSERT (activity, description) VALUES(ac.activity, ac.description)
    Please see Information Center for more detailed descriptions.
    MERGE

    Another issue is you need not RTRIM to compare strings.
    String comparisons

    RTRIM isn't an issue unless it is keeping me from creating that stored procedure. Plus, when passing parameters from c#, so far, it doesn't seem these are padded. RTRIM reassures that the length should be the same if the text is the same.

    Code:
    MERGE INTO CMUSRRQP a
      USING (SELECT SECQUES, SECANS FROM CMUSRREP) b
      ON (b.ACCNUM = a.ACCNUM and a.SICODE=b.SICODE and RTRIM(a.CSUSER)=RTRIM('RHATCHER') and RTRIM(a.PM46bA)=RTRIM('spartan'))
      WHEN MATCHED THEN 
        UPDATE set SECQUES='This is a question',
    	SECANS='is it?';
    Code:
    > MERGE INTO CMUSRRQP a   USING (SELECT SECQUES, SECANS FROM CMUSRREP) b   ON (b.ACCNUM = a.ACCNUM and a.SICODE=b.SICODE and RTRIM(a.CSUSER)=RTRIM('RHATCHER') and RTRIM(a.PM46bA)=RTRIM('spartan'))   WHEN MATCHED THEN      UPDATE set SECQUES='This is a question',  SECANS='is it?'
    
    SQL1240: ANS Flagging -- Statement MERGE not supported.
    SQL0362: Flagging detected an error in the current SQL statement.
    
    SQL State: 0168I
    Vendor Code: 362
    Message: [SQL0362] Flagging detected an error in the current SQL statement. Cause . . . . . :   The SQL statement contains syntax that is not standard at position 1. Recovery  . . . :   See previous messages in the job log for a more complete description of the error.  If conformance to the standard is needed, change the statement to conform and try the request again.
    
    Statement ran successfully, with warnings   (4 ms)
    
    
    Job Logs:
    
    ANS Flagging -- Statement MERGE not supported.           
    Flagging detected an error in the current SQL statement. 
    ANS Flagging -- Statement MERGE not supported.           
    Flagging detected an error in the current SQL statement. 
    ANS Flagging -- Statement MERGE not supported.           
    Flagging detected an error in the current SQL statement. 
    ANS Flagging -- Statement MERGE not supported.           
    Flagging detected an error in the current SQL statement.


    Edit: The MERGE statement was introduced in DB2 V8.1 FP1. We have v7.1
    Last edited by zaryk; 04-01-12 at 20:28.
    I would like for you to stay off this thread or tell me something useful. It must be relative to the question, and get me closer to fixing the problem. Just by me posting, I am considering that there may be someone with more knowledge than myself. I hate having to ask other people for help, so this is my last resort. Don't make it worse, because it can get worse. -Zaryk

  10. #10
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Did you executed on DB2 AS400 ISeries?

    I couldn't find SQL1240 on "IBM i Database SQL messages and codes 7.1".

    RTRIM isn't an issue unless it is keeping me from creating that stored procedure.
    It's useless and poosibility of inefficient access path.
    Good programmer wouldn't leave such problematic codes, even if it worked.

  11. #11
    Join Date
    Jan 2012
    Posts
    37
    Quote Originally Posted by tonkuma View Post
    Did you executed on DB2 AS400 ISeries?

    I couldn't find SQL1240 on "IBM i Database SQL messages and codes 7.1".


    It's useless and poosibility of inefficient access path.
    Good programmer wouldn't leave such problematic codes, even if it worked.

    Did you executed on DB2 AS400 ISeries?
    System I Navigator
    I don't know how to execute it directly from green screen.




    In that sense, let's leave out all the string comparison trims from both the script and c# code, and see what happens. More than likely what would have been working with trims, would stop working because nothing would match.

    I will agree, what you said is an ideal situation, wish it was that easy.



    EDIT: And before anyone else comments on stuff I am not asking about, you must realize, the place I work at is denormalized. I do not take responsibility for anything here. Our main applications are codeplex/coolplex, and as far as I'm concerned.....it is limited, and just plain stupid, and because of the limitations of codeplex/coolplex, our database is limited, and just plain stupid. And what is sad, I have more experience with as400 scripts and c# than most of the employees here, and I only have 1 year of programming work experience.
    Last edited by zaryk; 04-01-12 at 20:44.
    I would like for you to stay off this thread or tell me something useful. It must be relative to the question, and get me closer to fixing the problem. Just by me posting, I am considering that there may be someone with more knowledge than myself. I hate having to ask other people for help, so this is my last resort. Don't make it worse, because it can get worse. -Zaryk

  12. #12
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    ..., let's leave out all the string comparison trims from both the script and c# code, and see what happens.
    It's useless to mention c#.
    We are discussing a program written by SQL Language(SQL code) on DB2 for iSeries.
    And, trying to use common codes between SQL and c# are out of touch with reality.

  13. #13
    Join Date
    Jan 2012
    Posts
    37
    Quote Originally Posted by tonkuma View Post
    It's useless to mention c#.
    We are discussing a program written by SQL Language(SQL code) on DB2 for iSeries.
    And, trying to use common codes between SQL and c# are out of touch with reality.
    Ok, so tell me something useful. Like how to execute a script directly from the green screen, and not from System I Navigator. Or answer my initial question, and stop worrying about the fact that there are RTRIM. There is always a reason why I do something, and if I am not asking you if that is the correct way to write it, then don't worry about it.



    Edit: And you are discussing a program written by SQL Language(SQL code) on DB2 for iSeries. I am discussing a script that is going to be called by c#, so if the sql code works in system i navigator, and doesn't work in c#, then there is a problem. So, I write the sql to work with c#.
    I would like for you to stay off this thread or tell me something useful. It must be relative to the question, and get me closer to fixing the problem. Just by me posting, I am considering that there may be someone with more knowledge than myself. I hate having to ask other people for help, so this is my last resort. Don't make it worse, because it can get worse. -Zaryk

  14. #14
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    The MERGE statement was introduced in DB2 V8.1 FP1. We have v7.1
    DB2 on different platform should be considered diffrent product.

    Exact explanation might be:
    MERGE statement was introduced in DB2 for Linux, UNIX, and Windows V8.1 FP1.
    MERGE statement was introduced in DB2 for i V7.1.

    Please see "IBM i Information Center"
    MERGE

  15. #15
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    And you are discussing a program written by SQL Language(SQL code) on DB2 for iSeries. I am discussing a script that is going to be called by c#, so if the sql code works in system i navigator, and doesn't work in c#, then there is a problem. So, I write the sql to work with c#.
    How did you throw your SQL code from c# to DB2?

    Usual way might be to throw a string including SQL code which should also work in system i navigator.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •