Hai all,<br><br> 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> RETURNS SETOF growth_dealer_type <br> AS '<br>DECLARE<br> growth growth_dealer_type;<br> dealer RECORD;<br>BEGIN<br> FOR dealer IN<br> 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*/''China''<br> <br> LOOP<br><br> growth.dealercode:=dealer.dealercode;<br>
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*/''2006/11/1'' and<br>/*GrowthRateDealerFunctionPmb.toDate1*/ ''2007/4/1'');<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*/''2007/4/1'' and <br>/*GrowthRateDealerFunctionPmb.toDate2*/''2008/11/1'');<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> RETURN NEXT growth;<br> END LOOP;<br> RETURN; <br>
END; <br>' 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> 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> RETURNS SETOF growth_dealer_type<br> AS '<br>DECLARE<br> 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 "'<br>DECLARE<br> growth growth_dealer_type"<br>* * * * * * * * * */<br><br>Thanks in advance<br>
トマス<br>