This discussion is archived
1 2 Previous Next 23 Replies Latest reply: Feb 14, 2013 7:24 PM by Frank Kulash RSS

Generate an unique number without sequence

942572 Newbie
Currently Being Moderated
Hello,

I need generate an unique number without sequence. I am using Oracle 11.2, here is the details. The column idSeq is unique withing one specific idType. I don't want a sequence for each idType.

create table tb_test (idSeq number(5), idType number(5), addr varchar2(256));

insert into tb_test
(select case when idSeq is null then 1 else max(idSeq)+1 end, 3, 'Main street');

I am having ORA-00937 : not a single-group group function error, any suggestion?

Edited by: 939569 on 13-Feb-2013 11:21 AM
  • 1. Re: Generate an unique number without sequence
    SomeoneElse Guru
    Currently Being Moderated
    Why on earth would you not use a sequence?
  • 2. Re: Generate an unique number without sequence
    ranit B Expert
    Currently Being Moderated
    939569 wrote:
    Hello,

    I need generate an unique number without sequence. I am using Oracle 11.2, here is the details:

    create table tb_test (id number(5), addr varchar2(256));
    insert into tb_test
    (select case when id is null then 1 else max(id)+1 end, 'Main street');

    I am having ORA-00937 : not a single-group group function error, any suggestion?
    SYS_GUID - http://docs.oracle.com/cd/E14072_01/server.112/e10592/functions185.htm
    Check if this helps...

    Edited by: ranit B on Feb 14, 2013 1:02 AM
    -- link added
  • 3. Re: Generate an unique number without sequence
    942572 Newbie
    Currently Being Moderated
    Updated original question for not using sequence.
  • 4. Re: Generate an unique number without sequence
    Frank Kulash Guru
    Currently Being Moderated
    Hi,
    939569 wrote:
    Hello,

    I need generate an unique number without sequence.
    Explain what you're trying to do, and why a sequence won't do it.
    I am using Oracle 11.2, here is the details. The column idSeq is unique withing one specific idType. I don't want a sequence for each idType.

    create table tb_test (idSeq number(5), idType number(5), addr varchar2(256));

    insert into tb_test
    (select case when idSeq is null then 1 else max(idSeq)+1 end, 3, 'Main street');

    I am having ORA-00937 : not a single-group group function error,
    Right. Since the sub-query is using an aggregate function (MAX), everything in the SELECT clause must be an aggregate, a group by expression, a constant, or something that depends entirely on them. In this example, the CASE expression depends on idSeq, which is not an aggregate, not a group by expression (there is no GROUP BY clause) and not a constant.

    Also, the sub-query must have a FROM clause. All queries and sub-queries in Oracle require a FROM clause.
    any suggestion?
    Use a sequence. The numbers won't be consecutive within each idType, but what does that matter? Chances are, no matter what you do, you won't be able to maintain consecutive numbers within each idType anyway.

    If you won't use a sequence, then you can try:
    INSERT INTO  tb_test (idSeq, idType, addr)
    SELECT  1 + NVL ( MAX (id_seq)
                  , 0
              )
    ,       3
    ,     'Main street;
    FROM     tb_test
    WHERE   idType  = 3
    ;
  • 5. Re: Generate an unique number without sequence
    EdStevens Guru
    Currently Being Moderated
    939569 wrote:
    Updated original question for not using sequence.
    SomeoneElse's question stands.
    Why on earth would you not use a sequence?
  • 6. Re: Generate an unique number without sequence
    942572 Newbie
    Currently Being Moderated
    Hi Frank,

    What if I want to do it on sub query? I would like to copy most columns from tb_src to tb_trgt, except the totSeq will generated by itself as max(totSeq)+1.

    create table tb_src(idSeq number(5), idType number(5), nm varchar2(32), addr varchar2(20));
    create table tb_trgt(totSeq number(5), totType number(5), nm varchar2(32), addr varchar2(20));

    insert into tb_trgt (totSeq, totType, nm, addr)
    (
    select 1+nvl(max(totSeq),0), idType, nm, addr
    from tb_src
    where (idType, nm) in
    (select idType, nm from tb_src
    minus
    select seqType, nm from tb_trgt)
    );
  • 7. Re: Generate an unique number without sequence
    Frank Kulash Guru
    Currently Being Moderated
    Hi,
    939569 wrote:
    Hi Frank,

    What if I want to do it on sub query? I would like to copy most columns from tb_src to tb_trgt, except the totSeq will generated by itself as max(totSeq)+1.

    create table tb_src(idSeq number(5), idType number(5), nm varchar2(32), addr varchar2(20));
    create table tb_trgt(totSeq number(5), totType number(5), nm varchar2(32), addr varchar2(20));
    Thanks for posting the CREATE TABLE statements.
    You should also post INSERT statements for a little sample data, and the results you want from that sample data.
    If the problem involves DML (such as INSERT) then the sample data you post should reflect the tables as they exist before the DML, and the results will be the contents of the changed table after the DML.
    See the forum FAQ {message:id=9360002}
    insert into tb_trgt (totSeq, totType, nm, addr)
    (
    select 1+nvl(max(totSeq),0), idType, nm, addr
    from tb_src
    where (idType, nm) in
    (select idType, nm from tb_src
    minus
    select seqType, nm from tb_trgt)
    );
    You can use the analytic MAX fucntion to get the highest number already present, and the analytic ROW_NUMBER function to add 1, 2, 3, ... to that number. PARTITION bioth functions BY idType to get separate MAX and ROW_NUMBER results for each idType.
    Analytic functions can be tricky to use in INSERT (or UPDATE) statements. You'll probably wnat to use MERGE, even though you're only INSERTing.
    If you'd care to post some sample data and results, I could show you exactly.
  • 8. Re: Generate an unique number without sequence
    rp0428 Guru
    Currently Being Moderated
    >
    I need generate an unique number without sequence. I am using Oracle 11.2, here is the details. The column idSeq is unique withing one specific idType. I don't want a sequence for each idType.

    create table tb_test (idSeq number(5), idType number(5), addr varchar2(256));

    insert into tb_test
    (select case when idSeq is null then 1 else max(idSeq)+1 end, 3, 'Main street');

    I am having ORA-00937 : not a single-group group function error, any suggestion?
    >
    Do you understand that your attempt to generate unique numbers like this can't possibly work if more than one user tries to update the table at the same time?
  • 9. Re: Generate an unique number without sequence
    WhiteHat Expert
    Currently Being Moderated
    EdStevens wrote:
    939569 wrote:
    Updated original question for not using sequence.
    SomeoneElse's question stands.
    Why on earth would you not use a sequence?
    Unrelated to the OP's question but:

    I recently attended a real world performance day with Tom Kyte in Perth Australia and he mentioned an issue whereby there was a multi-master replication setup and there was issues using sequences.

    Unfortunately I didn't take the copious notes I wished I had, so sorry if I get this muddled up:

    But I think it went something along the lines of: there was contention on the index on insert so Tom suggested a reverse key index.

    This was determined to be inefficient still (still only 10 leaves in the bottom of the B-tree) so they changed it to a GUID.

    Then they realised that each site was only searching on its own data, despite the fact that it was replicated to all sites and so they added a site identifier to the front of each identifier to effectively partition by source database.

    I've not encountered that situation before so I haven't solidified it in my head, but they clearly demonstrated that in some situations, using a sequence to generate a unique identifier is not the best solution.

    I'll have to search Ask Tom a bit to see if he's described this somewhere.
  • 10. Re: Generate an unique number without sequence
    942572 Newbie
    Currently Being Moderated
    Step1.

    Insert into TB_src values(102, 3, 'john','Acton');

    After run the SQL, I expect in TB_trgt the row is 1,3,'john','Acton';

    Step 2.

    Insert into TB_src values(556,3,'john','Boston);

    After run the SQL, I expect in TB_trgt the following rows:
    1,3,'john','Acton'
    2,3,'john','Boston'
  • 11. Re: Generate an unique number without sequence
    Frank Kulash Guru
    Currently Being Moderated
    Hi,
    939569 wrote:
    More details needed
    Exactly! That's just what I said in my last message. {message:id=10849883}

    When you've posted those details, you'll find people ready (and finally able) to help you.
    Post INSERT statements for your sample data, and the results you want from that data.
  • 12. Re: Generate an unique number without sequence
    Hemant K Chitale Oracle ACE
    Currently Being Moderated
    In Multi-Master Replication, although Tables can be replicated Sequences cannot be. Therefore each site uses it's own Sequence. In order to ensure that two sites do not generate the same sequence number, they set the INCREMENT BY to the number of sites.

    For example, if there are 10 sites in a Multi-Master Replication environment, Site A uses the numbers 1,11,21,31... , Site B uses the numbers 2,12,22,32..., Site C uses the numbers 3,13,23,33 ...
    At Site A the sequence is defined as START WITH 1 INCREMENT BY 10
    At Site B the sequence is defined as START WITH 2 INCREMENT BY 10
    At Site C the sequence is defined as START WITH 3 INCREMENT BY 10

    Adding a Site Identifier is also valid. But a Sequence number may still be required to generate Unique Values !


    Hemant K Chitale
  • 13. Re: Generate an unique number without sequence
    942572 Newbie
    Currently Being Moderated
    I only have a single site master. All the data access of this table is via this SQL.
  • 14. Re: Generate an unique number without sequence
    rp0428 Guru
    Currently Being Moderated
    >
    I only have a single site master. All the data access of this table is via this SQL.
    >
    And do you understand that your attempt to generate unique numbers like this can't possibly work if more than one user tries to update the table at the same time?

    Even if you had to use seq numbers like this wouldn't it be simpler to just create a table with 100,000 records for each possible 5 digit number and just mark them used as you use each one?

    That way you could at least lock the row for update when you need to allocate one and then update the USED column to 'Y'.
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points