[dba-SQLServer] Iif in SQL Server

So is that gonna work for you?


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