Professional Web Applications Themes

converting an Access Query - Microsoft SQL / MS SQL Server

I have a query that needs converting to a stored procedure...goes something like this: Select field1, field2, field1 + field 2 as field3, field3 + 5 as field4, field4 + 492 as field 5....etc. There are about 6 actual fields, and a number of fields that are calculated based on other fields that are also calculated (fields 4 and 5 above). Access allows this, but I can't figure out how to convince SQL Server to do the same thing. The purpose is to pull a list of clients whose reports are "due", based on the contract date that is in ...

  1. #1

    Default converting an Access Query

    I have a query that needs converting to a stored procedure...goes something
    like this:

    Select
    field1,
    field2,
    field1 + field 2 as field3,
    field3 + 5 as field4,
    field4 + 492 as field 5....etc.

    There are about 6 actual fields, and a number of fields that are calculated
    based on other fields that are also calculated (fields 4 and 5 above).
    Access allows this, but I can't figure out how to convince SQL Server to do
    the same thing.

    The purpose is to pull a list of clients whose reports are "due", based on
    the contract date that is in the client table. I do not have the luxury of
    re-designing this very poorly writen db (yet).

    BTW...this thing has a ton of nested IIFs as well.....:-)

    Thanks for all suggestions!


    --
    Kevin Hill
    3NF Consulting
    www.3nf-inc.com




    Kevin Guest

  2. #2

    Default Re: converting an Access Query

    Kevin
    You can use either derived tables or do it with one select statement
    CREATE TABLE TEST
    (
    COL1 INT NOT NULL PRIMARY KEY,
    COL2 INT NOT NULL
    )

    INSERT INTO TEST VALUES (1,2)
    INSERT INTO TEST VALUES (2,3)
    INSERT INTO TEST VALUES (3,4)

    SELECT *,COL4+4 AS COL5
    FROM
    (
    SELECT *,COL3+5 AS COL4 FROM
    (
    SELECT COL1,COL2,COL1+COL2 AS COL3 FROM TEST
    ) AS D
    ) AS F
    ---OR

    SELECT COL1,COL2,COL1+COL2 AS COL3,COL1+COL2+5 AS COL4,COL1+COL2+5+400 AS
    COL5
    FROM TEST


    DROP TABLE TEST



    "Kevin 3NF" <com> wrote in message
    news:phx.gbl... 
    something 
    calculated 
    do 
    of 


    Uri Guest

  3. #3

    Default Re: converting an Access Query

    > BTW...this thing has a ton of nested IIFs as well.....:-)

    In SQL use CASE... ENDCASE instead of IIF. See BOL for details.

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --



    David Guest

  4. #4

    Default Re: converting an Access Query

    Yep....knew that....but the IIFs reference back to the newly calculated
    fields. There are 17 IIF statements in this one query, which is a
    fundamental query to the entire reporting side of the application.

    Thanks Uri and David!

    --
    Kevin Hill
    3NF Consulting
    www.3nf-inc.com


    "David Portas" <org> wrote in message
    news:%phx.gbl... 
    >
    > In SQL use CASE... ENDCASE instead of IIF. See BOL for details.
    >
    > --
    > David Portas
    > ------------
    > Please reply only to the newsgroup
    > --
    >
    >
    >[/ref]


    Kevin Guest

Similar Threads

  1. Converting a CSV to a Query
    By Wally in forum Coldfusion Database Access
    Replies: 2
    Last Post: October 18th, 07:34 PM
  2. Replies: 1
    Last Post: August 11th, 06:47 AM
  3. Converting a QMF SQL query to Cobol Pgm !!
    By Jagster in forum IBM DB2
    Replies: 1
    Last Post: July 26th, 01:33 AM

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