I am quite perplexed by the manner in which JAL handles single and
double quotes. I have a SQL query which is tested and works perfectly
in SQL Query #####yzer to extract some data and format it for saving to
a comma delimited file. The file format requires that all fields be
enclosed in quotes. I have attempted to double the quotes ("") in JAL to
display the single quote, use the double quote special character (\")
and enclose that in quotes ("\""). There have been some changes in the
data output but I still cannot get the file formatted as it was in
SQL Query #####yzer. The quotes appear doubled and in some cases tripled.
I have enclosed the original query in the hopes that you may have a
suggestion. I am using concatex to store the query to a variable to be
passed to the databaseretrieve command.I have also enclosed a line
from a sample extract file.

select '"05","","' + upper(rtrim(isnull(cs.lastname,''))) + '","'

+ upper(rtrim(isnull(cs.firstname,''))) + '","' + upper(isnull(cs.MI,''))

+ '","' + upper(rtrim(isnull(cs.address1,''))) + '","'

+ upper(rtrim(isnull(cs.address2,''))) + '","'

+ upper(rtrim(isnull(cs.city,''))) + '","'+ isnull(cs.homephone,'') + '","'

+ isnull(cs.businessphone,'') + '","","' + rtrim(isnull(cs.DL,'')) + '","'

+ isnull(cs.email,'') + '","' + replace(convert(varchar(10),cs.created,121),'-','')

+ '","' + isnull(replace(convert(varchar(10),cs.birthdate,121),'-',''),'') + '","","","'

+ case isnull(cs.#####,0)

when 0 then 'M'

when -1 then 'F'

when 1 then 'F'

else 'M'

end + '","0","' + '","'

+ '","","","","","","","","","' + '252525059' + right(cs.#####dno,6)

+ convert(varchar(1),enterprise.dbo.checkdigit('252525059' + right(cs.#####dno,6)))

+ '","' + case

when left(ecs.#####dno,5) = '98888' then '1'

when left(ecs.#####dno,4) = '2525' then '0'

end

+ '","' + case

when left(ecs.#####dno,4) = '2525' then ecs.#####dno

when left(ecs.#####dno,5) = '98888' then ''

end

+ '","C","","","","",' + '"0",' + '""' as record
from enterprise..customers cs join enterprise..customers ecs

on cs.#####dno = ecs.link
where cs.address1 is not null and cs.lastname not like '%customer%'

and (len(ecs.#####dno) = 11 or len(ecs.#####dno) = 16)

and (left(ecs.#####dno,4) = '2525' or left(ecs.#####dno,5) = '98888')

and left(ecs.#####dno,6) '988889'

and ecs.link is not null and ((left(ecs.#####dno,4) = '2525' and cs.#####dno in
(select link from enterprise..customers

where left(link,6) = '988889' and len(link) = 11

group by link

having count(link) = 2)) or cs.#####dno in
(select link from enterprise..customers

where left(link,6) = '988889' and len(link) = 11

group by link

having count(link) = 1))
and convert(varchar(10),cs.modified,121) = '2006-07-04'
order by cs.#####dno

"05","","DOE","JOHN","","HOME ADDRESS","","CITY","123-9999999","123-9999999","","ID 123456","","20060623","19740408","","","M","0","","","","","","","","","","","2525252512345678","1","","C","","","","","0",""

Please stop using "sysadmin" as a nikname for this message borad. You are not syysadmin here. Please use your real name and I will gladly help you.

PS. And sorry for treatment of the "Query An.lyzer" name. I guess you understand why it is masked.

: I am quite perplexed by the manner in which JAL handles single and
: double quotes. I have a SQL query which is tested and works perfectly
: in SQL Query #####yzer to extract some data and format it for saving to
: a comma delimited file. The file format requires that all fields be
: enclosed in quotes. I have attempted to double the quotes ("") in
: JAL to
: display the single quote, use the double quote special character (\")
: and enclose that in quotes ("\""). There have been some
: changes in the
: data output but I still cannot get the file formatted as it was in
: SQL Query #####yzer. The quotes appear doubled and in some cases tripled.
: I have enclosed the original query in the hopes that you may have a
: suggestion. I am using concatex to store the query to a variable to be
: passed to the databaseretrieve command.I have also enclosed a line
: from a sample extract file.

: select '"05","","' +
: upper(rtrim(isnull(cs.lastname,''))) + '","'

: + upper(rtrim(isnull(cs.firstname,''))) + '","' +
: upper(isnull(cs.MI,''))

: + '","' + upper(rtrim(isnull(cs.address1,''))) + '","'

: + upper(rtrim(isnull(cs.address2,''))) + '","'

: + upper(rtrim(isnull(cs.city,''))) + '","'+ isnull(cs.homephone,'')
: + '","'

: + isnull(cs.businessphone,'') + '","","' +
: rtrim(isnull(cs.DL,'')) + '","'

: + isnull(cs.email,'') + '","' +
: replace(convert(varchar(10),cs.created,121),'-','')

: + '","' +
: isnull(replace(convert(varchar(10),cs.birthdate,121),'-',''),'') +
: '","","","'

: + case isnull(cs.#####,0)

: when 0 then 'M'

: when -1 then 'F'

: when 1 then 'F'

: else 'M'

: end + '","0","' + '","'

: +
: '","","","","","","","","","'
: + '252525059' + right(cs.#####dno,6)

: + convert(varchar(1),enterprise.dbo.checkdigit('252525059' +
: right(cs.#####dno,6)))

: + '","' + case

: when left(ecs.#####dno,5) = '98888' then '1'

: when left(ecs.#####dno,4) = '2525' then '0'

: end

: + '","' + case

: when left(ecs.#####dno,4) = '2525' then ecs.#####dno

: when left(ecs.#####dno,5) = '98888' then ''

: end

: + '","C","","","","",'
: + '"0",' + '""' as record
: from enterprise..customers cs join enterprise..customers ecs

: on cs.#####dno = ecs.link
: where cs.address1 is not null and cs.lastname not like '%customer%'

: and (len(ecs.#####dno) = 11 or len(ecs.#####dno) = 16)

: and (left(ecs.#####dno,4) = '2525' or left(ecs.#####dno,5) = '98888')

: and left(ecs.#####dno,6) '988889'

: and ecs.link is not null and ((left(ecs.#####dno,4) = '2525' and cs.#####dno
: in
: (select link from enterprise..customers

: where left(link,6) = '988889' and len(link) = 11

: group by link

: having count(link) = 2)) or cs.#####dno in
: (select link from enterprise..customers

: where left(link,6) = '988889' and len(link) = 11

: group by link

: having count(link) = 1))
: and convert(varchar(10),cs.modified,121) = '2006-07-04'
: order by cs.#####dno

:
: "05","","DOE","JOHN","","HOME
: ADDRESS","","CITY","123-9999999","123-9999999","","ID
: 123456","","20060623","19740408","","","M","0","","","","","","","","","","","2525252512345678","1","","C","","","","","0",""