[dba-SQLServer] Iif in SQL Server

Are we talking about code in an Sproc here?  I am not talking about
AccessVB.  I am looking for something to work with 165 million records and
return results in a few minutes.

In the end I built a table with a PK field, a ZIPCode field and a Src field.
I made the PK field a PK so that it has a unique index.  I then wrote two
queries, one that pulls data from one field plus the PK plus a 1 as Src and
appends it to my new table.  The other query pulls data from the other field
plus the PK plus a 2 as the Src and appends to the table.  Run the first
query.  All records that have anything in the ZIP code are put in the table.
Run the second query.  All the records that have something in the second ZIP
field but aren't already in the table get put in the table.

Now I have a table with ONE field, with data from one or the other field,
with a column which tells me which source field it came from, with a PK to
join back up to the main table.

Of course my second query is failing to append at all because the PK already
exists in the new table.

John W. Colby
www.ColbyConsulting.com 

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Francisco
Tapia
Sent: Wednesday, September 22, 2004 1:30 AM
To: dba-sqlserver at databaseadvisors.com
Subject: Re: [dba-SQLServer] Iif in SQL Server


JOHN!

I know you're busy these days but it works like this


SELECT CASE WHEN FIELD = 'value' THEN FieldWhenTrue
                        WHEN Fieldothercase = 'othervalue THEN
FieldWhenOtherValue
                        ELSE FieldwhenELSE
               END AS AliasName,
             Next Field

>From TableName




On Wed, 22 Sep 2004 11:45:36 +1000, Stuart McLachlan
<stuart at lexacorp.com.pg> wrote:
> On 21 Sep 2004 at 21:38, John W. Colby wrote:
> 
> > Sorry, my Outlook is totally screwed up
> 
> That's tautology. <grin>
> 
> 
> 
> 
> --
> Stuart
> 
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
> 
> 



-- 
-Francisco
http://ft316db.VOTEorNOT.org _______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com