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
