Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
Function in SQL sort of kind of not working
Message
From
28/08/2008 15:07:12
 
 
To
28/08/2008 14:44:55
Jay Johengen
Altamahaw-Ossipee, North Carolina, United States
General information
Forum:
Visual FoxPro
Category:
Databases,Tables, Views, Indexing and SQL syntax
Environment versions
Visual FoxPro:
VFP 9 SP2
Miscellaneous
Thread ID:
01342723
Message ID:
01342756
Views:
9
>This was working and now it does not. The problem is that the result value of PAYORID = "ZZCERT" (derived from cr.PayorID) but the value sent to the EligibilityRequestFormat function in the first parameter (cr.PayorID) is an empty string when it gets to the function. Well, it has about 10 spaces in it. How can the value of cr.PayorID be "ZZCERT" in the one field, but be empty in the other?
>
>If I remove the NPI field, the SQL returns the correct values, so the JOINs are working fine.
>
>
>	SELECT ;
>		ALLTRIM(cr.PayorID) AS PAYORID, ;
>		ALLTRIM(cr.CarName) AS CARRNAME, ;
>		'XX' AS NPI_Q, ;
>		EligibilityRequestFormat(cr.PayorID, 'NPI', pt.PtSSN) AS NPI ;
>		FROM patient pt ;
>			JOIN ins_case ic ON ic.pat_no = pt.ptpn ;
>			JOIN covr cv ON cv.Pat_No = pt.ptpn ;
>				AND cv.cvps = 'P' ;
>				AND dDateOfService BETWEEN EVL( cv.Eff_Date, {1/1/1950} ) AND EVL( cv.Exp_Date, DATE() ) ;
>				JOIN inscarr cr ON cv.CvCarr = cr.inscarr ;
>		WHERE pt.PtPn = cPatientNumber INTO CURSOR PD READWRITE
>
I'm not sure how you're trying to set up your JOINs. Without looking at it too deeply, it looks like you're trying to JOIN inscarr:cr to covr:cv, but the way your syntax is written, might you actually be trying to JOIN it to patient:pt? If that's the case, the local alias "cr" you're using in the NPI column definition might not be returning what you think it should be.

Also, as a general rule (at least for troubleshooting), when doing comparisons of character strings, use the exact comparison operator "=='" rather than the single "=". I can't tell if you're comparing strings or numbers. My understanding is it's not Rushmore-optimizable but it can be more reliable, which is more important.
Regards. Al

"Violence is the last refuge of the incompetent." -- Isaac Asimov
"Never let your sense of morals prevent you from doing what is right." -- Isaac Asimov

Neither a despot, nor a doormat, be

Every app wants to be a database app when it grows up
Previous
Reply
Map
View

Click here to load this message in the networking platform