> Steve wrote:
>> IF strlen(source.corrected_procedure_code)
>> THEN:
>> summary.procedure_code=source.corrected_procedure_code
>> summary.wrong_procedure_code=source.procedure_code
>> ELSE:
>> summary.procedure_code=source.procedure_code
>> summary.wrong_procedure_code=NULL
>
> Um, so you test if source.corrected_procedure_code is an empty string? And if
> it is, summary.procedure_code is set to an empty string? But in
> wrong_procedure_code, you use NULLs?
Yeah; we could use empty strings if that make it easier for
whatever reason, but to our front end software NULL vs. empty string
doesn't actually matter and we never query based on these columns, they're
for display purposes only.
>> Simple, right? Making a C function to handle this should be no sweat -- I
>> would basically split this logic into two separate functions, one to
>> populate summary.procedure_code and one to populate
>> summary.wrong_procedure_code, and it removes the need of having any sort of
>> back and forth between the program and DB... I can just do like:
>>
>> update summary_table
>> set procedure_code=pickCorrect(source.procedure_code,
>> source.corrected_procedure_code),
>> wrong_procedure_code=pickWrong(source.procedure_code,
>> source.corrected_procedure_code),....
>> from source where summary_table.source_id=source.source_id;
>
> ISTM you could write this easily with a little bit of SQL, with no need for
> C-functions (I haven't run this, probably full of typos..) :
>
> update summary_table
> set procedure_code = (CASE WHEN source.corrected_procedure_code = '' THEN
> '' ELSE source.procedure_code END;),
> wrong_procedure_code = (CASE WHEN source.corrected_procedure_code = ''
> THEN source.procedure_code ELSE NULL END;)
> from source where summary_table.source_id=source.source_id;
This looks interesting and I'm going to give this a shot tomorrow
and see how it goes. Speed is somewhat of an issue which is why I
initially thought of the C function -- plus I wasn't aware you could do
CASE statements like that :) Thanks for the idea!
Steve