Thursday, March 29, 2012

Do not SELECT

Hi,
Is there any way how to formulate SQL query to select all columns except
column D and E?
So if the table has the columns A,B,C,D the result woud contain only columns
A,B,C
SELECT " * except D,E"
FROM myTable
Thanks,
Lubomir
SELECT A, B, C
FROM myTable
"Lubomir" <Lubomir@.discussions.microsoft.com> wrote in message
news:A8B69DA2-E5B7-4E91-BCE9-89A673C6CCA9@.microsoft.com...
> Hi,
> Is there any way how to formulate SQL query to select all columns except
> column D and E?
> So if the table has the columns A,B,C,D the result woud contain only
> columns
> A,B,C
> SELECT " * except D,E"
> FROM myTable
> Thanks,
> Lubomir
|||Hello,
If you want it permamanent then you can create a view and then query the
view
CREATE VIEW A1
as
Select A,B,C from myTable
After the creation query the view
Select * from A1
THanks
Hari
"Lubomir" <Lubomir@.discussions.microsoft.com> wrote in message
news:A8B69DA2-E5B7-4E91-BCE9-89A673C6CCA9@.microsoft.com...
> Hi,
> Is there any way how to formulate SQL query to select all columns except
> column D and E?
> So if the table has the columns A,B,C,D the result woud contain only
> columns
> A,B,C
> SELECT " * except D,E"
> FROM myTable
> Thanks,
> Lubomir
|||The problem is, the tables are created on the fly, so I don't know what
columns the particular table will have. I know however, every table has two
columns ("help" columns) that will not be displayed, as they are used for
another purposes.
From that reason, it would be very convenient to make query like SELECT *
and to specify those 2 columns to be exclusive.
Thanks,
Lubomir
"Hari Prasad" wrote:

> Hello,
> If you want it permamanent then you can create a view and then query the
> view
> CREATE VIEW A1
> as
> Select A,B,C from myTable
> After the creation query the view
> Select * from A1
> THanks
> Hari
>
> "Lubomir" <Lubomir@.discussions.microsoft.com> wrote in message
> news:A8B69DA2-E5B7-4E91-BCE9-89A673C6CCA9@.microsoft.com...
>
>
|||On Fri, 4 May 2007 09:37:00 -0700, Lubomir wrote:

>The problem is, the tables are created on the fly, so I don't know what
>columns the particular table will have.
Hi Lubomir,
That is indeed a problem. And it's also a sign that you are using the
database in a way that it's not intended to be used - applications that
need to create tables on the fly are almost always the result of some
bad design decisions.
Could you explain in some more detail WHY your application does not have
a fixed data model?

>From that reason, it would be very convenient to make query like SELECT *
>and to specify those 2 columns to be exclusive.
There is no syntax for this in SQL. (And if anyone ever proposes it, I'd
vote against it - personally, I'd rather remove the SELECT * than to
extend it!)
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
|||How do you manage, populate, update, etc., tables that you don't know the
structure of? How do you know you are getting the right number of columns,
with the correct names, in your UI application? How do you know that your
datasets will not break the front end? And if you're just not "displaying"
the help columns, then just don't "display" them. What you do or don't
display in the UI doesn't have to be the same as what you retrieve from the
database.
In all seriousness though, get rid of the SELECT * and start naming your
columns. It will save you a bunch of headaches down the road.
"Lubomir" <Lubomir@.discussions.microsoft.com> wrote in message
news:8587187A-53B8-4C53-A2DD-2F300FADFF3A@.microsoft.com...[vbcol=seagreen]
> The problem is, the tables are created on the fly, so I don't know what
> columns the particular table will have. I know however, every table has
> two
> columns ("help" columns) that will not be displayed, as they are used for
> another purposes.
> From that reason, it would be very convenient to make query like SELECT *
> and to specify those 2 columns to be exclusive.
> Thanks,
> Lubomir
> "Hari Prasad" wrote:
|||Hi Hugo,
The reason is, that that this application serves more application (like
plugins) with their own tables.
Lubomir
"Hugo Kornelis" wrote:

> On Fri, 4 May 2007 09:37:00 -0700, Lubomir wrote:
>
> Hi Lubomir,
> That is indeed a problem. And it's also a sign that you are using the
> database in a way that it's not intended to be used - applications that
> need to create tables on the fly are almost always the result of some
> bad design decisions.
> Could you explain in some more detail WHY your application does not have
> a fixed data model?
>
> There is no syntax for this in SQL. (And if anyone ever proposes it, I'd
> vote against it - personally, I'd rather remove the SELECT * than to
> extend it!)
> --
> Hugo Kornelis, SQL Server MVP
> My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
>
|||On Mon, 7 May 2007 09:38:00 -0700, Lubomir wrote:

>Hi Hugo,
>The reason is, that that this application serves more application (like
>plugins) with their own tables.
Hi Lubomir,
As long as the data requirements for all these plugin applications are
relatively stable, they can each have their own set of tables that
you'll have to design and deploy once (and possibly more often, if and
when requirements change).
If the data requirements can change on a daily basis, you're probably
best advised to move away from relational databases, since they're
really designed to be used with a fixed datamodel.
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
|||If you are talking about SQL 2005, then you can write DDL trigger and
programically create view or procedure wich select all columns from table
exept those 2.
Ramunas
"Lubomir" <Lubomir@.discussions.microsoft.com> wrote in message
news:07134C0B-DBB4-4B60-ADDD-BF2B2E808CB1@.microsoft.com...[vbcol=seagreen]
> Hi Hugo,
> The reason is, that that this application serves more application (like
> plugins) with their own tables.
> Lubomir
>
> "Hugo Kornelis" wrote:
*[vbcol=seagreen]
|||Yes, that could work.
Thanks,
Lubomir
"Ramunas Balukonis" wrote:

> If you are talking about SQL 2005, then you can write DDL trigger and
> programically create view or procedure wich select all columns from table
> exept those 2.
> Ramunas
> "Lubomir" <Lubomir@.discussions.microsoft.com> wrote in message
> news:07134C0B-DBB4-4B60-ADDD-BF2B2E808CB1@.microsoft.com...
> *
>
>

No comments:

Post a Comment