How to create a read sequence in a Select field in the Oracle Database?

Asked

Viewed 24 times

0

I have a problem in my code I would like a help with an excerpt of my code. the ITE.SEQUENCIA line did not want to leave the number 1 fixed, because it may be that an item is deleted and starts with the next number, it would be possible to create a sequence within the code in the Oracle database?

Thank you all.

Ex: Code


     (SELECT MAX(CAB2.VLRNOTA)
    FROM TGFCAB CAB2, TGFDIN DIN
        WHERE ITE.NUNOTA = CAB2.NUNOTA
        AND CAB2.STATUSNOTA = 'L'
        AND ITE.SEQUENCIA = 1 -- QUERIA QUE O ITE.SEQUENCIA PEGASSE SEMPRE O PRIMEIRO NUMERO DISPONÍVEL, POIS NÃO COMEÇARIA SEMPRE COM NUMERO 1
        AND ITE.NUNOTA = DIN.NUNOTA
        AND ITE.SEQUENCIA = DIN.SEQUENCIA) AS VALOR_DA_NOTA
sql database oracle oracle12c

  • Your description of the problem is quite confusing but, guessing, I believe that a subselect taking the lower value of the SEQUENCIA field than I believe to be the unlisted ITE table, can help you.

  • Sorry, the transcript is that it’s a very long code, it’s an ITE yes, with sequence, when it starts a sale, and if it cancels 1 item, it eliminates sequence 1, and goes to 2, if it excludes a second sequence goes to 2 and so on. I wanted to link for example VALUE table in only one field of the sequence, not to duplicate in a report. I don’t know if it’s any more confusing, but I’ll post the whole code. https://pastebin.com/AWeq4dyF

1 answer

0

Oracle has sequence functionality. See the link below for more details. Summarily, to create the sequence:

 CREATE SEQUENCE customers_seq
   START WITH     1000
   INCREMENT BY   1
   NOCACHE
   NOCYCLE;

But from his description, you don’t understand the scenario in which the sequence would be used and it’s not clear that a sequence is the best option.

https://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_6015.htm#SQLRF01314

Browser other questions tagged sql database oracle oracle12c

You are not signed in. Login or sign up in order to post.