[jira] Assigned: (DERBY-3373) SQL distinct and order by needed together
[ https://issues.apache.org/jira/browse/DERBY-3373?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Dyre Tjeldvoll reassigned DERBY-3373: - Assignee: Dyre Tjeldvoll (was: Bryan Pendleton) SQL distinct and order by needed together - Key: DERBY-3373 URL: https://issues.apache.org/jira/browse/DERBY-3373 Project: Derby Issue Type: Bug Components: SQL Affects Versions: 10.3.2.1 Environment: Solaris Dev Express, Java 5 Reporter: Thomas Vatter Assignee: Dyre Tjeldvoll Priority: Blocker Fix For: 10.3.2.2, 10.4.0.0 Attachments: allowExpressions.diff, mergeWith2351.diff I am pasting here the communication from the mailinglist. I am having a blocking and large problem with it because I have to make a release that needs the specified SQL query. tom_ wrote: The errormessage is The ORDER BY clause may not specify an expression, since the query specifies DISTINCT [Error Code: 2] [SQL State: 4287A] The statement is select distinct t1.t1_id, t2.t2value1, t2.t2value2, t2.t2value3 from t1, t2, t3 where ... order by lower(t2.t2value2) , lower(t2.t2value1) , lower(t2.t2value3) Dyre.Tjeldvoll wrote: tom_ [EMAIL PROTECTED] writes: I am using disctinct because of some self-joins and also needed to add an order by clause. An error is shown. Is it not possible to use distinct and order by together? I think it is allowed. Executing select distinct * from sys.systables order by tablename; in ij works just fine. Could you show the error message you get, and perhaps what the table looks like? -- dt « [hide part of quote] Hi Tom - I see what you mean using the demo DB toursDB: ij select * from airlines order by lower(airline_full); A|AIRLINE_FULL|BASIC_RATE|DISTANCE_DISCOUNT |BUSINESS_LEVEL_FACTOR |FIRSTCLASS_LEVEL_FACT|ECONOMY_SE|BUSINESS_S|FIRSTCLASS --- AA|Amazonian Airways |0.18 |0.03 |0.5 |1.5 |20 |10 |5 US|Union Standard Airlines |0.19 |0.05 |0.4 |1.6 |20 |10 |5 2 rows selected ij select distinct * from airlines order by lower(airline_full); ERROR 4287A: The ORDER BY clause may not specify an expression, since the query specifies DISTINCT. ij select distinct airline_full from airlines order by lower(airline_full); ERROR 4287A: The ORDER BY clause may not specify an expression, since the query specifies DISTINCT. ij I didn't find a JIRA enhancement to remove this restriction. I suggest you file an Enhancement request to remove the restriction reported by ERROR 4287A. -- This message is automatically generated by JIRA. - You can reply to this email to add a comment to the issue online.
[jira] Assigned: (DERBY-3373) SQL distinct and order by needed together
[ https://issues.apache.org/jira/browse/DERBY-3373?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Dyre Tjeldvoll reassigned DERBY-3373: - Assignee: Bryan Pendleton (was: Dyre Tjeldvoll) Sorry. I changed the wrong issue. Changing the assignee back to Bryan. SQL distinct and order by needed together - Key: DERBY-3373 URL: https://issues.apache.org/jira/browse/DERBY-3373 Project: Derby Issue Type: Bug Components: SQL Affects Versions: 10.3.2.1 Environment: Solaris Dev Express, Java 5 Reporter: Thomas Vatter Assignee: Bryan Pendleton Priority: Blocker Fix For: 10.3.2.2, 10.4.0.0 Attachments: allowExpressions.diff, mergeWith2351.diff I am pasting here the communication from the mailinglist. I am having a blocking and large problem with it because I have to make a release that needs the specified SQL query. tom_ wrote: The errormessage is The ORDER BY clause may not specify an expression, since the query specifies DISTINCT [Error Code: 2] [SQL State: 4287A] The statement is select distinct t1.t1_id, t2.t2value1, t2.t2value2, t2.t2value3 from t1, t2, t3 where ... order by lower(t2.t2value2) , lower(t2.t2value1) , lower(t2.t2value3) Dyre.Tjeldvoll wrote: tom_ [EMAIL PROTECTED] writes: I am using disctinct because of some self-joins and also needed to add an order by clause. An error is shown. Is it not possible to use distinct and order by together? I think it is allowed. Executing select distinct * from sys.systables order by tablename; in ij works just fine. Could you show the error message you get, and perhaps what the table looks like? -- dt « [hide part of quote] Hi Tom - I see what you mean using the demo DB toursDB: ij select * from airlines order by lower(airline_full); A|AIRLINE_FULL|BASIC_RATE|DISTANCE_DISCOUNT |BUSINESS_LEVEL_FACTOR |FIRSTCLASS_LEVEL_FACT|ECONOMY_SE|BUSINESS_S|FIRSTCLASS --- AA|Amazonian Airways |0.18 |0.03 |0.5 |1.5 |20 |10 |5 US|Union Standard Airlines |0.19 |0.05 |0.4 |1.6 |20 |10 |5 2 rows selected ij select distinct * from airlines order by lower(airline_full); ERROR 4287A: The ORDER BY clause may not specify an expression, since the query specifies DISTINCT. ij select distinct airline_full from airlines order by lower(airline_full); ERROR 4287A: The ORDER BY clause may not specify an expression, since the query specifies DISTINCT. ij I didn't find a JIRA enhancement to remove this restriction. I suggest you file an Enhancement request to remove the restriction reported by ERROR 4287A. -- This message is automatically generated by JIRA. - You can reply to this email to add a comment to the issue online.
[jira] Assigned: (DERBY-3373) SQL distinct and order by needed together
[ https://issues.apache.org/jira/browse/DERBY-3373?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Bryan Pendleton reassigned DERBY-3373: -- Assignee: Bryan Pendleton SQL distinct and order by needed together - Key: DERBY-3373 URL: https://issues.apache.org/jira/browse/DERBY-3373 Project: Derby Issue Type: Bug Components: SQL Affects Versions: 10.3.2.1 Environment: Solaris Dev Express, Java 5 Reporter: Thomas Vatter Assignee: Bryan Pendleton Priority: Blocker Fix For: 10.3.2.2 I am pasting here the communication from the mailinglist. I am having a blocking and large problem with it because I have to make a release that needs the specified SQL query. tom_ wrote: The errormessage is The ORDER BY clause may not specify an expression, since the query specifies DISTINCT [Error Code: 2] [SQL State: 4287A] The statement is select distinct t1.t1_id, t2.t2value1, t2.t2value2, t2.t2value3 from t1, t2, t3 where ... order by lower(t2.t2value2) , lower(t2.t2value1) , lower(t2.t2value3) Dyre.Tjeldvoll wrote: tom_ [EMAIL PROTECTED] writes: I am using disctinct because of some self-joins and also needed to add an order by clause. An error is shown. Is it not possible to use distinct and order by together? I think it is allowed. Executing select distinct * from sys.systables order by tablename; in ij works just fine. Could you show the error message you get, and perhaps what the table looks like? -- dt « [hide part of quote] Hi Tom - I see what you mean using the demo DB toursDB: ij select * from airlines order by lower(airline_full); A|AIRLINE_FULL|BASIC_RATE|DISTANCE_DISCOUNT |BUSINESS_LEVEL_FACTOR |FIRSTCLASS_LEVEL_FACT|ECONOMY_SE|BUSINESS_S|FIRSTCLASS --- AA|Amazonian Airways |0.18 |0.03 |0.5 |1.5 |20 |10 |5 US|Union Standard Airlines |0.19 |0.05 |0.4 |1.6 |20 |10 |5 2 rows selected ij select distinct * from airlines order by lower(airline_full); ERROR 4287A: The ORDER BY clause may not specify an expression, since the query specifies DISTINCT. ij select distinct airline_full from airlines order by lower(airline_full); ERROR 4287A: The ORDER BY clause may not specify an expression, since the query specifies DISTINCT. ij I didn't find a JIRA enhancement to remove this restriction. I suggest you file an Enhancement request to remove the restriction reported by ERROR 4287A. -- This message is automatically generated by JIRA. - You can reply to this email to add a comment to the issue online.
