| Author |
Message
|
| GRIFF |
 Posted: Wed Jan 17, 2007 11:00 am Post subject: 6.0.0.2 Interim fix 5 to 6.0.2 - esql issues |
 |
|
Acolyte
Joined: 19 Sep 2005
Posts: 64
Location: VA
|
Please help. I have upgrade my toolkit in hopes to improve performace but not all my functions and procedures in the esql files are showing errors which were not there before I upgraded. Now I have tons of red Xs on my esql files stating 'Incorrect Function or Procedure name "Procedure name here" or argument count'. The red Xs are on the create procedure statements which look like this:
CREATE PROCEDURE AddSAPH(IN refOutputRoot REFERENCE)
Please help when you have time,
GRIFF |
|
| Back to top |
|
 |
| GRIFF |
| Posted: Wed Jan 17, 2007 1:07 pm Post subject: |
|
|
Acolyte
Joined: 19 Sep 2005
Posts: 64
Location: VA
|
I have part of the problem solved; I had to unhide the broker schemas and import directly into them. Now I am getting what seems to be a true esql issue. Getting sytax error on the create of the function and the start of the CASE o; anyone see anything?
BROKER SCHEMA com
CREATE FUNCTION convertDate(IN inDate CHAR,IN mode INT) RETURNS CHAR
BEGIN
/*
1 - CCYYMMDD to CCYY-MM-DD
2 - DATE 'CCYY-MM-DD' to CCYY-MM-DD
3 - DATE 'CCYY-MM-DD' to CCYYMMDD
4 - MM/DD/YY or MM/D/Y or MM/DD/CCYY to CCYYMMDD
*/
DECLARE cYear CHAR;
DECLARE cMonth CHAR;
DECLARE cDay CHAR;
DECLARE cCentury CHAR;
DECLARE iPosition INTEGER;
CASE mode
-- CCYYMMDD to CCYY-MM-DD
WHEN 1 THEN
RETURN SUBSTRING(inDate FROM 1 FOR 4)
|| '-'
|| SUBSTRING(inDate FROM 5 FOR 2)
|| '-'
|| SUBSTRING(inDate FROM 7 FOR 2);
-- 'DATE 'CCYY-MM-DD'' to CCYY-MM-DD
WHEN 2 THEN
RETURN SUBSTRING(inDate FROM 7 FOR 10);
-- 'DATE 'CCYY-MM-DD'' to CCYYMMDD
WHEN 3 THEN
RETURN SUBSTRING(inDate FROM 7 FOR 4)
|| SUBSTRING(inDate FROM 12 FOR 2)
|| SUBSTRING(inDate FROM 15 FOR 2);
-- 1 - MM/DD/YY
-- 2 - MM/D/Y
WHEN 4 THEN
SET iPosition = POSITION('/' IN inDate);
IF iPosition > 0 THEN
SET cMonth = SUBSTRING(inDate FROM 1 FOR iPosition-1);
IF LENGTH(cMonth) = 1 THEN
SET cMonth = '0' || cMonth;
END IF;
END IF;
SET inDate = SUBSTRING(inDate FROM iPosition+1);
SET iPosition = POSITION('/' IN inDate);
SET cDay = SUBSTRING(inDate FROM 1 FOR iPosition-1);
IF LENGTH(cDay) = 1 THEN
SET cDay = '0' || cDay;
END IF;
SET cYear = SUBSTRING(inDate FROM iPosition+1);
IF LENGTH(cYear) = 1 THEN
Set cYear = '0' || cYear;
END IF;
--CH0003 - LC - 2006-09-18 - added logic below to allow for MM/DD/CCYY
IF LENGTH(cYear) = 4 THEN
SET cCentury = SUBSTRING(cYear FROM 1 FOR 2);
SET cYear = SUBSTRING(cYear FROM 3);
ELSE
SET cCentury = SUBSTRING(CAST(EXTRACT(YEAR FROM CURRENT_DATE) AS CHAR) FROM 1 FOR 2);
END IF;
-- SET cCentury = SUBSTRING(CAST(EXTRACT(YEAR FROM CURRENT_DATE) AS CHAR) FROM 1 FOR 2);
--CH0003 - End.
RETURN cCentury || cYear || cMonth || cDay;
END CASE; |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Wed Jan 17, 2007 4:05 pm Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
I see your begin,
Where is the corresponding end?
After end case?
Anyways I like to use a variable and set it in the case statement and use only one return in the end. But then that's just my programing style. 
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| GRIFF |
| Posted: Wed Jan 17, 2007 5:29 pm Post subject: |
|
|
Acolyte
Joined: 19 Sep 2005
Posts: 64
Location: VA
|
| Sorry; there is a corresponding END; that I forgot to include. Thanks for looking - any ideas? |
|
| Back to top |
|
|
| elvis_gn |
| Posted: Wed Jan 17, 2007 9:09 pm Post subject: |
|
|
Padawan
Joined: 08 Oct 2004
Posts: 1905
Location: Dubai
|
Hi GRIFF,
| ESQL Pdf wrote: |
The following example shows a CASE function with a simple WHEN clause. In this example, the CASE can be determined only by one variable that is specified next to the CASE keyword.
DECLARE CurrentMonth CHAR;
DECLARE MonthText CHAR;
SET CurrentMonth = SUBSTRING(InputBody.Invoice.InvoiceDate FROM 6 FOR 2);
SET MonthText = CASE CurrentMonth
WHEN ’01’ THEN ’January’
WHEN ’02’ THEN ’February’
WHEN ’03’ THEN ’March’
WHEN ’04’ THEN ’April’
WHEN ’05’ THEN ’May’
WHEN ’06’ THEN ’June’
ELSE ’Second half of year’
END
The following example shows a CASE function with a searched-when-clause. This example is still determined by one variable CurrentMonth:
DECLARE CurrentMonth CHAR;
DECLARE MonthText CHAR;
SET CurrentMonth = SUBSTRING(InputBody.Invoice.InvoiceDate FROM 6 FOR 2);
SET MonthText = CASE
WHEN Month = ’01’ THEN ’January’
WHEN Month = ’02’ THEN ’February’
WHEN Month = ’03’ THEN ’March’
WHEN Month = ’04’ THEN ’April’
WHEN Month = ’05’ THEN ’May’
WHEN Month = ’06’ THEN ’June’
ELSE ’Second half of year
END |
I think you need to do a
| Code: |
| SET dummyVar = CASE mode |
Regards. |
|
| Back to top |
|
|
|
|
