Adding an "indicator" number

22 Sep 2005 7:36 PM

Patrice

Hi,

I'm sure this is real easy, I just want to make sure that I do it the right
way, so I'm going to ask the question. I need to put an "8" or "9" in front
of an existing number (e.g.: 12010326) in a field in a table. I will be
pulling the data from a staging table into a fact table. The idea is to
create a "client Number" by adding the 8 in front of the policy number as to
make a unique number that does not currently exist. The 8 will be for one
company name and the 9 will be for another company name.

Thanks loads!
Patrice

22 Sep 2005 7:55 PM

Alejandro Mesa

Patrice,

Use a case expression.

Example:

insert into fact_table (c1, c2)
select
   case c1
   when 12010326 then 8
   when 12010327 then 9
   else null
   end,
   c1
from
   staging_table

AMB

Show quote

"Patrice" wrote:

> Hi,
>
> I'm sure this is real easy, I just want to make sure that I do it the right
> way, so I'm going to ask the question. I need to put an "8" or "9" in front
> of an existing number (e.g.: 12010326) in a field in a table. I will be
> pulling the data from a staging table into a fact table. The idea is to
> create a "client Number" by adding the 8 in front of the policy number as to
> make a unique number that does not currently exist. The 8 will be for one
> company name and the 9 will be for another company name.
>
> Thanks loads!
> Patrice

22 Sep 2005 7:58 PM

Jerry Spivey

Patrice,

I'm not so sure that would be the best way. What if the policy number needs
to be used in a join or a where clause? Maybe adding a new column to the
table that includes the company id (if it doesn't already exist in the
table) and using the combination of the company id and the policy number to
ensure uniqueness???

HTH

Jerry
Show quote

"Patrice" <Patr***@discussions.microsoft.com> wrote in message
news:A2910858-7C22-4C6E-A094-0CA4BB2A46C9@microsoft.com...
> Hi,
>
> I'm sure this is real easy, I just want to make sure that I do it the
> right
> way, so I'm going to ask the question. I need to put an "8" or "9" in
> front
> of an existing number (e.g.: 12010326) in a field in a table. I will be
> pulling the data from a staging table into a fact table. The idea is to
> create a "client Number" by adding the 8 in front of the policy number as
> to
> make a unique number that does not currently exist. The 8 will be for one
> company name and the 9 will be for another company name.
>
> Thanks loads!
> Patrice

22 Sep 2005 8:10 PM

Patrice

I think (in essence), that is what I am trying to do exactly. I can add a
column with the company id - so would I still use the case expression?

Show quote

"Jerry Spivey" wrote:

> Patrice,
>
> I'm not so sure that would be the best way. What if the policy number needs
> to be used in a join or a where clause? Maybe adding a new column to the
> table that includes the company id (if it doesn't already exist in the
> table) and using the combination of the company id and the policy number to
> ensure uniqueness???
>
> HTH
>
> Jerry
> "Patrice" <Patr***@discussions.microsoft.com> wrote in message
> news:A2910858-7C22-4C6E-A094-0CA4BB2A46C9@microsoft.com...
> > Hi,
> >
> > I'm sure this is real easy, I just want to make sure that I do it the
> > right
> > way, so I'm going to ask the question. I need to put an "8" or "9" in
> > front
> > of an existing number (e.g.: 12010326) in a field in a table. I will be
> > pulling the data from a staging table into a fact table. The idea is to
> > create a "client Number" by adding the 8 in front of the policy number as
> > to
> > make a unique number that does not currently exist. The 8 will be for one
> > company name and the 9 will be for another company name.
> >
> > Thanks loads!
> > Patrice
>
>
>

23 Sep 2005 6:28 AM

kishor

Hi,
as suggested by alejandro.. I can suggest you one more.. instead of adding a
column in table you can consider a writing a function and u can use that
function in select statement.

Kishor

Show quote

"Patrice" wrote:

> I think (in essence), that is what I am trying to do exactly. I can add a
> column with the company id - so would I still use the case expression?
>
> "Jerry Spivey" wrote:
>
> > Patrice,
> >
> > I'm not so sure that would be the best way. What if the policy number needs
> > to be used in a join or a where clause? Maybe adding a new column to the
> > table that includes the company id (if it doesn't already exist in the
> > table) and using the combination of the company id and the policy number to
> > ensure uniqueness???
> >
> > HTH
> >
> > Jerry
> > "Patrice" <Patr***@discussions.microsoft.com> wrote in message
> > news:A2910858-7C22-4C6E-A094-0CA4BB2A46C9@microsoft.com...
> > > Hi,
> > >
> > > I'm sure this is real easy, I just want to make sure that I do it the
> > > right
> > > way, so I'm going to ask the question. I need to put an "8" or "9" in
> > > front
> > > of an existing number (e.g.: 12010326) in a field in a table. I will be
> > > pulling the data from a staging table into a fact table. The idea is to
> > > create a "client Number" by adding the 8 in front of the policy number as
> > > to
> > > make a unique number that does not currently exist. The 8 will be for one
> > > company name and the 9 will be for another company name.
> > >
> > > Thanks loads!
> > > Patrice
> >
> >
> >