Questions with Linked Progress database and UNION

Posted by Blake Smith on 11-May-2017 17:42

I am currently working on a project that needed to create a Linked Server on a MS SQL server to a Progress database. I was able to get that accomplished and it is working great. I was also able to query the data in the Progress database.

I am needing to create a view in a MS SQL database using 2 query results from the Progress database. Since originally both queries did not contain the same columns, I have attempted to create those columns as static columns and set them to null. Rather than setting them to null it is inserting the value of null. How do I set them to null instead?

My second problem with the query is that there is a field that is a date type and another field which is a number type. When I try to set them using a static column, it gives the error message error converting data type. Below is the query I am using. Any help in figuring this out is much appreciated. I am not very knowledgeable with Progress, so I apologize if this is a no brainer.

SELECT * FROM OPENQUERY([SKYWARD1],
'SELECT "HAAPRO_PROFILE"."HAAPRO-ACTIVE", "HAAPRO_PROFILE"."nalphakey", "NAME_employee"."LAST-NAME", "NAME_employee"."FIRST-NAME", "NAME_employee"."MIDDLE-NAME", "NAME_DUSER"."DUSER-ID", "NAME_employee"."INTERNET-ADDRESS", "NAME_employee"."SECOND-PHONE", "ADDRESS"."STREET-NUMBER", "ADDRESS"."STREET-NAME", "HAAPRO_PROFILE"."HAAETY-EMP-TYPE-CODE", "HAAETY_EMP_TYPES"."HAAETY-SDESC", "HAABLD_BLD_CODES"."HAABLD-SDESC", "NAME_employee"."BIRTHDATE", "NAME_employee"."FEDERAL-ID-NO", "ZIP"."ZIP-CITY", "ZIP"."ZIP-STATE", "ZIP"."ZIP-CODE", "HAABLD_BLD_CODES"."HAABLD-BLD-CODE", "NAME_employee"."ALTERNATE-ID", ''null'' AS "HPMPLN-DESC", ''NULL'' AS "HPMASN-END-DATE", ''null'' AS "SUPERVISOR-LAST-NAME", ''null'' AS "SUPERVISOR-FIRST-NAME", ''null'' AS "HAADSC-DESC-ASN", ''null'' AS "HAADSC-DESC", ''null'' AS "SUPERVISOR-NALPHAKEY", ''null'' AS "FFAMAM-EDITED-ACCT", ''null'' AS "HPMASN-HRS-PER-DAY-DEC"
 
FROM   "SKYWARD"."PUB"."HAAPRO-PROFILE" "HAAPRO_PROFILE", "SKYWARD"."PUB"."NAME-DUSER" "NAME_DUSER", "SKYWARD"."PUB"."NAME" "NAME_employee", "SKYWARD"."PUB"."HAABLD-BLD-CODES" "HAABLD_BLD_CODES", "SKYWARD"."PUB"."HAAETY-EMP-TYPES" "HAAETY_EMP_TYPES", "SKYWARD"."PUB"."ADDRESS" "ADDRESS", "SKYWARD"."PUB"."ZIP" "ZIP"

 WHERE  ("HAAPRO_PROFILE"."NAME-ID"="NAME_DUSER"."NAME-ID") AND ("HAAPRO_PROFILE"."NAME-ID"="NAME_employee"."NAME-ID") AND ("HAAPRO_PROFILE"."HAABLD-BLD-CODE"="HAABLD_BLD_CODES"."HAABLD-BLD-CODE") AND ("HAAPRO_PROFILE"."HAAETY-EMP-TYPE-CODE"="HAAETY_EMP_TYPES"."HAAETY-EMP-TYPE-CODE") AND ("NAME_employee"."ADDRESS-ID"="ADDRESS"."ADDRESS-ID") AND ("ADDRESS"."ZIP-CODE"="ZIP"."ZIP-CODE") AND "HAAPRO_PROFILE"."HAAPRO-ACTIVE"=1 AND ((("HAAPRO_PROFILE"."HAAETY-EMP-TYPE-CODE"=''SUB'' OR "HAAPRO_PROFILE"."HAAETY-EMP-TYPE-CODE"=''INSUB'')) OR (("HAABLD_BLD_CODES"."HAABLD-BLD-CODE"=''98SCA'' OR "HAABLD_BLD_CODES"."HAABLD-BLD-CODE"=''97SCU'' OR "HAABLD_BLD_CODES"."HAABLD-BLD-CODE"=''95STR'')))')

UNION

SELECT * FROM OPENQUERY([SKYWARD1],
'SELECT "HAAPRO_PROFILE"."HAAPRO-ACTIVE", "HAAPRO_PROFILE"."nalphakey", "NAME_employee"."LAST-NAME", "NAME_employee"."FIRST-NAME", "NAME_employee"."MIDDLE-NAME",  "NAME_DUSER"."DUSER-ID", "NAME_employee"."INTERNET-ADDRESS", "NAME_employee"."SECOND-PHONE", "ADDRESS"."STREET-NUMBER", "ADDRESS"."STREET-NAME", "HAAPRO_PROFILE"."HAAETY-EMP-TYPE-CODE", "HAAETY_EMP_TYPES"."HAAETY-SDESC", "HAABLD_BLD_CODES"."HAABLD-SDESC", "NAME_employee"."BIRTHDATE", "NAME_employee"."FEDERAL-ID-NO", "ZIP"."ZIP-CITY", "ZIP"."ZIP-STATE", "ZIP"."ZIP-CODE", "HPMASN_ASSIGNMENTS"."HAABLD-BLD-CODE", "NAME_employee"."ALTERNATE-ID", "HPMPLN_PLAN"."HPMPLN-DESC", "HPMASN_ASSIGNMENTS"."HPMASN-END-DATE", "NAME_supervisor"."LAST-NAME" AS "SUPERVISOR-LAST-NAME", "NAME_supervisor"."FIRST-NAME" AS "SUPERVISOR-FIRST-NAME", "HPMASN_ASSIGNMENTS"."HAADSC-DESC-ASN", "HAADSC_DESCS"."HAADSC-DESC", "NAME_supervisor"."NALPHAKEY" AS "SUPERVISOR-NALPHAKEY", "FFAMAM_ACCT_MST"."FFAMAM-EDITED-ACCT", "HPMASN_ASSIGNMENTS"."HPMASN-HRS-PER-DAY-DEC"
 
FROM   "SKYWARD"."PUB"."HAAPRO-PROFILE" "HAAPRO_PROFILE", "SKYWARD"."PUB"."NAME-DUSER" "NAME_DUSER", "SKYWARD"."PUB"."NAME" "NAME_employee", "SKYWARD"."PUB"."HAABLD-BLD-CODES" "HAABLD_BLD_CODES", "SKYWARD"."PUB"."HAAETY-EMP-TYPES" "HAAETY_EMP_TYPES", "SKYWARD"."PUB"."HPMASN-ASSIGNMENTS" "HPMASN_ASSIGNMENTS", "SKYWARD"."PUB"."ADDRESS" "ADDRESS", "SKYWARD"."PUB"."ZIP" "ZIP", "SKYWARD"."PUB"."HAADSC-DESCS" "HAADSC_DESCS", "SKYWARD"."PUB"."HPMPLN-PLAN" "HPMPLN_PLAN", "SKYWARD"."PUB"."NAME" "NAME_supervisor", "SKYWARD"."PUB"."HAAACC-ACCT-DIST" "HAAACC_ACCT_DIST", "SKYWARD"."PUB"."FFAMAM-ACCT-MST" "FFAMAM_ACCT_MST"
 
WHERE  ("HAAPRO_PROFILE"."NAME-ID"="NAME_DUSER"."NAME-ID") AND ("HAAPRO_PROFILE"."NAME-ID"="NAME_employee"."NAME-ID") AND ("HAAPRO_PROFILE"."HAABLD-BLD-CODE"="HAABLD_BLD_CODES"."HAABLD-BLD-CODE") AND ("HAAPRO_PROFILE"."HAAETY-EMP-TYPE-CODE"="HAAETY_EMP_TYPES"."HAAETY-EMP-TYPE-CODE") AND ("NAME_employee"."NAME-ID"="HPMASN_ASSIGNMENTS"."NAME-ID") AND ("NAME_employee"."ADDRESS-ID"="ADDRESS"."ADDRESS-ID") AND ("ADDRESS"."ZIP-CODE"="ZIP"."ZIP-CODE") AND ("HPMASN_ASSIGNMENTS"."HAADSC-ID-DPT"="HAADSC_DESCS"."HAADSC-ID" (+)) AND ("HPMASN_ASSIGNMENTS"."HPMPLN-ID"="HPMPLN_PLAN"."HPMPLN-ID") AND ("HPMASN_ASSIGNMENTS"."HPMASN-SUPER-ID"="NAME_supervisor"."NAME-ID") AND ("HPMASN_ASSIGNMENTS"."HPMASN-ID"="HAAACC_ACCT_DIST"."HAAACC-SRC-ID") AND ("HAAACC_ACCT_DIST"."FFAMAM-ACCT-ID"="FFAMAM_ACCT_MST"."FFAMAM-ACCT-ID") AND ("HPMASN_ASSIGNMENTS"."HPMASN-END-DATE">=(SYSDATE-(60)) AND "HPMASN_ASSIGNMENTS"."HPMASN-END-DATE"<={d ''9999-12-31''}) AND "HAAPRO_PROFILE"."HAAETY-EMP-TYPE-CODE"<>''CASEM'' AND "HAAPRO_PROFILE"."HAAPRO-ACTIVE"=1 AND  NOT ("HPMASN_ASSIGNMENTS"."HAADSC-DESC-ASN"=''Leadership Stipend'' OR "HPMASN_ASSIGNMENTS"."HAADSC-DESC-ASN"=''Professional Development'' OR "HPMASN_ASSIGNMENTS"."HAADSC-DESC-ASN"=''Special Stipend'' OR "HPMASN_ASSIGNMENTS"."HAADSC-DESC-ASN"=''Westview Stipend'') AND ("HPMPLN_PLAN"."HPMPLN-DESC"=''2015-2016 Plan'' OR "HPMPLN_PLAN"."HPMPLN-DESC"=''2016-2017 Plan'') AND "HAAACC_ACCT_DIST"."HAAACC-SRC-IND"=''PMASN''')

Posted by steve pittman on 12-May-2017 14:08

Hi Blake,
 
OpenEdge sql does not currently support NULL   as a real value, only in special contexts, like INSERT.
 
You can use a scalar function to get a null value -  for example,    “  nullif(1,1)”  will give a null value.
 
Hope this helps,  ….sjp
 

All Replies

Posted by Blake Smith on 12-May-2017 13:46

I have fixed my errors with the data and number field by casting them to the correct data type.

I am still having issues setting the static columns to null though. So if anyone has any thoughts on how to fix that, I would greatly appreciate it.

Posted by steve pittman on 12-May-2017 14:08

Hi Blake,
 
OpenEdge sql does not currently support NULL   as a real value, only in special contexts, like INSERT.
 
You can use a scalar function to get a null value -  for example,    “  nullif(1,1)”  will give a null value.
 
Hope this helps,  ….sjp
 

This thread is closed