Anita’s SQL Tips
October 9, 2013 Ted Holt
|
I derive great satisfaction when something I say benefits someone else. Call me selfish, but I derive as much or more satisfaction when something someone else says benefits me. A case in point occurred when I spoke about SQL recently at the COMMON 2013 Fall Conference and Expo in St. Louis. Anita Corcoran, of StoneMor Partners, in Levittown, Pennsylvania, greatly honored me by coming to hear what I had to say. She shared an SQL tip that I had seen before and forgotten. Today I pass along to you that tip and a few other tips she emailed me. 1. I was explaining the use of row value expressions, and used a WHERE clause as an example. Anita spoke up and pointed out that row value expressions are even more useful in the JOIN clause. Instead of this: SELECT *
FROM cacmst a
left outer join cinvp b
on a.cmlocn = b.silocn and
a.cmcont = b.silocn
left outer join trudtp c
on b.silocn = c.tulocn and
b.sicont = c.tucont and
b.siitem = c.tuitem and
b.siseqn = c.tutseq and
b.silino = c.tulino
ORDER BY a.cmlocn, a.cmcont
You can do this: SELECT *
FROM cacmst a
left outer join cinvp b
on (a.cmlocn,a.cmcont) = (b.silocn,b.sicont)
left outer join trudtp c
on (b.silocn,b.sicont,b.siitem,b.siseqn,b.silino) =
(c.tulocn,c.tucont,c.tuitem,c.tuseqn,c.tulino)
ORDER BY a.cmlocn, a.cmcont
Yes! And to think I’ve been coding all those AND’ed joins. Here are more tips from Anita. I hope you find something helpful. 2. It is common to use the IN predicate to check a field for a list of literals. You can also reverse the order and check for the presence of a literal in multiple fields. For example, select customer records where SMITH is in the customer’s last name or the customer’s spouse’s last name. SELECT whatever FROM CustTable WHERE 'SMITH' in (LastName, SpouseLastName) Here’s another example. Select every item with the general ledger number 502.00 in any of the general ledger number fields: SELECT * FROM item WHERE 502.00 IN (glno, glpn, glan, glpu, glau) 3. V7.1 gave us full outer joins (finally). Full outer joins are a great way to check integrity between files. For example, suppose you’re looking for contracts with headers and no detail or vice versa. Full outer joins work wonderfully here since we can’t rely on either file to be consistently present. With D as
(SELECT location, contract, sum(price) as price_sum
FROM contractD
GROUP BY location, contract),
P as
(SELECT location, contract, sum(pay_amount) as pay_amount_sum
FROM contractP
GROUP BY location, contract)
SELECT *
FROM contractH H
full outer join D
on (H.location,H.contract)= (D.location,D.contract)
full outer join P
on (H.location,H.contract)= (P.location,P.contract)
WHERE H.contract is null or
D.contract is null or
P.contract is null or
H.contract_tot$ <> D.price_sum or
H.cust_pay$ <> P.pay_amount_sum
4. Common table expressions have lots of different uses, from the simple to the complex. For instance, a CTE works well as a “finder”. For example, provide a quick count of how many distinct customer numbers are in the contract file: WITH list as( SELECT distinct cmcust FROM cacmst ) SELECT count(*) FROM list Here’s a quick search for duplicate data. Find customers with the same phone number. With
phones as(
SELECT cuphnd as phone#, count(*)
FROM custp
WHERE cuphnd>0
GROUP BY cuphnd
HAVING count(*)>1
)
SELECT cucust, cufnm, culnm, cuphnd
FROM phones join custp on phone# = cuphnd
ORDER BY phone#, cucust
A great way to use CTE’s is to turn vertical data into columns. This example shows sales with current year figures in one column and prior year figures in another. with C as(
SELECT customer, sum(sales$) as sales$
FROM currentYear
GROUP BY customer
HAVING sum(sales$)<>0),
P as(
SELECT customer, sum(sales$) as sales$
FROM priorYear
GROUP BY customer
HAVING sum(sales$)<>0)
SELECT a.customer, a.custName, c.sales$, p.sales$,
c.sales$-p.sales$ as salesDiff
FROM customer a
left outer join c on a.customer=c.customer
left outer join p on a.customer=p.customer
RELATED STORIES Common Table Expressions Ease System Conversion Missing In Action: The Full Outer Join SQL Goodies in DB2 for i5/OS V5R4, Part 1 Reader Feedback and Insights: He Likes Common Table Expressions Common Table Expressions Make Summarizing Data Easy
|
