Have you ever needed to extract data from old tables and use it in a way the table designer never planned on? This seems to be an all-too-common experience.
Recently, I learned that I could parse a number out of a string field, cast it as an integer, and actually use that result in a JOIN (as if it were a foreign key) to another table's integer primary key. Here is the JOIN part of my query:
...
inner join listings as list
on list.listing_id =
CAST ( RIGHT(op.description,(LEN(op.description)-CHARINDEX(':',op.description))) AS integer )
A few years ago, before I began to understand the power of SQL, I would have done something like this by hand in Excel. Details of the problem and solution are included below if you'd like to read on ...
The Problem
A client asked me to dump some order data out of his database into a "simple" Excel sheet. It might have actually been simple if he had only needed the ledger, order, product and customer records. These were already linked together very logically with foreign key relationships.
However, he also needed specific product details that were stored in another set of relational tables. These tables were again linked to each other. However, this second set of tables was not explicitly linked back to the ledger/order/product set of tables.
The ledger/order/product tables were designed for products in general. We were working with a particular class of products that happened to be web-listing subscriptions. The listings were built separately and stored in a completely different set of tables.
The two sets of tables have a person_id in common, but this was of only limited help since one person can have many orders and also many listings. How could I tell which listings went with each order?
Would I have to try and match them by date? That didn't look promising, since the listing could be created one day and paid for another. It could also be renewed (paid for a second or third time, when it expired).
The Solution
I discovered that the order_product table included a description field, where someone had the foresight to enter the numeric ID of each listing along with the generic product name (like "One Month" or "One Year"). Retrieving the description column, I had string data that looked something like this:
One Quarter, ID:938
One Month, ID:4129
One Month, ID:2138
6 month listing, ID:7183
One Year, ID:8722
One Month, ID:10031
One Quarter, ID:8734
etc ...
The colon made a handy delimiter for extracting the number from the end of each string, no matter how long either section of the string might be. With a little experimentation I found that I could take the order_product.description field and easily
(1) find the position of the colon:
CHARINDEX(':',op.description)
(2) calculate the length of the numeric string I wanted to extract:
LEN(op.description)-CHARINDEX(':',op.description)
(3) parse out the key for each listing:
RIGHT(op.description,(LEN(op.description)-CHARINDEX(':',op.description)))
(4) cast it as an integer and give it an alias:
SELECT CAST ( RIGHT(op.description,(LEN(op.description)-CHARINDEX(':',op.description))) AS integer ) AS listing_id
Next, I found I could even join on this field to the corresponding records in the listing table as I demonstrated above.
When all was said and done, I had a query that looked something like this (after sparing you about 50% of the crazy-making complexity of this database):
SELECT
lgr.payment_date,
lgr.person_id,
per.first_name,
per.last_name,
prod.description as product,
op.price,
CAST ( RIGHT(op.description,(LEN(op.description)-CHARINDEX(':',op.description))) AS integer ) AS listing_id,
list.title,
list.city,
list.state,
list.country
FROM
ledger lgr
inner join link_order_ledger as lol
on lol.ledger_id = lgr.ledger_id
inner join order_product as op
on op.order_id = lol.order_id
inner join product as prod
on prod.product_id = op.product_id
inner join person as per
on per.person_id = lgr.person_id
left outer join listings as list
on list.listing_id = CAST ( RIGHT(op.description,(LEN(op.description)-CHARINDEX(':',op.description))) AS integer )
WHERE op.product_id in (
SELECT product_id
FROM product
WHERE (product_subtype_id = 1000) -- Subscription: Web Listing
)
ORDER BY lgr.payment_date
Now, this is one more trick in my SQL toolbox, for when the next data challenge comes along!
There are no comments for this entry.
[Add Comment] [Subscribe to Comments]