Condition outer join

[snufse]

Re: Condition outer join

Hi kpmac, still getting a syntax, I can't figure out where...

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

SQL State: 42601
Vendor Code: -104
Message: [SQL0104] Token was not valid. Valid tokens: ) UNION EXCEPT. Cause . . . . . : A syntax error was detected at token . Token is not a valid token. A partial list of valid tokens is ) UNION EXCEPT. 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.

[kpmac]

Re: Condition outer join

) needed at end of stament and once again the semi colon is in the wrong place. You need to work with me here as I do not have your file system to validate against.

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
) ; 

[Mercury]

Re: Condition outer join

Just one closing bracket missing.

on integer(substr(a.yteqcg, 2, length(a.yteqcg) -1)[B][COLOR="red"])[/COLOR][/B] = b.fanumb

I accompany kpmac here to also say that you need to work with us and make a little effort. Thx.

[snufse]

Re: Condition outer join

Hi Philippe,

Good news! With a couple of minor changes I got your code working. Good job....

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,             



                 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 = '0080     '  and    



                    yteqcg is not null  and                                       



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



                                '60100 ', '60115 ', '60120 ', '60125 ', '60130 ') 



          group by ytdwk, ytsub, ytpalf, ytphrw, yteqhr, b.faapid, b.fadl01;

Result:

2008-08-08 0080 ? ? Hernandez, V 16.00 0.00
2008-06-13 0080 ? ? Hernandez, M 5.50 0.00
2008-06-02 0080 01.20320 04 J.D. 270CLC EXCAV Bailey, Timo 2.50 3.00
2008-06-13 0080 03.40110 06 J.D. 650JLT CRAWL Jones, Patri 0.50 4.00

I very much appreciate all the help I've been given from all the members but especially from you Phillipe....