SELECT statement on the basis of an IF

SELECT statement on the basis of an IF

Post by Rahul Chatterje » Wed, 29 Jan 2003 22:13:15



Hello All

Let me begin my question by explaining the scenario. I have 2 tables - a
history table containing historical records of claims. This table has a
joint primary key of GroupNumber, Claim Number and Underwriter Number and
Claim Line Number. A single claim can have upto 8 records with the same
claim number, (but the claim line number would be different). In this table
there are 4 provider Key fields (say P1, P2, P3, P4).

The second table is the provider table where the primary key is Provider
Key. (say prov1)

To retrieve the names of each provider for each claim, I was joining P1 and
prov1 together. But it so happened that on one of the claims, fields P1, P2
were both populated and record was repeated twice with this information. In
the history table there is also a field called PRPTR (which is the service
provider pointer and signifies which provider key field to use)

What has happened here is that the claim had two service lines signifying
that the first claim was paid to provider in P1 and the second to P2. Since
I was joining on fields P1 and prov1 from the Provider Table, I am returning
the name of the same provider for both instances of the claim.

Is there any way, to do this on the basis of an if statement or any other
logic wherein I check if the PRPTR field is  1, I pick the provider key fom
P1 and if 2 I pick from P2?

Sorry for being so long winded and sounding confusing.

Thanks in advance

 
 
 

SELECT statement on the basis of an IF

Post by Michael MacGrego » Wed, 29 Jan 2003 22:29:03


Narrative is all well and good but inferior to DDL (CREATE TABLE), example
data (INSERT INTO), the query you are trying to get to work if you have one,
and your expected result based on the DDL and example data. In other words,
give us everything we need to cut and paste into Query Analyzer so we can
see what's going on. Much more informative.

Michael MacGregor
Database Architect
SalesDriver

 
 
 

SELECT statement on the basis of an IF

Post by Jacco Schalkwij » Wed, 29 Jan 2003 22:38:24


As a first remark: your database is not properly normalised. You should not
have repeating columns in a table (in your case P1, P2, P3, P4). From your
scenario it looks as you don't really use them actually, because you use the
PRPTR column to indicate which one to use. You might as well put the actual
provider for that record in one column (say provider) and do away with all
the others.

But as this all smells very much of a legacy system I guess there is little
you can do about it now.

Something like this should work (untested):

SELECT
    CASE PRPTR
        WHEN 1 THEN prov_1.provider_name
        WHEN 2 THEN prov_2.provider_name
        WHEN 3 THEN prov_3.provider_name
        WHEN 4 THEN prov_4.provider_name
    END AS provider_name,
    <the rest of your columns here>
FROM myTable t
LEFT OUTER JOIN provider_table prov_1
    ON t.P1 = prov_1.prov1
LEFT OUTER JOIN provider_table prov_2
    ON t.P2 = prov_2.prov2
LEFT OUTER JOIN provider_table prov_3
    ON t.P3 = prov_3.prov3
LEFT OUTER JOIN provider_table prov_4
    ON t.P4 = prov_4.prov4

--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.


Quote:> Hello All

> Let me begin my question by explaining the scenario. I have 2 tables - a
> history table containing historical records of claims. This table has a
> joint primary key of GroupNumber, Claim Number and Underwriter Number and
> Claim Line Number. A single claim can have upto 8 records with the same
> claim number, (but the claim line number would be different). In this
table
> there are 4 provider Key fields (say P1, P2, P3, P4).

> The second table is the provider table where the primary key is Provider
> Key. (say prov1)

> To retrieve the names of each provider for each claim, I was joining P1
and
> prov1 together. But it so happened that on one of the claims, fields P1,
P2
> were both populated and record was repeated twice with this information.
In
> the history table there is also a field called PRPTR (which is the service
> provider pointer and signifies which provider key field to use)

> What has happened here is that the claim had two service lines signifying
> that the first claim was paid to provider in P1 and the second to P2.
Since
> I was joining on fields P1 and prov1 from the Provider Table, I am
returning
> the name of the same provider for both instances of the claim.

> Is there any way, to do this on the basis of an if statement or any other
> logic wherein I check if the PRPTR field is  1, I pick the provider key
fom
> P1 and if 2 I pick from P2?

> Sorry for being so long winded and sounding confusing.

> Thanks in advance