Join between two tables in SQL/400

190 pts.
Tags:
AS/400
AS/400 commands
SQL/400
** Table 1 ** fld1 is 2A, fld2 is 3,0 & fld3 is 2A... ** Table2 ** fld1 is 7A and which is a combined result of fld1, fld2, fld3 from Table 1..... My requirement is, I would like to join the above tables using any join method and would like to list the records from table1 where all fields in table 1 has a mandatory entry in table 2.... Means while checking how can convert or typecast the decimal with char.... How can I write an SQL/400 query for the above mentioned? --Var

Software/Hardware used:
iSeries

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: 6  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
    It's not absolutely necessary to CAST() a numeric value to CHAR. You can use CONCAT to concatenate the values without casting anything. A CAST() will happen automatically. . However, it might not be satisfactory. The actual concatenation needed will depend on the type of concatenation that created FLD1 in TABLE2. For example, it seems likely that you'll need to preserve the digits of FLD2 from TABLE1 for one or more leading zeros. . Tom
    125,585 pointsBadges:
    report
  • Varun123
    Hi Tom, Is the following info true? DB2, itself provides implicit type casting when we use SUBSTR against Numeric field. So, in this case no need to use CAST/CONVERT right? And can I write the SQL query using SUBSTR as follows... Please let me know whether it is correct or not for comparing a numeric field with non-numeric field? SELECT * FROM TAB1, TAB2 WHERE T1.FLD1 = T2.SUBSTR(FLD1,1,2) AND T1.FLD1 = T2.SUBSTR(FLD1,3,3) AND T1.FLD1 = T2.SUBSTR(FLD1,6,2)
    190 pointsBadges:
    report
  • CharlieBrowne
    Rather than wait for Tom or someone else verify your assumption, why not just try it and see if it works. The big benefit for you is you will learn it better by doing that than just being handed the answer. I am not being negative; doing it yourself is a positive thing and help you grow and learn much faster.
    41,430 pointsBadges:
    report
  • Varun123
    Hi Tom & Charlie. The sql query is correct, actually I didnt have as400 access on that day and hence asked u about the correct sql query. Its working. -Var
    190 pointsBadges:
    report
  • TomLiotta
    There are some general issues with the syntax of the SELECT statement that I assumed were typos. You can't use the "T1" and "T2" correlatives until they have been associated with the tables. And instead of prefixing a function such as "T2.SUBSTR(FLD1,1,2)", you would prefix the column, "SUBSTR(T2.FLD1,1,2)". And I suspect that the three comparisons against "T1.FLD1" should be against three different columns. . With the syntax problems cleaned up, the SELECT statement should run without error. However, I wouldn't expect correct results in many cases. . The problem would most likely be obvious for "T1.FLD1 = SUBSTR(T2.FLD1,1,2)". When T2.FLD1 is cast as CHAR, leading zeros will be dropped. If T1.FLD1 is a CHAR(2) column that is expected to match the first two digits of T2.FLD1, then dropping leading zeros would easily result in a mismatch. . In order to allow for any leading zeros, the three comparisons should be like "T1.FLD1 = SUBSTR(DIGITS(T2.FLD1),1,2)". The DIGITS() function will preserve leading zeros during conversion to characters. Each of the SUBSTR() functions should be over DIGITS(T2.FLD1) rather than over the value from the column. . Tom
    125,585 pointsBadges:
    report
  • dinuas400
    Yes as Tom and varun mentioned we can use the both ways ... Substring and concat...
    510 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