Category Archives: SQL

Class Act

Posted on by
Reply

Only rarely am I impressed with vendors and companies in general. They serve no interest but their own and it’s almost always thoroughly spiritually corrupt. I just received an email from our database vendor, Sage, and it made me do a triple-take:

“I hope this message finds you safe and unaffected by the recent severe weather and storms.  We are very concerned about the damage you may have experienced.  We know you may have lost infrastructure and connectivity at your office or have employees that are scattered around the region who cannot return for a while to help you resume business operations.If you have been affected by the severe weather and storms, we’d like to offer some assistance. 

We are willing to help get you on our hosted systems and operational at no charge to your organization until you are able to resume normal operations.  We do have capabilities to host Millennium on our secure platform.  However, a backup of your Millennium database will be needed to restore your operations. Please contact us at ***-***-**** so we can discuss and determine your needs.

It is my sincere hope that you are unaffected by these storms.  In the event that you are, Sage would like to help. “

Jaw slack. Eyes goggling. Surety that vendor is inherently malevolent, shaken to the core. Bravo Sage, Bravo. I am impressed.

Doubt Resolved

Posted on by
Reply

A dear friend of mine who is a master of SQL programming told me that the operators that I’m using are deaf to null values when evaluating a comparison. The way around this problem is to use the ISNULL() function around the columns that might be null. Now I am going to amend my nightly code and avoid this nasty little accident once and for all!

It’s good to have smart friends. 🙂

Logical Doubt

Posted on by
Reply

From the last post, a new update. Comparing the two columns fail to show any selected information, however when I changed the query so that mainacctno is null and chart_acct is not null I found 1741 mismatches. So mainacctno is null and chart_acct is ‘00000GWTN’, and so on and so forth for 1741 items. I suspect the logical operators cannot distinguish between null and not-null values.

What a mess.

Not Equal

Posted on by
Reply

At work I have a pretty big database that might or might not have a problem and the issue is, I have doubt in SQL to find out if I truly have something to worry about or if it was just a fluke.

In my database there are two tables. The first table, which we’ll call “chart” has a relationship with another much bigger table called “main”. The two tables have an odd interrelationship, in so far that many values from chart are ‘cached’ in main, but since both tables are allowed to vary apart from each other they can drift apart. So, for example, if you add something novel to chart, it isn’t completely reflected in main everywhere you’d expect it, because many of the cached values don’t get automatically updated from chart to main. There is a user utility that can do this task and it’s called “data sync” but I have elected to not use it and instead I have created a rather long SQL query that finds where there are mismatches between main and chart and uses the values in chart to fill in the cached empties in main. For years this has been a daily scheduled job and I’ve been living with the faith that my SQL code is correct and is doing it’s job.

Yesterday a coworker of mine complained to me that their report no longer showed what it should. We verified that there were indeed 47 records in main that should have been selected but only 1 was. I started to query the database and compare the values in chart and their places along the 47 “should have been” selected records and discovered to my deep chagrin that the values were not properly cached as I had faith that they would be. Of course I felt panic starting to gnaw away on the edge of my mind as I instantly had doubt that my SQL code was working properly.

The SQL code that “failed” looks like this:

select m.mainacctno, c.chart_acct
from main m inner join chart c
on m.mainrest=c.chart_code
where m.mainacctno!=c.chart_acct

Here the two columns should be equal, mainacctno and chart_acct for each record that is joined by mainrest=chart_code. The mainrest column is good as is the chart_code column. What I want to see is where, for each comparison, that mainacctno doesn’t equal whatever value is in chart_acct. So, for these 47 records there should be a value of ‘00000PSGI’ that exists in chart and not in main. My query should show these, but yesterday it did not.

Yesterday I had my back up against the wall and couldn’t really spend much time analyzing the problem so I forced the matter with a direct update command which put the proper value in each of the 47 records, essentially shoving ‘00000PSGI’ into each main record.

What bothers me is that when I use the != operator I expect it to behave as I have learned it to behave, to evaluate as true when operand A doesn’t equal operand B. Both columns, mainacctno and chart_acct are both of type char(30). I’ve tried using the other style of not-equal operator that is <> but that doesn’t display either.

I have 1759 records in chart and 1,414,844 records in main. My doubt can only be assuaged by manual comparison and I’m not looking forward to that task. I’ve even tried the “not like” operator to no avail. I wouldn’t be in this situation if I didn’t run afoul of those 47 records where a blank mainacctno apparently equaled a not-blank chart_acct.

What bothers me the most, I think, is that I now doubt the logical operators in SQL. With something so fundamental, everything built on that foundation is now subject to doubt. Does my database really have referential integrity? If logical comparison operators no longer behave in a logical fashion, is the stone that I think my database castle is made of sand?

Williamsburg – November 17th 2010

Posted on by
Reply

Today flowed a lot like yesterday did. Woke up, got a continental breakfast and attended sessions. Most of the sessions were useful, one got me considering swapping out my homemade ‘clever SQL use’ for T-SQL Cursors (cue the horrified screams of SQL admins everywhere!) and we were able to enjoy a quick lunch and then have our users group meeting. Mostly we’re happy with the response we’ve gotten from Sage when we initially pitched huge fits back in Chicago, then in Denver, and finally in Atlanta during previous Sage Summit events. They listened to us and a good portion of that I believe was our particular user group writing a ‘Meeting Statement’ and sending that to Sage. By doing that and not leaving it all for the pleasantries of verbal communications they could take the things we wrote to corporate management and definitively show that the users were upset. This time around we decided to do the same document style, a written statement, but instead of being full of piss and vinegar we expressed how happy we were that they responded so well to our statements of displeasure. We also indicated some useful ideas for Sage Summit 2011 which will be held sometime in July in Washington, DC. It’s looking like I may be attending that event, but only time will tell if that’s the case.

Once the convention concluded at 3pm I figured I wasn’t going to be paying any visit to family on my trip to Virginia, which I half-expected, it’s just too much distance and too much trouble and in the end ‘All’s Well That Ends Well’. I instead helped my coworkers go through another round of shopping at the outlet malls, retail therapy doesn’t being to describe it. 🙂 I broke down and after several years of simply pocketing my ID and plastic I broke down and bought a front-pocket wallet. I didn’t even know really that they made such a thing and when I found them in a Totes outlet, even the proprietor didn’t even know they made them like that. They were in a discount rack, originally $15, marked to $9. What the hell, I figure since I saved scads of money on food and supplies last week and this week that I can certainly afford a few splurgy purchases.

Tomorrow is going to be a madhouse. Our flight leaves Richmond at 9:40am, so we have to be there by 8:40, and the people at Kingsmill said that the hour drive to get to Richmond is pretty spot on because as they said “All the Military people are travelling south in the morning, so you won’t run into traffic coming north.” So… we are planning on getting on the road by 7:30am. I’m all packed up and ready to go, all I have to do is shower and load the few toiletries into my bag and I’m ready to go. Once we get back to Kalamazoo we have to rush to the office so we can all fill out our reimbursement forms so we can be reimbursed in a timely fashion. I think right after that I’m going home, as there really isn’t any point in starting work half-way through the day.

Dinner was good tonight, we went to the Whaling Company restaurant in Williamsburg. We got a far better dinner for more competitive prices than the last place that I dinged so bad for having crappy selection and outrageous prices. Now I am sitting back, helping my one coworker polish off the beer she bought so she wouldn’t leave any behind. This is a difficult task I feel I have no choice but to accept. 😉

Tomorrow, the flight. Tonight? Sleep.

Williamsburg – November 16th 2010

Posted on by
Reply

Today wasn’t as ram-tear as yesterday was. Breakfast was a continental at the resort center which was a surprise considering all the stories I’ve heard about Kingsmill, but I chock up the differences to a cost-conscious host like Sage and not because the venue just can’t get their host on.

Most of the day was spent bouncing from one room to another, learning some initially upsetting things and then as time went on realizing the inherent rightness of what I needed to do, essentially upgrade via scorched-earth policy. The best way to go from where we are with our product and where we have to go is to rip out everything, and reinstall from scratch. What was going to be an onerous task now became a sluggardly onerous task, but not insurmountable.

Lunch was quick, another continental, and the rest of the sessions went by in a blur. I caught up on my email, caught up on a huge wad of unread RSS feed material and made some headway clearing out my “favorite twitter” queue.

Dinner was shortly after that. We went to Berret’s Seafood Restaurant and Taphouse Grill on South Boundary Street in Williamsburg. The restaurant was initially quite pleasant however it was designed by the same people who assembled our ballroom office-space in Walwood Hall, it is organized like a rabbit warren, little connecting pathways between staging rooms. It’s not that I found fault with my food, it’s not that I didn’t like the restaurant either, but the menu selections were agonizingly assorted. If you wanted Shrimp you could have that, but you also got Oysters. If you wanted Tilapia, you also got Crab Cakes. The short two-page menu was rife with this sort of thing and I looked it over and frankly couldn’t find anything that I could order from the menu that I wanted to eat. Individual items, of course, but each dish was a mishmash of different seafood types and I’m not one for clams, oysters, or mussels. I ended up selecting a special, Tilapia-in-a-bag and got hosed. The dish was $26 but I got food that was really $8.99, at the most generous. The scallops that came with this dish were quarter size and nominally acceptable, but they weren’t properly washed and so I got a little sand in my diet tonight. If you are going to pump a $26 plate, wash the ingredients. In the end the meal was “very light” and that was a generous estimation from some of my dining compatriots who also had what I had. “This is it?” was what we heard up and down. I didn’t pitch a fit because it was a very high-class establishment and in the end it wasn’t my money on the line. If you are visiting Williamsburg, trust me on skipping this restaurant. I’m sure their other foods are outstanding, but if you are in any way picky about your seafood like I am, you’ll either leave hungry or upset, and poorer for it.

After dinner we decided that the night wasn’t over and some of my peers went out to get beer and wine so we could have a chat about our convention and enjoy each others company. The “party” devolved into a conference group meeting and we talked about obvious things that were on our minds, mostly about the company hosting us, Sage. Almost everything we remarked on was positive and we were all generally pleased with how Sage had compensated for their earlier problems that we chided them on in Denver, Atlanta, and our Users Group meeting in St. Olaf. Some people are apparently driven to see the two co-chairs, me and another lady, attend the Sage Summit 2011 in Washington, DC. I don’t see the reason or the justification for it since I’ve already attended here in Williamsburg, but I may have to go in order to make sure that Sage keeps in line with the wishes from our group. Only time will tell with that one.

Meanwhile, I’m contemplating going to visit family tomorrow afternoon after my last session ends, but I don’t have any method of conveyance from Williamsburg to Virginia Beach as I don’t have access to the rental vehicle that I once thought I might have had. The visit to VA Beach is still a possibility, but I haven’t the foggiest how I’m going to get there.

There are some oddities that do bear sharing. Kingsmill is a fantastic resort, but their bathrooms have been outfitted with occupancy sensors that were mounted about head-level, so when you make the slightest move, the toilet flushes and you get progressively more and more spritzed. I discovered that I could fix this … annoying problem by wrapping the occupancy sensor in toilet paper until I was good and ready for it to figure out that it was time to flush. I also noticed that in high traffic areas Kingsmill spares no expense and lays out cloth napkins to dry your hands after using the lavatory. In lesser used areas? Just paper towels. It’s not a problem, but it is kind of funny to see cleverness all the way down to how patrons dry their hands. The only other irking thing has nothing to do with the resort itself, but the obvious and annoying lack of any cellular signal whatsoever. I suppose if you are visiting for the spa, or the golf, or the grounds you don’t care so much about cellular technology. For me it would be a problem, if it weren’t for free Wifi and Google Voice.

Tomorrow is another day, up at 7ish, some more conference sessions and then? We’ll have to see…

Bloody Hell

Posted on by
Reply

What started out as a very innocent SQL-related question quickly expanded into an abominable Frankensteins Monster.

One of my coworkers asked if there was a way to create a SQL View in SQL 2000 so that when queried it would spit out a constituent’s ID and then list, in comma-delimited fashion the years they won a degree from WMU’s Haworth College of Business, in earlier-then-later year order. So she wanted:

0000045321  1968, 1974, 1978

Here is how I did it. I created a Frankensteins Monster. It’s two views and a function. I have unleashed this abomination upon the face of the Earth and I feel a lot like Dr. Frankenstein, maniacally dry-washing my hands and laughing “Muhahahahaha!” a lot. It doesn’t take much to get that out of me. 🙂 The code is beyond the More… tag, if you care to expose yourself to the abomination… Continue reading →