SET TEXTMERGE ON SET TEXTMERGE TO (sys(2015)) TEXT TEXTMERGE NOSHOW CREATE or replace FUNCTION public.f_infA() RETURNS TABLE ( kuupaev date, nimi text, tasudok text, regnr text, kmprotsent text, neto numeric, myyk numeric, erisus01 bool, erisus02 bool, erisus03 bool ) AS $f_InfA$ WITH myyk as ( SELECT dok.kuupaev, dok.tasudok, klient.regnr, CASE when klient.regnr is null OR klient.regnr='' then klient.nimi else '' end::char(80) as nimi, 20 as kmprotsent, k01+k011+k01erik as myyk20, 0 as myyk9, MAX(klient.nimi)::char(80) as maxnimi, bool_or(k01erik<>0) as erisus01, bool_or(d09<>0) as erisus02, SUM( ROUND( rid.hind * case when rid.kogus<>0 then rid.kogus else 1 end* CASE when rid.kogpak<>0 then rid.kogpak else 1 end /case when dok.doktyyp<>'Y' then 1 else 1+myygikoo.kmprotsent/100 end * CASE when dok.raha=prpalk.pohiraha then 1 when dok.exchrate<>0 then dok.Exchrate else kurss.kurss end,2) ) as neto FROM prpalk, tehing JOIN dok ON tehing.dokumnr=dok.dokumnr JOIN rid ON dok.dokumnr=rid.dokumnr JOIN klient ON dok.klient=klient.kood LEFT JOIN myygikoo ON rid.myygikood=myygikoo.myygikood or ( rid.myygikood is null and myygikoo.myygikood ='' ) LEFT JOIN kurss ON dok.raha=kurss.raha AND dok.kuupaev=kurss.kuupaev LEFT JOIN toode ON rid.toode=toode.toode WHERE (rid.toode is null or toode.grupp<>'S' OR rid.toode='LE' or (toode.klass like '%T%' AND toode.klass not like '%E%' AND toode.klass not like '%M%')) and tehing.alusdok='LG' AND ( (k01+k011+k01erik)<>0 ) AND klient.nimi not ilike '%AUDIITORBÜROO%' AND klient.nimi not ilike '%ADVOKAADIBÜROO%' AND klient.nimi not ilike 'Notar %' AND klient.nimi not ilike '% Notar' AND rid.hind <> 0 -- Et ei tekiks arvetel taara 0 hinnaga väljastamisel erisuse koodi 03 AND klient.nimi not ilike 'Eraisik' AND klient.nimi not ilike 'Jaeostja' and not klient.kmdkeeld GROUP BY 1,2,3,4,5,6,7 union all SELECT dok.kuupaev, dok.tasudok, klient.regnr, CASE when klient.regnr is null OR klient.regnr='' then klient.nimi else '' end::char(80) as nimi, 9, 0 as myyk20, k02+k021 as myyk9, MAX(klient.nimi)::char(80) as maxnimi, bool_or(k01erik<>0) as erisus01, bool_or(d09<>0) as erisus02, SUM( ROUND( rid.hind * case when rid.kogus<>0 then rid.kogus else 1 end* CASE when rid.kogpak<>0 then rid.kogpak else 1 end /case when dok.doktyyp<>'Y' then 1 else 1+myygikoo.kmprotsent/100 end * CASE when dok.raha=prpalk.pohiraha then 1 when dok.exchrate<>0 then dok.Exchrate else kurss.kurss end,2) ) as neto FROM prpalk, tehing JOIN dok ON tehing.dokumnr=dok.dokumnr JOIN rid ON dok.dokumnr=rid.dokumnr JOIN klient ON dok.klient=klient.kood LEFT JOIN myygikoo ON rid.myygikood=myygikoo.myygikood or ( rid.myygikood is null and myygikoo.myygikood ='' ) LEFT JOIN kurss ON dok.raha=kurss.raha AND dok.kuupaev=kurss.kuupaev LEFT JOIN toode ON rid.toode=toode.toode WHERE (rid.toode is null or toode.grupp<>'S' OR rid.toode='LE' or (toode.klass like '%T%' AND toode.klass not like '%E%' AND toode.klass not like '%M%')) and tehing.alusdok='LG' AND (k02+k021)<>0 AND klient.nimi not ilike '%AUDIITORBÜROO%' AND klient.nimi not ilike '%ADVOKAADIBÜROO%' AND klient.nimi not ilike 'Notar %' AND klient.nimi not ilike '% Notar' AND rid.hind <> 0 -- Et ei tekiks arvetel taara 0 hinnaga väljastamisel erisuse koodi 03 AND klient.nimi not ilike 'Eraisik' AND klient.nimi not ilike 'Jaeostja' and not klient.kmdkeeld GROUP BY 1,2,3,4,5,6,7 union all SELECT omrid.adkuupaev, omrid.ettemarve, klient.regnr, CASE when klient.regnr is null OR klient.regnr='' then klient.nimi else '' end::char(80) as nimi, CASE when sum(k01+k011+k01erik)<>0 then 20 else 9 end, CASE when sum(k01+k01erik+k011)<>0 then sum( ROUND( case when omrid.hinnalis='LM' then -omrid.tasusumma else omrid.tasusumma end * case when omrid.kogus<>0 then omrid.kogus else 1 end * case when omrid.raha=prpalk.pohiraha then 1 else case when omrid.exchrate<>0 then omrid.Exchrate else kurss.kurss end END,2)) else 0 end as myyk20, CASE when sum(k02+k021)<>0 then sum( ROUND( case when omrid.hinnalis='LM' then -omrid.tasusumma else omrid.tasusumma end * case when omrid.kogus<>0 then omrid.kogus else 1 end * case when omrid.raha=prpalk.pohiraha then 1 else case when omrid.exchrate<>0 then omrid.Exchrate else kurss.kurss end END,2)) else 0 end as myyk9, MAX(klient.nimi)::char(80) as maxnimi, bool_or(k01erik<>0) as erisus01, bool_or(d09<>0) as erisus02, SUM( ROUND( case when omrid.hinnalis='LM' then -omrid.tasusumma else omrid.tasusumma end * case when omrid.kogus<>0 then omrid.kogus else 1 end * case when omrid.raha=prpalk.pohiraha then 1 else case when omrid.exchrate<>0 then omrid.Exchrate else kurss.kurss end END,2)) as neto FROM prpalk, tehing join omdok on tehing.dokumnr=omdok.dokumnr JOIN omrid ON omrid.dokumnr=tehing.dokumnr -- AND tehing.doknr=omrid.ettemarve JOIN klient ON omdok.klient=klient.kood LEFT JOIN kurss ON omrid.raha=kurss.raha AND omrid.adkuupaev=kurss.kuupaev WHERE tehing.alusdok in ('DT', 'DI') AND ( (k01+k01erik+ k011)>0 ) and omrid.ettemarve is not null and omrid.ettemarve is distinct from '' AND klient.nimi not ilike '%AUDIITORBÜROO%' AND klient.nimi not ilike '%ADVOKAADIBÜROO%' AND klient.nimi not ilike 'Notar %' AND klient.nimi not ilike '% Notar' and not klient.kmdkeeld GROUP BY 1,2,3,4 ), myyk1000 as ( SELECT regnr, nimi FROM myyk WHERE neto>0 GROUP BY 1,2 HAVING SUM(neto)>1000 ) SELECT myyk.kuupaev, myyk.maxnimi as nimi, myyk.tasudok, myyk.regnr, '20' as kmprotsent, round( SUM(myyk.neto),2) as neto, round( SUM(myyk.myyk20),2) as myyk, bool_or(erisus01) as erisus01, bool_or(erisus02) as erisus02, bool_or(myyk.kmprotsent=0) as erisus03 FROM myyk1000 JOIN myyk ON myyk1000.regnr=myyk.regnr AND myyk1000.nimi=myyk.nimi GROUP BY 1,2,3,4 HAVING SUM(myyk.myyk20)<>0 UNION ALL SELECT myyk.kuupaev, myyk.maxnimi as nimi, myyk.tasudok, myyk.regnr, '9', round( SUM(myyk.neto),2) , round( SUM(myyk.myyk9),2), false, bool_or(erisus02) as erisus02, bool_or(myyk.kmprotsent=0) as erisus03 FROM myyk1000 JOIN myyk ON myyk1000.regnr=myyk.regnr AND myyk1000.nimi=myyk.nimi GROUP BY 1,2,3,4 HAVING SUM(myyk.myyk9)<>0 UNION ALL SELECT myyk.kuupaev, myyk.maxnimi as nimi, myyk.tasudok, myyk.regnr, 'erisus20', round( SUM(myyk.neto),2) , round( SUM(myyk.myyk9),2), false, bool_or(erisus02) as erisus02, false as erisus03 FROM myyk1000 JOIN myyk ON myyk1000.regnr=myyk.regnr AND myyk1000.nimi=myyk.nimi GROUP BY 1,2,3,4 HAVING bool_or(erisus02) ORDER BY nimi, kuupaev, tasudok $f_infa$ LANGUAGE sql STABLE; RESET ROLE; ENDTEXT cSqlFail = SET('textmerge',2) SET TEXTMERGE TO SQLEXEC(m.g_server.nConnhandle, FILETOSTR(m.cSqlFail) ) connection string used: cConnString = "DRIVER={PostgreSQL Unicode};"+ ; "DATABASE=test";"+ ; "SERVER=localhost;" + ; "PORT=5432;" + ; "UID=user;" + ; "Protocol=-0;" + ; "B9=0"+ ; ";SSLMODE=allow"