Re: If statement in subqueries

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Keith Kratochvil (sqlguy.back2u_at_comcast.net)
Date: 12/01/04 

Date: Wed, 1 Dec 2004 08:26:49 -0600

So, have you reconsidered the use of TOP 100 PERCENT?
Three of us recommended that it be removed.

Have you tried re-writing the T-SQL to use a JOIN (as David and I
suggested)? Do you still get the same results? Are the results returned
faster? 

-- 
Keith
"Chris Kennedy" <chrisknospam@cybase.co.uk> wrote in message
news:uAERdS41EHA.1404@TK2MSFTNGP11.phx.gbl...
> Yes it's from Enterprise Manager. I am a programmer rather than a database
> expert and just find I forget the syntax as I'll only do one once
> everyweek/couple of days. I do know what you mean though about how some of
> the joins it generates aren't very good.
>
>
> "Sue Hoegemeier" <Sue_H@nomail.please> wrote in message
> news:kbhpq0l4mc1l87q5mfiu87k0h8iv0ltrj0@4ax.com...
> > Every time I've seen that used, its from people using
> > Enterprise Manager and one of the builders to do their
> > queries. I hate those queries. Some of the  joins it
> > generates are the most obnoxious things to look at, the
> > parens are all over the place. I've seen them be nothing
> > more than ugly messes.
> >
> > -Sue
> >
> > On Tue, 30 Nov 2004 11:26:37 -0500, "Aaron [SQL Server MVP]"
> > <ten.xoc@dnartreb.noraa> wrote:
> >
> > >> SELECT TOP 100 PERCENT Surname, Forename,
> > >
> > >Why are you using TOP 100 PERCENT?
> > >
> > >> CASE
> > >> WHEN tblNames.Surname='smith' THEN 0
> > >> else(SELECT Rank FROM dbo.tblKeynames WHERE (KeyName = Surname))
> > >
> > >How are you limiting this to one row?  Does tblKeynames have both a
> Keyname
> > >and a Surname column?  If so, how do you expect the result from the
> subquery
> > >to have any possible relationship with the parent query?
> > >
> > >I suggest you post table schema, sample data, and desired results (for
a
> > >useful example; I doubt that leaving out any 'Smith' family is the
actual
> > >business requirement).
> >
>
>

Relevant Pages

  • Re: A03 / SQL8.0: ADP oder ODBC?
    ... Allerdings ist es ratsam, die Verarbeitung in den Abfragen, also das, was Du zur Zeit an Queries verwendest, wenn diese Performance Probleme haben, auf dem Server als View auslagerst. ... Mit DAO kannst Du aber solche SPs genauso aufrufen, ... Viel wichtiger ist eben möglichst viel an Abfrage-Logik von Jet Queries in Views, ... Natürlich gibt's auch da noch einiges zu tun, da T-SQL ja nicht die gleiche Syntax hat, wie Jet-SQL. ...
    (microsoft.public.de.access.clientserver)
  • Re: Having difficulty setting Back Up to back up file wihout datetime stamp SQL 2K
    ... I don't have a problem creating the T-SQL commands, ... Enterprise Manager to do more than create dbs is where I get lost. ... recent backup HAS to succeed before it'll delete the older one. ...
    (comp.databases.ms-sqlserver)
  • Re: Need help choosing front end for SQL Server
    ... > queries on large datasets, which ideally should happen on the SQL ... how to accomplish the same function using SQL Server features and T-SQL. ... BY clause implicitly orders the records, but in T-SQL it doesn't. ...
    (microsoft.public.sqlserver.clients)
  • Re: Having difficulty setting Back Up to back up file wihout datetime stamp SQL 2K
    ... I don't have a problem creating the T-SQL commands, ... Enterprise Manager to do more than create dbs is where I get lost. ... GUI where you backup databases, and select Schedule somewhere on a button. ...
    (comp.databases.ms-sqlserver)
  • Re: Edit Identity Increment
    ... Enterprise manager, while performing the identity increment change, would be ... there is no way you can do without it using T-SQL. ... but if I could do it in a script it would simplify things greatly. ...
    (microsoft.public.sqlserver.server)