create view aplus.trusted_stage_view as
with
  cardNo(client_id, fromDate, toDate, cardno) as (
    SELECT client_id, issuedate_d, expiredate_d, cardno
    FROM FFP_REPL.CLIENT
  ),
  draft1 as (
    SELECT 
      st.farebasis_is_good,
      ca.cardno ffp_Card_No, 
      st.sirena_psgrname passenger_name,
      st.anext_coupon_no coupon_no,
      st.airlinenumcode,
      st.serialno,
      st.anext_check_digit check_digit,
      st.anext_farebasis farebasis,
      st.anext_flightno flightno,
      st.anext_flightdate flightdate,
      st.origin,
      st.destination
    FROM 
      APLUS.SIR_TICKET_WITHOUT_FFP st
      JOIN 
      APLUS.CREDENTIAL cr ON 
        (cr.credential_type, cr.credential_number) =
        (st.sirena_pdoc_type, st.sirena_pdoc_number)
      JOIN 
      APLUS.PERSON p ON
        (p.person_id = cr.person_id)
      JOIN
      APLUS.CLIENT2PERSON_LiNK c2p ON
        (p.person_id = c2p.person_id)
      JOIN 
      FFP_REPL.CLIENT cl ON
        (cl.client_id = c2p.client_id)
      JOIN
      cardno ca
      ON cl.client_id = ca.client_id
    WHERE 
      st.farebasis_is_good= 1 
      AND cr.valid_type= 1 
      AND st.anext_flightdate BETWEEN ca.fromDate AND ca.toDate
      AND EXISTS (
        SELECT * 
        FROM APLUS.PERSON_NAME pn
        WHERE (pn.person_id, pn.firstname, pn.lastname)
            = (p.person_id, st.firstname, st.lastname)
          AND pn.valid_type= 1 
      )
      and (cl.firstname, cl.lastname)
        = (st.firstname, st.lastname)
      and (ca.cardno = rtrim(st.anext_ffpcard)
        or rtrim(value(st.anext_ffpcard,''))=''
      )  
    )
select d.*
from draft1 d 

FAREBASIS_IS_GOOD SERIALNO             ORIGIN DESTINATION FLIGHTNO   FLIGHTDATE
----------------- -------------------- ------ ----------- ---------- ----------
                1 E6101029324          MOW    TJM         453        28.10.2003
                1 E6102758863          SGC    KRR         603        03.04.2004

FAREBASIS_IS_GOOD SERIALNO             ORIGIN DESTINATION FLIGHTNO   FLIGHTDATE
----------------- -------------------- ------ ----------- ---------- ----------
                1 E6101029324          MOW    TJM         453        28.10.2003
                0 E6102758863          SGC    KRR         603        03.04.2004

 
  case when fb.farebasis is null then  0  else  1  end
    as farebasis_is_good

...
WHERE (CASE 
WHEN Q4.$C14 IS NULL 
THEN  0  
ELSE  1  END =  1 ))

SELECT  1  AS "FAREBASIS_IS_GOOD"...

CREATE FUNCTION APLUS.word(some_string VARCHAR( 1000 ), n INTEGER)
RETURNS VARCHAR( 1000 )
LANGUAGE SQL
DETERMINISTIC
NO EXTERNAL ACTION
CONTAINS SQL
BEGIN ATOMIC
  DECLARE head VARCHAR( 1000 );
  DECLARE tail VARCHAR( 1000 );
  DECLARE i INTEGER;
  IF n <= 0  THEN 
    RETURN '';
  END IF;
  SET i = n;
  SET tail = RTRIM(LTRiM(some_string));
  WHILE i >  0  DO
    SET i = i -  1 ;
    SET head = 
        SUBSTR(tail, 1 ,CASE LOCATE(' ',tail)
          WHEN  0  THEN LENGTH(tail)
          ELSE LOCATE(' ',tail) -  1 
        END);
    SET tail = 
        LTRIM(SUBSTR(tail,CASE LOCATE(' ',tail)
          WHEN  0  THEN LENGTH(tail) +  1 
          ELSE LOCATE(' ',tail)
        END));
  END WHILE;
  RETURN head;
END
@

CREATE TABLE T1(
  t1_f1 INTEGER NOT NULL,
  t1_f2 VARCHAR( 10 )
)
@

INSERT INTO T1 VALUES
( 1 , '1x???'), 
( 2 , '2x???')@

-- aplus.SIRENA_PAXINFO_table
CREATE TABLE T2(
  t2_f1 INTEGER NOT NULL,
  t3_f1 VARCHAR( 18 ),
  t1_f1 INTEGER,
  t2_f2 VARCHAR( 100 )
)
@

INSERT INTO T2 VALUES
( 1 ,'1???', 1 , 'AAA1 AAA2 AAA4'),
( 2 ,'2???', 2 , 'BBB1 BBB2 BBB3')
@

CREATE TABLE T3(
  t3_f1 VARCHAR( 20 ) not null,
  t3_f2 integer NOT NULL
)
@

INSERT INTO T3 VALUES
('1???', 1 )@

CREATE VIEW V2 AS 
SELECT 
  CASE WHEN T3.t3_f1 IS NULL THEN  0  ELSE  1  END is_valid,
  APLUS.WORD(T2.t2_f2, 1 ) ln,
  T2.*
FROM T2 
  LEFT JOIN T3 ON t2.t3_f1=t3.t3_f1
@

CREATE VIEW V1 AS
SELECT T1.t1_f2, V2.*
FROM T1 JOIN V2 ON T1.t1_f1 = V2.t1_f1
WHERE V2.is_valid =  1 
@

RUNSTATS ON TABLE vvm.t1 WITH DISTRIBUTION AND DETAILED INDEXES ALL@
RUNSTATS ON TABLE vvm.t2 WITH DISTRIBUTION AND DETAILED INDEXES ALL@
RUNSTATS ON TABLE vvm.t3 WITH DISTRIBUTION AND DETAILED INDEXES ALL@

SELECT * FROM V2@
SELECT * FROM V1@

DROP TABLE T1@
DROP TABLE T2@
DROP TABLE T3@
DROP VIEW V1@
DROP VIEW V2@

drop FUNCTION APLUS.word(varchar( 1000 ), INTEGER)@

terminate@