Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Agung Kurnianto
  Where is Agung Kurnianto?
 Bogor
 Indonesia
 Agung Kurnianto
 To: Gerzon
  Where is Gerzon?
 Yogyakarta
 Indonesia
 Gerzon
 Tags
Subject: RE: Function IIF() only work in VFP not In SQl Server 2000
Thread ID: 112377 Message ID: 112392 # Views: 3 # Ratings: 0
Version: Visual FoxPro 8 Category: Databases, Tables and SQL Server
Date: Wednesday, November 08, 2006 5:43:10 AM         
   


> Hi experts,
> is IIF() Function Work with SPT that I sent to SQL server as Back End, here I send this Query :
>
MSQL=[SELECT A.NO_REKENING,C.NM_REKENING,B.NM_ACC_TYPE,IIF(ISNULL(A.SALDO_AWAL),A.SALDO_AWAL,0) AS SALDO  FROM SALDO_AWAL_REKENING A]+;
> 	[ LEFT JOIN REKENING C ON A.NO_REKENING=C.NO_REKENING]+;
> 	[ LEFT JOIN ACC_TYPE B ON C.KD_ACC_TYPE=B.KD_ACC_TYPE]
> 
> IF SQLEXEC(KONEKSI_KE_SERVER,MSQL,"CRS_DARI_REKENING")<=0
> 	=MESSAGEBOX("QUERY... KE SERVER FAIL..."+MESSAGE(),0+16,"WARNING....")
> 	RETURN
> ENDIF

>
> error message show me :
> QUERY... KE SERVER FAIL...Connectivity Error :[Microsoft][ODBC SQL Server Driver][SQL Server]The isnullfunction requires 2 arguments. IIF()function work fine in VFP. is SQL command that I excecute in SQLEXEC() funtion is SQL Server-SQL Statement not VFP SQL Statement?
>
> TIA
> regards
> Gerzon


Hi Gerzon,
IIF() function only works on VFP not on MSSQL2000, you may try this:
Text to SelectData textmerge noshow

   SELECT A.NO_REKENING,C.NM_REKENING,B.NM_ACC_TYPE,ISNULL(A.SALDO_AWAL,0) AS SALDO
   FROM SALDO_AWAL_REKENING A 
      LEFT JOIN REKENING C ON A.NO_REKENING=C.NO_REKENING
      LEFT JOIN ACC_TYPE B ON C.KD_ACC_TYPE=B.KD_ACC_TYPE

Endtext
IF SQLEXEC(KONEKSI_KE_SERVER,SelectData,"CRS_DARI_REKENING")<=0
   =MESSAGEBOX("QUERY... KE SERVER FAIL..."+MESSAGE(),0+16,"WARNING....")
   RETURN
ENDIF


Also it is some different function between ISNULL on MSSQL and ISNULL on VFP
ISNULL on MSSQL has statement ISNULL(A.SALDO_AWAL,0) will result 0 if the value is NULL
ISNULL on VFP has statement ISNULL(A.SALDO_AWAL) will result .T./.F., if the value is NULL then the result will be .T.

Thanks,
Agung

ENTIRE THREAD

Function IIF() only work in VFP not In SQl Server 2000 Posted by Gerzon @ 11/8/2006 2:08:57 AM
RE: Function IIF() only work in VFP not In SQl Server 2000 Posted by Mikha sutanto @ 11/8/2006 4:42:47 AM
RE: Function IIF() only work in VFP not In SQl Server 2000 Posted by Agung Kurnianto @ 11/8/2006 5:43:10 AM