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.

>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:

bSearchReferral := Trim(lbledtReferralNum.Text) <> '';
bSearchCustomerID := Trim(lbledtCustomerID.Text) <> '';
bSearchLastName := Trim(lbledtLastName.Text) <> '';
bSearchFirstName := Trim(lbledtFirstName.Text) <> '';
bSearchUnit := cmbxUnits.ItemIndex <> NO_SELECTION;
bSearchAdmitOrdinal := radgrpAdmitOrdinal.ItemIndex <> NO_SELECTION;
bClosedRecords := ckbxClosedRecords.Checked;

dataset.SelectSQL.Clear;
dataset.SelectSQL.Add('SELECT P.FIRSTNAME, P.LASTNAME, ');
dataset.SelectSQL.Add('CAST(''0'' || substring(A.UNIT_ID from 1 for
1)||''-''|| A.ADMISSION_ID ||''-0''|| A.ADMIT_ORDINAL AS VARCHAR(16)), ');
dataset.SelectSQL.Add('U.Unit, CAST(A.ADMIT_DATE AS DATE), ');
dataset.SelectSQL.Add('A.ADMISSION_ID, A.UNIT_ID, A.ADMIT_ORDINAL ');
dataset.SelectSQL.Add('FROM PATIENTS P ');
dataset.SelectSQL.Add('JOIN PATIENT_ADMISSIONS A ON P.PATIENT_ID =
A.ADMISSION_ID ');
dataset.SelectSQL.Add('JOIN PROGRAMUNIT U ON A.UNIT_ID = U.UNITNUM ');
dataset.SelectSQL.Add('WHERE ');
if bSearchReferral then begin
dataset.SelectSQL.Add('A.REFERRAL_CODE = :Referral AND ');
dataset.ParamByName('Referral').AsString := Trim(lbledtReferralNum.Text)
end;
if bSearchCustomerID then begin
dataset.SelectSQL.Add('A.ADMISSION_ID = :AdmissionID AND ');
dataset.ParamByName('AdmissionID').AsInteger :=
StrToInt(lbledtCustomerID.Text);
end;
if bSearchLastName then begin
dataset.SelectSQL.Add('P.LASTNAME = :LastName AND ');
dataset.ParamByName('LastName').AsString := Trim(lbledtLastName.Text);
end;
if bSearchFirstName then begin
dataset.SelectSQL.Add('P.FIRSTNAME = :FirstName AND ');
dataset.ParamByName('FirstName').AsString := Trim(lbledtFirstName.Text);
end;
if bSearchUnit then begin
sSQL := 'SELECT UNITNUM FROM PROGRAMUNIT WHERE UNIT = :UnitName';
sParamName := 'UnitName';
iUnitID :=
RetrieveIntValFromStrParam(sSQL, sParamName, cmbxUnits.Text,
dm.FIBDB);
dataset.SelectSQL.Add('A.UNIT_ID = :UnitID AND ');
dataset.ParamByName('UnitID').AsInteger := iUnitID;
end;
{ If an Admission Number is selected in the radio group, this Bool is
True}
if bSearchAdmitOrdinal then begin
dataset.SelectSQL.Add('A.ADMIT_ORDINAL = :AdmitOrdinal AND ');
dataset.ParamByName('AdmitOrdinal').AsInteger :=
radgrpAdmitOrdinal.ItemIndex+1;
end;
if bClosedRecords then begin
{ TODO : Add criteria needed ("if discharge summary has been added," but
what exactly does that mean?, and how will we be able to know that? }
end;
{ Always search dates, so we can rely on having at least one criteria (so
there is always a "WHERE" and this one is the last one, thus no "AND" }
dataset.SelectSQL.Add('A.ADMIT_DATE BETWEEN :From AND :To ');
dataset.ParamByName('From').AsDate := dtpFrom.Date;
dataset.ParamByName('To').AsDate := dtpTo.Date;

dataset.SelectSQL.Add('ORDER BY A.CREATED ');

dataset.Active := True;
{ Now that we know who it is, we can find out when their most recent
admission was, if that was selected }
cds.Filtered := ckbxMostCurrentRecord.Checked;
if cds.Filtered then begin
iAdmitOrdinal :=
GetLatestVisit(Dataset.FieldByName('ADMISSION_ID').AsInteger);
cds.Filter := Format('ADMIT_ORDINAL = %d', [iAdmitOrdinal]);
cds.Filtered := True;
end;
cds.Active := True;
if dataset.RecordCountFromSrv = 0 then begin
MessageDlg(SNoRecordsFoundBroadenYourSearchCriteria, mtInformation,
[mbOK], 0);
Height := INITIAL_HEIGHT;
Exit;
end else
dbgrd.Hint := s2ClickToTransfer;
end;


Clay Shannon,
Dimension 4 Software