SELECT
--  c.*
--  s.*
--  ss.*
--  c.DTYPE AS cl_dtype,
--  s.DTYPE AS s_DTYPE,
    c.LOCALSYSTEMID AS cl_localsystemid,
    s.LOCALSYSTEMID AS s_localsystemid,
    ss.LOCALSYSTEMID AS ss_localsystemid,
    s.IDENTIFITIER AS identifitier,
--  c.TITLE AS cl_title,
--  c.STARTDATE AS cl_date_start_inet,
    c.OWNER_LOGIN AS cl_owner_login,
--  s.OWNER_LOGIN AS s_owner_login,
--  ss.OWNER_LOGIN AS s_owner_login,
--  c.CURRENTVERSION AS cl_currentversion,
    c.NLS AS cl_NLS,
    c.APP_PKID AS cl_APPPKID,
    c.BIRTHPLACE ,
    c.NLSTYPE ,
    ss.TECHSERVICE_LOCALSYSTEMID AS techservice_localsystemid
--  s.TARIFFPLAN_LOCALSYSTEMID
FROM
    MDM6.CLIENT c
        INNER JOIN
    MDM6.SUBSCRIBER s
        ON s.CLIENT_LOCALSYSTEMID = c.LOCALSYSTEMID
        AND s.CLIENT_APP_PKID = c.APP_PKID
        AND s.CLIENT_VERSION = c.VERSION
        INNER JOIN
    MDM6.SUBSCRIBERSERVICE ss
        ON ss.SUBSCRIBER_LOCALSYSTEMID = s.LOCALSYSTEMID
        AND ss.SUBSCRIBER_APP_PKID = s.APP_PKID
        AND ss.SUBSCRIBER_VERSION = s.VERSION
WHERE 1=1
--  AND c.NLSTYPE = 'RT.VT.1.ACCOUNT_NUM'
--  AND  c.BIRTHPLACE IN ('Кострома', 'гор. Кострома', 'г Кострома' ,'г Ярославль', 'г Иваново', 'Г. ЯРОСЛАВЛЬ', 'ЯРОСЛАВЛЬ', 'ГОР. ЯРОСЛАВЛЬ', 'Г. ИВАНОВО')
--  AND c.BIRTHPLACE LIKE '%КИРОВ%'
    AND c.DELETED = 0
    AND ss.DELETED = 0
    AND s.DELETED = 0
    AND c.version = 0
    AND ss.VERSION = 0
    AND s.VERSION = 0
    AND c.ENDDATE > sysdate
    AND c.DTYPE = 'PhysicalClient'
    AND s.DTYPE = 'SubscriberIP'
--  AND s.LOCALSYSTEMID = '2954834920'
--  AND c.nls = '408000067119'
--  AND c.LOCALSYSTEMID = '14798555505'
--  AND s.IDENTIFITIER = 'wpxjw2gb@kirov.ru'
    AND ss.TECHSERVICE_LOCALSYSTEMID = 'YADISC_5'
    AND c.OWNER_LOGIN = 'center_vvf'
--  AND c.OWNER_LOGIN = 'testLogin'
--  AND c.APP_PKID = 8802
--  AND rownum <= 5