Results 1 to 3 of 3

Thread: Rank Query

  1. #1
    Join Date
    Nov 2004
    Posts
    2

    Post Unanswered: Rank Query

    I am trying to get a data rank based off of a table (magazine) and a view (mag_perf_summary_view). The magazine table contains the cost, retail and bipad, issue etc... mag_perf_summary_view contains basic sales activity of a magazine.

    I'm using Sybase 11.9.2 and I'm getting syntax errors but I don't 'see' what to change... The subquerys in the from clause run with no errors. Anyway here's the query...


    1. SELECT COUNT(*) AS RANK, t1.BIPAD, t1.PROFIT
    2. FROM (
    3. SELECT BIPAD=m.title_number,
    4. PROFIT=SUM((m.retail_price - m.customer_cost) * p.total_net_sold_qty)
    5. FROM magazine m, mag_perf_summary_view p
    6. WHERE (m.product_id = p.product_id)
    7. AND m.on_sale_date
    8. BETWEEN (DATEADD(DAY, - 455, CONVERT(datetime, CONVERT(CHAR(11), GETDATE(), 101))))
    9. AND (DATEADD(DAY, - 90, CONVERT(datetime, CONVERT(CHAR(11), GETDATE(), 101))))
    10. GROUP BY m.title_number
    11. ) t1,
    12. (
    13. SELECT BIPAD=m.title_number,
    14. PROFIT=SUM((m.retail_price - m.customer_cost) * p.total_net_sold_qty)
    15. FROM magazine m, mag_perf_summary_view p
    16. WHERE (m.product_id = p.product_id)
    17. AND m.on_sale_date
    18. BETWEEN (DATEADD(DAY, - 455, CONVERT(datetime, CONVERT(CHAR(11), GETDATE(), 101))))
    19. AND (DATEADD(DAY, - 90, CONVERT(datetime, CONVERT(CHAR(11), GETDATE(), 101))))
    20. GROUP BY m.title_number
    21. ) t2
    22. WHERE t1.PROFIT >= t2.PROFIT
    23. GROUP BY t1.BIPAD
    24. ORDER BY t1.BIPAD


    Sorry for any formatting issues.

    The errors I get are...

    Line 2: Incorrect syntax near '('
    Line 11: Incorrect syntax near 't1'
    Line 21: Incorrect syntax near 't2'

    Any help is appreciated.

    Tim

  2. #2
    Join Date
    Dec 2003
    Location
    FRANCE (Paris)
    Posts
    23
    Hi,

    Derived table are only available in ASE 12.5.1.
    Your query doesn't work in ASE 11.9.2

    Mickael

  3. #3
    Join Date
    Nov 2004
    Posts
    2
    Quote Originally Posted by danmick
    Hi,

    Derived table are only available in ASE 12.5.1.
    Your query doesn't work in ASE 11.9.2

    Mickael


    Thanks danmick. Not what I wanted to hear but... atleast I know.

    Tim

Posting Permissions

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