Untitled

mail@pastecode.io avatar
unknown
sql
a year ago
12 kB
3
Indexable
Never
SELECT "t"."ADDRESSADITIONAL", "t"."ADDRESSCITYID", "t"."c", "t"."c0", "t"."ADDRESSCOUNTRYID", "t"."c1", "t"."c2", "t"."ADDRESSID", "t"."ADDRESSNUMBER", "t"."ADDRESSPROVINCEID", "t"."c3", "t"."ADDRESSSTREET", "t"."ADDRESSZIPCODE", "t"."DESCRIPTION", "t"."AGREEMENTEXCEPTIONCHK", "t"."AGREEMENTID", "t"."BDPERSONALMAILING", "t"."PBRANCHID", "t"."CENTERNAME", "t"."CLIENTBRANCHID", "t"."CLIENTCENTERID", "t"."c4", "t"."ADDRESSADITIONAL0", "t"."ADDRESSCITYID0", "t"."c5", "t"."ADDRESSCOUNTRYID0", "t"."c6", "t"."ADDRESSID0", "t"."c7", "t"."ADDRESSNUMBER0", "t"."ADDRESSPROVINCEID0", "t"."c8", "t"."ADDRESSSTREET0", "t"."ADDRESSZIPCODE0", "t"."PBRANCHID0", "t"."c9", "t"."CLIENTID", "t"."CNAEID", "t"."c10", "t"."PCOMPANYID", "t"."c11", "t"."EXPECTEDWORKERS", "t"."FISCALNAME", "t"."FISCALNUMBER", "t"."FISCALTYPEID", "t"."c12", "t"."c13", "t"."GROUPID", "t"."c14", "t"."LEGALTYPEID", "t"."c15", "t"."SHORTNAME", "t"."STATUSID", "t"."TAXEXEMPTIONID", "t"."c16", "t"."c17", "t"."CLIENTID0", "t"."c18", "t"."CNAEID0", "t"."COMMENTS", "t"."PCOMPANYID0", "t"."c19", "t"."c20", "t"."UDATE", "t"."c21", "t"."ELEARNINGPOTENTIAL", "t"."c22", "t"."EMAIL", "t"."FAX", "t"."c23", "t"."c24", "t"."KBCLIE", "t"."c25", "t"."c26", "t"."c27", "t"."MARKETINGLEVELID", "t"."NAME", "t"."NONCOMMERCIALTREATMENTCHK", "t"."c28", "t"."c29", "t"."c30", "t"."c31", "t"."c32", "t"."PUSERID", "t"."PHONE1", "t"."PHONE2", "t"."c33", "t"."REASONMAILING", "t"."c34", "t"."SEGMENTFIVE", "t"."SEGMENTID", "t"."SHORTNAME0", "t"."c35", "t"."SIZEID", "t"."SSNUMBER", "t"."c36", "t"."STATUSID0", "t"."SUPPLIERNUMBER", "t"."IDATE", "t"."c37", "t"."c38", "t"."c39", "t"."c40", "t"."TERMPOTENTIAL", "t"."c41", "t"."c42", "t"."PUNITID", "t"."NAME0", "t"."c43", "t"."PROVINCEID", "t"."COUNTRYID", "t"."ADDRESSID1", "t"."AGREEMENTID0", "t"."CITYID", "t"."COUNTRYID0", "t"."ADDRESSID2", "t"."PROVINCEID0", "t"."BRANCHID", "t"."CNAEID1", "t"."COMPANYID", "t"."FISCALDOCID", "t"."GROUPID0", "t"."LEGALTYPEID0", "t"."TAXEXEMPTIONID0", "t"."CNAEID2", "t"."MARKETINGLEVELID0", "t"."SEGMENTID0", "t"."TYPEID", "t"."SIZEID0", "t"."STATUSID1", "t"."UNITID", "g18"."CITYID", "g18"."DELETEDID", "g18"."NAME", "g18"."PROVINCEID"
FROM (
    SELECT "m"."ADDRESSADITIONAL", "m"."ADDRESSCITYID", CASE
        WHEN "g1"."PROVINCEID" IS NOT NULL THEN 1
        ELSE 0
    END "c", (
        SELECT "g"."NAME"
        FROM "GTCITIES" "g"
        WHERE (((("g1"."PROVINCEID" IS NOT NULL) AND ("g1"."PROVINCEID" = "g"."PROVINCEID"))) AND ("g"."CITYID" = "m"."ADDRESSCITYID"))
        FETCH FIRST 1 ROWS ONLY) "c0", "m"."ADDRESSCOUNTRYID", CASE
        WHEN "g2"."COUNTRYID" IS NOT NULL THEN "g2"."DESCRIPTION"
        ELSE NULL
    END "c1", CASE
        WHEN "g3"."ADDRESSID" IS NOT NULL THEN "g3"."DESCRIPTION"
        ELSE NULL
    END "c2", "m"."ADDRESSID", "m"."ADDRESSNUMBER", "m"."ADDRESSPROVINCEID", CASE
        WHEN "g1"."PROVINCEID" IS NOT NULL THEN "g1"."NAME"
        ELSE NULL
    END "c3", "m"."ADDRESSSTREET", "m"."ADDRESSZIPCODE", "m0"."DESCRIPTION", "m"."AGREEMENTEXCEPTIONCHK", "m"."AGREEMENTID", "m"."BDPERSONALMAILING", "m"."PBRANCHID", "m"."CENTERNAME", "m"."CLIENTBRANCHID", "m"."CLIENTCENTERID", 0 "c4", "m1"."ADDRESSADITIONAL" "ADDRESSADITIONAL0", "m1"."ADDRESSCITYID" "ADDRESSCITYID0", CASE
        WHEN "g4"."CITYID" IS NOT NULL THEN "g4"."NAME"
        ELSE NULL
    END "c5", "m1"."ADDRESSCOUNTRYID" "ADDRESSCOUNTRYID0", CASE
        WHEN "g5"."COUNTRYID" IS NOT NULL THEN "g5"."DESCRIPTION"
        ELSE NULL
    END "c6", "m1"."ADDRESSID" "ADDRESSID0", CASE
        WHEN "g6"."ADDRESSID" IS NOT NULL THEN "g6"."DESCRIPTION"
        ELSE NULL
    END "c7", "m1"."ADDRESSNUMBER" "ADDRESSNUMBER0", "m1"."ADDRESSPROVINCEID" "ADDRESSPROVINCEID0", CASE
        WHEN "g7"."PROVINCEID" IS NOT NULL THEN "g7"."NAME"
        ELSE NULL
    END "c8", "m1"."ADDRESSSTREET" "ADDRESSSTREET0", "m1"."ADDRESSZIPCODE" "ADDRESSZIPCODE0", "m1"."PBRANCHID" "PBRANCHID0", CASE
        WHEN "m2"."BRANCHID" IS NOT NULL THEN "m2"."NAME"
        ELSE NULL
    END "c9", "m1"."CLIENTID", "m1"."CNAEID", CASE
        WHEN "g8"."CNAEID" IS NOT NULL THEN "g8"."DESCRIPTION"
        ELSE NULL
    END "c10", "m1"."PCOMPANYID", CASE
        WHEN "m3"."COMPANYID" IS NOT NULL THEN "m3"."FISCALNAME"
        ELSE NULL
    END "c11", "m1"."EXPECTEDWORKERS", "m1"."FISCALNAME", "m1"."FISCALNUMBER", "m1"."FISCALTYPEID", CASE
        WHEN "g9"."FISCALDOCID" IS NOT NULL THEN "g9"."DESCRIPTION"
        ELSE NULL
    END "c12", COALESCE("m1"."KEYACCOUNTCHK", 0) "c13", "m1"."GROUPID", CASE
        WHEN "m4"."GROUPID" IS NOT NULL THEN "m4"."NAME"
        ELSE NULL
    END "c14", "m1"."LEGALTYPEID", CASE
        WHEN "g10"."LEGALTYPEID" IS NOT NULL THEN "g10"."DESCRIPTION"
        ELSE NULL
    END "c15", "m1"."SHORTNAME", "m1"."STATUSID", "m1"."TAXEXEMPTIONID", CASE
        WHEN "g11"."TAXEXEMPTIONID" IS NOT NULL THEN "g11"."DESCRIPTION"
        ELSE NULL
    END "c16", COALESCE("m1"."WEBTIMESHEETCHK", 0) "c17", "m"."CLIENTID" "CLIENTID0", CASE
        WHEN "g12"."CNAEID" IS NOT NULL THEN "g12"."DESCRIPTION"
        ELSE NULL
    END "c18", "m"."CNAEID" "CNAEID0", "m"."COMMENTS", "m"."PCOMPANYID" "PCOMPANYID0", COALESCE("m"."CONTRACTSCOPIES", 0) "c19", CASE
        WHEN "m"."UDATE" IS NOT NULL THEN 1
        ELSE 0
    END "c20", "m"."UDATE", COALESCE("m"."ELEARNINGAMOUNT", 0.0) "c21", "m"."ELEARNINGPOTENTIAL", COALESCE("m"."ELEARNINGTARGETNUMBER", 0.0) "c22", "m"."EMAIL", "m"."FAX", COALESCE("m"."INTERIMNUMBER", 0.0) "c23", COALESCE("m"."ITERIMTARGETNUMBER", 0.0) "c24", "m"."KBCLIE", (
        SELECT "g0"."DESCRIPTION"
        FROM "STCLIENTSCENTERSLISMI" "s"
        INNER JOIN "GTFUNRANDLISMI" "g0" ON "s"."LISMIID" = "g0"."LISMIID"
        WHERE (((("m"."CLIENTID" = "s"."CLIENTID") AND ("m"."CLIENTCENTERID" = "s"."CLIENTCENTERID"))) AND ("s"."DELETEDID" = :NotDeletedId_0))
        FETCH FIRST 1 ROWS ONLY) "c25", (
        SELECT "s0"."LISMIID"
        FROM "STCLIENTSCENTERSLISMI" "s0"
        WHERE (((("m"."CLIENTID" = "s0"."CLIENTID") AND ("m"."CLIENTCENTERID" = "s0"."CLIENTCENTERID"))) AND ("s0"."DELETEDID" = :NotDeletedId_0))
        FETCH FIRST 1 ROWS ONLY) "c26", CASE
        WHEN "g13"."MARKETINGLEVELID" IS NOT NULL THEN "g13"."DESCRIPTION"
        ELSE NULL
    END "c27", "m"."MARKETINGLEVELID", "m"."NAME", "m"."NONCOMMERCIALTREATMENTCHK", (
        SELECT "s1"."NUMPERSONSCEE"
        FROM "STCLIENTSCENTERSLISMI" "s1"
        WHERE (((("m"."CLIENTID" = "s1"."CLIENTID") AND ("m"."CLIENTCENTERID" = "s1"."CLIENTCENTERID"))) AND ("s1"."DELETEDID" = :NotDeletedId_0))
        FETCH FIRST 1 ROWS ONLY) "c28", (
        SELECT "s2"."NUMPERSONSDON"
        FROM "STCLIENTSCENTERSLISMI" "s2"
        WHERE (((("m"."CLIENTID" = "s2"."CLIENTID") AND ("m"."CLIENTCENTERID" = "s2"."CLIENTCENTERID"))) AND ("s2"."DELETEDID" = :NotDeletedId_0))
        FETCH FIRST 1 ROWS ONLY) "c29", (
        SELECT "s3"."NUMPERSONSINT"
        FROM "STCLIENTSCENTERSLISMI" "s3"
        WHERE (((("m"."CLIENTID" = "s3"."CLIENTID") AND ("m"."CLIENTCENTERID" = "s3"."CLIENTCENTERID"))) AND ("s3"."DELETEDID" = :NotDeletedId_0))
        FETCH FIRST 1 ROWS ONLY) "c30", COALESCE("m"."OUTSOURCINGITTARGETNUMBER", 0.0) "c31", COALESCE("m"."OUTSOURCINGTARGETNUMBER", 0.0) "c32", "m"."PUSERID", "m"."PHONE1", "m"."PHONE2", CAST("m"."PRINCIPALINTERLOCUTORID" AS VARCHAR2(11)) "c33", "m"."REASONMAILING", CASE
        WHEN (("m"."PCOMPANYID" <> :FUN_3) OR ("m"."PCOMPANYID" IS NULL)) THEN CASE
            WHEN "g14"."SEGMENTID" IS NOT NULL THEN "g14"."DESCRIPTION"
            ELSE NULL
        END
        ELSE "g15"."DESCRIPTION"
    END "c34", "m"."SEGMENTFIVE", "m"."SEGMENTID", "m"."SHORTNAME" "SHORTNAME0", CASE
        WHEN "g16"."SIZEID" IS NOT NULL THEN "g16"."DESCRIPTION"
        ELSE NULL
    END "c35", "m"."SIZEID", "m"."SSNUMBER", CASE
        WHEN "g17"."STATUSID" IS NOT NULL THEN "g17"."DESCRIPTION"
        ELSE NULL
    END "c36", "m"."STATUSID" "STATUSID0", "m"."SUPPLIERNUMBER", "m"."IDATE", COALESCE("m"."TEMPSAVERAGE", 0.0) "c37", COALESCE("m"."TEMPSNUMBER", 0.0) "c38", COALESCE("m"."TEMPSTARGETNUMBER", 0.0) "c39", COALESCE("m"."TEMPSYESTERDAY", 0) "c40", "m"."TERMPOTENTIAL", COALESCE("m"."TERMSELECTIONS", 0.0) "c41", COALESCE("m"."TERMTARGETNUMBER", 0.0) "c42", "m"."PUNITID", "m5"."NAME" "NAME0", COALESCE("m"."WORKERSNUMBER", 0) "c43", "g1"."PROVINCEID", "g2"."COUNTRYID", "g3"."ADDRESSID" "ADDRESSID1", "m0"."AGREEMENTID" "AGREEMENTID0", "g4"."CITYID", "g5"."COUNTRYID" "COUNTRYID0", "g6"."ADDRESSID" "ADDRESSID2", "g7"."PROVINCEID" "PROVINCEID0", "m2"."BRANCHID", "g8"."CNAEID" "CNAEID1", "m3"."COMPANYID", "g9"."FISCALDOCID", "m4"."GROUPID" "GROUPID0", "g10"."LEGALTYPEID" "LEGALTYPEID0", "g11"."TAXEXEMPTIONID" "TAXEXEMPTIONID0", "g12"."CNAEID" "CNAEID2", "g13"."MARKETINGLEVELID" "MARKETINGLEVELID0", "g14"."SEGMENTID" "SEGMENTID0", "g15"."TYPEID", "g16"."SIZEID" "SIZEID0", "g17"."STATUSID" "STATUSID1", "m5"."UNITID"
    FROM "MTCLIENTSCENTERS" "m"
    LEFT JOIN "GTPROVINCES" "g1" ON "m"."ADDRESSPROVINCEID" = "g1"."PROVINCEID"
    LEFT JOIN "GTCOUNTRIES" "g2" ON "m"."ADDRESSCOUNTRYID" = "g2"."COUNTRYID"
    LEFT JOIN "GTADDRESSTYPES" "g3" ON "m"."ADDRESSID" = "g3"."ADDRESSID"
    LEFT JOIN "MTAGREEMENTS" "m0" ON "m"."AGREEMENTID" = "m0"."AGREEMENTID"
    INNER JOIN "MTCLIENTS" "m1" ON "m"."CLIENTID" = "m1"."CLIENTID"
    LEFT JOIN "GTCITIES" "g4" ON "m1"."ADDRESSCITYID" = "g4"."CITYID"
    LEFT JOIN "GTCOUNTRIES" "g5" ON "m1"."ADDRESSCOUNTRYID" = "g5"."COUNTRYID"
    LEFT JOIN "GTADDRESSTYPES" "g6" ON "m1"."ADDRESSID" = "g6"."ADDRESSID"
    LEFT JOIN "GTPROVINCES" "g7" ON "m1"."ADDRESSPROVINCEID" = "g7"."PROVINCEID"
    LEFT JOIN "MTBRANCHES" "m2" ON "m1"."PBRANCHID" = "m2"."BRANCHID"
    LEFT JOIN "GTCLIENTCNAES" "g8" ON "m1"."CNAEID" = "g8"."CNAEID"
    LEFT JOIN "MTCOMPANYS" "m3" ON "m1"."PCOMPANYID" = "m3"."COMPANYID"
    LEFT JOIN "GTCLIENTFISCALDOCS" "g9" ON "m1"."FISCALTYPEID" = "g9"."FISCALDOCID"
    LEFT JOIN "MTCLIENTSGROUPS" "m4" ON "m1"."GROUPID" = "m4"."GROUPID"
    LEFT JOIN "GTCLIENTLEGALTYPES" "g10" ON "m1"."LEGALTYPEID" = "g10"."LEGALTYPEID"
    LEFT JOIN "GTCLIENTTAXEXEMPTIONS" "g11" ON "m1"."TAXEXEMPTIONID" = "g11"."TAXEXEMPTIONID"
    LEFT JOIN "GTCLIENTCNAES" "g12" ON "m"."CNAEID" = "g12"."CNAEID"
    LEFT JOIN "GTSECTORS" "g13" ON "m"."MARKETINGLEVELID" = "g13"."MARKETINGLEVELID"
    LEFT JOIN "GTCLIENTSEGMENTS" "g14" ON "m"."SEGMENTID" = "g14"."SEGMENTID"
    LEFT JOIN "GTFUNRANDTYPES" "g15" ON "m"."SEGMENTID" = "g15"."TYPEID"
    LEFT JOIN "GTCLIENTSIZES" "g16" ON "m"."SIZEID" = "g16"."SIZEID"
    LEFT JOIN "GTCLIENTSTATUS" "g17" ON "m"."STATUSID" = "g17"."STATUSID"
    LEFT JOIN "MTUNITS" "m5" ON "m"."PUNITID" = "m5"."UNITID"
    WHERE (((("m"."DELETEDID" = :NotDeletedId_0) AND ("m"."CLIENTID" = :clientId_1))) AND ("m"."CLIENTCENTERID" = :centerId_2))
    FETCH FIRST 1 ROWS ONLY
    ) "t"
    LEFT JOIN "GTCITIES" "g18" ON "t"."PROVINCEID" = "g18"."PROVINCEID"
    ORDER BY "t"."CLIENTID0", "t"."CLIENTCENTERID", "t"."PROVINCEID", "t"."COUNTRYID", "t"."ADDRESSID1", "t"."AGREEMENTID0", "t"."CLIENTID", "t"."CITYID", "t"."COUNTRYID0", "t"."ADDRESSID2", "t"."PROVINCEID0", "t"."BRANCHID", "t"."CNAEID1", "t"."COMPANYID", "t"."FISCALDOCID", "t"."GROUPID0", "t"."LEGALTYPEID0", "t"."TAXEXEMPTIONID0", "t"."CNAEID2", "t"."MARKETINGLEVELID0", "t"."SEGMENTID0", "t"."TYPEID", "t"."SIZEID0", "t"."STATUSID1", "t"."UNITID", "g18"."CITYID"