Plateforme Level Extreme
Abonnement
Profil corporatif
Produits & Services
Support
Légal
English
Freaky SQL?
Message
De
17/02/2003 12:57:17
 
 
À
17/02/2003 12:39:52
Information générale
Forum:
Visual FoxPro
Catégorie:
Codage, syntaxe et commandes
Titre:
Divers
Thread ID:
00754144
Message ID:
00754159
Vues:
18
>Not necessarily freaky. Can you post the SQL Statement and the UDF code?
>
Well OK, I lied.... Two UDF's
SELECT ALLTRIM(c.lastname) + ', ' + ALLTRIM(c.firstname) + ' ' + IIF(!EMPTY(c.mi),c.mi + ' ','') AS cname,;
		 c.entrydate,;
		 birthdate,;
		 CalcAge(BirthDate) AS age,;
		 sex,;
		 PADR(GetCatCode(a.county,'County'),25) AS county,;
		 PADR(GetCatCode(race,'Race'),25) AS race,;
		 PADR(GetCatCode(c.status,'ClientStatus'),25) AS istatus,;
		 UPPER(TRIM(lastname) + firstname + mi) AS ifield,;
		 lcHeader AS header,;
		 lcOwner AS owner ;
	FROM selectedlist s ;
		INNER JOIN clients c ;
			ON c.clientid = s.clientid ;
		LEFT OUTER JOIN addresslink l ;
			ON l.tableid = c.clientid AND l.sourcetable = 'C' ;
		LEFT OUTER JOIN address a ;
			ON a.addressid = l.addressid ;
	WHERE !EMPTY(birthdate) ;
	INTO CURSOR clist READWRITE ;
	ORDER BY 9
GetCatCode:
FUNCTION GetCatCode
*- Returns category code description (catname) when data environment does not 
*- include category code cursor named to cattype
*- Also returns specified system table names

*- First two parameters are:
*           categoryid,cattype (fields in category table)
LPARAMETERS tcCode,tcCodeCategory,tlForceAsCategory
*- third parameter lets us force use of category codes table even though cattype is same as a THISAPPTITLE_LOC table name

LOCAL lnFieldLen,lnOldSelect,lcReturn,llWasOpen,lcKeyName,lnPresentKey,laTables[1],; 
	lcDataBase

=ADBOBJECTS(laTables,'TABLE')

*- third parameter tlForceAsCategory lets us force use of category codes table even though cattype is same as a THISAPPTITLE_LOC table name
IF tlForceAsCategory OR ASCAN(laTables,UPPER(tcCodeCategory),1,-1,1,15) = 0 && was table name passed as cattype?
	*- no, just get cat code in usual manner
	lnOldSelect = SELECT()
	=Open('category')

	lnFieldLen = LEN(category.cattype)


	IF NOT EMPTY(tcCode)	&&             cattype                               categoryid
		lcReturn = IIF(SEEK(PADR(ALLTRIM(tcCodeCategory),lnFieldLen) + ALLTRIM(tcCode),'category','typeid'),;
					  ALLTRIM(category.catname),;
					  tcCode)
	ELSE
		lcReturn = ''
	ENDIF
	SELECT(lnOldSelect)
ELSE	&& otherwise, table name was passed, go look up the appropriate name
	lcReturn = GetName(tcCode,tcCodeCategory)	&& function in Local.prg to return names from various tables
ENDIF 

RETURN lcReturn
and CalcAge:
FUNCTION CalcAge(tdBirthDate,tdRelativeToDate)

LOCAL lnAgeDays,lnAgeYears,lnAgeMonths

IF PCOUNT() < 2 OR VARTYPE(tdRelativeToDate) <> 'D'
	tdRelativeToDate = DATE()
ENDIF

lnAgeDays = tdRelativeToDate - tdBirthdate
lnAgeYears = INT(lnAgeDays/DAYS_PER_YEAR)
lnAgeMonths = ROUND(MOD(lnAgeDays,DAYS_PER_YEAR)/30.4375,0)

RETURN ALLTRIM(STR(lnAgeYears)) + 'Yrs, ' + ALLTRIM(STR(lnAgeMonths)) + 'Mos'
Cetin may be on to something with the datasession, although I don't see why that should cause a SYNTAX error???
Ray Roper
Précédent
Suivant
Répondre
Fil
Voir

Click here to load this message in the networking platform