Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Join Date
    Oct 2003
    Posts
    232

    Arrow Unanswered: Cursor from diff table -challenge

    I have 17 tables with names as Breaktable1, Breaktable2,.... till
    Breaktable17


    i make a query like this

    set nocount on

    Declare @sEventtable varchar(20)
    Declare @sstartzoneid varchar(20)
    Declare @ssttopzoneid varchar(20)



    Select @sstartzoneid ='1'
    Select @ssttopzoneid = '17'



    select @sstartzoneid = convert(smallint,@sstartzoneid)

    if @sstartzoneid<> 0
    DECLARE Tablecursor FOR <-------> ( error here )
    select status, sum(cost)
    FROM "breaktable " + @sstartzoneid
    where breakdate=DATEDIFF(day,'08/12/1960','03/29/2003')
    group by status


    CLOSE tableCursor
    DEALLOCATE tableCursor


    can somebody help ????
    bigfoots

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You can't use a variable that way in the DECLARE CURSOR. You may have to declare the table dynamically.

    Is now a good time to ask why you've got 17 tables when it seems that 1 would do quite nicely?

    -PatP

  3. #3
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    What do you want to do with the data accessed by the cursor? You can create a view comprised of all 17 tables with an artificially created field that would identify which table the data is coming from, and then do your cursor definition based on the value of that field. And of course, I agree with Pat 100%.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  4. #4
    Join Date
    Oct 2003
    Posts
    232
    I just want to read it for each day
    so that i can just change the date everyday i run the query

    Trust me it needed the 17 tables


    I am from oracle so a little tizzy in sql
    bigfoots

  5. #5
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Sorry, I don't trust anybody. I've seen 17-table implementation here, made by Anderson Consulting, and the company was paying them $180/hour. Just by looking at the names I can tell you that the design was not done by the person who understands either business or db design. How can I trust?
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I still can't see for the life of me any reason to have either 17 tables or a cursor. If those make you more comfortable, I guess that works Ok for me. I just can't see any reason for them.

    -PatP

  7. #7
    Join Date
    Oct 2003
    Posts
    232
    I agree , but i did not designed it i just have to use it

    so how do i make a query that will query all 17 tables and give me sum of cost and status


    cursor or no cursor ???


    table name break1 to break17
    bigfoots

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'd use:
    PHP Code:
    CREATE VIEW dbo.BigBreak AS
       
    SELECT 1 AS table_id, * FROM dbo.breaktable1
       UNION ALL SELECT  2
    , * FROM dbo.breaktable2
       UNION ALL SELECT  3
    , * FROM dbo.breaktable3
       UNION ALL SELECT  4
    , * FROM dbo.breaktable4
       UNION ALL SELECT  5
    , * FROM dbo.breaktable5
       UNION ALL SELECT  6
    , * FROM dbo.breaktable6
       UNION ALL SELECT  7
    , * FROM dbo.breaktable7
       UNION ALL SELECT  8
    , * FROM dbo.breaktable8
       UNION ALL SELECT  9
    , * FROM dbo.breaktable9
       UNION ALL SELECT 10
    , * FROM dbo.breaktable10
       UNION ALL SELECT 11
    , * FROM dbo.breaktable11
       UNION ALL SELECT 12
    , * FROM dbo.breaktable12
       UNION ALL SELECT 13
    , * FROM dbo.breaktable13
       UNION ALL SELECT 14
    , * FROM dbo.breaktable14
       UNION ALL SELECT 15
    , * FROM dbo.breaktable15
       UNION ALL SELECT 16
    , * FROM dbo.breaktable16
       UNION ALL SELECT 17
    , * FROM dbo.breaktable17
    GO 
    I'm not sure how well the optimizer will process it, but I think that it will do Ok. You'll definitely need to test it first.

    -PatP

  9. #9
    Join Date
    Apr 2004
    Posts
    12

    i did try this way but slow it is

    i went like this but takes about 30 mins to run

    select status, sum(cost) as zone1
    FROM event1
    where breakdate=DATEDIFF(day,'08/12/1960','04/15/2004')
    group by status


    select status, sum(cost) as zone2
    FROM event2
    where breakdate=DATEDIFF(day,'08/12/1960','04/15/2004')
    group by status

    TILL 18

    But takes about 35 mins for each run and slows all other user

    and then i have to do shut down restart to make it work nicely ????

    and the reason for 18 tables is that an event occurs and needs to be inserted(recorded) at 18 times at that exact moment (millisecond)
    Last edited by bigfoot; 04-22-04 at 17:20.

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    How did you jump from 17 to 18 tables? Why on earth do you need to record extremely time sensitive information 18 different times?

    -PatP

  11. #11
    Join Date
    Apr 2004
    Posts
    12

    hi

    we are an adsales company that insert ads on cable

    it has to insert those breaks in 18 zones at the same exact time

    whether its successfull or not it does an insert in each table.

  12. #12
    Join Date
    Apr 2004
    Posts
    12

    hi

    I am sure you can have a cursor within a cursor to do that

  13. #13
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    A cursor within a cursor ....

    I swear by the Holy book (guys here know what I mean) ... I would never even think of ever doing that ....
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  14. #14
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by bigfoot
    we are an adsales company that insert ads on cable

    it has to insert those breaks in 18 zones at the same exact time

    whether its successfull or not it does an insert in each table.
    Pat we got a CROSS THREAD JOIN going on here...

    In any case a Timezone type column is all you needed...

    Anderson Consulting...scrubs.....

    had to babysit everyone of them....

    I saw one created a 7 level nested cursor...wondered why it ran 7 hours....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  15. #15
    Join Date
    Apr 2004
    Posts
    12
    besides the time their are about 15 other fields that needs to be inserted and they are different !!!!

Posting Permissions

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