From: Michael Brennan <michael.brennan@...>
Date: 2010-11-04T00:11:07+09:00
Subject: Multiple Table Join

Hi, I am not sure if this is the rigth place to post this or not. The
file is .rb so I will give it a shot. I am trying to join a 3rd table to
my code that currently joins 2 tables. The original code was written by
a freelancer that my company used to use. We just need info from one
field in a 3rd table so I thought I would give it a go myself and got
stumped by this section of code. I am trying to add
FINMAIN2.VehicleSaleType where FINMAIN1.DealNumber =
FINMAIN2.DealNumber. The first section of code is what he is using. The
second section is my modified version that is not working correctly. I
am just not sure what I am doing wrong.


sales_begin = Time.now
select = "SELECT
FINMAIN1.DealNumber,FINCOMMISSION.SalespersonNumber,FINCOMMISSION.SalespersonName,FINCOMMISSION.SalespersonType
"
from = "FROM FINMAIN1 "
join = "LEFT OUTER JOIN FINCOMMISSION ON FINMAIN1.DealNumber =
FINCOMMISSION.DealNumber "
where = "WHERE ((FINMAIN1.DateSold >= '#{first}' AND FINMAIN1.DateSold <
'#{today}') AND (FINMAIN1.BuyerType='R' OR FINMAIN1.BuyerType IS NULL)
AND (FINMAIN1.DealNumberType='') AND (FINCOMMISSION.DealNumberType='')
AND (FINCOMMISSION.SalespersonType='1' OR
FINCOMMISSION.SalespersonType='2'))"
order = " ORDER BY FINCOMMISSION.SalespersonType DESC"
sales = db.select_all(select + from + join + where + order)
db.disconnect # close mainframe connection
logger.puts "\t(#{sales.length}) sales records found in
#{sec2min(Time.now - sales_begin)}"
flagged_deals = []








sales_begin = Time.now
select = "SELECT
FINMAIN1.DealNumber,FINCOMMISSION.SalespersonNumber,FINCOMMISSION.SalespersonName,FINCOMMISSION.SalespersonType,FINMAIN2.VehicleSaleType
"
from = "FROM FINMAIN1 "
join = "LEFT OUTER JOIN ((FINCOMMISSION ON FINMAIN1.DealNumber =
FINCOMMISSION.DealNumber) AND (FINMAIN2 ON FINMAIN1.DealNumber =
FINMAIN2.DealNumber)) "
where = "WHERE ((FINMAIN1.DateSold >= '#{first}' AND FINMAIN1.DateSold <
'#{today}') AND (FINMAIN1.BuyerType='R' OR FINMAIN1.BuyerType IS NULL)
AND (FINMAIN1.DealNumberType='') AND (FINMAIN2.DealNumberType='') AND
(FINCOMMISSION.DealNumberType='') AND (FINCOMMISSION.SalespersonType='1'
OR FINCOMMISSION.SalespersonType='2'))"
order = " ORDER BY FINCOMMISSION.SalespersonType DESC"
sales = db.select_all(select + from + join + where + order)
db.disconnect # close mainframe connection
logger.puts "\t(#{sales.length}) sales records found in
#{sec2min(Time.now - sales_begin)}"
flagged_deals = []

-- 
Posted via http://www.ruby-forum.com/.