Hello,
I have to re-write a weird (out of the box of an application) query.
In order to make it a little simpler - but not to ruin the logic.
and I am clueless where to start or how to do it
SELECT ( CASE WHEN ( (
mobili$CUSTOMERS.MOB_PASSPORT = 'Y' ) )
THEN ( '9' ) ELSE ( '0' )
END ) ,
( CASE WHEN
( (
mobili$CUSTOMERS.MOB_PASSPORT <> 'Y'
) )
THEN ( mobili$CUSTOMERS.CUSTNAME )
ELSE ( TOBILA.TOBILAf.rstrind( NVL(trim( trim('00000000' )
||
trim( mobili$CUSTOMERS.MOB_PASSPORTNUM ) ),U' ') , 1 ,9 ) )
END ) ,
mobili$CUSTOMERS.MOB_FIRSTNAME ,
mobili$CUSTOMERS.MOB_SURNAME ,
mobili$CUSTOMERS.CUSTDES ,
mobili$CUSTOMERS.MOB_BIRTHDAY ,
(
CASE WHEN
( (
mobili$CUSTOMERS.MOB_BIRTHDAY = 0
) )
THEN ( 0 )
ELSE ( ( ( TOBILA.TOBILAf.get_year (18383040 ) - TOBILA.TOBILAf.get_year ( mobili$CUSTOMERS.MOB_BIRTHDAY ) ) - (
CASE WHEN
( (
TOBILA.TOBILAf.get_month ( 18383040 ) > TOBILA.TOBILAf.get_month ( mobili$CUSTOMERS.MOB_BIRTHDAY )
) )
THEN ( 0 )
ELSE ( (
CASE WHEN
( (
TOBILA.TOBILAf.get_month (18383040 ) < TOBILA.TOBILAf.get_month ( mobili$CUSTOMERS.MOB_BIRTHDAY )
) )
THEN ( 1 )
ELSE ( ( CASE
WHEN (
( TOBILA.TOBILAf.get_day ( 18383040 ) >= TOBILA.TOBILAf.get_day ( mobili$CUSTOMERS.MOB_BIRTHDAY )
) )
THEN ( 0 )
ELSE ( 1 ) END ) )
END ) ) END ) ) )
END ) , mobili$CUSTOMERS.MOB_GENDER ,
mobili$MOB_CUSTOMERS.CELLPHONE , mobili$CUSTOMERS.PHONE ,
mobili$CUSTOMERS.FAX , mobili$CUSTOMERSA.EMAIL ,
mobili$BRANCHES.BRANCHDES , mobili$MOB_CUSTOMERSM.ALLOWFLAG ,
mobili$MOB_CUSTOMERSM.ALLOWSHOWFLAG ,
mobili$MOB_CUSTOMERS.NOPOSTREMIND , mobili$CUSTOMERS.EDOCUMENTS ,
mobili$MOB_PRIVILEGES.PRIVILEGECODE , mobili$MOB_PRIVILEGES.PRIVILEGEDES ,
mobili$CUSTSTATS.MOB_JUSTICEPROC , mobili$MOB_PRIVILEGES.PAYFACTOR ,
mobili$CUSTOMERS.CUSTNAME , mobili$CUSTOMERS.CUST ,
mobili$CUSTOMERS.BRANCH , mobili$BRANCHES.BRANCHNAME ,
mobili$CUSTOMERS.CUST , mobili$CUSTOMERS.CUST ,
mobili$CUSTOMERS.MOB_PASSPORT , mobili$CUSTOMERS.CUST ,
mobili$CUSTOMERS.MOB_PASSPORTNUM , mobili$CUSTOMERS.MOB_PRIVILEGE ,
mobili$CUSTOMERS.CUSTSTAT
FROM TOBILA.mobili$CUSTOMERS , TOBILA.mobili$MOB_CUSTOMERSM ,
TOBILA.mobili$BRANCHES , TOBILA.mobili$CUSTOMERSA ,
TOBILA.mobili$MOB_CUSTOMERS , TOBILA.mobili$MOB_PRIVILEGES ,
TOBILA.mobili$CUSTSTATS
WHERE
( (
CASE WHEN
( (
mobili$CUSTOMERS.MOB_PASSPORT = 'Y'
) )
THEN ( '9' ) ELSE ( '0' )
END
)
= '0'
) AND
( (
CASE WHEN
( (
mobili$CUSTOMERS.MOB_PASSPORT <> 'Y'
) )
THEN ( mobili$CUSTOMERS.CUSTNAME )
ELSE ( TOBILA.TOBILAf.rstrind( NVL(trim( trim( '00000000' )
||
trim( mobili$CUSTOMERS.MOB_PASSPORTNUM ) ),U' ') , 1 , 9 ) )
END
) = '3056533168'
) AND
( mobili$CUSTOMERS.CUSTNAME <> ' '
)
AND (
1 =1
)
AND
( mobili$CUSTOMERS.CUST = (
CASE WHEN
( (
( mobili$CUSTOMERS.MOB_PASSPORT = 'Y'
)
AND (
TOBILA.TOBILAf.isnumeric ( mobili$CUSTOMERS.MOB_PASSPORTNUM ) = 0
) ) )
THEN ( 0 ) ELSE ( mobili$CUSTOMERS.CUST )
END )
) AND
( mobili$MOB_CUSTOMERSM.CUST = mobili$CUSTOMERS.CUST
)
AND ( mobili$BRANCHES.BRANCH = mobili$CUSTOMERS.BRANCH
)
AND (
mobili$CUSTOMERSA.CUST = mobili$CUSTOMERS.CUST )
AND (
mobili$MOB_CUSTOMERS.CUST = mobili$CUSTOMERS.CUST )
AND (
mobili$MOB_PRIVILEGES.PRIVILEGE = mobili$CUSTOMERS.MOB_PRIVILEGE )
AND (
mobili$CUSTSTATS.CUSTSTAT = mobili$CUSTOMERS.CUSTSTAT )
ORDER BY 1 OFFSET 0 ROWS
FETCH NEXT 15000 ROWS ONLY
query_to_change.txt (9.14 KB)