I'm trying to wrap my head around a nasty MS-SQL one-to-many JOIN problem and I just can't get it to work out.
Basically, I have table a and table b, where a.index = b.index. b can have multiple entries per a.index, but I only care about the first match.
What I want to do is get something like this:
SELECT a.field1, a.field2, b.field
FROM a
JOIN b ON a.index = b.index
WHERE a.field1='this'
...etc...
But for every additional entry in b, I get another row for a. The only field guaranteed to be unique in b is a uniqueid field. It's hard to explain because the whole thing is a complicated mess.
Basically, a is a table of invoices, and b is a table of products added to the invoice. Most of the time, we have one product per invoice, but not always. I only care about seeing the first match. I thought a MIN() or MAX() would get me where I need to be, but it doesn't work—the only thing that's ever different in b is the uniqueid.
It'd be great if you could just JOIN TOP 1 in MSSQL but you can't. Any ideas?
9:05pm Update: I just had a thought about this: how about JOINing to a view that is just a simple SELECT DISTINCT a.uniqueid FROM b? I'll have to try this out tomorrow…
I found out some interesting changes in my recent upgrade of WordPress. They've added drop-down controls for Archives and Categories, something I wrote widgets for quite a while back. They seem to work well with one exception—the Links still aren't XHTML 1.0 Strict compliant! Since those are the standards I hold my blog up to, I will be maintaining my strict_links widget as well.
Today was SQL day. I've been working in databases and with scripts that manipulate SQL databases all day long.
I suspect tomorrow will be very similar to today.
And, for the first time, I found a need for using NOT EXISTS syntax in MS SQL.
I had a meeting at 10am this morning to discuss online training for our software, and how to handle signing up, payments, scheduling classes, etc for it.
The phrase that scared me the most was just duplicate the whole interface we use for classroom training, but in the end we decided to start a little smaller (thank goodness, I hate duplicating code, even scripting/database stuff) and just let the sales staff handle scheduling and sign-ups on an internal calendar. When the program gets off the ground, and if it becomes popular, then we'll revisit the online sign-up process and its requisite code.
I think that's one of the very few times I left a meeting with absolutely no work to do. Not that I'm complaining…
The WordPress plugin for Google Analytics by Rich Boakes works great except for one small part: it rewrites a link under comment_author_link and doesn't add a space between elements in the a tag, which causes a validation problem with XHTML.
It's simple to add a space on the proper line (272 in v0.68), but I created a diff file you can use to patch it. Hopefully the author updates the file and nobody else has to deal with this problem.
Get the diff file, copy it into your plugins directory where the googleanalytics.php file is, then run the following command:
patch -p1 < googleanalytics.diff
If this is too confusing for you, or you use Windows, change the line (272) from:
return $matches[1] . "\"" . $matches[2] . "\"" . $coolBit . $matches[3];
to
return $matches[1] . "\"" . $matches[2] . "\" " . $coolBit . $matches[3];
I've been working on some areas of PHP that I'm not quite as familiar with lately, getting ready to take the Zend PHP 5 certification exam.
Today I played around a little with sessions. I was able to use them to avoid the annoying browser history "this page was generated from POST data, do you want to resend the POST data?" problem when you go back to a page where login information had been authenticated.
The trick? I use header("Location: /path/to/logged-in-home.php"); after successful authentication, but otherwise display a page with error messages. It's a small problem, but it's nice to do away with it.
This was annoyingly difficult to track down, so I'm posting a very straightforward version for posterity.
How to change the QLCDNumber foreground color
Clock::Clock(QWidget *parent) : QLCDNumber(parent)
{
QPalette *p = new QPalette;
p->setColor(QPalette::WindowText, QColor(158, 193, 76));
setPalette(*p);
setSegmentStyle(Filled);
}
Fixing the Internet, one page at a time…
I played the heck out of this game called M.U.L.E. back in they heyday of the Commodore 64. There have been a few "tributes" to it, but I haven't found any good substitute for the original version played on a C64 emulator (yuck!).
So in the DIY spirit, I've decided to make my own:
And come on, you can't beat the name I picked out…
Hopefully this goes a bit faster than my other code projects (I never give up on them, just stop working on them for an unspecified amount of time).
A friend of mine is great at doing graphics, and he digs the game, too. I'll do the back-end work for clients (in Qt for cross-platform playability), and write an Internet server, something nobody's ever gotten around to.
This is in the very early prototype phase with only placeholder graphics and a very rudimentary interface (I just learned how to do this stuff in Qt!). I'll publish more updates as they become available.
I ran across a nasty problem today that MS, a former cow-orker, failed to solve. And failed to notice. This isn't a huge deal, but things aren't getting reported right because of it.
Here are the basics:
Three fields of type money are being SELECTed from a table. Sometimes one of them is NULL. Two of the fields are being added together, and one is being subtracted from that sum.
The problem is, any time a NULL value is present, then entire mathematical operation evaluates to NULL as well.
Through some research, I discovered Microsoft's T-SQL CASE keyword:
SELECT (Tax + CASE WHEN Freight IS NULL THEN '0.00' ELSE Freight END - Discount) AS 'Cost' FROM myTable
Of course, the fields Tax, Freight, and Discount are all the in the table myTable.
This actually works quite nicely. The problem was tracking down the information. I'm posting it here mainly for my own use later, when I forget how to do something weird like this.
Normally I'd just set the NULL values to 0.00 and forge on ahead, but in this case I have no idea what I may be breaking somewhere else and needed a simple, non-intrusive peek into the database.
2008 April 25 Note: Today I needed this information. I'm a genius.
Building up on yesterday's AJAX code, I finished by getting an official list of US and Canadian postal codes, cities, provinces, and some extra information hooked up. Now I can auto-populate City, State, and Country based on a quick postal-code lookup. It works beautifully.
I've been trying for at least a year to figure out a way to do some dynamic updating in Microsoft CRM v3 (or Dynamics, or whatever). Anyway, the problem is you don't have access to the source code for the pages, and even the customizable onLoad() or onChange() functionality just isn't enough sometimes. Perhaps you need an onKeyDown() or onBlur() function linked to an entity, which was my problem. Well, today I finally figured out how.
In the entity's onLoad() function box (we're talking Account, Contact, Lead etc), you need to write the javascript function to handle the job (I'm using some AJAX myself), and you need to attach an event to the element of the form you need to watch.
The element name is the ID found in the customization interface (in my case, I'm looking at a zip code as it is changing), the one I needed was address1_postalcode:
var xmlHttp = null;
var formZip = document.getElementById("address1_postalcode");
var formCity = document.getElementById("address1_city");
var formState = document.getElementById("address1_stateorprovince");
formZip.attachEvent("onkeydown", show);
function setCity(city) {
formCity.value = city;
}
function getCity(zip) {
xmlHttp = GetXmlHttpObject();
if(xmlHttp == null) {
alert("Browser does not support HTTP requests!");
return;
}
var uri = "/path/to/ajax/page.php?zip=" + zip;
xmlHttp.onreadystatechange = stateChanged;
xmlHttp.open("GET", uri, true);
xmlHttp.send(null);
}
function stateChanged() {
if(xmlHttp.readyState == 4 || xmlHttp.readyState == "complete") {
var results = xmlHttp.responseText.split(",");
setCity(results[0]);
setState(results[1]);
}
}
function GetXmlHttpObject() {
var objXMLHttp = null;
if(window.XMLHttpRequest) {
objXMLHttp = new XMLHttpRequest();
} else {
objXMLHttp = new ActiveXObject("Microsoft.XMLHTTP");
}
return objXMLHttp;
}
function show() {
var zip = formZip.DataValue;
switch(zip.length) {
case 5:
getCity(zip);
break;
default:
return true;
}
return true;
}
Everything goes into the entity's onLoad block, and not the individual attribute's onChange block. Obviously, you'd need to code your own URI to submit to, making sure it returned the properly formatted data, but this should be plenty to get anybody going with AJAX in Microsoft CRM.
The key to everything working, for me, was figuring out the attachEvent bit. Once I had that, everything else came together pretty quickly. You can change the event type to any of the other standard javascript events that happen on normal web pages.
They say there's no such thing. I suppose there's always a price, but today I had some Caruso's deli goodness and listened to an instructional talk about RAII and smart pointers in C++. We ran out of time talking about the std::auto_ptr and never even got to boost::shared_ptr. I also learned that Boost has a set of pointer containers that I didn't know about. I suppose I would have if I read their documentation, but seriously, who reads that stuff anyway?
The Free lunch continues tomorrow with the rest of the lesson and pizza. Mmmmmm, pizza.
I've got a good bit of documentation done for my MUD driver, you can see some temporary results here.
I still believe there is no better way to document your code than Doxygen.
I've been writing documentation for my code project, MUD3. In case you didn't know, I call it MUD3 because it's my
Anyway, I'd be dead if it wasn't for Doxygen, it's a life saver! The HTML output is quite good, and it's an easy command (uh, make pdf) to also create a PDF manual.
When I finish my documentation later this weekend, perhaps I'll make the documentation public.
Yup, you guessed it, it was me!
Lots going on today, including two trips to Spokane (one only to Liberty Lake, technically), one short trip down S Highway 95 (two of the three trips were for colocated server maintenance).
JM says I shouldn't be using an enum in my base class to determine what the derived class is (yes, I know it's redundant), but at the same time, how do I know what class I can downcast to if I don't know the type? Do you just have a big list and try casting to each one? What about multiple inheritance?
For instance, say I have class Animal and class Quadruped. Say I make a class Cow : public Animal, public Quadruped. Now if I test for a non-NULL pointer for each of those types, they all work, I just have to know that Cow is somehow greater than Animal and Quadruped. But what if I just have a Quadruped, not a Cow? Perhaps I'm missing the boat on the reason for not double-storing the type information, but I have to ask: isn't it better than a whole bunch of attempts to cast it to something it may or may not be? What if I have 50 or 100 classes to check? Do I really want a giant if clause that attempts to cast to each type? Doesn't it make more sense, in this case, to use an enum and switch directly to the downcast you know should work? The only real hangup here is if the type information got borked up, then I'd really be toast. But if I'm careful, isn't this a good way? Anybody?