Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467

    Unanswered: order by in subquery

    Why would I get ORA-00907: missing right parenthesis

    from
    Code:
    select platform
    from imr_etcm_platforms
    where platform = (select platform
                     from imr_etcm_platforms
                     order by dbms_random.value)
    NOTE: Please disregard the label "Senior Member".

  2. #2
    Join Date
    Oct 2004
    Posts
    145
    Why would you use order by in the sub select clause ? You would not gain anything from it.

    Also you have the same table in main select and sub query select and what does dbms_random.value refer to? What are you attempting to accomplish with this query ?

  3. #3
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467
    dbms_random.value allows my to get a random sampling of records from the data base.

    I am not so much concerned about the final "order" I just want them to be randomly selected.

    My original code was like
    Code:
    select platform
    from (select platform
            from imr_etcm_platforms
            order by dbms_random.value)
    but the volume of data being returned was smacking my rollbacks before it would finish. What I am trying to do is get the random sample then use it to feed my actual query. NOTE: These are not the table/columns my actual query uses, just a representation of what I want to do.
    NOTE: Please disregard the label "Senior Member".

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I don't see a reason for doing that either ... Your subquery selects ALL "platforms" (you'd like to have them randomly ordered), and then - from that set (all platforms) you again select all platforms. This could obviously be

    SELECT platform FROM imr_etcm_platforms
    ORDER BY dbms_random_value;

    P.S.
    It wouldn't let me be so I searched a little bit ... it really IS strange (this ORA-00907). If you check this subquery picture, it clearly shows that ORDER BY clause is allowed in a subquery. Formatting a query and searching for an error in the "Formatter Plus" also didn't complain. But running it - no way ... fails just like you said. It looks like you can't have an ORDER BY in a subquery.

    Thinking of your query, it seemed more likely to receive TOO_MANY_ROWS error (if your subquery returned more than one row, which would be expected as you pointed that one of variations screws up rollback segments so one could conclude your table doesn't contain a single row).

    I've seen that a HAVING clause was added to a subquery in Oracle 9.2. There was no word about ORDER BY clause. Maybe, just maybe your (and mine, 8.1.7.) DB versions don't support ORDER BY clauses in a subquery.

    Honestly, I have no idea ... perhaps I'm just blind (stupid in Croatian), but this issue makes me confused ...
    Last edited by Littlefoot; 01-07-05 at 16:46.

  5. #5
    Join Date
    Oct 2004
    Posts
    145
    Just a thought.

    I do not know enough about the dbms_random but if volume is the problem could you not limit by where clause for rownum (eg, where rownum < 100,000)?

  6. #6
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    If you wanted 100 random samples, then do the following.

    select a.platform
    from (select platform
    from imr_etcm_platforms
    order by dbms_random.value) a
    where rownum < 101;

    If you don't limit the sample, your query makes no sense. You could also try the new sample clause, which is used to get a random sample of rows. See

    http://download-west.oracle.com/docs...2.htm#i2105143
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  7. #7
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467
    Thanks for all your reponses. I am sorry for the confusion I caused by not posting my actual query. I was trying to eliminate the need for everyone to count all the () combos in my actual query. The small one reacted the same.

    Anyway ...

    The sample clause does not work in my case because I need 1000 rows. (Refer to the above explanation) Combing rownum and dbms_random returns the same rows for each iteration instead of a different set of rows.

    Just for closure sake here is my real query.
    Code:
    select  service_call__, a_workrequest_, 
            ClosedDate,  SubmitDate,
            platform,  category,  type,  acct__,  cod,  service_tier,  responsible_group,
            detail,  description1,  point_of_error,  point_of_error_reason,  cause_code, 
            resolved_by_group,  sla_met_y_n,  caller_status, 
            total_work_time,  account_error_code,  fee_description,  caller_type,
            high_valued_customer,  au,  escalation_reason, 
            status, ACTIONS
    from	   (select  service_call__, a_workrequest_, 
     		    	to_char(TO_DATE(aradmin.FROM_REMEDY_TIME( closed_date_time-(6*60*60)),'fmMonth DD, YYYY   HH:MI:SS AM'),'mm/dd/rr hh24:mi:ss') ClosedDate,
    				to_char(TO_DATE(aradmin.FROM_REMEDY_TIME( create_date-(6*60*60)),'fmMonth DD, YYYY   HH:MI:SS AM'),'mm/dd/rr hh24:mi:ss') SubmitDate,
    				platform,  category,  type,  acct__,  cod,  service_tier,  responsible_group,
    				detail,  description1,  point_of_error,  point_of_error_reason,  cause_code, 
    		        resolved_by_group,  sla_met_y_n,  caller_status, 
     				total_work_time,  account_error_code,  fee_description,  caller_type,
     				high_valued_customer,  au,  escalation_reason, 
    				DECODE( STATUS, 0, 'Open', 1,  'InProgress', 6, 'Pending',  4, 'Resolved', 3, 'Resubmit', 2, 'Duplicate', 5, 'Closed') status,
    				diary_field(substr(long_to_char(sus.service_call__,'service_call__','ARADMIN.SERVICE_CALL','ACTIONS'), 1, 1500))  ACTIONS
    			from aradmin.sc_union_scarchive
    			where service_call__ IN
    			( select service_call__
    			   from aradmin.sc_union_scarchive
    
    			   where   platform = 'Bank Connect'
    			
       			   and  create_date BETWEEN  (TO_NUMBER(LPAD (to_date('10/1/2004','MM/DD/YYYY') - TO_DATE('01/01/1970',	
    				'MM/DD/YYYY'), 19)) * 86400) + (6 * 60 * 60) AND (TO_NUMBER(LPAD (to_date('12/31/2004','MM/DD/YYYY') + 1 - TO_DATE('01/01/1970',	
    				'MM/DD/YYYY'), 19)) * 86400) + (6 * 60 * 60)
    			   order by dbms_random.value)
     
                 where rownum < 1001)
    NOTE: Please disregard the label "Senior Member".

  8. #8
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    This might be useful but it limits you to a percentage of the table NOT x rows.

    http://www.jlcomp.demon.co.uk/faq/random.html

    Alan

  9. #9
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467
    Where it true. :-( The author of the article has it wrong. Sample(10) does not return 10 percent of the rows sample(10) means that each row has a 10 percent probability of being selected. Here is what the doc says "This percentage indicates the probability of each row, or each cluster of rows in the case of block sampling, being selected as part of the sample. It does not mean that the database will retrieve exactly sample_percent of the rows of table."

    I barked along this path in the beginning because it seemed to be the perfect fit. Alas ....

    Thanks for the input regardless.
    NOTE: Please disregard the label "Senior Member".

  10. #10
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    try the link at the bottom of the page ...

    Alan

  11. #11
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467
    which page would that be?? :-)
    NOTE: Please disregard the label "Senior Member".

  12. #12
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    heres the actual link of JL's page

    http://www.revealnet.com/newsletter-v2/randomising.htm

  13. #13
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467
    Thanks for the link Alan. However I think it is no different that what I am doing with dbms_random.value. I know he calls it his "ugly" solution, but isn't it just like his "good" solution? He is still reading all the rows right? He is just hiding that read within a view. Then he selects his rownum <. Just as I found in my research over the last months, if you combine your random (from whichever method) and your rownum in a single query you always get the same result. If somebody can show me different I am all eyes.
    NOTE: Please disregard the label "Senior Member".

  14. #14
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Todd, what is your concern about the sample clause ? can you show us why it woulnd't work for you ? Also, I am a litle bit confused about your query. You use IN in a subquery ordering its values randomly, but the same effect would be applied if you dont sort the rows at all .. ??

  15. #15
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467
    In my experince because of the way sample works you can not guarantee that you will get the required number of rows. When I was trying to select 1000 "random" rows from a table that had 120K rows if I said
    Code:
    select column
    from table sample(20)
    where rownum < 1001
    it was possible that I would get less than 1000 rows.

    More confusion. The code I listed earlier was the code that I was getting the "ORA-00907: missing right parenthesis" error on. There was a complaint that I hadn't listed the actual code I was referencing. Here is the final/actual code that is being used.
    Code:
    select  service_call__, a_workrequest_, 
            ClosedDate,  SubmitDate,
            platform,  category,  type,  acct__,  cod,  service_tier,  responsible_group,
            detail,  description1,  point_of_error,  point_of_error_reason,  cause_code, 
            resolved_by_group,  sla_met_y_n,  caller_status, 
            total_work_time,  account_error_code,  fee_description,  caller_type,
            high_valued_customer,  au,  escalation_reason, 
            status, ACTIONS
    from	   (select  service_call__, a_workrequest_, 
     		    	to_char(TO_DATE(aradmin.FROM_REMEDY_TIME( closed_date_time-(6*60*60)),'fmMonth DD, YYYY   HH:MI:SS AM'),'mm/dd/rr hh24:mi:ss') ClosedDate,
    				to_char(TO_DATE(aradmin.FROM_REMEDY_TIME( create_date-(6*60*60)),'fmMonth DD, YYYY   HH:MI:SS AM'),'mm/dd/rr hh24:mi:ss') SubmitDate,
    				platform,  category,  type,  acct__,  cod,  service_tier,  responsible_group,
    				detail,  description1,  point_of_error,  point_of_error_reason,  cause_code, 
    		        resolved_by_group,  sla_met_y_n,  caller_status, 
     				total_work_time,  account_error_code,  fee_description,  caller_type,
     				high_valued_customer,  au,  escalation_reason, 
    				DECODE( STATUS, 0, 'Open', 1,  'InProgress', 6, 'Pending',  4, 'Resolved', 3, 'Resubmit', 2, 'Duplicate', 5, 'Closed') status,
    				diary_field(substr(long_to_char(service_call__,'service_call__','ARADMIN.SERVICE_CALL','ACTIONS'),1,1500))  ACTIONS
    			from aradmin.sc_union_scarchive
    
    			where   (platform = 'Home Equity' or platform = 'Loan Hub')
    						
     			and  create_date BETWEEN TO_NUMBER(to_date('01-oct-04') - TO_DATE('011970', 'MMYYYY')) * 86400 + (6 * 60 * 60) 
    			                 AND     TO_NUMBER(to_date('31-dec-04') - TO_DATE('011970', 'MMYYYY') + 1) * 86400 + (6 * 60 * 60)
    			order by dbms_random.value)
     
    where rownum < 1001
    NOTE: Please disregard the label "Senior Member".

Posting Permissions

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