SQL SELECT from a table with optional columns 1

[Mike Lewis]

I've flagged this post as a "helpful tip", but to be honest I'm not sure if it is a tip, a question, or a bug report.

Suppose you have a table that may contain up to four optional columns named extra_1, extra_2, extra_3, extra_4. In any given instance of the table, some or all those columns might or might not be present, in any combination.

Now suppose you want to get all of the fields from the table into a result set; for any missing optional fields, you want a blank column in the result set. You might do this:

SELECT ID, Name, extra_1, extra_2, extra_3, extra_4 ;
  FROM TheTable

But not surprisingly, that will give a "column not found" error if any of the optional fields is not present.

So you do this instead:

SELECT ID, Name, ;
  IIF(TYPE("TheTable.extra_1")="C", Extra_1, " ") AS Extra_1, ;
  IIF(TYPE("TheTable.extra_2")="C", Extra_2, " ") AS Extra_2 ;
  IIF(TYPE("TheTable.extra_3")="C", Extra_3, " ") AS Extra_3 ;
  IIF(TYPE("TheTable.extra_4")="C", Extra_4, " ") As Extra_4 ;
  FROM TheTable

Now, you might expect that to work. After all, if a given optional field is missing, the IIF() would evaluate to .F., and the corresponding "extra" field would not be referenced. But it seems that this also gives a "column not found" error - even though the SELECT doesn't need to find the relevant column in the input table.

So, my first questions are: Has anyone seen this behaviour before? And is it what you would expect?

One solution might be to construct the SELECT statement dynamically and then macro-execute it. But here's a simpler solution:

LOCAL extra_1, extra_2, extra_3, extra_4
STORE "" TO extra_1, extra_2, extra_3, extra_4
SELECT ID, Name, extra_1, extra_2, extra_3, extra_4 ;
  FROM TheTable

This works, because, if the relevant column is not found, the SELECT will look for a memory variable with the same name instead.

Do you think this is a sensible solution? Are there any dangers or disadvantages?

Any comments or opinions would be appreciated.

Mike








__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[EzLogic]

I use that solution all the time, the last sql select with the Variable (in case the field is not found)


Ez Logic
Michigan

 

[Olaf Doschke]

This is the very case, when the prioritisation of fields over variables is fine.
Sometimes I'd like it vice versa, but since there is m. you can enforce it via that memory object.

Of course never use m for table alias.

Clear
Create Cursor curTest (id int)
Append Blank
m.id = 1
? "field id:",id
? "variable m.id:",m.id
Select m.id from curTest as m into cursor curResult && here m.id will take curTest.id, not m.id
? "selected value m.id:",curResult.id

I wonder about the field lengths correctness, if you preset variables with empty string. I know VFP can generate C(0) fields from a varchar(max) errror with MSSQL Native ODBC Drivers. this might not play a role.

In regard to why VFP still error in case of the IIFs: I think hile IIF itslef will not evaluate parameters unless the bool has the correct value, the sql parser is adressing all epxressions and doesn't find fields. You would need to go one step further, if you wanted to stick with the IIF solution:

Create Cursor curTest (id int)
Append Blank

Release extra_1

SELECT ID, ;
  IIF(TYPE("curTest.extra_1")="C", Evaluate("curTest.extra_1"), " ") AS Extra_1 ;
  FROM curTest

But this looks pretty ugly, if you know what I mean, in comparison to just using the variables.

Bye, Olaf.

 

[Mike Lewis]

EZ Logic,

Thank you for your excellent reply. It makes a big difference to know that this is a common technique used by an experienced programmer like yourself, rather than a kludgy solution that depends on some quirky behaviour.

Olaf,

Good points. I think you're right about it being the SQL parser rather than the parsing of the IIF() that throws the error. I like your ingenuity in using EVALUATE() to get round that. But, as you say, using memory variables is simpler.

I also take your point about field lengths. When I discovered this behaviour, I was doing an INSERT INTO ... SELECT rather than a plain SELECT, so in that case the field lengths were already fixed. If I was just wanting to get a result set, I would preset the memory variables to the desired lengths.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Dan Freeman]

This works, because, if the relevant column is not found, the SELECT will look for a memory variable with the same name instead.

Another way to say that is to say it works because of the scoping and precedence rules of Xbase.

I would never do something like that without LENGTHY explanatory comments. This is ONE place where I would actually explain the arcana of the programming language in comments because a less experienced programmer who comes along doing maintenance on the code may not know the precise rules of the language. (In fact, it's likely.)

I'm spending my days resuscitating code originally written in VFP that uses techniques that would have been more at home (but still slightly obscure) in dBase III. I'm having quite an exercise figuring out what they're actually doing.

Give the next guy a fighting chance.

 

[Mike Lewis]

I would never do something like that without LENGTHY explanatory comments.

You're spot on, Dan. I couldn't agree more.

In the particular case that gave rise to this thread, I wrote a comment that ended up longer than the code. That's not unusual for me. And it's not just for the benefit of the next guy. My own comments make essential reading when I have to maintain my own code.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Dan Freeman]

I wrote a comment that ended up longer than the code

Not unusual in my code, either.

Some of the best advice I got early in my career was to comment your code as if the next guy to work on it was a sociopathic mass murderer who knows your address. He just might be!

I also use a Foxcode macro so any time I type KLUDGE in a code window it inserts this:

*******************************************
*******************************************
***           KLUDGE ALERT!             ***
*******************************************
*******************************************

Some things need a little extra warning.

 

[Olaf Doschke]

Did you know the german word for giving applause is not applauding (well, there is the foreign word applaudieren), but "klatschen"?
It's pronounced quite similar to kudge ending in an n vowel. The t is a sharper d, as in english, and sch is rather sh, but in this case the dg sounds quite like a sh.

What does this mean? This kludge is still worth an applause.

Bye, Olaf.

 

[Mike Lewis]

That's interesting. I didn't know the German word klatschen, but I have heard applaudieren. I assumed that was the usual German word for "applause". But, according to Chambers, the derivation is the Latin applaudere (which, come to think of it, is obvious).

Regarding the pronunciation "kludge", in Britain (and I assume most other English-speaking countries), it's pronounced to rhyme with "judge". But in the US, I've often heard it said like "kloodge" - where the first syllable is like "clue".

I just thought you'd like to know.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

Well, appplaudieren is a very formal term, while Applaus (=applause) is a commonly used word to describe the whole applause, the common rather is klatschen. It has side meanings in the direction of violence.

Didn't know about the US pronunciation of kludge, that wouldn't match klatschen at all. the dj sound surely isn't matching sh 1:1 anyway, but you could interpret it as mumbled sh.

Bye, Olaf.

 

[Olaf Doschke]

... the common verb rather is klatschen...