Re: Select question

From: simon (simon.zupan_at_stud-moderna.si)
Date: 10/22/04 

Date: Fri, 22 Oct 2004 09:18:25 +0200

Thank you Hugo.
I don't have presentation layer at all.

I created temp table and it works fine (for now) on my testing version where
I don't have a lot of data.

So, creating view is always the best solution when you have to repeat the
subquery that defines the
derived table or is it faster to repeat the subquery? I have many times the
similar situation.

I'm starting with SQL2005 , is there the same problem or is that solved?

Thank you,
Simon

"Hugo Kornelis" <hugo@pe_NO_rFact.in_SPAM_fo> wrote in message
news:se5gn09geo3tcervsf2l8m9arcg7gfvb7l@4ax.com...
> On Thu, 21 Oct 2004 16:01:47 +0200, simon wrote:
>
> (snip)
>>I hope you understand now what I want.
>
> Hi Simon,
>
> I'm not sure. Posting CREATE TABLEs, INSERTs for sample data and expected
> output is really a much better way to explain what you want than any
> narrative and made up examples. Judging by your e-mail address and posting
> host, English is not your native language. It's not mine either, so you
> should expect misunderstandings if you don't use the one language that we
> do have in common: SQL.
>
> Anyway, if I understand your need correctly, you're doing something that
> you might want to do in the presentation layer isntead. If that option is
> not available, read on.
>
>
>>If that won't be derived table, it's simple:
>>
>>SELECT T1.orderId,T1.productId,T1.value,
>>quantity1=(SELECT sum(value) FROM table WHERE orderID=T1.orderID),
>>quantity2=(SELECT sum(value) FROM table WHERE orderID=T1.orderID AND
>>T1.crossell=0)
>>FROM table as T1
>>
>>but if table is derived you can't create the same query
>
> Yes, you can. But you'd have to repeat the subquery that defines the
> derived table several times. Ugly, I know. Plus, I have seen evidence that
> seems to indicate that SQL Server's optimizer doesn't take advantage of
> the fact that the three subqueries are exactly identical.
>
> An alternative would be to define a view that holds the definition of the
> derived table. You can then reference the view as often as you wish in the
> query. That would make the query as clean as it can be, but you might
> still find SQL Server's optimizer producing odd-looking plans.
>
> Other alternatives would involve temp tables, or derived tables using
> GROUP BY, GROUPING and nasty CASE expressions. They'll defefinitely be
> lots uglier than just repeating the derived table, so I wouldn't even
> think about going there if I were you.
>
> Best, Hugo
> --
>
> (Remove _NO_ and _SPAM_ to get my e-mail address)

Relevant Pages

  • Re: Monster of a formula!!
    ... The first three columns repeat themselves in the last three. ... Dim FirstAdr As String ... Dim i As Long, temp As Long ... CkID = "Good" ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Spotlight and /Volumes
    ... set targ to choose from list with multiple selections allowed -- wrapped if targ is false then error number -128 set pw to text returned of -- wrapped set stats to ... set scpt to "mdutil -s /volumes/" repeat with aDisk in targ set temp to quoted form of contents of aDisk set end of stats to -- wrapped end repeat ...
    (comp.sys.mac.system)
  • Re: Temporary Disk
    ... By clause of the subquery: this field has no place there because the only ... only after the append query, ... the compact and repair after each error message. ... I found the temp file Access is using but not sure how to change size ...
    (microsoft.public.access.queries)
  • Mail Merge issue
    ... Hi Friends, I have a question and wondering if anybody out there can ... merge with that temp table. ... Rest of the fields should repeat ... I have a word document and i have done everything that needs to be done ...
    (microsoft.public.access.forms)
  • RE: Row inputs for a stored procedure
    ... Repeatedly call the SP with a single set of parameters ... Create a temp table and populate it ... repeat until you run out of entries. ... This will probably need the transaction control unless the SP inserts into a table and updates in one lump at the end. ...
    (microsoft.public.sqlserver.programming)