Professional Web Applications Themes

Selecting all columns, but in a different order in SELECT statement - Microsoft SQL / MS SQL Server

Thanks, i thought that was the case... just wanted to confirm it before i started working on it. Alex "Vishal Parkar" <vgparkarhotmail.com> wrote in message news:uNEVdtxQDHA.2476TK2MSFTNGP10.phx.gbl... > yes, you will have to make use of dynamic sql in this case. You can refer to > information_schema.columns view to pick up the required columns of the > table. > > -- > --Vishal > > "Alex" <nospamhotmail.com> wrote in message > news:3f070897$0$7733$fa0fcedblovejoy.zen.co.uk... > > Hi all, > > > > I have a table with a very large number of columns (around 200), which is > > used to populate a ...

  1. #1

    Default Re: Selecting all columns, but in a different order in SELECT statement

    Thanks, i thought that was the case... just wanted to confirm it before i
    started working on it.

    Alex


    "Vishal Parkar" <vgparkarhotmail.com> wrote in message
    news:uNEVdtxQDHA.2476TK2MSFTNGP10.phx.gbl...
    > yes, you will have to make use of dynamic sql in this case. You can refer
    to
    > information_schema.columns view to pick up the required columns of the
    > table.
    >
    > --
    > --Vishal
    >
    > "Alex" <nospamhotmail.com> wrote in message
    > news:3f070897$0$7733$fa0fcedblovejoy.zen.co.uk...
    > > Hi all,
    > >
    > > I have a table with a very large number of columns (around 200), which
    is
    > > used to populate a client application.
    > >
    > > I'm currently using a SELECT * FROM statement, but because I need the
    > > columns to appear in particular orders (depending on the scenario), I'm
    > now
    > > forced to do a
    > > SELECT col1, col2, col3... col200 FROM...
    > >
    > > or
    > > SELECT col2, col3, col5, col7.... col1, col6 etc
    > >
    > > (depending on the order we need them to appear in the application, or
    > > whatever component will display the data).
    > >
    > > This is very painstaking, especially as there's quite a large
    combination
    > > that we need to cover (about 20-30 SELECT statements in total, with more
    > to
    > > come!!!)
    > >
    > > Is there a way to do something like:
    > > SELECT col1, col2, col4, col5, col9, col20, * FROM...
    > >
    > > but replace the "*" with something else that means "the rest of the
    > > columns"? I don't want columns appearing twice in the same result set.
    > >
    > > I'm thinking of using dynamic SQL to do this, but was wondering if there
    > is
    > > any better way around it.
    > >
    > > Thanks,
    > > Alex
    > >
    > >
    >
    >

    Alex Guest

  2. #2

    Default Re: Selecting all columns, but in a different order in SELECT statement

    >> have a table with a very large number of columns (around 200), which
    is used to populate a client application. I'm currently using a SELECT
    * FROM statement, but because I need the columns to appear in particular
    orders <<

    Never use SELECT * in production code; it is a shorthand for interactive
    tools or for use in an [NOT] EXISTS (SELECT * FROM ...) predicate. If
    anyone modifies the table, then the * will use whatever was there when
    it was compiled. You never know what that might happen to be.

    I am trying to figure out how you got a 200+ column table; we used to
    have Cobol files like that in the 1960's. This sounds more like a
    "holding area" for a result set than a part of a data model.

    I am also trying to figure why the ordering of the *columns* matters to
    you. Rows, sure, since you need to declare a cursor with an ORDER BY
    clause. How are you putting the column values into host variables, as
    per your data model? The host program can re-order the fields in its
    records for output to a sequential file.

    In the meantime, you can use the schema information tables to get the
    column names to save yourself some typing.

    --CELKO--
    ===========================
    Please post DDL, so that people do not have to guess what the keys,
    constraints, Declarative Referential Integrity, datatypes, etc. in your
    schema are.

    *** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
    Don't just participate in USENET...get rewarded for it!
    Joe Celko Guest

  3. #3

    Default Re: Selecting all columns, but in a different order in SELECT statement

    Alex (nospamhotmail.com) writes:
    > I'm currently using a SELECT * FROM statement, but because I need the
    > columns to appear in particular orders (depending on the scenario), I'm
    > now forced to do a
    > SELECT col1, col2, col3... col200 FROM...
    >
    > or
    > SELECT col2, col3, col5, col7.... col1, col6 etc
    >
    > (depending on the order we need them to appear in the application, or
    > whatever component will display the data).
    >
    > This is very painstaking, especially as there's quite a large
    > combination that we need to cover (about 20-30 SELECT statements in
    > total, with more to come!!!)
    >
    > Is there a way to do something like:
    > SELECT col1, col2, col4, col5, col9, col20, * FROM...
    >
    > but replace the "*" with something else that means "the rest of the
    > columns"? I don't want columns appearing twice in the same result set.
    >
    > I'm thinking of using dynamic SQL to do this, but was wondering if there
    > is any better way around it.
    My first reaction is that this whole arrangement screams for a redesign.
    Must return columns in certain order! How about accessing the columns
    by their names instead?

    Or if this is because you want columns in certain order when you present
    them, how about performing the mapping in the client rather in SQL.

    And do you really need all 200 columns in all SELECTs? Will the user
    actualy look at them all?

    Yes, you will have to use dynamic SQL to fulfil all these requirements,
    but keep in mind that users need to be granted direct permissions for
    the tables.

    But as I said, I don't think you are on the right track.


    --
    Erland Sommarskog, SQL Server MVP, [email]sommaralgonet.se[/email]

    Books Online for SQL Server SP3 at
    [url]http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp[/url]
    Erland Sommarskog Guest

  4. #4

    Default Re: Selecting all columns, but in a different order in SELECT statement

    Unfortunately i don't have much of a choice as to how the database is
    designed, as it was in place already. The table in question is used to
    gather and hold sensor readings from around 200 sensors.

    The client applications use a stored procedure to retrieve the data and
    perform certain calculations on it, and the simplest way to perform some new
    caclulations that are needed is to modify the order the columns are returned
    from the SELECT statement in the stored proc (modifying the client app is
    not a straightforward task - don't ask why!!!).

    The whole system will be redesigned in the near future, but this is
    something we needed to do in the interim.

    Thanks for your input.

    Alex

    "Erland Sommarskog" <sommaralgonet.se> wrote in message
    news:Xns93AFF38E6529EYazorman127.0.0.1...
    > Alex (nospamhotmail.com) writes:
    > > I'm currently using a SELECT * FROM statement, but because I need the
    > > columns to appear in particular orders (depending on the scenario), I'm
    > > now forced to do a
    > > SELECT col1, col2, col3... col200 FROM...
    > >
    > > or
    > > SELECT col2, col3, col5, col7.... col1, col6 etc
    > >
    > > (depending on the order we need them to appear in the application, or
    > > whatever component will display the data).
    > >
    > > This is very painstaking, especially as there's quite a large
    > > combination that we need to cover (about 20-30 SELECT statements in
    > > total, with more to come!!!)
    > >
    > > Is there a way to do something like:
    > > SELECT col1, col2, col4, col5, col9, col20, * FROM...
    > >
    > > but replace the "*" with something else that means "the rest of the
    > > columns"? I don't want columns appearing twice in the same result set.
    > >
    > > I'm thinking of using dynamic SQL to do this, but was wondering if there
    > > is any better way around it.
    >
    > My first reaction is that this whole arrangement screams for a redesign.
    > Must return columns in certain order! How about accessing the columns
    > by their names instead?
    >
    > Or if this is because you want columns in certain order when you present
    > them, how about performing the mapping in the client rather in SQL.
    >
    > And do you really need all 200 columns in all SELECTs? Will the user
    > actualy look at them all?
    >
    > Yes, you will have to use dynamic SQL to fulfil all these requirements,
    > but keep in mind that users need to be granted direct permissions for
    > the tables.
    >
    > But as I said, I don't think you are on the right track.
    >
    >
    > --
    > Erland Sommarskog, SQL Server MVP, [email]sommaralgonet.se[/email]
    >
    > Books Online for SQL Server SP3 at
    > [url]http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp[/url]

    Alex Guest

  5. #5

    Default Re: Selecting all columns, but in a different order in SELECT statement

    Alex (nospamhotmail.com) writes:
    > Unfortunately i don't have much of a choice as to how the database is
    > designed, as it was in place already. The table in question is used to
    > gather and hold sensor readings from around 200 sensors.
    I did not actually mean to say that you should redesign the database.
    Rather I was thinking of a redesign of who the clients accesses the
    data.

    But when you mention it... It sound as it is worth considering, tilting
    the table, so rather than having one column per sensor, there should be
    one row per sensor. Makes it a lot easier to a add a new sensor. Or
    more data about a sensor than just a reading.

    I often hear this "have no choice, because this and that is this way".
    Maybe it is. But when one is about bend over backwards to implement
    something there is all reason what is the most expensive: make a
    redesign, or implement weird solutions that are hard to understand
    and maintain.

    Since you say that this system is anticipating a redesign in the future,
    my suggestion is that you drop the whole idea about dynamic SQL, and
    write the 200-column SELECT statements. Of course, writing such code
    is a bit error-prone, but do like this: Save a template on a file
    where you have the SELECT col1, col2, .... in a certain order. Then
    for each procedure you need to write, include that template, and select
    the columns that must appear in the beginning and drag them into place.
    As long as the template is correct, you can be sure that no column
    is missing or appear twice.

    This is maybe not as y as reading metadata, but the performance is
    better. And the code is easier to understand.

    --
    Erland Sommarskog, SQL Server MVP, [email]sommaralgonet.se[/email]

    Books Online for SQL Server SP3 at
    [url]http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp[/url]
    Erland Sommarskog Guest

  6. #6

    Default Re: Selecting all columns, but in a different order in SELECT statement

    Alex,

    On a rather different note, I'd be very interested to talk to you about the
    system you're working on, from a business perspective.

    Regards,
    Danny.


    "Alex" <nospamhotmail.com> wrote in message
    news:3f088f09$0$7720$fa0fcedblovejoy.zen.co.uk...
    > Unfortunately i don't have much of a choice as to how the database is
    > designed, as it was in place already. The table in question is used to
    > gather and hold sensor readings from around 200 sensors.
    >
    > The client applications use a stored procedure to retrieve the data and
    > perform certain calculations on it, and the simplest way to perform some
    new
    > caclulations that are needed is to modify the order the columns are
    returned
    > from the SELECT statement in the stored proc (modifying the client app is
    > not a straightforward task - don't ask why!!!).
    >
    > The whole system will be redesigned in the near future, but this is
    > something we needed to do in the interim.
    >
    > Thanks for your input.
    >
    > Alex
    >
    > "Erland Sommarskog" <sommaralgonet.se> wrote in message
    > news:Xns93AFF38E6529EYazorman127.0.0.1...
    > > Alex (nospamhotmail.com) writes:
    > > > I'm currently using a SELECT * FROM statement, but because I need the
    > > > columns to appear in particular orders (depending on the scenario),
    I'm
    > > > now forced to do a
    > > > SELECT col1, col2, col3... col200 FROM...
    > > >
    > > > or
    > > > SELECT col2, col3, col5, col7.... col1, col6 etc
    > > >
    > > > (depending on the order we need them to appear in the application, or
    > > > whatever component will display the data).
    > > >
    > > > This is very painstaking, especially as there's quite a large
    > > > combination that we need to cover (about 20-30 SELECT statements in
    > > > total, with more to come!!!)
    > > >
    > > > Is there a way to do something like:
    > > > SELECT col1, col2, col4, col5, col9, col20, * FROM...
    > > >
    > > > but replace the "*" with something else that means "the rest of the
    > > > columns"? I don't want columns appearing twice in the same result set.
    > > >
    > > > I'm thinking of using dynamic SQL to do this, but was wondering if
    there
    > > > is any better way around it.
    > >
    > > My first reaction is that this whole arrangement screams for a redesign.
    > > Must return columns in certain order! How about accessing the columns
    > > by their names instead?
    > >
    > > Or if this is because you want columns in certain order when you present
    > > them, how about performing the mapping in the client rather in SQL.
    > >
    > > And do you really need all 200 columns in all SELECTs? Will the user
    > > actualy look at them all?
    > >
    > > Yes, you will have to use dynamic SQL to fulfil all these requirements,
    > > but keep in mind that users need to be granted direct permissions for
    > > the tables.
    > >
    > > But as I said, I don't think you are on the right track.
    > >
    > >
    > > --
    > > Erland Sommarskog, SQL Server MVP, [email]sommaralgonet.se[/email]
    > >
    > > Books Online for SQL Server SP3 at
    > > [url]http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp[/url]
    >
    >

    Daniel Johnson Guest

Similar Threads

  1. SELECT * / Order of Columns
    By dj shane in forum Coldfusion Database Access
    Replies: 6
    Last Post: November 6th, 07:32 PM
  2. Selecting multi columns into one ?
    By Gurra in forum ASP Database
    Replies: 6
    Last Post: February 28th, 04:43 PM
  3. Replies: 0
    Last Post: July 2nd, 06:18 AM
  4. Replies: 3
    Last Post: April 18th, 12:52 PM
  5. Replies: 0
    Last Post: April 15th, 01:22 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139