ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

Condition outer join

Collapse
This topic is closed.
X
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Condition outer join

    Have following proc:

    Code:
     create procedure get_labdet                                           
                    (in @jobnumber char(12),                               
                        @costcode  char(8),                                
                        @jdedatefrom int,                                  
                        @jdedateto int)                                    
                    result set 1                                           
                    language sql                                           
                    reads sql data                                         
                    set option datfmt = *iso                               
      begin                                                                
              declare c1 scroll cursor with return for                     
              select date(digits(dec(ytdwk + 1900000,7,0))) as workdate,   
                     ytsub as costcode,                                    
                     b.fapid as equipmentid,                               
                     substr(b.fadl01, 1, 20) as equipmentname,             
                     ytpalf as employeename,                               
                     sum(decimal(ytphrw * .01, 31, 2)) as employeehours,   
                     sum(decimal(ytexr * .01, 31, 2)) as equipmenthours    
              from vgiprdhrp/f0618lg a                                     
                     left outer join vgiprddta/f1201la b                   
                     on integer(substr(a.yteqcg, 2, length(a.yteqcg) -1)) = b.fanumb    
              where  ytmcu = @jobnumber and                                             
                     ytsub = @costcode and                                              
                     (ytobj = '70100 ' or ytobj = '70105 ' or ytobj = '70110 ' or       
                     ytobj = '70130 ' or ytobj = '60100 ' or ytobj = '60115 ' or        
                     ytobj = '60120 ' or ytobj = '60125 ' or ytobj = '60130 ')          
              group by ytdwk, ytsub, yteqcg, ytpalf, b.fapid, b.fadl01;                 
              open c1;                                                                  
      end;

    Reading records from the F0618LG file, field "yteqcg" may or may not be empty (null). So I need to condition the "left outer join" only to happen if "yteqcg" is not null. Currently I get a mapping error. Is that possible? Thank you.
    Last edited by snufse; December 11, 2008, 07:40 AM.

  • #2
    Re: Condition outer join

    The plot thickens

    Code:
              select date(digits(dec(ytdwk + 1900000,7,0))) as workdate,   
                     ytsub as costcode,                                    
                     b.fapid as equipmentid,                               
                     substr(b.fadl01, 1, 20) as equipmentname,             
                     ytpalf as employeename,                               
                     sum(decimal(ytphrw * .01, 31, 2)) as employeehours,   
                     sum(decimal(ytexr * .01, 31, 2)) as equipmenthours    
              from vgiprdhrp/f0618lg a                                     
                     left outer join vgiprddta/f1201la b                   
                     on integer(substr(a.yteqcg, 2, length(a.yteqcg) -1)) = b.fanumb    
              where  ytmcu = @jobnumber and                                             
                     ytsub = @costcode and    
                     [B]yteqcg is not null and                                          
                     (ytobj in ('70100 ', '70105 ', '70110 ', '70130 ', 
                                '60100 ', '60115 ', '60120 ', '60125 ', '60130 ') 
              group by ytdwk, ytsub, ytpalf, b.fapid, b.fadl01[/B];
    1/ I added the null predicate against yteqcg column. The NULL predicate tests for null values.

    2/ I replaced the ORed list by an expression values on a IN list. The IN predicate compares a value with a set of values.

    3/ I suppressed yteqcg column because of no use on the GROUP BY clause.
    Last edited by Mercury; December 11, 2008, 07:53 AM. Reason: typo
    Philippe

    Comment


    • #3
      Re: Condition outer join

      Hi Philippe,

      I ran the above code and get error:

      Code:
      OLE DB provider "MSDASQL" for linked server "AS400SRV_MSDASQL" returned message "[IBM][iSeries Access ODBC Driver][DB2 UDB]SQL0802 - Data conversion or data mapping error.".
      Msg 7215, Level 17, State 1, Line 29
      Could not execute statement on remote server 'AS400SRV_MSDASQL'.
      I assume this has to do with field "yteqcg" being null (for some records). If I exclude the "outer join..." section everything works fine.
      Last edited by snufse; December 11, 2008, 08:05 AM.

      Comment


      • #4
        Re: Condition outer join

        If I did this:

        Code:
        yteqcg is not null and yteqcg <> ' ' and
        it works.

        Now, this excludes any records where yteqcg is null or blank. I need thos records included as well and brings me back to "is it possible to condition the join" ?

        Comment


        • #5
          Re: Condition outer join

          Code:
                    select date(digits(dec(ytdwk + 1900000,7,0))) as workdate,   
                           ytsub as costcode,                                    
                          [B] COALESCE(b.fapid, 0)[/B] as equipmentid,                               
                           [B]COALESCE([/B]substr(b.fadl01, 1, 20)[B], ' ') [/B]as equipmentname,             
                           ytpalf as employeename,                               
                           sum(decimal(ytphrw * .01, 31, 2)) as employeehours,   
                           sum(decimal(ytexr * .01, 31, 2)) as equipmenthours    
                    from vgiprdhrp/f0618lg a                                     
                           [B]left join [/B]vgiprddta/f1201la b                   
                           on integer(substr(a.yteqcg, 2, length(a.yteqcg) -1)) = b.fanumb    
                    where  ytmcu = @jobnumber and                                             
                           ytsub = @costcode and    
                           [B]yteqcg is not null and  [/B]                                        
                           ytobj in ('70100 ', '70105 ', '70110 ', '70130 ', 
                                      '60100 ', '60115 ', '60120 ', '60125 ', '60130 ') 
                    group by ytdwk, ytsub, ytpalf, b.fapid, b.fadl01;
          The COALESCE function returns the value of the first non-null expression.

          Here's an example from the SQL Redbook of what the COALESCE function does.

          When selecting the employee number (EMPNO) and salary (SALARY) from all the rows in the EMPLOYEE table, if the salary is missing (that is null), then return a value of zero.
          SELECT EMPNO, COALESCE(SALARY, 0)
          FROM EMPLOYEE
          Last edited by Mercury; December 11, 2008, 09:29 AM. Reason: typo as usual
          Philippe

          Comment


          • #6
            Re: Condition outer join

            Hi Philippe,

            This is what I run (I've modfied the code a little bit) and use iseries navigator to test:

            Code:
            select date(digits(dec(ytdwk + 1900000,7,0))) as workdate,   
            
                             ytsub as costcode,                                    
            
                             COALESCE(b.faapid, ' ') as equipmentid,                               
            
                             COALESCE(substr(b.fadl01, 1, 20), ' ') as equipmentname,             
            
                             ytpalf as employeename,                               
            
                             decimal(ytphrw * .01, 31, 2) as employeehours,   
            
                             decimal(yteqhr * .01, 31, 2) as equipmenthours    
            
                      from vgiprdhrp.f0618lg a                                     
            
                             left join vgiprddta.f1201la b                   
            
                             on integer(substr(a.yteqcg, 2, length(a.yteqcg) -1)) = b.fanumb    
            
                      where  ytmcu = '     1100281' and                                             
            
                             ytsub = '0130     ' and    
            
                             yteqcg is not null and  yteqcg <> '  '   and                                      
            
                             ytobj in ('70100 ', '70105 ', '70110 ', '70130 ', 
            
                                        '60100 ', '60115 ', '60120 ', '60125 ', '60130 ') 
            
                      group by ytdwk, ytsub, ytpalf, b.faapid, b.fadl01, ytphrw, yteqhr;
            Here is the result:

            2008-08-07 0130 07.20430 XX CAT IT28G LOADER Molina, Fran 5.00 3.00
            2008-05-16 0130 01.20320 04 J.D. 270CLC EXCAV Bailey, Timo 0.50 1.00
            2008-08-07 0130 01.30120 Excavator Cat 330BL Bailey, Timo 2.00 4.00
            2008-05-16 0130 33.23250 06 CHEVROLET C3500 C Bailey, Timo 0.50 1.00
            2008-05-16 0130 07.20460 06 KOMATSU WA250-5L Molina, Fran 1.00 1.00
            2008-05-15 0130 01.20320 04 J.D. 270CLC EXCAV Bailey, Timo 0.50 1.00
            2008-08-07 0130 33.23250 06 CHEVROLET C3500 C Bailey, Timo 2.00 4.00
            2008-05-16 0130 07.20460 06 KOMATSU WA250-5L Molina, Fran -1.00 -1.00
            2008-05-15 0130 33.23250 06 CHEVROLET C3500 C Bailey, Timo 0.50 1.00
            2008-05-15 0130 32.30650 04 CHEVROLET 3500 CR Jones, Patri 1.50 1.50


            In file f0618lg, there are additional 18 records where field "yteqcg" have Null or blank value. These are not being included. These records have employee hours only and no equipment hours.

            Now, if I remove the "yteqcg is not null and yteqcg <> ' ' and" and run code like this:


            Code:
            select date(digits(dec(ytdwk + 1900000,7,0))) as workdate,   
            
                             ytsub as costcode,                                    
            
                             COALESCE(b.faapid, ' ') as equipmentid,                               
            
                             COALESCE(substr(b.fadl01, 1, 20), ' ') as equipmentname,             
            
                             ytpalf as employeename,                               
            
                             decimal(ytphrw * .01, 31, 2) as employeehours,   
            
                             decimal(yteqhr * .01, 31, 2) as equipmenthours    
            
                      from vgiprdhrp.f0618lg a                                     
            
                             left join vgiprddta.f1201la b                   
            
                             on integer(substr(a.yteqcg, 2, length(a.yteqcg) -1)) = b.fanumb    
            
                      where  ytmcu = '     1100281' and                                             
            
                             ytsub = '0130     ' and    
            
                                       
            
                             ytobj in ('70100 ', '70105 ', '70110 ', '70130 ', 
            
                                        '60100 ', '60115 ', '60120 ', '60125 ', '60130 ') 
            
                      group by ytdwk, ytsub, ytpalf, b.faapid, b.fadl01, ytphrw, yteqhr;

            I get mapping error:

            SQL State: 22023
            Vendor Code: -802
            Message: [SQL0802] Data conversion or data mapping error. Cause . . . . . : Error type 6 has occurred. Error types and their meanings are: 1 -- Arithmetic overflow. 2 -- Floating point overflow. 3 -- Floating point underflow. 4 -- Floating point conversion error. 5 -- Not an exact result. 6 -- Numeric data that is not valid. 7 -- Double-byte character set (DBCS) or UTF-8 data that is not valid. 8 -- Division by zero. 9 -- Hash value cannot be computed for the requested query. 10 -- User-defined function returned a mapping error. 11 -- Not valid length found in a varying-length column returned from an array result set. 12 -- Result of a concatenation operation on a varying-length field exceeded the maximum allowed length of the result type. If the error occurred when assigning a value to a host variable of a FETCH, embedded SELECT, SET, or VALUES INTO statement, the host variable name is *N and the relative position of the host variable in the INTO clause is 0. If the host variable name is *N, the error occurred when attempting to resolve a search condition. If more than one data mapping error occurred, this is a description of the first error that occurred. For a description of any other data mapping errors, see the previously listed messages in the job log. Recovery . . . : The error was caused by data that was not valid or that was too large. Look at the previously listed messages in the job log (DSPJOBLOG command) or press F10 (Display messages in job log) on this display to determine what row and columns were involved in the error. Correct the data and then try the request again.
            Last edited by snufse; December 11, 2008, 10:51 AM.

            Comment


            • #7
              Re: Condition outer join

              Hi Phillipe,

              I have now created 2 procedures, one that will return employee hours on eqipment and one that will return employee hours only (no equipment). I merge the results into a temp table. It is not the best solution as I am doing 2 roundtrips. This will work for the time being unless you have some magic ideas....

              Proc1

              Code:
              select date(digits(dec(ytdwk + 1900000,7,0))) as workdate,              
                     ytsub as costcode,                                               
                     b.fapid as equipmentid,                                          
                     substr(b.fadl01, 1, 20) as equipmentname,                        
                     ytpalf as employeename,                                          
                     decimal(ytphrw * .01, 31, 2) as employeehours,                   
                     decimal(yteqhr * .01, 31, 2) as equipmenthours                   
              from vgiprdhrp/f0618lg a                                                
                     left outer join vgiprddta/f1201la b                              
                     on integer(substr(a.yteqcg, 2, length(a.yteqcg) -1)) = b.fanumb  
              where  ytmcu = @jobnumber and                                           
                     ytsub = @costcode and                                            
                     yteqcg is not null and yteqcg <> ' ' and                         
                     ytobj in ('70100 ', '70105 ', '70110 ',                          
                               '70130 ', '60100 ', '60115 ',                          
                               '60120 ', '60125 ', '60130 ')                          
              group by ytdwk, ytsub, ytpalf, ytphrw, yteqhr, b.fapid, b.fadl01;

              Proc 2:

              Code:
              select date(digits(dec(ytdwk + 1900000,7,0))) as workdate,      
                      ytsub as costcode,                                       
                      ytpalf as employeename,                                  
                      decimal(ytphrw * .01, 31, 2) as employeehours            
               from vgiprdhrp/f0618lg                                          
               where  ytmcu = @jobnumber and                                   
                      ytsub = @costcode and                                    
                      yteqcg = ' ' and                                         
                      ytobj in ('70100 ', '70105 ', '70110 ',                  
                                '70130 ', '60100 ', '60115 ',                  
                                '60120 ', '60125 ', '60130 ')                  
               group by ytdwk, ytsub, ytpalf, ytphrw;

              Comment


              • #8
                Re: Condition outer join

                If you want to this in one query you will need to simulate a full outer join by combining your left outer and an exception join with a union.

                I think.....

                http://publib.boulder.ibm.com/iserie...HDRSIMULATEFOJ

                PHP Code:
                select date(digits(dec(ytdwk 1900000,7,0))) as workdate,   
                                 
                ytsub as costcode,                                    
                                 
                COALESCE(b.faapid' ') as equipmentid,                               
                                 
                COALESCE(substr(b.fadl01120), ' ') as equipmentname,             
                                 
                ytpalf as employeename,                               
                                 
                decimal(ytphrw .01312) as employeehours,   
                                 
                decimal(yteqhr .01312) as equipmenthours    
                          from vgiprdhrp
                .f0618lg a                                     
                                 left join vgiprddta
                .f1201la b                   
                                 on integer
                (substr(a.yteqcg2length(a.yteqcg) -1)) = b.fanumb    
                          where  ytmcu 
                '     1100281' and                                             
                                 
                ytsub '0130     ' and    
                                 
                yteqcg is not null and  yteqcg <> '  '   and                                      
                                 
                ytobj in ('70100 ''70105 ''70110 ''70130 '
                                            
                '60100 ''60115 ''60120 ''60125 ''60130 '
                          
                group by ytdwkytsubytpalfb.faapidb.fadl01          
                Union 
                (
                select date(digits(dec(ytdwk 1900000,7,0))) as workdate,   
                                 
                ytsub as costcode,                                    
                                 
                COALESCE(b.faapid' ') as equipmentid,                               
                                 
                COALESCE(substr(b.fadl01120), ' ') as equipmentname,             
                                 
                ytpalf as employeename,                               
                                 
                decimal(ytphrw .01312) as employeehours,   
                                 
                decimal(yteqhr .01312) as equipmenthours    
                          from vgiprddta
                .f1201la b  
                      
                                 Exception join vgiprdhrp
                .f0618lg a  
                                 on integer
                (substr(a.yteqcg2length(a.yteqcg) -1)) = b.fanumb    
                          where  ytmcu 
                '     1100281' and                                             
                                 
                ytsub '0130     ' and    
                                 
                yteqcg is not null and  yteqcg <> '  '   and                                      
                                 
                ytobj in ('70100 ''70105 ''70110 ''70130 '
                                            
                '60100 ''60115 ''60120 ''60125 ''60130 '
                          
                group by ytdwkytsubytpalfb.faapidb.fadl01   
                 


                You should be able to copy and paste this snipped of code into i-nav to see what I am referring to:

                PHP Code:

                Select 
                from (
                (
                Select 1 as num from Sysibm.sysdummy1
                 Union Select 2 
                as num from Sysibm.sysdummy1
                 Union Select 3 
                as num from Sysibm.sysdummy1 x
                 Left Outer join 
                Select 1 as num from Sysibm.sysdummy1
                                   Union Select 3 
                as num from Sysibm.sysdummy1
                                   Union Select 5 
                as num from Sysibm.sysdummy1 y
                 on x
                .num y.num
                )
                 
                Union Select  From (
                Select 1 as num from Sysibm.sysdummy1
                                   Union Select 3 
                as num from Sysibm.sysdummy1
                                   Union Select 5 
                as num from Sysibm.sysdummy1 )y
                 Exception Join 
                (Select 1 as num from Sysibm.sysdummy1
                 Union Select 2 
                as num from Sysibm.sysdummy1
                 Union Select 3 
                as num from Sysibm.sysdummy1 x

                 on y
                .num x.num
                )

                Last edited by kpmac; December 11, 2008, 02:48 PM.
                Predictions are usually difficult, especially about the future. ~Yogi Berra

                Vertical Software Systems
                VSS.biz

                Comment


                • #9
                  Re: Condition outer join

                  @#%@#%@#%@#% nice work for a BA
                  All my answers were extracted from the "Big Dummy's Guide to the As400"
                  and I take no responsibility for any of them.

                  www.code400.com

                  Comment


                  • #10
                    Re: Condition outer join

                    That's Business Systems Analyst (BSA) to you buddy!

                    What can I say. When you gots it, you gots it.
                    Predictions are usually difficult, especially about the future. ~Yogi Berra

                    Vertical Software Systems
                    VSS.biz

                    Comment


                    • #11
                      Re: Condition outer join

                      Again I'd use COALESCE on the SELECT for all the fields beginning by "yt" and wouldn't remove the "yteqcg is not null and yteqcg <> ' '.

                      PHP Code:
                                select date(digits(dec(ytdwk 1900000,7,0))) as workdate,   
                                       
                      ytsub as costcode,                                    
                                       
                      COALESCE(b.fapid0) as equipmentid,                               
                                       
                      COALESCE(substr(b.fadl01120), '?') as equipmentname,             
                                       
                      COALESCE(ytpalf'**Unknown**' as employeename,                               
                                       
                      sum(decimal(COALESCE(ytphrw0) * .01312)) as employeehours,   
                                       
                      sum(decimal(COALESCE(ytexr0) * .01312)) as equipmenthours    
                                from vgiprdhrp
                      /f0618lg a                                     
                                       left join vgiprddta
                      /f1201la b                   
                                       on integer
                      (substr(a.yteqcg2length(a.yteqcg) -1) = b.fanumb    
                                where  ytmcu 
                      = @jobnumber and                                             
                                       
                      ytsub = @costcode and    
                                       
                      yteqcg is not null and                                          
                                       
                      ytobj in ('70100 ''70105 ''70110 ''70130 '
                                                  
                      '60100 ''60115 ''60120 ''60125 ''60130 '
                                
                      group by ytdwkytsubytpalfb.fapidb.fadl01
                      it's at least worth giving a go.
                      Philippe

                      Comment


                      • #12
                        Re: Condition outer join

                        You R the Man
                        All my answers were extracted from the "Big Dummy's Guide to the As400"
                        and I take no responsibility for any of them.

                        www.code400.com

                        Comment


                        • #13
                          Re: Condition outer join

                          Tried to run the code posted by kpmac:

                          Code:
                          select date(digits(dec(ytdwk + 1900000,7,0))) as workdate,    
                          
                                           ytsub as costcode,                                     
                          
                                           COALESCE(b.faapid, ' ') as equipmentid,                                
                          
                                           COALESCE(substr(b.fadl01, 1, 20), ' ') as equipmentname,              
                          
                                           ytpalf as employeename,                                
                          
                                           decimal(ytphrw * .01, 31, 2) as employeehours,    
                          
                                           decimal(yteqhr * .01, 31, 2) as equipmenthours     
                          
                                    from vgiprdhrp.f0618lg a                                      
                          
                                           left join vgiprddta.f1201la b                    
                          
                                           on integer(substr(a.yteqcg, 2, length(a.yteqcg) -1)) = b.fanumb     
                          
                                    where  ytmcu = '     1100281' and                                              
                          
                                           ytsub = '0130     ' and     
                          
                                           yteqcg is not null and  yteqcg <> '  '   and                                       
                          
                                           ytobj in ('70100 ', '70105 ', '70110 ', '70130 ', 
                          
                                                      '60100 ', '60115 ', '60120 ', '60125 ', '60130 ') 
                          
                                    group by ytdwk, ytsub, ytpalf, ytphrw, yteqhr, b.faapid, b.fadl01;           
                          
                          Union ( 
                          
                          select date(digits(dec(ytdwk + 1900000,7,0))) as workdate,    
                          
                                           ytsub as costcode,                                     
                          
                                           COALESCE(b.faapid, ' ') as equipmentid,                                
                          
                                           COALESCE(substr(b.fadl01, 1, 20), ' ') as equipmentname,              
                          
                                           ytpalf as employeename,                                
                          
                                           decimal(ytphrw * .01, 31, 2) as employeehours,    
                          
                                           decimal(yteqhr * .01, 31, 2) as equipmenthours     
                          
                                    from vgiprddta.f1201la b   
                          
                                 
                          
                                           Exception join vgiprdhrp.f0618lg a   
                          
                                           on integer(substr(a.yteqcg, 2, length(a.yteqcg) -1)) = b.fanumb     
                          
                                    where  ytmcu = '     1100281' and                                              
                          
                                           ytsub = '0130     ' and     
                          
                                           yteqcg is not null and  yteqcg <> '  '   and                                       
                          
                                           ytobj in ('70100 ', '70105 ', '70110 ', '70130 ', 
                          
                                                      '60100 ', '60115 ', '60120 ', '60125 ', '60130 ') 
                          
                                    group by ytdwk, ytsub, ytpalf, ytphrw, yteqhr, b.faapid, b.fadl01;
                          Get following error:

                          SQL State: 42601
                          Vendor Code: -199
                          Message: [SQL0199] Keyword UNION not expected. Valid tokens: ( END GET SET CALL DROP FREE HOLD LOCK OPEN WITH ALTER BEGIN. Cause . . . . . : The keyword UNION was not expected here. A syntax error was detected at keyword UNION. The partial list of valid tokens is ( END GET SET CALL DROP FREE HOLD LOCK OPEN WITH ALTER BEGIN. This list assumes that the statement is correct up to the unexpected keyword. The error may be earlier in the statement but the syntax of the statement seems to be valid up to this point. Recovery . . . : Examine the SQL statement in the area of the specified keyword. A colon or SQL delimiter may be missing. SQL requires reserved words to be delimited when they are used as a name. Correct the SQL statement and try the request again.

                          Comment


                          • #14
                            Re: Condition outer join

                            Code posted by mercury, get syntax error:

                            Code:
                            select date(digits(dec(ytdwk + 1900000,7,0))) as workdate,   
                            
                                             ytsub as costcode,                                    
                            
                                             COALESCE(b.faapid, 0) as equipmentid,                               
                            
                                             COALESCE(substr(b.fadl01, 1, 20), '?') as equipmentname,             
                            
                                             COALESCE(ytpalf, '**Unknown**') as employeename,                               
                            
                                             sum(decimal(COALESCE(ytphrw, 0) * .01, 31, 2)) as employeehours,   
                            
                                             sum(decimal(COALESCE(yteqhr, 0) * .01, 31, 2)) as equipmenthours    
                            
                                      from vgiprdhrp.f0618lg a                                     
                            
                                             left outer join vgiprddta.f1201la b                   
                            
                                             on integer(substr(a.yteqcg, 2, length(a.yteqcg) -1) = b.fanumb    
                            
                                      where  ytmcu = '     1100281' and                                             
                            
                                                ytsub = '0130     '  and    
                            
                                                yteqcg is not null and  and yteqcg <> ' '  and                                       
                            
                                                ytobj in ('70100 ', '70105 ', '70110 ', '70130 ', 
                            
                                                            '60100 ', '60115 ', '60120 ', '60125 ', '60130 ') 
                            
                                      group by ytdwk, ytsub, ytpalf, b.faapid, b.fadl01;

                            SQL State: 42601
                            Vendor Code: -104
                            Message: [SQL0104] Token = was not valid. Valid tokens: ) ,. Cause . . . . . : A syntax error was detected at token =. Token = is not a valid token. A partial list of valid tokens is ) ,. This list assumes that the statement is correct up to the token. The error may be earlier in the statement, but the syntax of the statement appears to be valid up to this point. Recovery . . . : Do one or more of the following and try the request again: -- Verify the SQL statement in the area of the token =. Correct the statement. The error could be a missing comma or quotation mark, it could be a misspelled word, or it could be related to the order of clauses. -- If the error token is , correct the SQL statement because it does not end with a valid clause.

                            Comment


                            • #15
                              Re: Condition outer join

                              Forgot to remove the semi colon before the union.

                              PHP Code:
                              select date(digits(dec(ytdwk 1900000,7,0))) as workdate,    

                                               
                              ytsub as costcode,                                     

                                               
                              COALESCE(b.faapid' ') as equipmentid,                                

                                               
                              COALESCE(substr(b.fadl01120), ' ') as equipmentname,              

                                               
                              ytpalf as employeename,                                

                                               
                              decimal(ytphrw .01312) as employeehours,    

                                               
                              decimal(yteqhr .01312) as equipmenthours     

                                        from vgiprdhrp
                              .f0618lg a                                      

                                               left join vgiprddta
                              .f1201la b                    

                                               on integer
                              (substr(a.yteqcg2length(a.yteqcg) -1)) = b.fanumb     

                                        where  ytmcu 
                              '     1100281' and                                              

                                               
                              ytsub '0130     ' and     

                                               
                              yteqcg is not null and  yteqcg <> '  '   and                                       

                                               
                              ytobj in ('70100 ''70105 ''70110 ''70130 '

                                                          
                              '60100 ''60115 ''60120 ''60125 ''60130 '

                                        
                              group by ytdwkytsubytpalfytphrwyteqhrb.faapidb.fadl01           

                              Union 


                              select date(digits(dec(ytdwk 1900000,7,0))) as workdate,    

                                               
                              ytsub as costcode,                                     

                                               
                              COALESCE(b.faapid' ') as equipmentid,                                

                                               
                              COALESCE(substr(b.fadl01120), ' ') as equipmentname,              

                                               
                              ytpalf as employeename,                                

                                               
                              decimal(ytphrw .01312) as employeehours,    

                                               
                              decimal(yteqhr .01312) as equipmenthours     

                                        from vgiprddta
                              .f1201la b   

                                     

                                               Exception join vgiprdhrp
                              .f0618lg a   

                                               on integer
                              (substr(a.yteqcg2length(a.yteqcg) -1)) = b.fanumb     

                                        where  ytmcu 
                              '     1100281' and                                              

                                               
                              ytsub '0130     ' and     

                                               
                              yteqcg is not null and  yteqcg <> '  '   and                                       

                                               
                              ytobj in ('70100 ''70105 ''70110 ''70130 '

                                                          
                              '60100 ''60115 ''60120 ''60125 ''60130 '

                                        
                              group by ytdwkytsubytpalfytphrwyteqhrb.faapidb.fadl01
                              Predictions are usually difficult, especially about the future. ~Yogi Berra

                              Vertical Software Systems
                              VSS.biz

                              Comment

                              Working...
                              X