SebastianJ SebastianJ - 3 months ago 10
SQL Question

Firebird (how to find a parent category using SQL)

I have the following table:

enter image description here

I want to create a procedure that takes in a category and gives me back
category parents, like this:

enter image description here

I have already succeded to create a procedure but it doesn't display data
in the way I wanted.
Besides, I believe there's a better way (maybe recursion ?) to accomplish the task, but I don't know how to implement it. Do you have any hints ?

--Database Query--

-- Creating Domain Boolean (didn't exist in FireBird) --

CREATE DOMAIN DBOOLEAN
AS Smallint
DEFAULT 0
NOT NULL
check (Value in (0,1));

-- Creating table --
CREATE TABLE TCATEGORIE
(CODE_CAT Char(5) NOT NULL,
NOM_CAT DNOM,
CUISINE_CAT DBOOLEAN DEFAULT 0,
FKCATPRINC_CAT Char(5),
PRIMARY KEY (CODE_CAT)
);

-- Inserted data --
Insert Into TCATEGORIE
(CODE_CAT, NOM_CAT, CUISINE_CAT, FKCATPRINC_CAT)
values ('B', 'Boissons', 0,null);
Insert Into TCATEGORIE
(CODE_CAT, NOM_CAT, CUISINE_CAT, FKCATPRINC_CAT)
values ('BS', 'Boissons froides', 0, 'B');
Insert Into TCATEGORIE
(CODE_CAT, NOM_CAT, CUISINE_CAT, FKCATPRINC_CAT)
values ('BV', 'Vins', 0, 'B');
Insert Into TCATEGORIE
(CODE_CAT, NOM_CAT, CUISINE_CAT, FKCATPRINC_CAT)
values ('BVR', 'Vin rouge', 0, 'BV');
Insert Into TCATEGORIE
(CODE_CAT, NOM_CAT, CUISINE_CAT, FKCATPRINC_CAT)
values ('BVB', 'Vin Blanc', 0, 'BV');
Insert Into TCATEGORIE
(CODE_CAT, NOM_CAT, CUISINE_CAT, FKCATPRINC_CAT)
values ('BVROS', 'Vin Rose', 0, 'BV');
Insert Into TCATEGORIE
(CODE_CAT, NOM_CAT, CUISINE_CAT, FKCATPRINC_CAT)
values ('BC', 'Boissons chaudes', 0, 'B');
Insert Into TCATEGORIE
(CODE_CAT, NOM_CAT, CUISINE_CAT, FKCATPRINC_CAT)
values ('V', 'Viande', 1, null);
Insert Into TCATEGORIE
(CODE_CAT, NOM_CAT, CUISINE_CAT, FKCATPRINC_CAT)
values ('VR', 'Viande Rouge', 1, 'V');
Insert Into TCATEGORIE
(CODE_CAT, NOM_CAT, CUISINE_CAT, FKCATPRINC_CAT)
values ('VB', 'Viande Blanche', 1, 'V');
Insert Into TCATEGORIE
(CODE_CAT, NOM_CAT, CUISINE_CAT, FKCATPRINC_CAT)
values ('P', 'Poisson', 1, null);
Insert Into TCATEGORIE
(CODE_CAT, NOM_CAT, CUISINE_CAT, FKCATPRINC_CAT)
values ('F', 'Fromage', 1, null);
Insert Into TCATEGORIE
(CODE_CAT, NOM_CAT, CUISINE_CAT, FKCATPRINC_CAT)
values ('S', 'Sauce', 1, null);
Insert Into TCATEGORIE
(CODE_CAT, NOM_CAT, CUISINE_CAT, FKCATPRINC_CAT)
values ('D', 'Dessert', 1, null);
Insert Into TCATEGORIE
(CODE_CAT, NOM_CAT, CUISINE_CAT, FKCATPRINC_CAT)
values ('DC', 'Dessert chaud', 1, 'D');
Insert Into TCATEGORIE
(CODE_CAT, NOM_CAT, CUISINE_CAT, FKCATPRINC_CAT)
values ('DG', 'Dessert glace', 1, 'D');


--My Procedure--

set term^;
create procedure CatParent (choix
type of column TCATEGORIE.CODE_CAT)
returns (CODE_CAT1 type of column TCATEGORIE.CODE_CAT,
CODE_CAT2 type of column TCATEGORIE.CODE_CAT,
CODE_CAT3 type of column TCATEGORIE.CODE_CAT,
NOM_CAT1 type of column TCATEGORIE.NOM_CAT,
NOM_CAT2 type of column TCATEGORIE.NOM_CAT,
NOM_CAT3 type of column TCATEGORIE.NOM_CAT )
as
begin

if(char_length(trim(choix))>=3)
then
select c.CODE_CAT, c2.CODE_CAT, c3.CODE_CAT,
c.NOM_CAT, c2.NOM_CAT, c3.NOM_CAT
from TCATEGORIE c
left join TCATEGORIE c2
on c.CODE_CAT=c2.FKCATPRINC_CAT
left join TCATEGORIE c3
on c2.CODE_CAT=c3.FKCATPRINC_CAT
where c.FKCATPRINC_CAT is null and
c3.code_cat=:choix into :CODE_CAT1, :CODE_CAT2, :CODE_CAT3,
:NOM_CAT1, :NOM_CAT2, :NOM_CAT3;

else if(char_length(trim(choix))=2)
then
select first 1 c.CODE_CAT, c2.CODE_CAT, c.NOM_CAT, c2.NOM_CAT
from TCATEGORIE c
left join TCATEGORIE c2
on c.CODE_CAT=c2.FKCATPRINC_CAT
left join TCATEGORIE c3
on c2.CODE_CAT=c3.FKCATPRINC_CAT
where c.FKCATPRINC_CAT is null and
c2.code_cat=:choix
into :CODE_CAT1, :CODE_CAT2,
:NOM_CAT1, :NOM_CAT2;

else
select first 1 c.CODE_CAT, c.NOM_CAT
from TCATEGORIE c
left join TCATEGORIE c2
on c.CODE_CAT=c2.FKCATPRINC_CAT
left join TCATEGORIE c3
on c2.CODE_CAT=c3.FKCATPRINC_CAT
where c.FKCATPRINC_CAT is null and
c.code_cat=:choix into :CODE_CAT1, :NOM_CAT1;


end^
set term;^

Answer
SET TERM ^ ;

create or alter procedure CAT_PARENT (
ICODE_CAT varchar(5))
returns (
CODE_CAT char(20),
NOM_CAT varchar(256),
CUISINE_CAT smallint,
FKCATPRINC_CAT char(20))
as
BEGIN
  FOR
   select
        tcategorie.code_cat,
        tcategorie.nom_cat,
        tcategorie.cuisine_cat,
        tcategorie.fkcatprinc_cat
    from tcategorie
    where 
       (
          (tcategorie.code_cat = :icode_cat)
       )
    INTO :CODE_CAT,
         :NOM_CAT,
         :CUISINE_CAT,
         :FKCATPRINC_CAT
  DO
  BEGIN
   suspend;
    while (:FKCATPRINC_CAT is not null) do
      begin
        execute procedure cat_parent(:FKCATPRINC_CAT)
        returning_values( :CODE_CAT,
           :NOM_CAT,
           :CUISINE_CAT,
           :FKCATPRINC_CAT);
           SUSPEND;
      end
  END
END^

SET TERM ; ^

Edit :

You can also use CTE(Common Table Expression like this :

SET TERM ^ ;

create or alter procedure CAT_PARENT_CTE (
ICODE_CAT varchar(5))
returns (
    CODE_CAT char(20),
    NOM_CAT varchar(256))
as
BEGIN
 for with recursive dept_code
  as (
      select tcategorie.code_cat, tcategorie.nom_cat, tcategorie.fkcatprinc_cat
  from tcategorie
  where (tcategorie.code_cat = :icode_cat)
  union all
  select tcategorie.code_cat, tcategorie.nom_cat, tcategorie.fkcatprinc_cat from dept_code
  inner join tcategorie on tcategorie.code_cat = dept_code.fkcatprinc_cat
 )

  select dept_code.code_cat, dept_code.nom_cat from dept_code
  into :CODE_CAT,:NOM_CAT

  DO
  BEGIN
   suspend;
  END
END^
Comments