> Try not to post MIME, Dirk :-) A cleaned up version follows.
> Looks like you need to upgrade: LPAD is there in version 9.
> --
> Regards,
> Doug Lawry
> www.douglawry.webhop.org
>
> "Dirk Moolman" <DirkM@mxgroup.co.za> wrote in message
> news:1106662226.b5a997af030137130b3480a9499796a4@teranews...
> >
> > Oracle has an "lpad" function, to add characters to the left of a string
> > - what is the equivelant of this function in Informix ? Cannot find
> > lpad function in the SQL manuals ...
> >
> > The problem is, I don't want to concatenate, ||, the result must always
> > be to characters long.
> >
> > If cl_m_d_no is 0, I want 00
> > If cl_m_d_no is 9, I want 09
> > If cl_m_d_no is 11, it stays 11
> >
> > Oracle select below:
> >
> > SELECT "cl_bt_no"
> >      ,"cl_no"
> >      ,"cl_item_no"
> >      ,"cl_operator_code"
> >      ,substr("cl_scheme",3) cl_scheme
> >      ,"cl_dr_no"
> >      ,"cl_m_no"
> >      ,"cl_d_no"
> >      ,rtrim("cl_m_no")||lpad("cl_d_no",2,'0') cl_m_d_no
> >      ,"cl_rams_no"
> >      ,"cl_dt_service_in"
> >      ,"cl_dt_service_out"
> >      ,"cl_dt_payment"
> >      ,"cl_dt_claim_rcvd"
> >      ,"cl_dt_processed"
> >      ,"cl_ta_code"
> >      ,"cl_units"
> >      ,"cl_m_plan"
> >      ,"cl_dr_plan"
> >      ,"cl_ta_rule"
> >      ,"cl_run_no"
> >      ,"cl_sequence"
> >      ,"cl_tooth_no"
> >      ,"dr_name"
> >      ,"cl_pay_ind"
> >      ,"cl_supp_ref_no"
> >      ,"cl_reject_code"
> >      ,"cl_remark_code"
> >      ,"cl_booking_no"
> >      ,"cl_claim_amount"
> >      ,"cl_tariff_amount"
> >      ,"cl_benefit_amount"
> >      ,"cl_members_amount"
> >      ,"cl_discount_amount"
> >      ,"cl_status"
> >      ,"cl_diag_code"
> >      ,"cl_levy_amount"
> >      ,"cl_iteration"
> >      ,"cl_auth_no"
> >      ,"cl_scheme_amount"
> > FROM   "claim"@medclear
> >     , "doctor"@medclear
> > WHERE "cl_dr_no" = "dr_no"