SQL combine two convert functions into one field

[shelby101]

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?ponder. 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 ](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

 

[Andrzejek]

What do you get if you do not combine them?

Something like:[tt]
convert ('character', nullif( pro_element ( "dw_fact"."dwf_cha" ,5 ,5),'?') )
AS 'Combined_01',
convert ('character', nullif( pro_element ( "dw_fact"."dwf_cha" ,3 ,3),'?') )
AS 'Combined_02'[/tt]

What do you get in the 2 columns: Combined_01 and Combined_02

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[shelby101]

Hi Andy,

The two expressions in this issue do return data individually. They are "WorkMobile" and "OptOutMobile" listed above in the statement. I attached a sample of what is returning and the desired outcome.

 

[fredericofonseca]

looks like a char(0) in the field.

try

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'
     , replace(convert ('character', nullif( pro_element ( dw_fact.dwf_cha ,5 ,5),'?') ), char(0), '')
        + replace(convert ('character', nullif( pro_element ( dw_fact.dwf_cha ,3 ,3),'?') ), char(0), '') AS 'Combined'
     , convert(varbinary(100), convert ('character', nullif( pro_element ( dw_fact.dwf_cha ,5 ,5),'?') )
        + convert ('character', nullif( pro_element ( dw_fact.dwf_cha ,3 ,3),'?') )) as rawdata
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

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[shelby101]

fredericofonseca

Hi,
I tried to suggestion..... thank you very much by the way.

When I tried the code I got an Syntax error in SQL statement at or about "varbinary(100)

 

[fredericofonseca]

remove those lines. the important one is the replace ... char(0) one.

and what version of sql server do you have? either the one you have does not support varbinary or I have a slight syntax error which you should be able to fix as it would be around open/close brackets

oops. you have progress, not sql server. see if the replace still works though.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886