At 08:30 AM 6/05/2005 -0500, you wrote:

>At 04:47 PM 5/05/2005 -0500, you wrote:
>><<CAST('0' || substring(A.UNIT_ID from 1 for 1) ||'-'|| A.ADMISSION_ID
>>||'-0'|| A.ADMIT_ORDINAL AS VARCHAR(16)),>>
>>
>>OK, that worked great. Now I need to know how I can append 2 zeros if a
>>value is 1..9, but only one zero if it is 10..99. I have:
>>
>>CAST(''0'' || substring(A.UNIT_ID from 1 for 1)||''-''|| A.ADMISSION_ID
>>||''-0''|| A.ADMIT_ORDINAL AS VARCHAR(16)), ')
>
><This won't work. Mistyping?>
>
>It works; it's just copied from the Delphi editor, which requires double
>quotes around strings.

Ach, DOUBLED quotes, yes. Sorry, I get my emails in proportional text.

>>But need the last '0' to be two '00s if A.ADMIT_ORDINAL is < 10.
>
><<Try this:
>
>CASE
> WHEN A.ADMIT_ORDINAL < 10 THEN
> CAST('0' || substring(A.UNIT_ID from 1 for 1) ||'-'|| A.ADMISSION_ID
>||'-00'|| A.ADMIT_ORDINAL AS VARCHAR(16))
> WHEN A.ADMIT_ORDINAL > 99 THEN
> CAST('0' || substring(A.UNIT_ID from 1 for 1) ||'-'|| A.ADMISSION_ID
>||'-'|| A.ADMIT_ORDINAL AS VARCHAR(16))
> ELSE
> CAST('0' || substring(A.UNIT_ID from 1 for 1) ||'-'|| A.ADMISSION_ID
>||'-0'|| A.ADMIT_ORDINAL AS VARCHAR(16))
>END AS BLAH,>>
>
>But in the case (no pun intended) where I'm building a SQL statement for a
>whole set of records, and the value of ADMIT_ORDINAL is unknown prior to the
>statement being executed, how can this be done? Maybe it's easiest just to
>show my whole SQL statement, which is dynamic (query by form) based on what
>the user has checked/selected:

It doesn't make sense to say that "the value of ADMIT_ORDINAL is unknown...blah". It's always true in a SELECT statement, n'est-ce pas? The SELECT list is a formula for what you want in the output. The only difference from what you have now is some more strings.

Let me illustrate. In this bird's nest, you currently have:

....
dataset.SelectSQL.Add('CAST(''0'' || substring(A.UNIT_ID from 1 for
1)||''-''|| A.ADMISSION_ID ||''-0''|| A.ADMIT_ORDINAL AS VARCHAR(16)), ');
....

That becomes:
var
EasyString: string;
begin
...
with dataset.SelectSQL do
begin
....
EasyString := 'CASE WHEN A.ADMIT_ORDINAL < 10 THEN ' +
'CAST(''0'' || substring(A.UNIT_ID from 1 for 1) ||''-''|| A.ADMISSION_ID''||''-00''|| A.ADMIT_ORDINAL ' +
'AS VARCHAR(16)) ' ;
Add(EasyString);
EasyString := 'WHEN A.ADMIT_ORDINAL > 99 THEN ' +
'CAST(''0'' || substring(A.UNIT_ID from 1 for 1) ||''-''|| A.ADMISSION_ID ||''-''|| A.ADMIT_ORDINAL ' +
'AS VARCHAR(16)) ' ;
Add(EasyString);
EasyString := 'ELSE CAST(''0'' || substring(A.UNIT_ID from 1 for 1) ||''-''|| A.ADMISSION_ID ||''-0''|| A.ADMIT_ORDINAL ' +
'AS VARCHAR(16)) END AS FormattedStuff' ;
Add(EasyString);
....
end;

./hb