I am using MyEclipse Reports 2.2 for generating PDF reports in my project but was struck with as it does not support the following features out-of-box.
1) Stored procedure Datasets
2) OUT parameters
I sought of worked around this issue by doing the following:
1) Used Eclipse BIRT to create a report(which supports the above features)
2) copied over the Dataset configurations from the BIRT Report XML file to MyEclipse report xml file.
This works like a charm!
Note: Make sure you have appropriate version number in your Report design XML file.
Hope this might save somebody sometime, which would otherwise be spent trying to resolve this issue.
I use this blog to share the code snippets that I find interesting and might be useful to someone.
Tuesday, July 19, 2011
SYBASE SQL for formatting Phone numbers
This sql formats a phone number without using Regular expression.
Steps:
-----
1) check for empty or NULL value.
2) check if size is greater than 10.
a) size > 10, format with extension
b) else, format as a 10 digit phone number
select
case when len(str_replace(phone_no,'',NULL)) = NULL
then ''
when len(str_replace(str_replace(str_replace(str_replace(phone_no,')',''),'(',''), '-', ''),'',NULL)) > 10
then
'('||substring(str_replace(str_replace(str_replace(str_replace(phone_no,')',''),'(',''), '-', ''),'',NULL), 1, 3)
|| ')' || substring(str_replace(str_replace(str_replace(str_replace(phone_no,')',''),'(',''), '-', ''),'',NULL), 4, 3)
|| '-' || substring(str_replace(str_replace(str_replace(str_replace(phone_no,')',''),'(',''), '-', ''),'',NULL), 7, 4)
|| ' ext:' || substring(str_replace(str_replace(str_replace(str_replace(phone_no,')',''),'(',''), '-', ''),'',NULL), 10,5)
else
'('||substring(str_replace(str_replace(str_replace(str_replace(phone_no,')',''),'(',''), '-', ''),'',NULL), 1, 3)
|| ')' || substring(str_replace(str_replace(str_replace(str_replace(phone_no,')',''),'(',''), '-', ''),'',NULL), 4, 3)
|| '-' || substring(str_replace(str_replace(str_replace(str_replace(phone_no,')',''),'(',''), '-', ''),'',NULL), 7,4)
end as PHONE_NO
from person
I have not checked if Sybase supports regular expression, in which case it might be much simpler. Please share if you have a better solution.
Steps:
-----
1) check for empty or NULL value.
2) check if size is greater than 10.
a) size > 10, format with extension
b) else, format as a 10 digit phone number
select
case when len(str_replace(phone_no,'',NULL)) = NULL
then ''
when len(str_replace(str_replace(str_replace(str_replace(phone_no,')',''),'(',''), '-', ''),'',NULL)) > 10
then
'('||substring(str_replace(str_replace(str_replace(str_replace(phone_no,')',''),'(',''), '-', ''),'',NULL), 1, 3)
|| ')' || substring(str_replace(str_replace(str_replace(str_replace(phone_no,')',''),'(',''), '-', ''),'',NULL), 4, 3)
|| '-' || substring(str_replace(str_replace(str_replace(str_replace(phone_no,')',''),'(',''), '-', ''),'',NULL), 7, 4)
|| ' ext:' || substring(str_replace(str_replace(str_replace(str_replace(phone_no,')',''),'(',''), '-', ''),'',NULL), 10,5)
else
'('||substring(str_replace(str_replace(str_replace(str_replace(phone_no,')',''),'(',''), '-', ''),'',NULL), 1, 3)
|| ')' || substring(str_replace(str_replace(str_replace(str_replace(phone_no,')',''),'(',''), '-', ''),'',NULL), 4, 3)
|| '-' || substring(str_replace(str_replace(str_replace(str_replace(phone_no,')',''),'(',''), '-', ''),'',NULL), 7,4)
end as PHONE_NO
from person
I have not checked if Sybase supports regular expression, in which case it might be much simpler. Please share if you have a better solution.
Oracle SQL - Formatting zip code
This code will format zip code in the format xxxxx-xxx..., using regular expression.
Steps involved:
1) first removes any non-numeric characters
2) if the lenght is greater than 5 characters, introduces a '-' after first 5 characters.
3) returns the non-numeric zip, otherwise.
select decode(ZIP,NULL,'', CHR(13)
||
case when length(REGEXP_REPLACE(ZIP, '[^0-9]+', '')) > 5
then
REGEXP_REPLACE(REGEXP_REPLACE(ZIP, '[^0-9]+', ''),'(.....)(.*)','\1-\2')
else
REGEXP_REPLACE(ZIP, '[^0-9]+', '')
end
)
from address;
Steps involved:
1) first removes any non-numeric characters
2) if the lenght is greater than 5 characters, introduces a '-' after first 5 characters.
3) returns the non-numeric zip, otherwise.
select decode(ZIP,NULL,'', CHR(13)
||
case when length(REGEXP_REPLACE(ZIP, '[^0-9]+', '')) > 5
then
REGEXP_REPLACE(REGEXP_REPLACE(ZIP, '[^0-9]+', ''),'(.....)(.*)','\1-\2')
else
REGEXP_REPLACE(ZIP, '[^0-9]+', '')
end
)
from address;
Subscribe to:
Posts (Atom)