Need help dis ciphering code

[skwon9642]

Hello all, this is part of a sqlrpgle code that i am working with right now. I need help understanding what it is actually doing.

The Code:

Insert into QTEMP/EDIPF                                                                      
      (SELECT EMDID, EMBOX From EDI4XXDTA/EDMAST              
       Join EDI4XXDTA/EDMTAB on EMFAST = MBMSTS Where          
       EMDID IN (Select CAST(CCDESC as char) from ZCC          
       Where CCSDSC = CAST(:WRKRANK as GRAPHIC))              
       And EMFAST not in (Select CAST(CCDESC as char) from ZCC)
       GROUP BY EMDID, EMBOX                                  
       ORDER BY EMDID, EMBOX);

couple things to note: ZCC is a "table" with fields that start with "CC"
:WRKRANK is a packed 2:0

Here is what I understand so far.
Insert into QTEMP/EDIPF // put the following into the EDIPF file inside the QTEMP library
(SELECT EMDID, EMBOX From EDI4XXDTA/EDMAST // get fields EMDID and EMBOX from EDMAST inside the EDI4XXDTA library
Join EDI4XXDTA/EDMTAB on EMFAST = MBMSTS // join EMFAST from EDMAST and MBMSTS from EDMTAB
Where
EMDID IN (Select CAST(CCDESC as char) from ZCC // from here I don't understand...
Where CCSDSC = CAST(:WRKRANK as GRAPHIC))
And EMFAST not in (Select CAST(CCDESC as char) from ZCC)

EDIT: I know how the cast works, just need help with the sql bits!

[Scott Klement]

The easiest way to understand is to start at the innermost statement.

Select CAST(CCDESC as char) from ZCC

This is going to give you a list of the CCDESC values in the ZCC file. I'm not familiar with that file and can't tell you what these would look like. But it's used like this:

And EMFAST not in (the above statement)

So rows from the joined EDMAST/EDMTAB will only be returned if the value of EMFAST is not in the list of CCDESC values in the ZCC file.

Next you have this:

         Select CAST(CCDESC as char) from ZCC          
         Where CCSDSC = CAST(:WRKRANK as GRAPHIC)

Basically this is a list of CCDESC values whre CCSDSC is the same as the WRKRANK variable in the program. It is used like this:

      Where EMDID IN ( above statement )

So here only rows are included where the EMDID field matches one of the CCDESC values that are in rows where CCSDSC = WRKRANK.

So put the whole thing together (reformatted, hopefully for clarity):

Insert into QTEMP/EDIPF                                                                      
   ( SELECT EMDID, EMBOX
       From EDI4XXDTA/EDMAST              
       Join EDI4XXDTA/EDMTAB on EMFAST = MBMSTS
      Where EMDID IN (
         Select CAST(CCDESC as char) from ZCC          
         Where CCSDSC = CAST(:WRKRANK as GRAPHIC)
      )              
      And EMFAST not in (
         Select CAST(CCDESC as char) from ZCC
      )
      GROUP BY EMDID, EMBOX                                  
      ORDER BY EMDID, EMBOX
    );

So any record where EMDID is in the list of alll CCDESC in ZCC where CCSDSC=WRKRANK and EMFAST is not in the list of all CCDESC in ZCC will be written to the EDIPF file in QTEMP. The group by / order by just eliminates duplicates, so if there are two rows wiht the same EMDID/EMBOX only one will be written to QTEMP/EDIPF.

[Jim_IT]

Scott provided a proper description for the way the statement is currently written, but the entire statement could be defined much simpler.

Insert into QTEMP/EDIPF
Select Distinct EMDID, EMBOX
From EDI4XXDTA/EDMAST
Join EDI4XXDTA/EDMTAB
On EMFAST = MBMSTS
Join ZCC
On Cast(EMDID As GRAPHIC) = CCDESC
Exception Join ZCC
On Cast(EMFAST As GRAPHIC) = CCDESC
Where EMDID = Cast(:WRKRANK As Char(2))

It would help if I knew how the fields EMDID & EMFAST were defined, but this should be close and much faster.

Jim

[skwon9642]

Thank you Scott and Jim very much for a clear explanation!

[skwon9642]

so the code above is a code they gave me as a guideline, but it is quite different to what they want me to do, so I decided to scrap it and start new. But using the knowledge from above, I made this. Naturally, my task is different now than what the code above is saying.

my task: I have a variable WrkRank, which can be 1, 2 or 3 depending on the time of day.

I have a table called ZCC (there are more fields and values but this is a table that our system uses collectively, so I included only the parts that I will use for this program)

                           [B]CCTABL               CCDESC           CCSDSC[/B]
                           DOCTYPCT               856                    1
                           DOCTYPCT               830                    1
                           DOCTYPCT               850                    1
                           DOCTYPCT               824                    1
                           DOCTYPCT               810                    3
                           DOCTYPCT               861                    2

Table called EDMAST (there are more fields and values but this is a table that our system uses collectively, so I included only the parts that I will use for this program)

                           [B]EMDID               [/B]
                           856      
                           830        
                           830        
                           810        
                           874        
                           863

My psudo code:
- match WrkRank with CCSDSC from ZCC
- get corresponding CCDESC from ZCC
- see if there is a matching EMDID from EDMAST
- get those values from EDMAST and put in EMDID in a new table called EDIPF

My Code:

SELECT EMDID INTO QTEMP/EDIPF                
  FROM EDI4XXDTA/EDMAST,                      
     (SELECT CCDESC FROM ZCC WHERE CCSDSC = WrkRank)
  WHERE EDI4XXDTA/EDMAST.EMDID = CCDESC

is this the correct translation?

edit: I think I found another way to do this:

Exec SQL                                           
    Select EDI4XXDTA/EDMAST.EMDID Into QTEMP/EDIPF 
      From EDI4XXDTA/EDMAST, ZCC                   
     Where EDI4XXDTA/EDMAST.EMDID = ZCC.CCDESC And 
           ZCC.CCSDSC = WrkRank;

[skwon9642]

For future reference, here is the working code. Thanks all for the help!!

 Exec SQL                                        
    insert into qtemp/edipf (EMDID, EMBOX)
    SELECT EMDID, EMBOX                          
    FROM EDI4XXDTA/EDMAST INNER JOIN ZCC ON      
    EMDID = CAST(ZCC.CCDESC AS CHAR(3))          
    WHERE CAST(ZCC.CCSDSC AS Char(2)) = :WrkRank and    
          ZCC.CCTABL = 'DOCTYPCT';

[Vectorspace]

Glad to hear that works, but be aware there is a possible pitfall with this method
You are using a join to test for a corresponding record:
select a,b from EDMAST inner join ZCC on c = d

This code is not actually (select each row from EDMAST once if a corresponding row exists in ZCC)
This is (select each row from EDMAST once for each matching row in ZCC)

Which means - if EDMAST row 1 matches three different rows in ZCC, then EDMAST row 1 will be returned three times. And your statement would insert duplicate rows into EDIPF.

If the rules of the join (ON a = b) mean there can only be zero or 1 matching rows, then you are fine. But if that is not guaranteed, you could have duplicate records.

The simplest alternative that guarantees you don't have duplicates, is to use the EXISTS clause instead of a join. Your example would be:

  Exec SQL
  insert into qtemp/edipf (EMDID, EMBOX)
  SELECT EMDID, EMBOX
  FROM EDI4XXDTA/EDMAST F1

  WHERE exists(select 1 from ZCC F2 where F1.EMDID = CAST(F2.CCDESC AS CHAR(3)))

  and CAST(ZCC.CCSDSC AS Char(2)) = :WrkRank and
  ZCC.CCTABL = 'DOCTYPCT';

the exists() clause contains its own select statement, it returns true if that statement returns at least 1 record. Then no matter how many rows ZCC returns, you only get each EDMAST row once
Note that I added table aliases F1 and F2 to represent EDI4XXDTA/EDMAST and ZCC respectively, so I could use them to explicitly specify which fields in the exists subselect belong to which file. It's neater than using the whole filename as you did with ZCC.CCDESC, and it ensures the file linking always works as you expect.

I am not certain how efficient where exists is compared to a join - there are alternate methods that allow a join to work by using row numbering and the partition by clause.

[skwon9642]

Glad to hear that works, but be aware there is a possible pitfall with this method
You are using a join to test for a corresponding record:
select a,b from EDMAST inner join ZCC on c = d

This code is not actually (select each row from EDMAST once if a corresponding row exists in ZCC)
This is (select each row from EDMAST once for each matching row in ZCC)

Which means - if EDMAST row 1 matches three different rows in ZCC, then EDMAST row 1 will be returned three times. And your statement would insert duplicate rows into EDIPF.

If the rules of the join (ON a = b) mean there can only be zero or 1 matching rows, then you are fine. But if that is not guaranteed, you could have duplicate records.

The simplest alternative that guarantees you don't have duplicates, is to use the EXISTS clause instead of a join. Your example would be:

Exec SQL
insert into qtemp/edipf (EMDID, EMBOX)
SELECT EMDID, EMBOX
FROM EDI4XXDTA/EDMAST F1

WHERE exists(select 1 from ZCC F2 where F1.EMDID = CAST(F2.CCDESC AS CHAR(3)))

and CAST(ZCC.CCSDSC AS Char(2)) = :WrkRank and
ZCC.CCTABL = 'DOCTYPCT';

the exists() clause contains its own select statement, it returns true if that statement returns at least 1 record. Then no matter how many rows ZCC returns, you only get each EDMAST row once
Note that I added table aliases F1 and F2 to represent EDI4XXDTA/EDMAST and ZCC respectively, so I could use them to explicitly specify which fields in the exists subselect belong to which file. It's neater than using the whole filename as you did with ZCC.CCDESC, and it ensures the file linking always works as you expect.

I am not certain how efficient where exists is compared to a join - there are alternate methods that allow a join to work by using row numbering and the partition by clause.

Thank you for the concern! the rows in ZCC that I am comparing to with EDMAST are unique so I don't think this would be a problem. I will keep this in mind for whenever/if ever there is a time where we have to make non-unique zcc rows. Thank you again!

[skwon9642]

Another question, but didn't want to make a new thread, so I'll post it here. Its definately a smaller question.

so after I do the query above, I get a table (or in this case a Physical file in the QTEMP library). I want to export this file out as an excel file. but I cannot do that from QTEMP library.
So I decided to create another EDIPF in the ISE library.

I have done the following query:

Exec SQL                   
    Create Table ISE/EDIPF 
    (EMDID Char(10),       
      EMBOX Char(10),                         
      MBCODE Char(4),                         
      EMDAT Char(8));                         

 Exec SQL                                     
     Select * into ISE/EDIPF from QTEMP/EDIPF;

What I mean to do is to create another EDIPF in the ISE library, then duplicate all the data that is inside the QTEMP/EDIPF. But after doing so, my ISE/EDIPF is empty...it shows the 4 fields correctly however...any ideas?

[Jim_IT]

The correct syntax is:

Insert Into ISE/EDIPF Select * From QTEMP/EDIPF

[kitvb1]

You could also try

Exec SQL
   CREATE TABLE ISE/EDIPF
   AS (SELECT * FROM QTEMP/EDIPF) WITH DATA;

instead of using two statements.

[skwon9642]

Thank you all. that worked. I initially got the CPA5305 (record not added. Member EDIPF is full) but taking option 9999 has increased the memory temporarily. And now I have the duplicate file. Awesome.

now my code has evolved to account for 2 more fields, so 4 fields total.

Exec SQL                                        
   INSERT INTO QTEMP/EDIPF                      
   SELECT EMDID, EMBOX, EDMTAB.MBCODE, EMDAT    
   FROM EDI4XXDTA/EDMAST as EDMAST              
   INNER JOIN ZCC AS ZCC                        
   ON EDMAST.EMDID = CAST(ZCC.CCDESC AS CHAR(3))
   INNER JOIN EDI4XXDTA/EDMTAB AS EDMTAB        
   ON EDMAST.EMFAST = EDMTAB.MBMSTS             
   WHERE CAST(ZCC.CCSDSC AS Char(2)) = 01 AND   
         ZCC.CCTABL = 'DOCTYPCT'                
   ORDER BY EMDID, MBCODE;

I want to get a count of how many of each record in the MBCODE field, so for example,
upon querying "select distinct MBCODE from EDMTAB", I get the following.

T/F 
T/E 
REJ 
MOV 
RCV 
SNT 
T/G 
RDY 
HLD 
UTP 
TRK 
*ALL
PRG 
F/C 
F/F 
F/R 
C/E 
TCP 
PND
COL
RFS
FLD
MBX
M/F
M/I

I want to get a count of how many T/F, T/E REJ and so on.

I am doing select "MBCODE, count(*) from qtemp/edipf group by MBCODE order by MBCODE"
but the counts are different from the actual counts done in excel with the same table. it looks like if it's counting from multiple EMDID columns, its missing some, OR the original query is counting duplicates like Vectorspace has mentioned. Could that be the issue??

[B.Hauser]

Please post you SQL statement, not only the result

Birgitta

[skwon9642]

Please post you SQL statement, not only the result

Birgitta

I posted all 3 sql statements in question. I am not sure which sql statement you mean?

[Vectorspace]

If this is your SQL:
"MBCODE, count(*) from qtemp/edipf group by MBCODE order by MBCODE"
Then it is fine. For each distinct MBCODE in file EDIPF, you will get the number of instances of that MBCODE in EDIPF

Your Excel is doing something different though - For each code, you have four different columns that sum to the total? Is that not looking at EDIPF?