Don’t Let Users Wreck Their Joins
January 26, 2011 Ted Holt
You’re swamped with work, and here comes Harold in Accounts Receivable yet again for help with some query he threw together that doesn’t work correctly. Harold’s not a bad guy–his wife, his kids, and his dog adore him. He just doesn’t understand computer stuff. Here’s a way you can help him help you.
Harold’s problem is that he doesn’t understand how to join files properly. What you need is a way to store join field information on the system. That is, you need a way to store the fact that file A and file B join over field C and field D so Harold doesn’t have to know that information. And here is that way.
Let’s say you have four physical files (or tables, in database jargon) with related data.
This makes perfect sense to you, but not to Harold. To help him, define the joins for him, like this:
create view slsordv1 as (select oh.ORDERNO, oh.CUSTPO, oh.STATUS as OrderStat, oh.ORDERDATE, oh.SHIPDATE, od.LINENO, od.ITEMNO, it.DESCRIP as ItemDesc, it.CLASS as ItemClass, it.COST as BaseCost, it.PRICE as BasePrice, it.MINORDQTY, it.MAKEBUY, it.WEIGHT, it.DISCOUNT, it.VMI, it.ACTIVE, od.QTYORDERED, od.QTYSHIPPED, od.PRICE, oh.COMPANYNO, oh.CUSTOMERNO, cus.CUSNAM, cus.BILLSTREET, cus.BILLCITY, cus.BILLSTATE, cus.BILLZIP, cus.SHIPSTREET, cus.SHIPCITY, cus.SHIPSTATE, cus.SHIPZIP, cus.TYPE as CusType from salesordh as oh left outer join salesordd as od using (orderno) left outer join customers as cus using (companyno, customerno) left outer join items as it using (itemno) )
Create view builds an unkeyed logical file that Harold can query as he would a physical file. Harold operates under the illusion that all of that data is stored in one big database file, and that means no joins. Harold can select fields (columns), select records (rows), sort, etc., and you can work on something more challenging.
Have some compassion. Don’t make Harold join physical files.