some .NULL. anywhere?
Thierry Nivelet
FoxInCloud
Give your VFP app. a new life in the cloud
Le 12/02/12 13:26, Andrew Stirling a écrit :
> Hi
> I have a select with
> IIF(EMPTY(natno) OR ninoconfirm = .f.,.t.,.F.) as choose , ;
> which is fine
> but when I try to restrict the output via
> WHERE choose = .t. ;
> it errors with
> 'Operator/operand type mismatch'
> What am I doing wrong?
> Thanks
_______________________________________________
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/4F37AFCB.8080402@foxincloud.com
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Hi
I have a select with
IIF(EMPTY(natno) OR ninoconfirm = .f.,.t.,.F.) as choose , ;
which is fine
but when I try to restrict the output via
WHERE choose = .t. ;
it errors with
'Operator/operand type mismatch'
What am I doing wrong?
Thanks
--
Kind regards
Andrew Stirling
01250 874580
support@calcpay.co.uk
HMRC Accredited UK payroll program
_______________________________________________
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/4F37B011.6040602@calcpay.co.uk
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Ta Thierry
The SQL works fine.
Its only when I put the 'WHERE choose = .t. ;' in that it errors
Kind regards
Andrew Stirling
01250 874580
support@calcpay.co.uk
HMRC Accredited UK payroll program
On 12/02/2012 12:25, Thierry Nivelet wrote:
> some .NULL. anywhere?
>
> Thierry Nivelet
> FoxInCloud
> Give your VFP app. a new life in the cloud
>
>
> Le 12/02/12 13:26, Andrew Stirling a écrit :
>> Hi
>> I have a select with
>> IIF(EMPTY(natno) OR ninoconfirm = .f.,.t.,.F.) as choose , ;
>> which is fine
>> but when I try to restrict the output via
>> WHERE choose = .t. ;
>> it errors with
>> 'Operator/operand type mismatch'
>> What am I doing wrong?
>> Thanks
>
[excessive quoting removed by server]
_______________________________________________
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/4F37B2CC.3040100@calcpay.co.uk
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
OK fixed it by reversing the where & select
SELECT ;
.t. as choose , ;
natno, ;
ninoconfirm , ;
surname ;
FROM calcpay!employee ;
WHERE EMPTY(natno) OR ninoconfirm = .f.;
ORDER BY refn DESC ;
INTO CURSOR Empinfo READWRITE
I take it that the SELECT does the WHERE first to see if it needs to
include the data.
Kind regards
Andrew Stirling
01250 874580
support@calcpay.co.uk
HMRC Accredited UK payroll program
On 12/02/2012 12:26, Andrew Stirling wrote:
> Hi
> I have a select with
> IIF(EMPTY(natno) OR ninoconfirm = .f.,.t.,.F.) as choose , ;
> which is fine
> but when I try to restrict the output via
> WHERE choose = .t. ;
> it errors with
> 'Operator/operand type mismatch'
> What am I doing wrong?
> Thanks
_______________________________________________
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/4F37BC4F.5090803@calcpay.co.uk
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Andrew, I don not thonk that a where clause can work on a calculated field. So
the correct query is somthing like :
SELECT ;
IIF(EMPTY(natno) OR ninoconfirm = .f.,.t.,.F.) as choose, ;
natno, ;
ninoconfirm , ;
surname ;
FROM calcpay!employee ;
WHERE EMPTY(natno) OR ninoconfirm = .f.;
ORDER BY refn DESC ;
INTO CURSOR Empinfo READWRITE
Working on sunday ??!!!
Best regards
The Foxil
_______________________________________________
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/4F37CCCC.5090307@wanadoo.fr
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
> From: "Jean MAURICE" <jsm.maurice@wanadoo.fr>
> Andrew, I don not think that a where clause can work on a calculated
> field. So
> the correct query is somthing like :
>
>
> SELECT ;
> IIF(EMPTY(natno) OR ninoconfirm = .f.,.t.,.F.) as choose, ;
> natno, ;
> ninoconfirm , ;
> surname ;
> FROM calcpay!employee ;
> WHERE EMPTY(natno) OR ninoconfirm = .f.;
> ORDER BY refn DESC ;
> INTO CURSOR Empinfo READWRITE
Yes. But he could use the name of the calculated field in an having clause !
SELECT ;
IIF(EMPTY(natno) OR ninoconfirm = .f.,.t.,.F.) as choose, ;
natno, ;
ninoconfirm , ;
surname ;
FROM calcpay!employee ;
HAVING choose ;
ORDER BY refn DESC ;
INTO CURSOR Empinfo READWRITE
>
> Working on sunday ??!!!
>
You toooo ?
(famous sound of train whistle) ;o)
Gérard.
_______________________________________________
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/A539E2EDF18D43689C350B3B2DB6767A@MuriellePC
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Good to hear from you Jean.
Yes, I can see the logic in that, but it then means I only need to put
.t. as choose , ;
instead of
IIF(EMPTY(natno) OR ninoconfirm = .f.,.t.,.F.) as choose, ;
in the field selection.
Working Sunday, HMRC has decided that the information in EVERY payrun
has to be sent to them, before they just got the totals at the year end.
So to stay with the big boys, I have to put in the hours!
Kind regards
Andrew Stirling
01250 874580
support@calcpay.co.uk
HMRC Accredited UK payroll program
On 12/02/2012 14:29, Jean MAURICE wrote:
> Andrew, I don not thonk that a where clause can work on a calculated field. So
> the correct query is somthing like :
>
>
> SELECT ;
> IIF(EMPTY(natno) OR ninoconfirm = .f.,.t.,.F.) as choose, ;
> natno, ;
> ninoconfirm , ;
> surname ;
> FROM calcpay!employee ;
> WHERE EMPTY(natno) OR ninoconfirm = .f.;
> ORDER BY refn DESC ;
> INTO CURSOR Empinfo READWRITE
>
>
> Working on sunday ??!!!
>
> Best regards
> The Foxil
>
_______________________________________________
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/4F37E3BD.7090204@calcpay.co.uk
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Thanks Gérard
I will try to remember that, the HAVING clause.
Kind regards
Andrew Stirling
01250 874580
support@calcpay.co.uk
HMRC Accredited UK payroll program
On 12/02/2012 15:08, Gérard Lochon wrote:
>> From: "Jean MAURICE"<jsm.maurice@wanadoo.fr>
>
>
>> Andrew, I don not think that a where clause can work on a calculated
>> field. So
>> the correct query is somthing like :
>>
>>
>> SELECT ;
>> IIF(EMPTY(natno) OR ninoconfirm = .f.,.t.,.F.) as choose, ;
>> natno, ;
>> ninoconfirm , ;
>> surname ;
>> FROM calcpay!employee ;
>> WHERE EMPTY(natno) OR ninoconfirm = .f.;
>> ORDER BY refn DESC ;
>> INTO CURSOR Empinfo READWRITE
>
>
> Yes. But he could use the name of the calculated field in an having clause !
>
> SELECT ;
> IIF(EMPTY(natno) OR ninoconfirm = .f.,.t.,.F.) as choose, ;
> natno, ;
> ninoconfirm , ;
> surname ;
> FROM calcpay!employee ;
> HAVING choose ;
> ORDER BY refn DESC ;
> INTO CURSOR Empinfo READWRITE
>
_______________________________________________
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/4F37EB1D.2030102@calcpay.co.uk
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
On Sun, Feb 12, 2012 at 6:26 AM, Andrew Stirling <support@calcpay.co.uk> wrote:
> Hi
> I have a select with
> IIF(EMPTY(natno) OR ninoconfirm = .f.,.t.,.F.) as choose , ;
> which is fine
> but when I try to restrict the output via
> WHERE choose = .t. ;
> it errors with
> 'Operator/operand type mismatch'
> What am I doing wrong?
> Thanks
> --
You are assigning the name in the field section and it is not yet
defined in the plan yet in the where clasuse.
> IIF(EMPTY(natno) OR ninoconfirm = .f.,.t.,.F.) as choose , ;
so use :
where ( EMPTY(natno) OR ninoconfirm = .f.)
HTH
Stephen Russell
901.246-0159 cell
_______________________________________________
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/CAJidMY+QrA5ehOmbLyiO4ddQuMdzU3igGogu_wi9ozy5AQE+Ng@mail.gmail.com
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.