Multiple Table Join

[Michael Brennan]

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

 

[Robert Klemme]

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.

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

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