Joined Files

1780 pts.
Tags:
AS/400
Lets say I have 2 files I want to join. Lets also say that fielda is 2 positions packed and fieldb is 2 positions numeric. How can I get them to join. It does not like one being packed and the other being numeric. Any ideas? Thanks

Answer Wiki

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

ok before anyone asks. FILE 1 is the packed field A.. FILE 2 is the Numeric Field b. Then join using those 2 fields

Discuss This Question: 14  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
  • ASWDEVELOPER
    assumption : the files are created separately and joined after the fact ... and can't be changed so the field types match ... solution : create file c with field a and field b ... join file c it to file a with field a and file b with field b ... downside : depending on how new records are added to file a and file b, you may need some type of utility to delete the logical join file, update file c ... then recreate the logical ... not the most elegant solution ...
    405 pointsBadges:
    report
  • TomLiotta
    Is this an OUTER or INNER JOIN? I'd expect that a simple inner join via the WHERE clause would work. Tom
    125,585 pointsBadges:
    report
  • RonKoontz
    THe data and files already exist. I just want to join the 2 files based on field A from file 1 and field B from file 2. I don;t really care how its being done as long as its not a wreck. I thought of making a new file and copying the records into it or doing a file create using embed'd SQL in the RPG program. I just was thinking there is a way to join the 2 files together with a pakced and numeric field. I was thinking there had to be away to do it.
    1,780 pointsBadges:
    report
  • TomLiotta
    Ah, this must be a join LF rather than a SQL JOIN. I can create SQL INNER and OUTER JOIN views with different numeric data types. It's been years since a created a join LF. I wasn't thinking. Any chance a SQL VIEW might be acceptable? Tom
    125,585 pointsBadges:
    report
  • RonKoontz
    I tried with an SQL.. Here is what I got. It still does not retreive any data. I think its b/c of the packed and numeric. CREATE TABLE KOONTZ/TESTFILEAA ( W1CPY, W1CUST, W1NAME ) AS ( SELECT DISTINCT ECPY, ECUST, SHASNM FROM EDISPECIAL EXCEPTION JOIN CUST ON ECPY = SHACPY AND ECUST = SHASNO ORDER BY ECPY, ECUST, SHASNM ) -- THIS IS AN MQT! LOAD WITH REFRESH TABLE STATEMENT! DATA INITIALLY IMMEDIATE REFRESH DEFERRED MAINTAINED BY USER; CREATE INDEX KOONTZ/TESTFILEA1 ON KOONTZ/TESTFILEAA -- ALTER TABLE TESTFILEAA -- ADD PRIMARY KEY ( W1CPY, W1CUST, W1NAME );
    1,780 pointsBadges:
    report
  • RonKoontz
    CREATE TABLE KOONTZ/TESTFILEAA ( W1CPY, W1CUST, W1NAME ) AS ( SELECT DISTINCT ECPY, ECUST, SHASNM FROM EDISPECIAL EXCEPTION JOIN CUST ON ECPY = SHACPY AND ECUST = SHASNO ORDER BY ECPY, ECUST, SHASNM ) -- THIS IS AN MQT! LOAD WITH REFRESH TABLE STATEMENT! DATA INITIALLY IMMEDIATE REFRESH DEFERRED MAINTAINED BY USER; CREATE INDEX KOONTZ/TESTFILEA1 ON KOONTZ/TESTFILEAA -- ALTER TABLE TESTFILEAA -- ADD PRIMARY KEY ( W1CPY, W1CUST, W1NAME );
    1,780 pointsBadges:
    report
  • RonKoontz
    500 CREATE TABLE KOONTZ/TESTFILEAA 12/16/09 600 ( 12/16/09 601 W1CPY, 12/16/09 700 W1CUST, 12/16/09 800 W1NAME 12/16/09 900 ) 12/16/09 1000 AS 12/16/09 1100 ( 12/16/09 1200 SELECT DISTINCT 12/16/09 1300 ECPY, 12/16/09 1400 ECUST, 12/16/09 1401 SHASNM 12/16/09 1500 FROM EDISPECIAL EXCEPTION JOIN CUST ON ECPY = SHACPY 12/16/09 1600 AND ECUST = SHASNO 12/16/09 1700 ORDER BY 12/16/09 1800 ECPY, 12/16/09 1801 ECUST, 12/16/09 1900 SHASNM 12/16/09 2500 ) 03/03/09 2501 -- THIS IS AN MQT! LOAD WITH REFRESH TABLE STATEMENT! 12/16/09 2502 DATA INITIALLY IMMEDIATE 12/16/09 2503 REFRESH DEFERRED 12/16/09 2504 MAINTAINED BY USER; 12/16/09 2505 12/16/09 2506 CREATE INDEX KOONTZ/TESTFILEA1 12/16/09 2507 ON KOONTZ/TESTFILEAA 12/16/09 2508 -- ALTER TABLE TESTFILEAA 12/16/09 2509 -- ADD PRIMARY KEY 12/16/09 2510 ( 12/16/09 2511 W1CPY, 12/16/09 2512 W1CUST, 12/16/09 2513 W1NAME 12/16/09 2514 );
    1,780 pointsBadges:
    report
  • RonKoontz
    for some reason I can not past the code in here
    1,780 pointsBadges:
    report
  • RonKoontz
    I tried an SQL.. still nothing
    1,780 pointsBadges:
    report
  • RonKoontz
    CODE....
    
    500  CREATE TABLE KOONTZ/TESTFILEAA                                                                                 12/16/09
        600  (                                                                                                              12/16/09
        601    W1CPY,                                                                                                       12/16/09
        700    W1CUST,                                                                                                      12/16/09
        800    W1NAME                                                                                                       12/16/09
        900  )                                                                                                              12/16/09
       1000  AS                                                                                                             12/16/09
       1100  (                                                                                                              12/16/09
       1200     SELECT DISTINCT                                                                                             12/16/09
       1300            ECPY,                                                                                                12/16/09
       1400            ECUST,                                                                                               12/16/09
       1401            SHASNM                                                                                               12/16/09
       1500    FROM EDISPECIAL EXCEPTION JOIN CUST ON ECPY = SHACPY                                                         12/16/09
       1600    AND ECUST = SHASNO                                                                                           12/16/09
       1700    ORDER BY                                                                                                     12/16/09
       1800             ECPY,                                                                                               12/16/09
       1801             ECUST,                                                                                              12/16/09
       1900             SHASNM                                                                                              12/16/09
       2500 )                                                                                                               03/03/09
       2501 -- THIS IS AN MQT!  LOAD WITH REFRESH TABLE STATEMENT!                                                          12/16/09
       2502 DATA INITIALLY IMMEDIATE                                                                                        12/16/09
       2503 REFRESH DEFERRED                                                                                                12/16/09
       2504 MAINTAINED BY USER;                                                                                             12/16/09
       2505                                                                                                                 12/16/09
       2506 CREATE INDEX KOONTZ/TESTFILEA1                                                                                  12/16/09
       2507 ON KOONTZ/TESTFILEAA                                                                                            12/16/09
       2508 -- ALTER TABLE TESTFILEAA                                                                                       12/16/09
       2509 -- ADD PRIMARY KEY                                                                                              12/16/09
       2510 (                                                                                                               12/16/09
       2511   W1CPY,                                                                                                        12/16/09
       2512   W1CUST,                                                                                                       12/16/09
       2513   W1NAME                                                                                                        12/16/09
       2514 );                      
    
    1,780 pointsBadges:
    report
  • philpl1jb
    Tom is right, SQL should work, wish you could get the code in. Phil
    50,505 pointsBadges:
    report
  • TomLiotta
    Heh, a "preview" option would be handy, eh? Are you pasting into a CODE block (and then closing the CODE block) or simply pasting as a normal comment? The "How to use this text editor" link isn't very helpful. For example, it doesn't warn you that back-slashes must be escaped or else they're dropped. I suspect that a few additional details are also not disclosed. Tom
    125,585 pointsBadges:
    report
  • RonKoontz
      CREATE TABLE KOONTZ/TESTFILEAA                                
      (                                                             
        W1CPY,                                                      
        W1CUST,                                                     
        W1NAME                                                      
      )                                                             
      AS                                                            
      (                                                             
         SELECT DISTINCT                                            
                ECPY,                                               
                ECUST,                                              
                SHASNM                                              
        FROM CUST JOIN EDISPECIAL ON DEC(SHACPY,2,0) =              
        DEC(ECPY,2,0) AND DEC(SHASNO,7,0) = DEC(ECUST,7,0)          
        ORDER BY                                                    
                 ECPY,                                              
                 ECUST,                                             
                 SHASNM                                             
     )                                                              
     -- THIS IS AN MQT!  LOAD WITH REFRESH TABLE STATEMENT!         
     DATA INITIALLY IMMEDIATE                                       
     REFRESH DEFERRED                                               
     MAINTAINED BY USER;                                            
                                                                    
     CREATE INDEX KOONTZ/TESTFILEA1                                 
     ON KOONTZ/TESTFILEAA                                           
     -- ALTER TABLE TESTFILEAA                                      
     -- ADD PRIMARY KEY                                             
     (                                                              
       W1CPY,                                                       
       W1CUST,                                                      
       W1NAME                                                       
     );                                                             
    
    1,780 pointsBadges:
    report
  • RonKoontz
    I got it to work with SQL.. But not DDS.. AND I got the code to paset today...
    1,780 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