OraFAQ Forum: SQL & PL/SQL » randomize records and groupby date.

Home » SQL & PL/SQL » SQL & PL/SQL » randomize records and groupby date.

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

randomize records and groupby date. [message #614411]

Wed, 21 May 2014 09:41

rlsublime
Messages: 10
Registered: March 2014

Junior Member

Hello, I have the following code that will randomized records by TMI_BATCHNO. I an trying to also get the code to sort the records by 'TMI_DATE'. So basically when the query is refreshed I am trying to get it to display the next date in descending order.

 SELECT TMI_BATCHNO FROM

(

SELECT TMI_BATCHNO FROM TRAILING_MAIL_INPUT

ORDER BY dbms_random.value

)

WHERE rownum = 1

order by 1

Report message to a moderator

Re: randomize records and groupby date. [message #614414 is a reply to message #614411]

Wed, 21 May 2014 09:53

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

What's the relationship between tmi_batchno and tmi_date?
What is the next date in this context?

You need to supply some sample data and the expected output. I have to absolutely no idea what you want.

Report message to a moderator

Re: randomize records and groupby date. [message #614416 is a reply to message #614411]

Wed, 21 May 2014 09:55

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

Your query is quite confusing and hard to relate it to your requirement as explained in words. Please post a test case with input values and desired output.

Report message to a moderator

Re: randomize records and groupby date. [message #614421 is a reply to message #614416]

Wed, 21 May 2014 10:15

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>I have the following code that will randomized records by TMI_BATCHNO.
>ORDER BY dbms_random.value

I don't believe you.

SQL> exec dbms_random.value;
BEGIN dbms_random.value; END;

      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'VALUE'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

Report message to a moderator

Re: randomize records and groupby date. [message #614425 is a reply to message #614421]

Wed, 21 May 2014 10:23

DrabJay
Messages: 32
Registered: May 2013

Member

I do!

SQL> select dbms_random.value from dual;

     VALUE
----------
.908665842

Report message to a moderator

Re: randomize records and groupby date. [message #614428 is a reply to message #614421]

Wed, 21 May 2014 10:25

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

BlackSwan wrote on Wed, 21 May 2014 16:15

>I have the following code that will randomized records by TMI_BATCHNO.
>ORDER BY dbms_random.value

I don't believe you.
SQL> exec dbms_random.value;
BEGIN dbms_random.value; END;

      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'VALUE'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

I don't believe you posted that.
You're calling it as a procedure.
If you call it as a function it works just fine:

SQL> SELECT dbms_random.value FROM dual;

     VALUE
----------
.733925463

Report message to a moderator

Re: randomize records and groupby date. [message #614430 is a reply to message #614428]

Wed, 21 May 2014 10:28

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

I need another cup of coffee!

Report message to a moderator

Re: randomize records and groupby date. [message #614459 is a reply to message #614414]

Wed, 21 May 2014 21:33

rlsublime
Messages: 10
Registered: March 2014

Junior Member

Every record will have a date attached to it. There are multiple users keying data in the records. the randomize feature prevents multiple users from being in the same record at the same time. But the users should key the oldest data first. For example APR then MAY. so i am trying to generate random records ordered by oldest date to newest date. So only when APR is complete, should MAY be available for keying.

Thanks

Report message to a moderator

Re: randomize records and groupby date. [message #614460 is a reply to message #614459]

Wed, 21 May 2014 21:45

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>the randomize feature prevents multiple users from being in the same record at the same time.
No, not really.

the ORDER BY clause can utilize an integer, which control which column is used to sort the result set; but since only single column is returned it is moot.
>>ORDER BY dbms_random.value
above is as useful as WHERE 1 = 1

SELECT MIN(TMI_BATCHNO) FROM TRAILING_MAIL_INPUT; --produces the same results

Report message to a moderator

Re: randomize records and groupby date. [message #614466 is a reply to message #614459]

Thu, 22 May 2014 00:26

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

There are multiple users keying data in the records. the randomize feature prevents multiple users from being in the same record at the same time.

User should then use a SELECT FOR UPDATE to lock the row then no one could use the same row at the same time.

Report message to a moderator

Re: randomize records and groupby date. [message #614474 is a reply to message #614466]

Thu, 22 May 2014 02:53

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

How do you know when a month is complete?

Report message to a moderator

Previous Topic:	convert string to date
Next Topic:	plsql procedure gets invalidated

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Apr 24 16:36:11 CDT 2024