[Seasar-user:16425] Can DBflute support SQL functions

[John Peter]

Hai all,

          I am new to dbflute.
I have one doubt.can dbflute support SQL functions.

i wrote one SQL functions,but i have to use this function in dbflute

can any body help me in this.....


Here i attached my sql Function


-- #GrowthRateDealerFunction#
-- !GrowthRateDealerFunctionPmb!
-- !!String Nsccode!!
-- !!Date fromDate1!!
-- !!Date toDate1!!
-- !!Date fromDate2!!
-- !!Date toDate2!!

CREATE OR REPLACE FUNCTION get_GrowthRate_dealer()
  RETURNS SETOF growth_dealer_type
  AS '
DECLARE
  growth growth_dealer_type;
  dealer RECORD;
BEGIN
  FOR dealer IN
    select distinct tdealer.dealercode from mnsc,tdealer,tdos where
tdealer.nsccode=mnsc.nsccode
and tdealer.dealercode=tdos.dealercode and
/*IF GrowthRateDealerFunctionPmb.nsccode!= null*/mnsc.nsccode=/*IF
GrowthRateDealerFunctionPmb.nsccode*/''China''

  LOOP

    growth.dealercode:=dealer.dealercode;
    growth.periodone:=(select count(*) from tdos,tdealer,mnsc,mstandard
where tdos.standardcode = mstandard.standardcode and
tdos.dealercode=tdealer.dealercode and
tdealer.nsccode = mnsc.nsccode and
tdos.dosresult = 1 and
tdealer.dealercode=dealer.dealercode and
tdos.dosnakbn = 0 and
tdos.dosdatersv1 between
/*GrowthRateDealerFunctionPmb.fromDate1*/''2006/11/1'' and
/*GrowthRateDealerFunctionPmb.toDate1*/ ''2007/4/1'');
growth.periodtwo:=(select count(*) from tdos,tdealer,mnsc,mstandard
where tdos.standardcode = mstandard.standardcode and
tdos.dealercode=tdealer.dealercode and
tdealer.nsccode = mnsc.nsccode and
tdealer.dealercode=dealer.dealercode and
tdos.dosresult = 1 and
tdos.dosnakbn = 0 and
tdos.dosdatersv1 between
/*GrowthRateDealerFunctionPmb.fromDate2*/''2007/4/1'' and
/*GrowthRateDealerFunctionPmb.toDate2*/''2008/11/1'');
if(growth.periodtwo!=0) then
growth.growth_rate =
(round((growth.periodtwo-growth.periodone)*100/(1.0*growth.periodtwo),0));
else
growth.growth_rate =0;
end if;
    RETURN NEXT growth;
  END LOOP;
  RETURN;
END;
' LANGUAGE plpgsql STRICT;

This is my SQL function.

if i run SQL2Entity,it shows following Error..

BUILD FAILED
C:\AgnieWork\workspace\nsdos\mydbflute\dbflute-0.8.4\build-torque.xml:323:
org.s
easar.dbflute.helper.jdbc.sqlfile.DfSQLExecutionFailureException: Look! Read
the
 message below.
/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
It failed to execute the SQL!

[SQL File]
..\src\main\resources\sql\growthrate\growthrateDealer.sql

[Executed SQL]
-- #GrowthRateDealerFunction#
-- !GrowthRateDealerFunctionPmb!
-- !!String Nsccode!!
-- !!Date fromDate1!!
-- !!Date toDate1!!
-- !!Date fromDate2!!
-- !!Date toDate2!!

CREATE OR REPLACE FUNCTION get_GrowthRate_dealer()
  RETURNS SETOF growth_dealer_type
  AS '
DECLARE
  growth growth_dealer_type

[SQLState]
42601

[ErrorCode]
0

[SQLException]
org.postgresql.util.PSQLException
ERROR: unterminated quoted string at or near "'
DECLARE
  growth growth_dealer_type"
* * * * * * * * * */

Thanks in advance
トマス
-------------- next part --------------
HTMLの添付ファイルを保管しました...
URL: <http://ml.seasar.org/archives/seasar-user/attachments/20081202/2aec7e70/attachment-0001.html>