0

I'm trying to find the first free number (SYMBOL_ZAM) in a table and add 1, to make a unique one for my new record. First I'm selecting the last added number into a varchar variable (OST_SZUK_NR) and then I was trying to make field NOWY_SYMB_ZAM the same value as OST_SZUK_NR but declaring it as a integer, so I can add the 1, but it didn't work. Any tips how I can make this work?

begin
    -- POBRANIE DANYCH DO ZALOZENIA NAGLOWKA
     for select KATEGORIA, ROK_ZAM, SYMBOL_ZAM from M_ZAMWLASNE
     WHERE SYMBOL_ZAM =:symbol_zam_wyk
     into :KATEGORIA, :ROK_ZAM, :SYMBOL_ZAM
     DO begin
        -- STWORZENIE NAGLOWKA

        --OKRESLENIE DO KTOREJ BIBLIOTEKI WPADNIE OFERTA
        if (:KATEGORIA IN ('KOI','KOM','KOL')) then
            parametr='TOK';
        if (:KATEGORIA LIKE ('KOS')) then
            PARAMETR='SPA';
            --WYSZUKANIE NAJBLIZSZEGO WOLNEGO SYMBOLU ZLECENIA
            FOR select symbol_zam FROM m_zamwlasne 
            WHERE KATEGORIA=:parametr AND ROK_ZAM=:rok_zam
            ORDER BY symbol_zam desc
            INTO: OST_SZUK_NR
            --PRZEROBIENIE WYSZUKANEGO NUMERU Z VARCHAR NA INTEGER I DODANIE 1
            nowy_symb_zam=:ost_szuk_nr
            nowy_symb_zam=:nowy_symb_zam + 1
Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
Oliver Sznapka
  • 1
  • 1
    One option is to use a sequence: http://www.firebirdfaq.org/faq29/. – Gordon Linoff Feb 12 '20 at 11:34
  • Please provide a [mre] that includes the declaration of the tables, sample data and the declaration of the stored procedure itself and its variables, and provide the specific use case for this. Consider translating that example (and its comments) to English as that makes it a lot easier for people to understand context instead of having seemingly meaningless table, column and variable names. Be aware that tricks like this will fail under concurrent use. It is probably better to use a generator (sequence). – Mark Rotteveel Feb 12 '20 at 12:08
  • 1
    And please explain why `OST_SZUK_NR` is a varchar variable when the value is an integer. Why not use an integer from the start? – Mark Rotteveel Feb 12 '20 at 12:10
  • Another question, are you aware of the existence of [`CAST`](https://firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref25-functions-scalarfuncs.html#fblangref25-functions-scalarfuncs-cast)? If not, then `cast(OST_SZUK_NR as integer)` might just solve your problem. – Mark Rotteveel Feb 12 '20 at 12:25
  • `first free number (SYMBOL_ZAM) in a table and add 1, to make a unique one` but this "unique" number moight already be taken!!! For example, you have the numbers in your database: 1,2,3,6,8,9,10,15,..... You have to insert new row, so you find the "first free" - that is 4. You take 4+1 =5 and insert new record. Now your database has rows 1,2,3,5,6,8,9,10,15,..... You have to insert one more row, you find the "first free" number - that is 4. You increaset and get 4+1=5. Now you try to insert row with ID=5 - and you get collision, as you already inserted it a minute ago. – Arioch 'The Feb 12 '20 at 14:46

0 Answers0