Interbase - Update & Select

[Henry Olive 5]

I wish everyone a healthy day.

Table-1 (PK = CustNo & ItemNo)

CUSTNO...ITEMNO...PRICE

1....................AA..............100

1....................BB...............150

2....................AA..............100

2....................CC..............200

 

I want to Insert Table-1's all record into a new Table-2 

and i dont have any Generator for LINENO.

Is it possible to add LINENO field a counter number by Insert Sql ?

( The result for Table-2 should be like below )

 

Table-2 ( PK=CustNo & LineNo )

CUSTNO...LINENO...ITEMNO...PRICE

1...........................1............AA.............100

1...........................2............BB..............150

2...........................1............AA.............100

2...........................2............CC.............200

 

Thank You
 

 

 

[haentschman 92]

Hi...

via SQL or code?

[Guest]

What essentially will happen if you succeed is that the "LINENO" field will reflect the "natural order" of the "Table-1".

That does not sound very "planned" to me.

First off, what order do you want that field to indicate? Or maybe you would like the lines sorted after "ITEM"? Then you won't need the extra column at all.

If you want a "cronological" order as to when the user posted the record (in relation to other lines), then perhaps you should use a generator in a trigger (insert), does NOT have to correlate with the PK.

You will be better off with a generator than for example rdb$key or some such (aka natural if we stretch things).

And to add to all of this, neither Table-1 not Table-2 above seems to have a unique PK (?).

To have a unique index (like not being able to add two lines with the same ITEM for the same CUSTNO/INVOICENO) and using that as the PK can seem very logical when you start but it's much better to use an (generator) index instead.

Also remember, that if you get your sequence (ordinal) field right using a generator, if someone points out that there are "holes" in the number series, that not a problem at all.

For a "series of auditable numbers", you need other techniques.

Edited September 30, 2021 by Guest

[Henry Olive 5]

Thank you Hanetchman i just wondered if it is possible to insert SQL ( not proc)  with one of a field with auto counter

(like generator, but w/o created any generator)

 

Thank you so much Danny for your detailed explanation i really appriciate for the time you spent for me.

[Serge_G 87]

Well, with Firebird it is, using variable context (old way). I do not remember if Interbase had this capacity (guess yes)

P.S. By the way, what is your Interbase version ?
 

SELECT CUSTNO,      

           rdb$get_context('USER_TRANSACTION', 'row#') as  LINENO,

           rdb$set_context('USER_TRANSACTION','row#',
                  coalesce(cast(rdb$get_context('USER_TRANSACTION', 'row#') as integer), 0) + 1),

          ITEMNO,

          PRICE

FROM table-1 ORDER BY CUSTNO

But you need to add some context variable to check and reset to 1 when changing of CUSTNO

 

New ways  

   - Recursive Common Table Expression (I will not expand because best way is below)

 

   - Windows function

SELECT  
 CUSTNO,
 ROW_NUMBER() OVER (partition by CUSTNO) LINENO,
 ITEM,
 PRICE 
FROM TABLE_1
ORDER BY CUSTNO

 

I suggest you to search in Interbase (version ?) documentation with these two terms      

 

 

By the way, I suggest you a best way to ask a SQL question : add a script

CREATE TABLE table_1
(
   CUSTNO INTEGER NOT NULL,
   ITEM CHAR(2),
   PRICE NUMERIC(10,2)
);
INSERT INTO TABLE_1 VALUES (1,'AA',100);
INSERT INTO TABLE_1 VALUES (1,'BB',150);
INSERT INTO TABLE_1 VALUES (2,'AA',100);
INSERT INTO TABLE_1 VALUES (2,'CC',200);
INSERT INTO TABLE_1 (CUSTNO, ITEM, PRICE) VALUES ('3', 'BB', '150.00');
INSERT INTO TABLE_1 (CUSTNO, ITEM, PRICE) VALUES ('3', 'AA', '100.00');
INSERT INTO TABLE_1 (CUSTNO, ITEM, PRICE) VALUES ('3', 'CC', '200.00');

and then the expected result (ok, here I cheat it's the result of the windows function SQL using the data I used
 

Quote

 

1    1    AA    100.00
1    2    BB    150.00
2    1    AA    100.00
2    2    CC    200.00
3    1    AA    100.00
3    2    BB    150.00
3    3    CC    200.00

 

 

 

 

Edited October 2, 2021 by Serge_G
Post Scriptum : Interbase version ?

[Henry Olive 5]

Thank you so much Serge

Unfortunatelly there is no recursive CTE in Interbase

( I'm planning to move Firebird )

I decided to create a Generator

Thank you again

[Guest]

AFAIR rbd$get_context and rdb$set_context was introduced not too long ago (2.0, 2.5?) in FB. Perhaps IB got something similar, but i would not know.

IB and FB was compatible for a long time after the first fork (1.0 for FB). Would be interesting with a comprehensive "split" chart. But who would have the time?

 

@Henry Olive, generators is an old and well working concept. The new number is incremented outside of transaction "control". This is key.

So the difference would "only" be that you would have to explain to someone the "ordinal" concept. It does not matter if on series starts with 1 and another with 1001 and that there migt be "wholes" inte the series. Some colleagues and/or clients (though you should not have to show the actual values to the user) may frown upon the solution.

[Serge_G 87]

CTE, ok, but windows functions ? I am really disappointed if Interbase  don't applies new SQL standard.

 

22 hours ago, Dany Marmur said:

AFAIR rbd$get_context and rdb$set_context was introduced not too long ago (2.0, 2.5?) in FB. Perhaps IB got something similar, but i would not know.

IB and FB was compatible for a long time after the first fork (1.0 for FB). Would be interesting with a comprehensive "split" chart. But who would have the time?

Time is the key word, if I have some in future week  I will install manually (because of 3050 port) Interbase 2020 Delphi version just to check what's new

 

But there are some comparisons available (not commercial Embarcadero ones)

https://db-engines.com/en/system/Firebird%3BInterbase

 

or you can read

https://ib-aid.com/en/articles/differences-between-firebird-and-interbase/

vs

https://www.embarcadero.com/fr/products/interbase/compare/interbase_firebird

 

And make a choice

 

On 10/4/2021 at 11:46 AM, Henry Olive said:

( I'm planning to move Firebird )

Well I do that years ago and had no problem with

Edited October 6, 2021 by Serge_G

[Serge_G 87]

Well, I install Interbase and was rather disappointed! No context variables (a bypass :  using a temporary table) nor windows functions . I don't investigate for Recursive CTE, but I am afraid there is not.

How can that be possible as of today? I understand more Ann Harrison mother of Interbase/Firebird point of view.

As quick as I install Interbase, I desinstall it 😖 really disappointed. I have no doubt there are good things in Interbase but my way is now Firebird (event if missing some crypto columns)

[Serge_G 87]

Hi,

I have to put water in my wine even though it's a wine heresy

I just read that the interbase 2020 update 2 allows recursive CTEs  https://docwiki.embarcadero.com/InterBase/2020/en/What's_New_in_InterBase_2020_Update_2

so I do a first try (with firebird)  not a good one SQL but as first attempt not so bad

WITH RECURSIVE
     R AS (SELECT CUSTNO,1 AS LNo,Item,Price FROM TABLE_1 
           UNION all
           SELECT r.CUSTNO,r.LNo+1,Item,Price FROM R WHERE r.LNo< (SELECT COUNT(*) LINES FROM TABLE_1 WHERE CUSTNO=r.CUSTNO) 
          )             
SELECT CUSTNO,LNO,item,price FROM r

Give me a first look, not a good one but an approximation