Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2004
    Location
    Kuala Lumpur
    Posts
    38

    Unanswered: error : sort key too long

    Hi guys,

    I write a query in coldfusion. In My query i will loop my query from min yr to max yr

    ex:
    Select
    <cfloop index="i" from="#min_year#" to="#max_year#" step="1">
    SUM(DECODE(TO_CHAR(wbs_budget_date, 'YYYY'), #i#, wbs_budget_amt,NULL)) yr#i#,
    SUM(DECODE(TO_CHAR(wbs_budget_date, 'YYYY'), #i#, wba.Monte_Carlo_Min, NULL)) Monte_Carlo_Min#i#,
    SUM(DECODE(TO_CHAR(wbs_budget_date, 'YYYY'), #i#, wba.Monte_Carlo_Max, NULL)) Monte_Carlo_Max#i#,
    SUM(DECODE(TO_CHAR(wbs_budget_date, 'YYYY'), #i#, wba.Monte_Carlo_Likeliest, NULL)) Monte_Carlo_Likeliest#i#,

    MAX(DECODE(TO_CHAR(wbs_budget_date, 'YYYY'), #i#, substr(wba.Comment_Text,1,100), NULL)) Comment_Text#i#,
    MAX(DECODE(TO_CHAR(wbs_budget_date, 'YYYY'), #i#, wba.Monte_Carlo_Distribution_Type, NULL)) MonteCarloDistribution#i#,
    MAX(DECODE(TO_CHAR(wbs_budget_date, 'YYYY'), #i#, wba.Discretionary_Spending_Status, NULL)) DiscretionarySpending#i# ,

    SUM(DECODE(TO_CHAR(wbs_budget_date, 'YYYY'), #i#, wba.MONTE_CARLO_MAX_PER, NULL)) Monte_Carlo_Max_Per#i#,
    SUM(DECODE(TO_CHAR(wbs_budget_date, 'YYYY'), #i#, wba.MONTE_CARLO_MIN_PER, NULL)) Monte_Carlo_Min_Per#i#,
    SUM(DECODE(TO_CHAR(wbs_budget_date, 'YYYY'), #i#, wba.MONTE_CARLO_LIKELIEST_PER, NULL)) Monte_Carlo_Likeliest_Per#i#
    </cfloop>
    From table name.

    So if total dif years in more than 30 years, it will return error"sort key too long " So now I need any ideas how to prevent this from happened. I will appriciate any ideas from you guys. Really need yr help. Thx

    Regards,
    Shaffiq

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    I don't imagine the problem is with these bits, they don't involve any sorting. What does the rest of the query look like?

  3. #3
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Talking


    Error: ORA-1467
    Text: sort key too long
    -------------------------------------------------------------------------------
    Cause: A DISTINCT, GROUP BY, ORDER BY, or SET operation requires
    a sort key longer than that supported by Oracle. Either
    too many columns or too many group functions were specified
    in the SELECT statement.
    Action: Reduce the number of columns or group functions involved
    in the operation.

    Check out Metalink Note:18870.1

    The error in your SQL statement may be caused by the MAX() functions which require some type of "sorting" and the key for these functions may be larger that one database block:

    Code:
    substr(wba.Comment_Text,1,100)             100 Characters
    wba.Monte_Carlo_Distribution_Type        + ???
    wba.Discretionary_Spending_Status        + ???
                                            ------
                                             = ???  X 30 = 3000++

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  4. #4
    Join Date
    Oct 2004
    Location
    Kuala Lumpur
    Posts
    38
    Can I create function or sp for this? how to put loop in function or sp?
    here the full statement : -

    ELECT wth.wbs_template_header_cd wbs_template_header_code, wtl.wbs_template_level_cd
    wbs_template_level_code, wth.wbs_template_header_name wbs_template_header_name,
    wtl.wbs_template_level_name wbs_template_level_name, wtl.wbs_template_level_id
    wbs_template_level_id, wlac.comment_text wbs_template_level_comment,
    <cfloop index="i" from="#min_year#" to="#max_year#" step="1">
    <cfif i EQ "#max_year#">
    SUM(DECODE(TO_CHAR(wbs_budget_date, 'YYYY'), #i#, wbs_budget_amt,NULL)) yr#i#,
    SUM(DECODE(TO_CHAR(wbs_budget_date, 'YYYY'), #i#, wba.Monte_Carlo_Min, NULL)) Monte_Carlo_Min#i#,
    SUM(DECODE(TO_CHAR(wbs_budget_date, 'YYYY'), #i#, wba.Monte_Carlo_Max, NULL)) Monte_Carlo_Max#i#,
    SUM(DECODE(TO_CHAR(wbs_budget_date, 'YYYY'), #i#, wba.Monte_Carlo_Likeliest, NULL)) Monte_Carlo_Likeliest#i#,

    MAX(DECODE(TO_CHAR(wbs_budget_date, 'YYYY'), #i#, substr(wba.Comment_Text,1,100), NULL)) Comment_Text#i#,
    MAX(DECODE(TO_CHAR(wbs_budget_date, 'YYYY'), #i#, wba.Monte_Carlo_Distribution_Type, NULL)) MonteCarloDistribution#i#,
    MAX(DECODE(TO_CHAR(wbs_budget_date, 'YYYY'), #i#, wba.Discretionary_Spending_Status, NULL)) DiscretionarySpending#i# ,

    SUM(DECODE(TO_CHAR(wbs_budget_date, 'YYYY'), #i#, wba.MONTE_CARLO_MAX_PER, NULL)) Monte_Carlo_Max_Per#i#,
    SUM(DECODE(TO_CHAR(wbs_budget_date, 'YYYY'), #i#, wba.MONTE_CARLO_MIN_PER, NULL)) Monte_Carlo_Min_Per#i#,
    SUM(DECODE(TO_CHAR(wbs_budget_date, 'YYYY'), #i#, wba.MONTE_CARLO_LIKELIEST_PER, NULL)) Monte_Carlo_Likeliest_Per#i#

    <cfelse>

    SUM(DECODE(TO_CHAR(wbs_budget_date, 'YYYY'), #i#, wbs_budget_amt,NULL)) yr#i#,
    SUM(DECODE(TO_CHAR(wbs_budget_date, 'YYYY'), #i#, wba.Monte_Carlo_Min, NULL)) Monte_Carlo_Min#i#,
    SUM(DECODE(TO_CHAR(wbs_budget_date, 'YYYY'), #i#, wba.Monte_Carlo_Max, NULL)) Monte_Carlo_Max#i#,
    SUM(DECODE(TO_CHAR(wbs_budget_date, 'YYYY'), #i#, wba.Monte_Carlo_Likeliest, NULL)) Monte_Carlo_Likeliest#i#,

    MAX(DECODE(TO_CHAR(wbs_budget_date, 'YYYY'), #i#, substr(wba.Comment_Text,1,100),NULL)) Comment_Text#i#,
    MAX(DECODE(TO_CHAR(wbs_budget_date, 'YYYY'), #i#, wba.Monte_Carlo_Distribution_Type, NULL)) MonteCarloDistribution#i#,
    MAX(DECODE(TO_CHAR(wbs_budget_date, 'YYYY'), #i#, wba.Discretionary_Spending_Status, NULL)) DiscretionarySpending#i#,

    SUM(DECODE(TO_CHAR(wbs_budget_date, 'YYYY'), #i#, wba.MONTE_CARLO_MAX_PER, NULL)) Monte_Carlo_Max_Per#i#,
    SUM(DECODE(TO_CHAR(wbs_budget_date, 'YYYY'), #i#, wba.MONTE_CARLO_MIN_PER, NULL)) Monte_Carlo_Min_Per#i#,
    SUM(DECODE(TO_CHAR(wbs_budget_date, 'YYYY'), #i#, wba.MONTE_CARLO_LIKELIEST_PER, NULL)) Monte_Carlo_Likeliest_Per#i#,
    </cfif>
    </cfloop>
    FROM wbs_template_header wth, wbs_template_level wtl, wbs_level_amt_comment
    wlac, wbs_budget_amt wba WHERE wth.wbs_template_header_id = wtl.wbs_template_header_id
    AND wtl.wbs_template_level_id = wlac.wbs_template_level_id (+) AND wlac.option_id
    (+)=
    <cfqueryparam value="#forecast_option_selected#">
    AND wlac.prj_version_id (+)=
    <cfqueryparam value="#project_version_id#">
    AND wlac.prj_master_id (+)=
    <cfqueryparam value="#project_master_id#">
    AND wtl.wbs_template_level_id = wba.wbs_template_level_id (+) AND wba.option_id
    (+)=
    <cfqueryparam value="#forecast_option_selected#">
    AND wba.prj_version_id (+)=
    <cfqueryparam value="#project_version_id#">
    AND wba.prj_master_id (+)=
    <cfqueryparam value="#project_master_id#">
    GROUP BY wth.wbs_template_header_cd, wtl.wbs_template_level_cd, wth.wbs_template_header_name,
    wtl.wbs_template_level_name, wtl.wbs_template_level_id, wlac.comment_text
    </cfquery>

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    See this AskTom thread. It looks like you have hit a limit when having so many aggregate function calls in your SELECT (10 functions * 30 years = 300 functions). Perhaps it would be best to simply add TO_CHAR(wbs_budget_date, 'YYYY') to the GROUP BY clause, select the 10 function values and then perform the transformation from 30 rows with 10 values to 1 row with 300 values in your ColdFusion application.

Posting Permissions

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