Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Anders Altberg
  Where is Anders Altberg?
 Uppsala
 Sweden
 Anders Altberg
 To: Cecil Champenois
  Where is Cecil Champenois?
 Little Elm
 Texas - United States
 Cecil Champenois
 Tags
Subject: RE: SQL statement error
Thread ID: 345261 Message ID: 345264 # Views: 37 # Ratings: 0
Version: Visual FoxPro 9 SP2 Category: Databases, Tables and SQL Server
Date: Monday, May 21, 2012 8:46:54 PM         
   


> I keep getting the error, "Function name is missing )."
>
> I will show only the part of the SQL statement that seems to have an issue:
>
> 		CAST(IIF(cv.Status<>"T","20120401", SPACE(8)) AS Character 8) AS PremBegin, ;
> 		CAST(IIF(cv.Status<>"T","20120430", SPACE(8)) AS Character 8) AS PremEnd, ;
> 		CAST(ICASE(in.Bill_Mode="M", "12", ;
> 			  in.Bill_Mode="A", "01", ;
> 			  in.Bill_Mode="S", "02", ;
> 			  in.Bill_Mode="Q", "04") AS Character 2) AS Pmt_Mode ;
> 	FROM GRPEXTIN in ;
> 	JOIN GRPEXTDP dp ON in.GrpNum = dp.GrpNum ;
> 	JOIN GRPEXTCV cv ON in.GrpNum = cv.GrpNum ;
> 

> There is more, of course to the above statement but I've put in the part of the code where it won't work. I had to put in the CAST() function in order to keep the field width at 8 characters in the first two fields (PremBegin and PremEnd). AS for the ICAST(), it seems to be okay, but there is still a problem here. Don't know what it could be yet.
>
> Cecil Champenois, Jr.

The () in Char(8) is missing. The CASE() is not necesssary as the IIF() aleays either returns an 8-char string of doigits or spaces.
 CAST(IIF(cv.Status<>"T","20120401", SPACE(8)) AS Char(8)) 
*or just
 IIF(cv.Status<>"T","20120401", SPACE(8))

The ICASE() should ideally instead referernce a table of bill_modes from the joining to Grpextin, and pick up the numeric values '12', 01' .. from there.

ENTIRE THREAD

SQL statement error Posted by Cecil Champenois @ 5/21/2012 7:57:39 PM
RE: SQL statement error Posted by Anil Sharma @ 5/21/2012 8:08:05 PM
RE: SQL statement error Posted by Cecil Champenois @ 5/21/2012 8:12:55 PM
RE: SQL statement error Posted by Anders Altberg @ 5/21/2012 8:46:54 PM