[WD17] Conditional Select in Query

Hi
I'm using HF C/S & have used the Query editor to create a query with a calculated field. The SQL code which is generated by the editor for the calculated field is as follows:

CASE {pCostBasis}
    WHEN 'A' THEN stkm.AverageCost
    WHEN 'L' THEN stkm.LastCost
    ELSE  stkm.StandardCost
END AS ComponentCost,

When I run the query on its own as a test, it works exactly as expected. However when I run it from my code using HExecuteQuery, it doesn't return any values. It also doesn't return an error but as soon as I try to access any of the data, for example HNBRec(Qry) or HreadFirst it then gives me an "Internal error in WDHF.DLL", presumably because there is no result set.

Apart from the calculated field this query is basically identical to quite a few others, all of which run without a problem & if I remove the calculated field this one also works correctly. As far as I am concerned, the SQL code is correct. Why does this work in "run the Query Test" mode but not when called from within the program with absolutely identical parameters?

I haven't had much to do with HF files or the Query editor before so I have a couple of questions:
1. Is this not the way to do this? If not, how should it be done, apart from physically entering the SQL code or is that the only way? If it should work as I have done it I would think this a pretty significant bug &will get on to PC-Soft.
2. If I have to run it as an SQL query, can I (how do I) pass the pCostBasis parameter or do I have to use StringBuild etc?

Thanks

David

David
Just a guess..........

Declare another calculated field as 'MyCostBasis = pCostBasisis'

Place this before your existing calculation and use this in your case statement.

Could be that the query cannot see the parameter when used as a case variable

Thanks Derek but no good I'm afraid.

It won't allow me to declare a calculated field as being equal to anything.

I might have to do it as an SQL Query & run it as such which is rather disappointing; my intention was to find out how good, bad or otherwise it was to use the query editor & I guess I've got my answer a lot sooner than I expected!

If it can't even do a simple CASE statement based on a parameter it's pretty well useless to me & I might as well go back to coding the queries myself & running them as SQL.

What really bugs me is that the query works fine in test mode but then it all turns to crap when I try using it in the program. If the test facility can't give the same result as the program then its useless & we'd be better off without it. What's the point of it? Sorry, I'm just a bit p'd off with it at the moment


Cheers

David

Hi David,

When you say the test works but HExecuteQuery does not return any values, do you mean there are no records?

Are you using selection criteria? Are these set when you do the HExecuteQuery? Remember that they reset to null each time you do an HExecuteQuery. I know that trips us up from time to time.

We always use the HF C/S query editor and we have some complicated queries with calculated values as well. I suspect there is a simple solution.

HTH,

Hi Jeff
That's correct, no records are returned & I've looked at the selection criteria time & time again trying to convince myself that it's something stupid that I've done! Using HF Classic, running info(HnbRec(QueryName)) returns 0, however the same code using HF C/S (which is what I am using) returns an "Internal Error in WDHF.DLL" and crashes the whole thing.

There are 2 parameters only, the record id of the master file which is what the select is based on and the Cost Basis to use. These are both set immediately prior to running the query. I have also tried passing the values within the HExecuteQuery command with the same result.

If I delete the calculated field from the query it works correctly. The calculated field is based on an alias of the master file so it's definitely something to do with either the alias or the Case statement. I would be more than happy if someone can tell me it's something stupid I've done but it still begs the question as to why it works in test mode but not in program mode.

Cheers

David

Hi David,

I had a query, also with a CASE-WHEN in it. But I edited the query after generating the vase of it via the query editor. This query also doesn't execute at runtime, but does in testmode (GO form query editor). I send it to tch support of PCSoft and got as an answer that it was not a genuine query anymore. They sait, when you can't reverse engineer it, you have to execute it via HExecuteSQLQuery() and build the query with i.e. buildstring().

I hope it helps you.

Hi Stefan
Yes, I suspect I'm going to get the same answer. Just for fun I tried it in WD14 & it wouldn't run at all. In test mode it told me I couldn't use parameters with HExecuteSQLQuery so behind the scenes it must run the SQL code.

I have now reworked it as an SQL query and, if I get the same answer as you I'll forget all about the query editor & go back to my old ways. It'll definitely be a lot more than 10 times quicker than this has been!

Cheers

David

Hi David,

You might consider building the query with the editor, copy the code and execute it as a string.
Just change all the parameters in the code to %1, %2 etc and use stringbuild to insert the parameters.
A very convenient syntax in the code editor is the use of square brackets:

sSQL= [
SELECT 
Member.MemberNr AS LidNr,
Member.BirthDate AS BirthDate,
Member.Name+', '+Member.FirstName+' '+Member.Insertion AS FullName,
Member.email AS email
FROM Member
WHERE
Member.BirthDate Between '%1' AND '%2'
]
HexecuteSQLquery(ds_MySource,hModifyFile,StringBuild(sSQL,sDate1,sDate2)

I use this all the time for queries that I only use once in my project and are not linked to controls or tables. I'm guess this method might also saves resources.
Of course a disadvantage is that you cannot use links to the datasource.

Regards,
Piet

Hi All

FIXED!

I sent this to PC-Soft last weekend & they identified it as a bug. They have fixed it already & sent me a patch so full marks to them. The fix is for version 01A170078N & involves an update to WD170SQL.DLL & the 64bit framework.

I don't think I'd be breaching any PC-Soft copyright by posting the link here but rather than taking the chance, if anyone else needs the update let me know & I'll email the fix.

Cheers

David

It is always a pitty that they only send the fix to the person requesting based on an error that person found, instead of posting it as an update online so everybody can use the right code without asking ;-)

Hi David,

I would like to have that fix.
my email is stefan at provelu . nl

Thanks in advance.

Actually there is a place for fixes:
[windev.com]
and then check the 3th option.

But according to the fixes you see there, I guess:
pcsoft products are 99.9% bug free
or
pcsoft only has one developer, working one day a month or so to fix bugs.

bud why isn't this 'automaticly' supplied with the update tool that is included, it checks every time when I start bud very rare it finds an update ;-)
most updates you need to download by hand

Quote
Arie Mars
pcsoft only has one developer, working one day a month or so to fix bugs.

Hi Arie,

Are you sure about this? I don't think they have anybody working on the bugs. Quite obviously they are all too busy carrying the upgrade money to the bank with a wheelbarrow and preparing their sales brochure for the next upgrade round...:mad:

best regards
Ola