CREATE TABLE CASEEXPERT (
    XCASEEXPERT  DX /* DX = INTEGER NOT NULL */,
    NAME         STRING90 /* STRING90 = VARCHAR(90) */,
    XPARENT      DX /* DX = INTEGER NOT NULL */,
    CHILDCOUNT   DINTLONGCOUNT /* DINTLONGCOUNT = INTEGER DEFAULT 0 CHECK(VALUE >=0) */,
    USE          BOOLEAN DEFAULT 1 /* BOOLEAN = SMALLINT DEFAULT 0 CHECK(VALUE BETWEEN 0 AND 1) */,
    VAL          DCENT /* DCENT = INTEGER DEFAULT 100 NOT NULL CHECK(value between 0 and 100) */,
    KOD          STRING4 /* STRING4 = VARCHAR(4) */,
    DEF          BOOLEAN /* BOOLEAN = SMALLINT DEFAULT 0 CHECK(VALUE BETWEEN 0 AND 1) */,
    VISORDER     DINTSHORT /* DINTSHORT = SMALLINT */
);


ALTER TABLE CASEEXPERT ADD CONSTRAINT PK_CASEEXPERT PRIMARY KEY (XCASEEXPERT);

ALTER TABLE CASEEXPERT ADD CONSTRAINT FK_CASEEXPERT_1 FOREIGN KEY (XPARENT) REFERENCES CASEEXPERT (XCASEEXPERT);

CREATE TABLE CASEEXPERTING (
    XCASEEXPERTING  DX /* DX = INTEGER NOT NULL */,
    XCASERECORD     DX /* DX = INTEGER NOT NULL */,
    UKLMARK         DINTCOUNT DEFAULT 0 /* DINTCOUNT = INTEGER DEFAULT 0 NOT NULL */,
    EXPERT          STRING40 /* STRING40 = VARCHAR(40) */,
    MEMO            DBLOB /* DBLOB = BLOB SUB_TYPE 0 SEGMENT SIZE 80 */,
    DATEMO          DTS /* DTS = TIMESTAMP DEFAULT 'NOW' */
);

ALTER TABLE CASEEXPERTING ADD CONSTRAINT PK_CASEEXPERTING PRIMARY KEY (XCASEEXPERTING);

ALTER TABLE CASEEXPERTING ADD CONSTRAINT FK_CASEEXPERTING_1 FOREIGN KEY (XCASERECORD) REFERENCES CASERECORD (XCASERECORD);

CREATE TABLE CASEEXPERTING_CASEEXPERT (
    XCASEEXPERTING  DX /* DX = INTEGER NOT NULL */,
    XCASEEXPERT     DX /* DX = INTEGER NOT NULL */,
    VAL             DCENT /* DCENT = INTEGER DEFAULT 100 NOT NULL CHECK(value between 0 and 100) */
);

ALTER TABLE CASEEXPERTING_CASEEXPERT ADD CONSTRAINT PK_CASEEXPERTING_CASEEXPERT PRIMARY KEY (XCASEEXPERT, XCASEEXPERTING);

ALTER TABLE CASEEXPERTING_CASEEXPERT ADD CONSTRAINT FK_CASEEXPERTING_CASEEXPERT_1 FOREIGN KEY (XCASEEXPERTING) REFERENCES CASEEXPERTING (XCASEEXPERTING);
ALTER TABLE CASEEXPERTING_CASEEXPERT ADD CONSTRAINT FK_CASEEXPERTING_CASEEXPERT_2 FOREIGN KEY (XCASEEXPERT) REFERENCES CASEEXPERT (XCASEEXPERT);

create or alter procedure GET_CHILD (
    TABLENAME varchar(31),
    X_ integer)
returns (
    X integer,
    NAME varchar(512))
as
declare variable CC integer;
begin
  x=:x_;

  if (:tablename='caseexpert') then
   begin
     select s.name, s.childcount from caseexpert s where s.xcaseexpert=:x_ into :name, :cc;
     suspend;
     if (:cc>0) then
        for select s.xcaseexpert, s.name from caseexpert s where s.xparent=:x_ order by s.xcaseexpert into :x, :name
          do for select g.x, g.name from get_child(:tablename, :x) g into :x, :name do suspend;
   end


end

create or alter procedure GET_CHILD_NN (
    X_ integer)
returns (
    X integer,
    NAME varchar(512))
as
declare variable CC integer;
begin
  x=:x_;

     select s.name, s.childcount from caseexpert s where s.xcaseexpert=:x_ into :name, :cc;
     suspend;
     if (:cc>0) then
        for select s.xcaseexpert, s.name from caseexpert s where s.xparent=:x_ order by s.xcaseexpert into :x, :name
          do for select g.x, g.name from GET_CHILD_nn(:x) g into :x, :name do suspend;

end