FW: Equivelant for lpad in Informix ? IDS7.31

#1 (**permalink**) 04-20-2008, 08:30 AM

This is a multi-part message in MIME format.

------_=_NextPart_001_01C502D8.B8E4FBB6
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

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

=20

The problem is, I don't want to concatenate, ||, the result must always
be to characters long.

=20

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

=20

=20

Oracle select below:

=20

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" =20

,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" =20

,"cl_pay_ind" =20

,"cl_supp_ref_no"=20

,"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" =20

FROM "claim"@medclear

, "doctor"@medclear=20

WHERE "cl_dr_no" =3D "dr_no" =20

=20

------_=_NextPart_001_01C502D8.B8E4FBB6
Content-Type: text/html;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

<html xmlns

=3D"urn:schemas-microsoft-com

ffice

ffice" =
xmlns:w=3D"urn:schemas-microsoft-com

ffice:word" =
xmlns=3D"http://www.w3.org/TR/REC-html40">

<head>
<meta http-equiv=3DContent-Type content=3D"text/html; =
charset=3Dus-ascii">
<meta name=3DGenerator content=3D"Microsoft Word 11 (filtered medium)">
<style>
<!--
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman";}
a:link, span.MsoHyperlink
{color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{color

urple;
text-decoration:underline;}
p.MsoPlainText, li.MsoPlainText, div.MsoPlainText
{margin:0cm;
margin-bottom:.0001pt;
font-size:10.0pt;
font-family:"Courier New";}
span.EmailStyle18
{mso-style-type

ersonal-reply;
font-family:Arial;
color:navy;}
@page Section1
{size:612.0pt 792.0pt;
margin:72.0pt 77.95pt 72.0pt 77.95pt;}
div.Section1
{page:Section1;}
-->
</style>

</head>

<body lang=3DEN-US link=3Dblue vlink=3Dpurple>

<div class=3DSection1>

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 =
…<o

></o

>

<o

> </o

>=


The problem is, =
I don’t
want to concatenate, ||, the result must always be to characters =
long.<o

></o

>

<o

> </o

>=


If cl_m_d_no is =
0, I want
00<o

></o

>

If cl_m_d_no is =
9, I want
09<o

></o

>

If cl_m_d_no is =
11, it
stays 11<o

></o

>

<o

> </o

>

<o

> </o

>=

Oracle select =
below:<o

></o

>

<o

> </o

></p=
>

SELECT "cl_bt_no"<o

></o

>

      =
,"cl_no"<o

></o

>

      =
,"cl_item_no"<o

></o

>

      =
,"cl_operator_code"<o

></o

>

      ,substr("cl_scheme",3)
cl_scheme<o

></o

>

      =
,"cl_dr_no"<o

></o

>

      =
,"cl_m_no"<o

></o

>

      ,"cl_d_no"  =
<o

></o

>

      =
,rtrim("cl_m_no&quot

||lpad("cl_d_no",2,'0') =
cl_m_d_no<o

></o

>

      =
,"cl_rams_no"<o

></o

>

      =
,"cl_dt_service_in"<o

></o

>

      =
,"cl_dt_service_out"<o

></o

>

      =
,"cl_dt_payment"<o

></o

>

      =
,"cl_dt_claim_rcvd"<o

></o

>

      =
,"cl_dt_processed"<o

></o

>

      =
,"cl_ta_code"<o

></o

>

      =
,"cl_units"<o

></o

>

      =
,"cl_m_plan"<o

></o

>

      =
,"cl_dr_plan"<o

></o

>

      =
,"cl_ta_rule"<o

></o

>

      =
,"cl_run_no"<o

></o

>

      =
,"cl_sequence"<o

></o

>

      =
,"cl_tooth_no"<o

></o

>

      ,"dr_name"  =
<o

></o

>

     
,"cl_pay_ind"     =
<o

></o

>

      ,"cl_supp_ref_no" =
<o

></o

>

      =
,"cl_reject_code"<o

></o

>

      =
,"cl_remark_code"<o

></o

>

      =
,"cl_booking_no"<o

></o

>

      =
,"cl_claim_amount"<o

></o

>

      =
,"cl_tariff_amount"<o

></o

>

      =
,"cl_benefit_amount"<o

></o

>

      =
,"cl_members_amount"<o

></o

>

      =
,"cl_discount_amount"<o

></o

>

      =
,"cl_status"<o

></o

>

      =
,"cl_diag_code"<o

></o

>

      =
,"cl_levy_amount"<o

></o

>

      =
,"cl_iteration"<o

></o

>

      =
,"cl_auth_no"<o

></o

>

     
,"cl_scheme_amount"   &nb sp;   =
   
     <o

></o

>

FROM   =
"claim"@medclear<o

></o

>

     , "doctor"@medclear =
<o

></o

>

WHERE "cl_dr_no" =3D =
"dr_no"    <o

></o

>

<o

> </o

>

</div>

</body>

</html>

------_=_NextPart_001_01C502D8.B8E4FBB6--
sending to informix-list

#2 (**permalink**) 04-20-2008, 08:30 AM

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@t eranews...
>
> 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"

#3 (**permalink**) 04-20-2008, 08:30 AM

Doug Lawry wrote:

> Try not to post MIME, Dirk :-) A cleaned up version follows.
> Looks like you need to upgrade: LPAD is there in version 9.

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

Assuming cl_m_d_no is a numeric datatype, you could write

trunc(cl_m_d_no / 10) || mod(cl_m_d_no, 10)

instead of lpad(cl_m_d_no, 2, "0")
or

trunc(cl_m_d_no / 100) || mod(trunc(cl_m_d_no / 10), 10) || mod(cl_m_d_no, 10)

instead of lpad(cl_m_d_no, 3, "0")

Regards

--
Roland Wintgen (Systemadministrator)

EVG Elektro-Vertriebs-Gesellschaft Martens GmbH & Co KG
Trompeterallee 244-246, D-41189 Moenchengladbach
Tel. +49 21 66 / 55 08 23, Fax +49 21 66 / 55 08 90
www.evg.de rw@evg.de

#4 (**permalink**) 04-20-2008, 08:30 AM

Lpad works fine on my 7.31 system and I even found lpad in a 7.31 SQL Syntax
book from Feb 1998.

Rtrim is a bit different though try this...
trim(trailing from cl_m_no)||lpad(cl_d_no,2,'0') cl_m_d_no

You may want to have another look at your manuals as well.

"Doug Lawry" <lawry@nildram.co.uk> wrote in message
news:ct5oo8$oin$1@nntp0.reith.bbc.co.uk...
> 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@t eranews...
> >
> > 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"
>
>

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode