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 = []

On Wed, Nov 3, 2010 at 4:11 PM, Michael Brennan
[email protected] wrote:

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.

Including error messages and / or stack traces usually helps us a
great deal to help you.

AND (FINCOMMISSION.SalespersonType=‘1’ OR

FINCOMMISSION.DealNumber) AND (FINMAIN2 ON FINMAIN1.DealNumber =
#{sec2min(Time.now - sales_begin)}"
flagged_deals = []

Can you really join two tables with a single JOIN with your RDBMS? I
am not aware of any which support this syntax of yours:

LEFT OUTER JOIN ((FINCOMMISSION ON FINMAIN1.DealNumber =
FINCOMMISSION.DealNumber) AND (FINMAIN2 ON FINMAIN1.DealNumber =
FINMAIN2.DealNumber))

I would have expected something like

LEFT OUTER JOIN FINCOMMISSION ON FINMAIN1.DealNumber =
FINCOMMISSION.DealNumber,
LEFT OUTER JOIN FINMAIN2 ON FINMAIN1.DealNumber =
FINMAIN2.DealNumber

Btw, you should probably rewrite this code to use bind variables.
This is much less error prone and less prone to SQL injection attacks.

Kind regards

robert

This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.

| Privacy Policy | Terms of Service | Remote Ruby Jobs