May 11, 2017 at 3:33 pm
I am working on a project where I have created a Linked Server to a Progress database. The project requires me to pull data from the Progress database into a view so it can be queried from a 3rd party software. Because of the way the data is stored in the database, I am needing to do 2 queries to pull the data I need. My problem is that I need to have the results from both queries within the same view. Both queries have some of the same columns, but not all of the same columns. Is there a way to accomplish this? My first thought was to do a Union, but since both queries don't have all of the same columns I didn't know if there was a way to get that to work. Thank you in advance for helping.
UPDATE:
Here are the 2 queries that I need to use:SELECT * FROM OPENQUERY([SKYWARD1],
'SELECT "HAAPRO_PROFILE"."HAAPRO-ACTIVE", "HAAPRO_PROFILE"."nalphakey", "NAME_employee"."LAST-NAME", "NAME_employee"."FIRST-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", "NAME_employee"."MIDDLE-NAME"
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'')))')
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", "HPMASN_ASSIGNMENTS"."HAABLD-BLD-CODE", "NAME_employee"."BIRTHDATE", "NAME_employee"."FEDERAL-ID-NO", "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", "ZIP"."ZIP-CITY", "ZIP"."ZIP-STATE", "HPMASN_ASSIGNMENTS"."HAADSC-DESC-ASN", "ZIP"."ZIP-CODE", "HAADSC_DESCS"."HAADSC-DESC", "NAME_supervisor"."NALPHAKEY" AS "SUPERVISOR-NALPHAKEY", "NAME_employee"."ALTERNATE-ID", "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''')
May 11, 2017 at 3:49 pm
Is the best option to just insert nulls like what is suggested here?
May 11, 2017 at 4:39 pm
If the two result sets have a 1:1 relationship, use a JOIN. If the two sets do not overlap, use UNION.
May 12, 2017 at 8:24 am
pietlinden - Thursday, May 11, 2017 4:39 PMIf the two result sets have a 1:1 relationship, use a JOIN. If the two sets do not overlap, use UNION.
Thank you pitelinden. It is not a 1:1 relationship, so I chose to use a UNION.
After trying to use a UNION I quickly discovered that since they both did not have the same columns I needed to add static columns to 1st SELECT. I was testing it as I was going and it was working great until I got to 2 fields, ''NULL'' AS "HPMASN-END-DATE" which is a date column and the other ''null'' AS "HPMASN-HRS-PER-DAY-DEC" which is a number column. For ''NULL'' AS "HPMASN-END-DATE" I receive the error message "Conversion failed when converting date and/or time from character string." For ''null'' AS "HPMASN-HRS-PER-DAY-DEC" I receive the error message "Error converting data type varchar to numeric.". I have tried multiple changes, but cannot correct the errors. Any ideas?
The last problem I have is that rather than setting the columns to null, it is actually inserting a string of 'null'. Does anyone know how I correct that as well?
Below is the entire SQL query.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''')
May 12, 2017 at 12:48 pm
I have fixed my date and number errors by casting them to the correct data types. I am still having a problem setting the other columns to null though.
May 12, 2017 at 2:46 pm
SELECT ... NULL AS ColumnName
FROM MyTable
doesn't work? Got some dummy data we can play with?
May 12, 2017 at 2:57 pm
pietlinden - Friday, May 12, 2017 2:46 PMSELECT ... NULL AS ColumnName
FROM MyTable
doesn't work? Got some dummy data we can play with?
Come to find out OpenEdge sql does not currently support NULL as a real value, only in special contexts, like INSERT. - See more at: https://community.progress.com/community_groups/openedge_rdbms/f/18/t/33559#sthash.Vshx4pXc.dpuf
May 13, 2017 at 10:54 am
Instead of using "NULL" inside the openquery query string, use it outside. In other words something like
SELECT HAAPRO-ACTIVE, ......, CONVERT(VARCHAR(100),NULL) AS HPMASN-HRS-PER-DAY-DEC
FROM OPENQUERY([SKYWARD1],.........<do not list the HPMASN-HRS-PER-DAY-DEC inside the query>.......
UNION
SELECT .....
May 19, 2017 at 2:09 pm
Can the 3rd Party Software pull data from a stored procedure, or are you stuck with a view as a source?
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply