Idea: new database object that defines a FROM clause

[John Rivers]

create from dbo.fromExample
as
from dbo.Customers c
join dbo.Invoices i
on c.CustomerID = i.CustomerID

now you can use it as follows:

select c.CustomerID, i.InvoiceNo
from dbo.fromExample

I find I reuse FROM clauses alot

and reusing a "base query" requires writing a very large and cumbersome
select clause

[Stu]

Re: Idea: new database object that defines a FROM clause

Not trying to be too snarky, but have you considered using a view?


John Rivers wrote:

create from dbo.fromExample
as
from dbo.Customers c
join dbo.Invoices i
on c.CustomerID = i.CustomerID
>
now you can use it as follows:
>
select c.CustomerID, i.InvoiceNo
from dbo.fromExample
>
I find I reuse FROM clauses alot
>
and reusing a "base query" requires writing a very large and cumbersome
select clause

[David Portas]

Re: Idea: new database object that defines a FROM clause

John Rivers wrote:

create from dbo.fromExample
as
from dbo.Customers c
join dbo.Invoices i
on c.CustomerID = i.CustomerID
>
now you can use it as follows:
>
select c.CustomerID, i.InvoiceNo
from dbo.fromExample
>
I find I reuse FROM clauses alot
>
and reusing a "base query" requires writing a very large and cumbersome
select clause

Use a CTE
or a view
or a derived table

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:

http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx

--

[John Rivers]

Re: Idea: new database object that defines a FROM clause



Thanks for your reply

CTEs, Views and Derived Tables

all require a SELECT clause

what I am trying to achieve is a reusable FROM clause





On Jan 7, 8:38 am, "David Portas"
<REMOVE_BEFORE_ REPLYING_dpor.. .@acm.orgwrote:

John Rivers wrote:

create from dbo.fromExample
as
from dbo.Customers c
join dbo.Invoices i
on c.CustomerID = i.CustomerID

>

now you can use it as follows:

>

select c.CustomerID, i.InvoiceNo
from dbo.fromExample

>

I find I reuse FROM clauses alot

>

and reusing a "base query" requires writing a very large and cumbersome
select clauseUse a CTE

or a view
or a derived table
>
--
David Portas, SQL Server MVP
>
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
>
SQL Server Books Online:http://msdn2.microsoft.com/library/m...S,SQL.90).aspx
--- Hide quoted text -- Show quoted text -

[Shuurai]

Re: Idea: new database object that defines a FROM clause


John Rivers wrote:

Thanks for your reply
>
CTEs, Views and Derived Tables
>
all require a SELECT clause
>
what I am trying to achieve is a reusable FROM clause

So essentially you want to avoid having to type "SELECT" and some
column names?

[Ed Murphy]

Re: Idea: new database object that defines a FROM clause

Shuurai wrote:

John Rivers wrote:

>Thanks for your reply
>>
>CTEs, Views and Derived Tables
>>
>all require a SELECT clause
>>
>what I am trying to achieve is a reusable FROM clause

>
So essentially you want to avoid having to type "SELECT" and some
column names?

I suppose he wants to avoid having to limit the common thing to certain
column names. Which he could do by creating a view that includes all
columns (renaming some as needed to avoid duplicate names), then doing
various SELECTs against that view.

[Hugo Kornelis]

Re: Idea: new database object that defines a FROM clause

On 8 Jan 2007 15:38:14 -0800, John Rivers wrote:

>
>
>Thanks for your reply
>
>CTEs, Views and Derived Tables
>
>all require a SELECT clause
>
>what I am trying to achieve is a reusable FROM clause

Hi John,

Though I personally fail to see the advantage of a reusable FROM clause
over the existing alternatives, you are of course free to suggest this
addition to Microsoft at http://connect.microsoft.com/SQLServer.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis