Crystal 2011 sql command (combine two convert functions)

Hi,
I am using Crystal 2011 sql command to pull data from a progress environment. see command below. Problem is the highlighted line is only returning the second convert function. Can someone please tell me what I am doing wrong?. I would really appreciate help.

SELECT "pp_organisation"."ppo_kco", "pp_organisation"."ppo_dept", "pp_organisation"."hrp_id", "pp_organisation"."ppo_surname", "pp_organisation"."ppo_1stname", "pp_organisation"."ppo_2ndname", "pp_organisation"."por_suffix", "pp_organisation"."ppo_seq","dw_fact"."kco", "dw_fact"."dwf_cha", "dw_fact"."dwf_key1", "dw_fact"."dwf_key2", "dw_fact"."dwf_key3",
convert ('character', nullif( pro_element ( "dw_fact"."dwf_cha" ,1 ,1),'?') ) AS 'HomePhone',
convert ('character', nullif( pro_element ( "dw_fact"."dwf_cha" ,2 ,2),'?') ) AS 'WorkEmail',
convert ('character', nullif( pro_element ( "dw_fact"."dwf_cha" ,3 ,3),'?') ) AS 'OptOutMobile',
convert ('character', nullif( pro_element ( "dw_fact"."dwf_cha" ,4 ,4),'?') ) AS 'MobileEmail',
convert ('character', nullif( pro_element ( "dw_fact"."dwf_cha" ,5 ,5),'?') ) AS 'WorkMobile',
convert ('character', nullif( pro_element ( "dw_fact"."dwf_cha" ,6 ,6),'?') ) AS 'OfficePhone',
convert ('character', nullif( pro_element ( "dw_fact"."dwf_cha" ,7,7),'?') ) AS 'OfficePhoneExt',
[highlight #E9B96E](convert ('character', nullif( pro_element ( "dw_fact"."dwf_cha" ,5 ,5),'?') ) + convert ('character', nullif( pro_element ( "dw_fact"."dwf_cha" ,3 ,3),'?') ))AS 'Combined'[/highlight]
FROM "COINS"."PUB"."pp_organisation" "pp_organisation" INNER JOIN "COINS"."PUB"."dw_fact" "dw_fact" ON "pp_organisation"."ppo_kco"=CAST("dw_fact"."dwf_key1" AS INT) AND
"pp_organisation"."ppo_seq"= "dw_fact"."dwf_key2"
WHERE "dw_fact"."dwt_type"='SCUSGPR001' AND "pp_organisation"."ppo_leaver"=0

 

[hilfy]

This is more of a Progress SQL question than a Crystal question. Since you already have the parts to this in the query, I would remove that line all together and use the "WorkMobile" and "OptOutMoble" fields in a formula in Crystal to get this information.

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[shelby101]

Hi Dell,

Thank you for your response. Combining the fields in crystal will not be useful with particular report. The access query I have to connect to could potentially have both workmobile and optoutmobile data. The field that I am trying to create is the field that I want to use for my link to both tables.

I will repost under a SQL forum.

Thanks again.