MB Software Solutions wrote:
> Eugene Vital wrote:
>
>> Howdy all,
>>
>>
>> I am working on an app using VFP9 hitting a PostgreSQL 8.2 database
>> using SPT trying to concatenate
>> 2 fields using the COALESCE() function.
>>
>> The query works fine except the results are being returned as a Memo
>> instead of the needed character type. Is there a way to specify that the
>> results to come back in a character field instead of the memo?
>>
>>
>> Thanks....
>>
>>
>> loProjSignOffBizObj.sqlexecute("SELECT proj_signoff_id,
>> proj_signoff_date, COALESCE(proj_signoff_notes, '') as
>> proj_signoff_notes, " +;
>> "(usr.usr_fname || ' ' || usr.usr_lname) as usr,
>> proj_signoff_approval " +;
>> "FROM project_signoff ps " +;
>> "LEFT OUTER JOIN usr on usr.usr_id = ps.usr_id "+;
>> "WHERE ps.proj_id = '" + ALLTRIM(TRANSFORM(liProjectId)) + "'",
>> loProjSignOffBizObj.CursorName)
>>
>>
>
> I'm unfamiliar with PostgreSQL's COALESCE function...can you wrap a
> CAST( ) around it to force the resulting type?
>
> hth,
>
BINGO! Thanks Mike.... Ever notice how the most obvious solutions can
evade us sometimes?
COALESCE is more or less a vfp NVL() replacement
|COALESCE|(/value/ [, ...])
The |COALESCE| function returns the first of its arguments that is not
null. Null is returned only if all arguments are null. It is often used
to substitute a default value for null values when data is retrieved for
display, for example:
SELECT COALESCE(description, short_description, '(none)') ...
Like a CASE expression, |COALESCE| will not evaluate arguments that are
not needed to determine the result; that is, arguments to the right of
the first non-null argument are not evaluated. This SQL-standard
function provides capabilities similar to |NVL| and |IFNULL|, which are
used in some other database systems.
> --Mike (not trying to pad the statistics...just trying to help! <g>)
>
>
_______________________________________________
Post Messages to: [email protected]
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/[EMAIL PROTECTED]
** All postings, unless explicitly stated otherwise, are the opinions of the
author, and do not constitute legal or medical advice. This statement is added
to the messages for those lawyers who are too stupid to see the obvious.
