Hai all,<br><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; I am new to dbflute.<br>I have one doubt.can dbflute support SQL functions.<br><br>i wrote one SQL functions,but i have to use this function in dbflute<br><br>can any body help me in this.....<br>
<br><br>Here i attached my sql Function<br><br><br>-- #GrowthRateDealerFunction#<br>-- !GrowthRateDealerFunctionPmb!<br>-- !!String Nsccode!!<br>-- !!Date fromDate1!!<br>-- !!Date toDate1!!<br>-- !!Date fromDate2!!<br>-- !!Date toDate2!!<br>
<br>CREATE OR REPLACE FUNCTION get_GrowthRate_dealer() <br>&nbsp; RETURNS SETOF growth_dealer_type <br>&nbsp; AS &#39;<br>DECLARE<br>&nbsp; growth growth_dealer_type;<br>&nbsp; dealer RECORD;<br>BEGIN<br>&nbsp; FOR dealer IN<br>&nbsp;&nbsp;&nbsp; select distinct tdealer.dealercode from mnsc,tdealer,tdos where tdealer.nsccode=mnsc.nsccode<br>
and tdealer.dealercode=tdos.dealercode and <br>/*IF GrowthRateDealerFunctionPmb.nsccode!= null*/mnsc.nsccode=/*IF GrowthRateDealerFunctionPmb.nsccode*/&#39;&#39;China&#39;&#39;<br>&nbsp;&nbsp;&nbsp; <br>&nbsp; LOOP<br><br>&nbsp;&nbsp;&nbsp; growth.dealercode:=dealer.dealercode;<br>
&nbsp;&nbsp;&nbsp; growth.periodone:=(select count(*) from tdos,tdealer,mnsc,mstandard<br>where tdos.standardcode = mstandard.standardcode and<br>tdos.dealercode=tdealer.dealercode and<br>tdealer.nsccode = mnsc.nsccode and<br>tdos.dosresult = 1 and<br>
tdealer.dealercode=dealer.dealercode and<br>tdos.dosnakbn = 0 and<br>tdos.dosdatersv1 between /*GrowthRateDealerFunctionPmb.fromDate1*/&#39;&#39;2006/11/1&#39;&#39; and<br>/*GrowthRateDealerFunctionPmb.toDate1*/ &#39;&#39;2007/4/1&#39;&#39;);<br>
growth.periodtwo:=(select count(*) from tdos,tdealer,mnsc,mstandard<br>where tdos.standardcode = mstandard.standardcode and<br>tdos.dealercode=tdealer.dealercode and<br>tdealer.nsccode = mnsc.nsccode and<br>tdealer.dealercode=dealer.dealercode and<br>
tdos.dosresult = 1 and<br>tdos.dosnakbn = 0 and<br>tdos.dosdatersv1 between /*GrowthRateDealerFunctionPmb.fromDate2*/&#39;&#39;2007/4/1&#39;&#39; and <br>/*GrowthRateDealerFunctionPmb.toDate2*/&#39;&#39;2008/11/1&#39;&#39;);<br>
if(growth.periodtwo!=0) then<br>growth.growth_rate = (round((growth.periodtwo-growth.periodone)*100/(1.0*growth.periodtwo),0));<br>else<br>growth.growth_rate =0;<br>end if;<br>&nbsp;&nbsp;&nbsp; RETURN NEXT growth;<br>&nbsp; END LOOP;<br>&nbsp; RETURN;&nbsp; <br>
END;&nbsp; <br>&#39; LANGUAGE plpgsql STRICT;<br><br>This is my SQL function.<br><br>if i run SQL2Entity,it shows following Error..<br><br>BUILD FAILED<br>C:\AgnieWork\workspace\nsdos\mydbflute\dbflute-0.8.4\build-torque.xml:323: org.s<br>
easar.dbflute.helper.jdbc.sqlfile.DfSQLExecutionFailureException: Look! Read the<br>&nbsp;message below.<br>/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *<br>It failed to execute the SQL!<br><br>[SQL File]<br>
..\src\main\resources\sql\growthrate\growthrateDealer.sql<br><br>[Executed SQL]<br>-- #GrowthRateDealerFunction#<br>-- !GrowthRateDealerFunctionPmb!<br>-- !!String Nsccode!!<br>-- !!Date fromDate1!!<br>-- !!Date toDate1!!<br>
-- !!Date fromDate2!!<br>-- !!Date toDate2!!<br><br>CREATE OR REPLACE FUNCTION get_GrowthRate_dealer()<br>&nbsp; RETURNS SETOF growth_dealer_type<br>&nbsp; AS &#39;<br>DECLARE<br>&nbsp; growth growth_dealer_type<br><br>[SQLState]<br>42601<br>
<br>[ErrorCode]<br>0<br><br>[SQLException]<br>org.postgresql.util.PSQLException<br>ERROR: unterminated quoted string at or near &quot;&#39;<br>DECLARE<br>&nbsp; growth growth_dealer_type&quot;<br>* * * * * * * * * */<br><br>Thanks in advance<br>
トマス<br>