Recursive SQL error

25 pts.
Tags:
SQL error messages
SQL statements
For the life of me I can not see what my problem is. Any help would be appreciated. *************** Beginning of data **************************************** WITH RPL (LEVEL, PINBR, CINBR, QTYPR) AS (SELECT 1, ROOT.PINBR, ROOT.CINBR, ROOT.QTYPR FROM ITSTRU ROOT WHERE ROOT.PINBR = '8B714-0005651' UNION ALL SELECT PARENT.LEVEL+1, CHILD.PINBR, CHILD.CINBR, CHILD.QTYPR FROM RPL PARENT, ITSTRU CHILD WHERE PARENT.CINBR = CHILD.PINBR AND PARENT.LEVEL < 2 ) SELECT PINBR, LEVEL, CINBR, QTYPR FROM RPL; ****************** End of data *******************************************

Software/Hardware used:
Iseries

Answer Wiki

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

It is hard to see exactly what you are doing.

Looks like a RUNSQLSTM

If so, can you just paste the source code.

BTW, if it is RUNSQLSTM and all you are doing is a SELECT, then that is the problem. With RUNSQLSTM, you can Update, Delete, Insertt, Create, everything except a simple SELECT.

Discuss This Question: 8  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
  • carlosdl
    Not sure who the author of the above answer is, but the answer makes sense, since the error message reads "SQL statement not allowed". Additionally, I'm not sure if the OP is intending to use the word LEVEL as a reserved word.  If that is the case, that could be a problem, as AFAIK the LEVEL keyword is only allowed on hierarchical queries, which as far as I remember must include the CONNECT BY clause.  (I might not remember well. though)
    69,920 pointsBadges:
    report
  • philpl1jb
    I think this is the SQL but that's as far as I can go. 
    WITH RPL (LEVEL, PINBR, CINBR, QTYPR) AS 
    
    (SELECT 1, ROOT.PINBR, ROOT.CINBR, ROOT.QTYPR 
    FROM ITSTRU ROOT 
    WHERE ROOT.PINBR = '8B714-0005651' 
    
    UNION ALL 
    
    SELECT PARENT.LEVEL+1, CHILD.PINBR, CHILD.CINBR, CHILD.QTYPR 
    FROM RPL PARENT, ITSTRU CHILD 
    WHERE PARENT.CINBR = CHILD.PINBR 
    AND PARENT.LEVEL < 2 ) 
    
    SELECT PINBR, LEVEL, CINBR, QTYPR FROM RPL; 
    50,505 pointsBadges:
    report
  • TomLiotta
    Actual SQL appears to look this way (and I hope formatting works):
    UPDATE TSTSQL/TSTTBL SET NAMECOL = 'THIRD' WHERE IDCOL = 3
     WITH RPL (LEVEL, PINBR, CINBR, QTYPR) AS
          (SELECT 1, ROOT.PINBR, ROOT.CINBR, ROOT.QTYPR
                  FROM ITSTRU ROOT
                  WHERE ROOT.PINBR = '8B714-0005651'
           UNION ALL
                 SELECT PARENT.LEVEL+1, CHILD.PINBR, CHILD.CINBR, CHILD.QTYPR
                   FROM RPL PARENT, ITSTRU CHILD
                 WHERE PARENT.CINBR = CHILD.PINBR
                    AND PARENT.LEVEL < 2
          )
     SELECT PINBR, LEVEL, CINBR, QTYPR
            FROM RPL;
    But I'm not clear on a couple things. First thing that I don't see is how carlosdl knew what the error message was. I don't see any mention of an error message, only that some problem exists.   Next, I can see why RUNSQLSTM would be a possibility; but it could also be a QM query. With no explanation of what is being attempted nor what any problem is, it's impossible to say much.   Tom
    125,585 pointsBadges:
    report
  • TomLiotta
    And it's also not clear why the editor often inserts <BR> tags in code but the tags don't get rendered when shown in browsers; but that's separate from the actual 'Question'. -- Tom
    125,585 pointsBadges:
    report
  • carlosdl
    Tom, what I don't know is where you saw that it was an update ;-)  It seems that the question has been editted since I first saw it.  Originally it included an error message.
    69,920 pointsBadges:
    report
  • philpl1jb
    Right I listed the SQL code as it appeared originally .. at that time it included an error statement but no   UPDATE TSTSQL/TSTTBL SET NAMECOL = ‘THIRD’ WHERE IDCOL = 3 Edited twice is suspect .. once adding the update and subsequently removing it.    
    50,505 pointsBadges:
    report
  • carlosdl
    But that update doesn't make much sense (at least to me), unless it is a separate statement.  Could it be that Tom was testing something else, and included that line here by mistake? (not likely, I know).
    69,920 pointsBadges:
    report
  • TomLiotta
    Yeah, it's tricky when reviewing questions that are updated. I'm also not sure where the UPDATE line in the [code][/code] came from. Very possibly, it was a line that I failed to replace when I pasted into a temporary Notepad doc I was using.   But if "SQL statement not allowed" was original error text, then the 'Answer' is almost certainly correct. A SELECT statement is not meaningful as a RUNSQLSTM statement.   Tom
    125,585 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