Professional Web Applications Themes

Normalization causing cursor nightmare. - Microsoft SQL / MS SQL Server

I have been working on converting a set of procedures accessing data on a SQL 6.5 box to a completely different set of data on a SQL 7 box. A problem that I have ran into plays out as follows. This is the schema of the SQL 6.5 table: CREATE TABLE dbo.Prop ( Type char(5) NOT NULL, LoanType char(5) NOT NULL) INSERT Prop VALUES ('FORD', 'CASH') INSERT Prop VALUES ('FORD', 'LOAN') INSERT Prop VALUES ('CHEVY', 'CASH') INSERT Prop VALUES ('CHEVY', 'LOAN') INSERT Prop VALUES ('CHEVY', 'CASH') INSERT Prop VALUES ('CHEVY', 'CASH') INSERT Prop VALUES ('FORD', 'TRADE') I have the same ...

  1. #1

    Default Normalization causing cursor nightmare.

    I have been working on converting a set of procedures accessing data on a
    SQL 6.5 box to a completely different set of data on a SQL 7 box. A problem
    that I have ran into plays out as follows.

    This is the schema of the SQL 6.5 table:

    CREATE TABLE dbo.Prop
    ( Type char(5) NOT NULL,
    LoanType char(5) NOT NULL)

    INSERT Prop VALUES ('FORD', 'CASH')
    INSERT Prop VALUES ('FORD', 'LOAN')
    INSERT Prop VALUES ('CHEVY', 'CASH')
    INSERT Prop VALUES ('CHEVY', 'LOAN')
    INSERT Prop VALUES ('CHEVY', 'CASH')
    INSERT Prop VALUES ('CHEVY', 'CASH')
    INSERT Prop VALUES ('FORD', 'TRADE')


    I have the same data in SQL 7 system but a little more normalized:

    CREATE TABLE dbo.Ford
    ( Type char(4) NOT NULL,
    LoanType char(5) NOT NULL)

    INSERT Prop VALUES ('FORD', 'CASH')
    INSERT Prop VALUES ('FORD', 'LOAN')
    INSERT Prop VALUES ('FORD', 'TRADE')

    CREATE TABLE dbo.Chevy
    ( Type char(5) NOT NULL,
    LoanType char(4) NOT NULL)

    INSERT Prop VALUES ('CHEVY', 'CASH')
    INSERT Prop VALUES ('CHEVY', 'LOAN')
    INSERT Prop VALUES ('CHEVY', 'CASH')
    INSERT Prop VALUES ('CHEVY', 'CASH')

    I have a cursor declared with the following select statement:

    select loantype, convert(decimal(5,2),(count(*) * 100.00) / (SALCYTD *
    1.00)) -- SALCYTD is the number of cars sold from another step, 14 in this
    example.
    from prop
    group by prop.loantype

    The cursor works fine in 6.5 with this logic, there has been logic removed.
    The problem occurs when I try to split the declaration portion of the cursor
    into 2 select statements to pull from the Ford and Chevy tables in the
    "normalized" SQL 7 db. Since I need them grouped by loantype and total
    percentage of each loantype I can't seem to find a way to do this.

    I would go into what I have tried but it obviously didn't work.

    Thanks
    Charlie


    Charles Guest

  2. #2

    Default Re: Normalization causing cursor nightmare.

    It is hard to say what went wrong because there is so much code missing....

    Why not create a view that combines your "normalized" data and reference that within your cursor?

    The view would look something like this:
    CREATE VIEW Prop AS
    SELECT type, loantype FROM dbo.Ford
    UNION
    SELECT type, loantype FROM dbo.Chevy


    --
    Keith, SQL Server MVP

    "Charles Deaton" <realcomp.com> wrote in message news:phx.gbl... 
    Keith Guest

  3. #3

    Default Re: Normalization causing cursor nightmare.

    Neither design appears to be normalized. First, what is the PK? You have multiple rows for ('CHEVY', 'CASH'). Also, I doubt if you really need a cursor. Perhaps you can give use the business requirements and then we can put together a data model. From that, the query should be straightforward.

    --
    Tom

    ---------------------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional
    Toronto, ON Canada
    www.pinnaclepublishing.com/sql


    "Charles Deaton" <realcomp.com> wrote in message news:phx.gbl...
    I have been working on converting a set of procedures accessing data on a
    SQL 6.5 box to a completely different set of data on a SQL 7 box. A problem
    that I have ran into plays out as follows.

    This is the schema of the SQL 6.5 table:

    CREATE TABLE dbo.Prop
    ( Type char(5) NOT NULL,
    LoanType char(5) NOT NULL)

    INSERT Prop VALUES ('FORD', 'CASH')
    INSERT Prop VALUES ('FORD', 'LOAN')
    INSERT Prop VALUES ('CHEVY', 'CASH')
    INSERT Prop VALUES ('CHEVY', 'LOAN')
    INSERT Prop VALUES ('CHEVY', 'CASH')
    INSERT Prop VALUES ('CHEVY', 'CASH')
    INSERT Prop VALUES ('FORD', 'TRADE')


    I have the same data in SQL 7 system but a little more normalized:

    CREATE TABLE dbo.Ford
    ( Type char(4) NOT NULL,
    LoanType char(5) NOT NULL)

    INSERT Prop VALUES ('FORD', 'CASH')
    INSERT Prop VALUES ('FORD', 'LOAN')
    INSERT Prop VALUES ('FORD', 'TRADE')

    CREATE TABLE dbo.Chevy
    ( Type char(5) NOT NULL,
    LoanType char(4) NOT NULL)

    INSERT Prop VALUES ('CHEVY', 'CASH')
    INSERT Prop VALUES ('CHEVY', 'LOAN')
    INSERT Prop VALUES ('CHEVY', 'CASH')
    INSERT Prop VALUES ('CHEVY', 'CASH')

    I have a cursor declared with the following select statement:

    select loantype, convert(decimal(5,2),(count(*) * 100.00) / (SALCYTD *
    1.00)) -- SALCYTD is the number of cars sold from another step, 14 in this
    example.
    from prop
    group by prop.loantype

    The cursor works fine in 6.5 with this logic, there has been logic removed.
    The problem occurs when I try to split the declaration portion of the cursor
    into 2 select statements to pull from the Ford and Chevy tables in the
    "normalized" SQL 7 db. Since I need them grouped by loantype and total
    percentage of each loantype I can't seem to find a way to do this.

    I would go into what I have tried but it obviously didn't work.

    Thanks
    Charlie



    Tom Guest

  4. #4

    Default Re: Normalization causing cursor nightmare.


    "Charles Deaton" <realcomp.com> wrote in message
    news:phx.gbl... 
    problem 
    this 
    removed. 
    cursor 

    I can't make any sense out of this. These tables are unusable in their 6.5
    form, and they are not fixed yet. Once the tables are sane, the queries
    will be easy.

    What are these tables supposed to be? What type of entity is stored there.
    But most of all, why are there duplicate rows?!?!?

    Explain a little more about what you are trying to acomplish, and perhaps
    post some more DDL of related tables.

    David


    David Guest

  5. #5

    Default Re: Normalization causing cursor nightmare.

    Okay lets skip the DDL, it is fictional anyways, and cut to the heart of the
    problem. I need to group by one attribute in 2 different tables, For example
    a LOANTYPE. The result is the declaration statement of a cursor. Is this
    clear? It the example mentioned below it needs to return two values, the
    LOANTYPE and the percentage of loans that were of that type. The other logic
    in the procedure warrants the use of a cursor by the way, trust me I would
    prefer not using them.

    Charlie.



    "Charles Deaton" <realcomp.com> wrote in message
    news:phx.gbl... 
    problem 
    this 
    removed. 
    cursor 


    Charlie Guest

  6. #6

    Default Re: Normalization causing cursor nightmare.

    Try what Keith said, except use UNION ALL:

    The view would look something like this:
    CREATE VIEW Prop AS
    SELECT type, loantype FROM dbo.Ford
    UNION ALL
    SELECT type, loantype FROM dbo.Chevy


    If that's not it, type out the exact sequence of rows you
    want your cursor to traverse, given the sample data you
    provided. Without a clear statement of what you need,
    you won't get a clear statement of how to get it.

    SK

    Charlie wrote:
     
    >>There are no keys, so these things are not tables by definition. And
    >>the Ford and Chevy table are very denormalized -- Chris date calls it
    >>orthogonal design and I call it attribute splitting, which is a more
    >>dscriptive name (take an attribute and make each of its values into a
    >>table).
    >>
    >>Making a wild guess, I think you want the percentage of sales by make of
    >>auto.
    >>
    >>CREATE TABLE AutoSales
    >>(vin CHAR(17) NOT NULL PRIMARY KEY,
    >> ...
    >> make CHAR(5) NOT NULL,
    >> loan_type CHAR(1)NOT NULL
    >> CHECK(loan_type IN ('C', 'T','L')
    >> ...);
    >>
    >>SELECT loan_type, make,
    >> (COUNT(*) * 100.00)/(SELECT COUNT(*) FROM AutoSales)
    >> FROM AutoSales
    >> GROUP BY loan_type, make;
    >>
    >>Why are you doing things with cursors and in steps? That is procedural
    >>code and not SQL.
    >>
    >>--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 http://www.developersdex.com ***
    >>Don't just participate in USENET...get rewarded for it!
    >>
    >>[/ref]
    >
    >
    >
    >[/ref]

    Steve Guest

  7. #7

    Default Re: Normalization causing cursor nightmare.

    >> I just need to find the loan type and percentage of each from two
    separate table grouping by the loan type ..<<

    SELECT loan_type, make,
    (COUNT(*) * 100.00)/(SELECT COUNT(*) FROM AutoSales)
    FROM (StupidTable_1
    UNION ALL
    Stupidtable_2) AS AutoSales (loan_type, make, ...)
    GROUP BY loan_type, make;
     [/ref]

    Why? Poor performance and proprietary code are usually not part of a
    the specs. You can also use a VIEW, if you need to fake a correct
    design in several places.
     [/ref]
    described and have no recourse but to work with what I have without
    re-writing applications or designing a new database. <<

    Now, how did the moron that built this ensure the attribute split tables
    are disjoint? I'll bet he did not do anything and that the data is
    already in error.

    The horrible truth is that you DO need to design a new database ...

    --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 http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Joe Guest

  8. #8

    Default Re: Normalization causing cursor nightmare.

    Are you sure this UNION ALL between two table names works in MSSQL7? I can't
    seem to get it to work nor does BOL indicate that this is the proper
    form/syntax of TSQL.

    "Joe Celko" <edu> wrote in message
    news:%phx.gbl... [/ref]
    > separate table grouping by the loan type ..<<
    >
    > SELECT loan_type, make,
    > (COUNT(*) * 100.00)/(SELECT COUNT(*) FROM AutoSales)
    > FROM (StupidTable_1
    > UNION ALL
    > Stupidtable_2) AS AutoSales (loan_type, make, ...)
    > GROUP BY loan_type, make;
    > [/ref]
    >
    > Why? Poor performance and proprietary code are usually not part of a
    > the specs. You can also use a VIEW, if you need to fake a correct
    > design in several places.
    > [/ref]
    > described and have no recourse but to work with what I have without
    > re-writing applications or designing a new database. <<
    >
    > Now, how did the moron that built this ensure the attribute split tables
    > are disjoint? I'll bet he did not do anything and that the data is
    > already in error.
    >
    > The horrible truth is that you DO need to design a new database ...
    >
    > --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 http://www.developersdex.com ***
    > Don't just participate in USENET...get rewarded for it![/ref]


    Charlie Guest

  9. #9

    Default Re: Normalization causing cursor nightmare.

    >> Are you sure this UNION ALL between two table names works in MSSQL7?
    <<

    I amj missing a paren and you have to change it to "SELECT * FROM ..."

    --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 http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!

    Joe Guest

Similar Threads

  1. mysql database design normalization question
    By manunderstress in forum MySQL
    Replies: 2
    Last Post: March 8th, 11:05 PM
  2. [OT] Normalization
    By Håvard in forum PHP Development
    Replies: 35
    Last Post: February 7th, 02:42 PM
  3. cursor 200-problem on mac but not pc? how to swap cursor image?
    By nickelsock webforumsuser@macromedia.com in forum Macromedia Director Lingo
    Replies: 5
    Last Post: August 2nd, 10:58 AM
  4. Change the "web hand" cursor in normal arrow cursor?
    By FB1976 in forum Adobe Dreamweaver & Contribute
    Replies: 1
    Last Post: July 9th, 07:15 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