Comment On Not Less Than And Not Greater Than

Interesting...could have shortened the query to:
SELECT DISTINCT COUNT(*) FROM JFA50100  and saved some heap memory....

In the xbase database language, (x >= y and x <= y) is not the same as (x=y)! Perhaps the author of this code came from an xbase background...?

(Mind you, in xbase you write (x==y) to get an exact string comparison, so the author of this code is being suboptimal even in the xbase world...)

I'm the poster... and the guy who had to fix many problems in this project...

Nope, most people who worked on this project were newbies; They didn't had xbase experience...  And this is a VB6 project, using SQL Server.

Are you sure about this in xbase ? I mean, a value cannot be < and also > than another value at the same time... so it's only the = that count...

martinm1000:

I'm the poster... and the guy who had to fix many problems in this project...

Nope, most people who worked on this project were newbies; They didn't had xbase experience...  And this is a VB6 project, using SQL Server.

Are you sure about this in xbase ? I mean, a value cannot be < and also > than another value at the same time... so it's only the = that count...

It will probably be converted to some numeric format with <= and >= instead of a fixed-string comparison (as stated...)

JFA50000 and JFA50100?  I'm sure there is a reason behind this, but if not I'm glad to see that they left 100 spots between JFA50000 and JFA50100.  That way the system can easily be expanded.  Or something.

I see the problem.... its a French system.

There are other tables between those 2 numbers... Actually, we do not always have control over the tables; they are from an accounting system.

I just started typing some comment, then stopped and went back to look at the query again...and now all I can say is "wow".

@Steve O. I don't think your suggestion works, as they still need FORMATID = gsFormatID and the BillGroupe thing equal to the input value as well.

I'd recommend using NOT <> rather than <= and >= it's much easier to read as the NOT <> gets rid of the pesky = signs altogether.

gmiller:

I'd recommend using NOT <> rather than <= and >= it's much easier to read as the NOT <> gets rid of the pesky = signs altogether.

Nice -- I like that one.  Alternatively, you could use:

(NOT < x) AND (NOT > x)

That might work quite well also.

Am I really going to be the first one to point out that you've made a bit of a WTF with this:

that endlessly confusing equals ("=") operator

which should probably have been "==" ?

"Am I really going to be the first one to point out that you've made a bit of a WTF..."
No, you are just the first one confusing SQL equals sign with C (and alike) equals sign.

The a<=b and a>=b conditions are just a way to mess with the db engine. WTF, no more, no less.
Besides that the snipped shows a lot of ugliness in its own. Like:
But that is not all. I see:

Inconsistency: GetSingleValueLong returns a boolean, the long value is returned with a parameter. But in GetRecordCount the result seems to be returned directly from the function.

Implicit type conversions: assuming bOnlyOneCopy is boolean: bOnlyOneCopy = Trim$... but I blame the language for this one.

Global variables to hold the connection object: assuming that hungarian notation is right. I loathe this.

Client side count.

I'm not sure we've spotted the real WTF...

It doesn't really seem to be a French system, it looks like Frenglish (Franglais pour les francophones).

This makes for about 4 languages in just this little snippet: English, French, B.A.S.I.C. and S.Q.L. (Throw in some hungarian notation for spice)

If GetSingleValueLong...bad things...
Is glLangID byRef? Does the return value even matter?

"Con" in French means stupid; is gobjCon in Hung-frenglish-> Global Object Stupid?

bOnlyOneCopy... please be only (err is it minus?) one.

I dunno what assParams is, but don't the functions with "$" at the end modify the string they operate on?

A classic situation : It might run now, but it is unsupportable.

I suggest finding out what it is supposed to do and writing new code to do just that.

(Free as in beer, not speech)

I think what Raymond was getting at is that = and == are different in xbase languages... e.g "FAT" = "FATTER" returns true, because the smaller string is identical to the equal-length portion of the longer string. "FAT" == "FATTER" is false, as is "FATTER" = "FAT".
Even so, I don't think you could excuse something like this! ;)

OMFG! [8-|]

Obviously gsBillGroup and gsFormatId are functions that return different value on each call. But why no bind variables in the SQL?

Steve O.:

Interesting...could have shortened the query to:
SELECT DISTINCT COUNT(*) FROM JFA50100  and saved some heap memory....

Unintentional WTF there, methinks. Something more like SELECT COUNT(DISTINCT Bill_Groupe) but even that's not right because you'd want DISTINCT Bill_Groupe, FormatId, CopyCode, which you can't do.

LOL, you are correct Rob, wrote that before first coffee...however he could have just done:
SELECT COUNT(*) FROM JFA50100 GROUP BY Bill_Groupe, FormatId, CopyCode
 and get the record count he wanted.

Steve O.:

LOL, you are correct Rob, wrote that before first coffee...however he could have just done:
SELECT COUNT(*) FROM JFA50100 GROUP BY Bill_Groupe, FormatId, CopyCode
 and get the record count he wanted.

Actually, no he couldn't -- that would not return the same results.  the correct way is simply to use a derived table by surrounding his current SQL statement with

"SELECT COUNT(*) FROM (" & currentSQL & " ) A"

However, we all know the best way is just to use a stored procedure.

I'm a little surprised no one has mentioned this particular WTF part:

Select Case glLangID
       Case LANGUAGE_ID_FRENCH, LANGUAGE_ID_ENGLISH
      
       Case Else
         glLangID = LANGUAGE_ID_FRENCH
End Select


I mean what's the point of even having this case statement?  It's a completely retarded way to implement a default value for gLangID.

--The Dan

TheDan666 :
Just for the evil...

I didn't mention it, but that why I was wondering : Is glLangID byRef?

I'm not sure it does nothing, if the system is one language, but the language to use isn't English or French, it will be French after that case...

Nope... they are global strings to the application !

WTF. Just use = , plain and simple.

[H]

I'm not 100% sure of this, but I recall maintaining some systems (that really should have had nullable=false). They were using a similar structure of query to get results that either = or were null. Yes it would have been much clearer to do an = value or is null search, but the dba/project lead didn't like that. Horray for Gov't!