00000001:Incorrect function

[ariftheruvath]

VFP 9.0 return an error message with the following code :-

mmm=SQLExec(hr_handle,"set nocount on select (select emp_name from emp_master where emp_ID=b.emp_ID) name, "+;
"(select gross from pay_data where emp_ID=b.emp_ID and "+;
"(_year*416)+(_month*32)=b.yy_) gross,YY_/416 year,(YY_%416)/32 month,emp_ID from "+;
"(select emp_ID,max(intYY) yy_ from "+;
"(select emp_ID,min((_year*416)+(_month*32)) intYY from pay_data group by emp_ID,gross ) t "+;
" group by emp_ID ) b "+;
" where dbo.ifcancelled(emp_ID)=0 ",'lastINC')
if mmm<1
aerr(abcd)
wait wind abcd(1,3)
endif


got an error message "00000001:Incorrect function" with aerror()
but the same Sql Query works well when i run it right at SQL SERVER
pls help..

 

[Olaf Doschke]

What means "right at sql server", there only is the server and clients, SQL server Management Studio for example, also is just a client.

What is your connectionsrting? Active Database? dbo.ifcancelled may not be known in the context, there may be a semicolon missing after SET NOCOUNT ON, there can be many reasons.
The aerror message should tell you more, typically it will point to a postion in the query failing "near ...".

Bye, Olaf.

 

[Mike Lewis]

I've been trying to work out what your SQL code is doing, but I have to say it's difficult to understand it, because the code is quite convoluted. That doesn't mean it's wrong, but it is hard to read and therefore hard to debug.

I would suggest the following:

1. Instead of creating the SQL command from a series of substrings, use VFP's TEXT / ENDTEXT to write the SQL code. That way, you can make it much more readable, and any errors will be more obvious.

2. Going further, use the TO <variable> clause of TEXT / ENDTEXT to store the command in a variable, then pass that variable as the second parameter of SQLEXEC. The advantage is that you can copy the contents of the variable to the clipboard and then paste it into a text file. That way, you see exactly the command that you are sending to the back end.

3. If the above doesn't help you spot the error, try building up the command gradually, one clause at a time, until you reach the point where it fails. That should tell you which clause is causing the problem.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[TamarGranor]

Don't you need a semi-colon after SET NOCOUNT ON and before SELECT?

Tamar

 

[ariftheruvath]

thanks all.
solved

 

[Mike Lewis]

thanks all.
solved

For the benefit of anyone else with a similar problem (and as a courtesy to those who replied to your post), please tell us how it was solved.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[ariftheruvath]

so sorry mike
in fact i was trying to do with

if mmm>1
browse
else
aerr(abcd)
wait wind abcd(1,3)
endif

'mmm' would never be >1 as the no of result set always=1. so it skips IF part and moves to ELSE.

the correct code is:-

if mmm<1
aerr(abcd)
wait wind abcd(1,3)
else
browse
endif

thank u

 

[Olaf Doschke]

Well, in asybnchronous mode 0 would also be okay, but you'd need to wait for the result then. Only negative numbers are indicating a problem.

Bye, Olaf.