help with iif in sql please 1

[johncook]

Problem:
I have some records in Account_debits that have empty or probably NULL in the DateTime field dtxndatetm.

When I set my paramaters and requery my view I get the "Operator/Operand type mismatch" error. I amalmost positive the null comparison is the problem because:
1. I know there are null values in the datetime field of some recs
2. I can take the filter on VP_ttxndatetm off and the error goes away.

I can't find any syntax that works.

My sql:

SELECT Account_debits.cinmates_id,;
IIF(EMPTY(Account_debits.yamount).OR.ISNULL(Account_debits.yamount),0.00,SUM(Account_debits.yamount)) AS ydebits;
FROM inmatetrustfund!account_debits;
WHERE Account_debits.cinmates_id = ?VP_cinmates_id;
AND Account_debits.ycreditedamt = 0.00;
AND ?VP_ttxndatetm > IIF(EMPTY(Account_debits.dtxndatetm).OR.ISNULL(Account_debits.dtxndatetm),{},Account_debits.dtxndatetm);
GROUP BY Account_debits.cinmates_id

Other syntax I have tried:
(EMPTY(Account_debits.dtxndatetm) OR;
ISNULL(Account_debits.dtxndatetm) OR;
Account_debits.dtxndatetm <= ?VP_ttxndatetm);

Thanks,
John

 

[ramani]

Hi

Try

SELECT Account_debits.cinmates_id,;
SUM(NVL(Account_debits.yamount,0.00) AS ydebits;
FROM inmatetrustfund!account_debits;
WHERE Account_debits.cinmates_id = ?VP_cinmates_id;
AND Account_debits.ycreditedamt = 0.00;
AND ?VP_ttxndatetm > ;
IIF(EMPTY(Account_debits.dtxndatetm).OR. ;
ISNULL(Account_debits.dtxndatetm), ;
{},Account_debits.dtxndatetm) ;
GROUP BY Account_debits.cinmates_id

Hi I am suspecting your where conditions. I suggest you try (if the above does not yield result) with one where condition at a time and identify the problem.


ramani
(Subramanian.G)

http://winnersoft.coolfreepages.com/

 

[jimoo]

You're IIF looks okay to me, and I just verified (for sanity purposes) that empty() and ISNULL work okay with the numeric data type.

Are you sure it's not the parameter cause the problem?

Check these guys out.

?VP_cinmates_id
?VP_ttxndatetm > IIF

Jim Osieczonek
Delta Business Group, LLC

http://www.deltabg.com

 

[johncook]

Jimoo and ramani,
Thanks.

I have checked the data. There are no instances where the other data fields are empty or null. The following works perfectly:

CREATE SQL VIEW &quot;VL_OI_DEBITS&quot; ;
AS SELECT Account_debits.cinmates_id,;
IIF(EMPTY(Account_debits.yamount).OR.ISNULL(Account_debits.yamount);
,0.00,SUM(Account_debits.yamount)) AS ydebits;
FROM inmatetrustfund!account_debits;
WHERE Account_debits.cinmates_id = ?VP_cinmates_id AND;
Account_debits.ycreditedamt = 0.00;
GROUP BY Account_debits.cinmates_id

This may be the problem but if so I do not know howto correct it:
The parameter, VP_ttxndatetm is defined as DateTime. Perhaps it should be Character? The field Account_debits.dtxndatetm is a DateTime field.

Thanks,
John

 

[jimoo]

IF the field is a datetime then you must convert it to a string if you are comparing it against a string, or convert the sting to a datetime. Ultimaley, the data types must be the same.

Take a look at the DATETIME() function.

Type HELP DATETIME() in the command window for the syntax.



Jim Osieczonek
Delta Business Group, LLC

http://www.deltabg.com

 

[ramani]

Hi Jim,

SO in that case, you can use the TTOD() function.. example.

TTOD(myDateTimeField)



ramani
(Subramanian.G)

http://winnersoft.coolfreepages.com/

 

[johncook]

Then I should not be having this problem because the parameter, VP_ttxndatetm and the field, Account_debits.dtxndatetm are BOTH datetime type.

In my code where I return () when the field is empty or null, is that the correct value to return or should I return CTOT(&quot;&quot?

Thanks,
John

 

[craigsboyd]

johncook,

The problem is almost certainly the parameter as the other will evaluate to a &quot;D&quot; or &quot;T&quot; data type. A couple of things though...I don't see the need for the check on empty() since an empty datetime and {} will evaluate indentically. Though, {} is a &quot;D&quot; data type and if the field was empty it would be {/:}...

?TYPE(&quot;{/:}&quot
?TYPE(&quot;{}&quot

...but for the purposes of what you are doing they would be the same...better to use NVL(Account_debits.dtxndatetm, {/:}) in your SQL where clause if you ask me, but I am not certain the version of VFP you are running either, so this option may not be available to you.

As to what is the correct thing to return...if the parameter was truly a datetime this wouldn't be an issue since you can evaluate a datetime versus a date and not end up with an error. The parameter is probably a string however (just a guess) so I would convert the parameter to a datetime using CTOT(). If you are certain that the parameter is indeed a datetime and that the field Account_debits.dtxndatetm is a datetime then your problem is definately elsewhere...but having said that you seem to know for sure that the problem is with that one part of the where clause since if you remove it everything runs fine. Cast that parameter as a datetime and it should solve your problem.

Slighthaze = NULL

[ul][li]FAQ184-2483
An excellent guide to getting a fast and accurate response to your questions in this forum.[/li][/ul]

 

[craigsboyd]

sorry for the long winded answer above, my proposed solution is as follows:

SELECT Account_debits.cinmates_id, ;
SUM(NVL(Account_debits.yamount,0.00)) AS ydebits ;
FROM inmatetrustfund!account_debits ;
WHERE Account_debits.cinmates_id = ?VP_cinmates_id ;
AND Account_debits.ycreditedamt = 0.00 ;
AND CTOT(?VP_ttxndatetm) > NVL(Account_debits.dtxndatetm,{/:}) ;
GROUP BY Account_debits.cinmates_id

...I just typed this in here without having tested it so I hope I haven't made any typos or mistakes.

Slighthaze = NULL

[ul][li]FAQ184-2483
An excellent guide to getting a fast and accurate response to your questions in this forum.[/li][/ul]

 

[johncook]

Thanks Slighthaze and all,
Slighthaze you taught me several things I did not know. Thanks for the tipes.

Found the problem. It was the parameter but not so simple. Seems when I 1st defined it the view designer changed a property in my framework that set the class library incorrectly. My framework is VFE. It took a while but some help on that forum finally found the problem. It appears that under certain circumstances such as changing the name of the parameter and/or type after 1st defined (whicgh in this case, I did) it just gets flaky.

Thanks again,
John

 

[craigsboyd]

johncook,

Glad I could help and to hear that you got your problem figured out.

Slighthaze = NULL

[ul][li]FAQ184-2483
An excellent guide to getting a fast and accurate response to your questions in this forum.[/li][/ul]