[dba-SQLServer] Iif in SQL Server

No, I don't want a report, at least not atm.  I need the ability to count
these things over and over and over.  Thus it has to be FAST.  Clients call
and ask "how many addresses in these 230 zip codes?  That kind of stuff.
The zips can come from two different places, thus I want to pull them out
and place them in a single table where I can tell where they came from if I
need to but I only have to do a count on a single indexed column.

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 2:06 PM
To: dba-sqlserver at databaseadvisors.com
Subject: Re: [dba-SQLServer] Iif in SQL Server


You want a report right? ie a resultset... yes a sproc John.. you don't have
to CREATE a sproc, but doing so will store the optimizations for the sproc
in the server and possibly even caching parts of the report (since the data
hasn't changed).

to create a sproc


CREATE PROCEDURE stp_MyNewSprocNamingConvention 

AS

SELECT FIELD1, Field2, Field3, Case...
          FROM Table1
WHERE ClauseoptionsHere




On Wed, 22 Sep 2004 08:39:44 -0400, John W. Colby
<jwcolby at colbyconsulting.com> wrote:
> 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>



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