My as/400 SQL is getting a ‘ Value for column too long.’ error msg

385 pts.
Tags:
AS/400 administration
AS/400 SQL
SQL tables
Value for column or variable SPDESC too long.

UPDATE mbmtest01/wbmscur

set spdesc = (Select itdsc FROM amflib7/itemasa WHERE itnbr = spptno)

 WHERE EXISTS (SELECT DISTINCT itdsc FROM amflib7/itemasa WHERE itnbr = spptno)

 How do I make the field truncate the larger input field. The input field is 30 bytes and the receiving field is 15 bytes. Thanks

Answer Wiki

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

You make the value shorter by using a built-in function such as LEFT() or SUBSTR(). For example, this:<pre>
Select itdsc FROM amflib7/itemasa</pre>
…might become:<pre>
Select LEFT(itdsc,15) FROM amflib7/itemasa</pre>
And as Carlosdl says, EXISTS only tells you if any row meets your criteria. It <i>does not tell you</i> how many rows meet your criteria. You can only UPDATE your spdesc column with a single value. If there are multiple rows that satisfy your WHERE clause, the UPDATE will choke. You will then need to refine your WHERE clause to ensure a one-to-one match.

But that comes after this part works, and it might not be a problem anyway.

Tom

Discuss This Question: 10  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
    Just a comment while you wait for an answer from an AS400 expert. The EXISTS condition is considered to be met if the subquery returns at least one row. The data from the returned row(s) is not important. It only checks if some record is returned, therefore you could remove unnecessary overhead from the database engine by doing something like this:
    ...
    WHERE EXISTS (SELECT 1 FROM amflib7/itemasa WHERE itnbr = spptno)
    68,495 pointsBadges:
    report
  • nitzinger
    Ok I got my query to work. Thanks to Tom and carlosdl. One last question. Why do I have trouble processing only the rows that the spdesc = ' ' equal blank. When I try this Null values not allowed in column or variable SPDESC. UPDATE mbmtest01/wbmscur set spdesc = (Select LEFT(itdsc,15) FROM amflib7/itemasa WHERE itnbr = spptno and spdesc = '') WHERE EXISTS (SELECT DISTINCT itdsc FROM amflib7/itemasa WHERE itnbr = spptno) Remove the spdesc = '' condition and every row is processed not only the blank desc rows?
    385 pointsBadges:
    report
  • TomLiotta
    Remove the spdesc = ” condition... Are you intending it to be {spdesc = ”} or should it be {spdesc = ' '}? If you are comparing against actual spaces, then there must be a space between the single-quote marks. If there isn't at least one space, then it is a null string. If the spdesc column does not allow for null values, then that's an invalid comparison. I suspect that spdesc is a fixed-length, non-null column, rather than a VARCHAR that includes allowing nulls. It might just be this editor, but in your comment, it actually came across as a double-quote mark rather than two single-quote marks. Tom
    125,585 pointsBadges:
    report
  • nitzinger
    I have tried the spdesc = ' ' both ways and both provide the same result. I really only want to process the description fields that are blank and not all 32000+ rows that exist on the table. Anyhow either way I get the errror message 'Null values not allowed in column or variable SPDESC' when I inculde the (and spdesc = ' ' ) part. Thanks to all...
    385 pointsBadges:
    report
  • TomLiotta
    I get the errror message ‘Null values not allowed in column or variable SPDESC’ when... Please describe the environment. Is the query run in STRSQL? Embedded in RPG? (COBOL? C? REXX?) Run with iSeries Navigator 'Run SQL scripts...'? Also, please supply the column definition for spdesc. Is this in a SQL TABLE or a DDS PF? Tom
    125,585 pointsBadges:
    report
  • nitzinger
    I get the errror message ‘Null values not allowed in column or variable SPDESC’ when… I run the Query in STRSQL and the field is 15 A - DDS PF
    385 pointsBadges:
    report
  • carlosdl
    "Remove the spdesc = ” condition and every row is processed not only the blank desc rows" I find this really interesing, as the mentioned condition only affects the subquery that provides the new value for the spdesc column, so any change to it will not produce any change in the number of rows being affected by the UPDATE command. As for the 'null values not allowed..." message, the problem could be that NULLs are not allowed in column spdesc from mbmtest01/wbmscur, but the subquery from amflib7/itemasa is returning a record with a NULL value in the itdsc column when the spdesc = ' ' condition is added.
    68,495 pointsBadges:
    report
  • nitzinger
    I'm confused. I thought that the code following the" set= " keyword would decide which rows are processed. Depending upon if the ( spdesc = ' ' ) part of the where clause is included or not included should allow the processing of all the rows or just the rows where spdesc = blanks. UPDATE mbmtest01/wbmscur set spdesc = (Select LEFT(itdsc,15) FROM amflib7/itemasa WHERE itnbr = spptno and spdesc = ' ') WHERE EXISTS (SELECT 1 FROM amflib7/itemasa WHERE itnbr = spptno)
    385 pointsBadges:
    report
  • carlosdl
    The SET keyword is used to assign a new value to the desired column. In every SQL command, it is the WHERE clause which decides what rows are going to be affected. In this case the spdesc = ‘ ‘ coindition is part of the WHERE clause of the subquery, and it only affects the rows that are going to be returned by the subquery (which should return only 1 row when used in the right-hand side of an assignment). To change the rows that are going to be affected you need to add the necessary conditions to the WHERE clause of the UPDATE command (which currently has only the "EXISTS (SELECT 1 FROM amflib7/itemasa WHERE itnbr = spptno)" condition).
    68,495 pointsBadges:
    report
  • nitzinger
    Thank you Carlosdl. The (spdesc = ' ') code needed to be in the second where clause. I was believing the first where clause was deciding which rows to process. Thanks to all for there help.
    385 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