Hello All,
I'm trying to execute a left outer join on two tables - it's a bit convoluted, but what I'm trying to achieve is something like all rows in a subset of the first table and, where available, the value of a single column in a related table (also constrained by a condition). This is to be executed under ADO.Net using an ODBC provider (05.20.0039 (b0034, u0022)) and running against an OpenEdge Progress 10.1b database.
The problem is that although the query returns all values for the relevant rows of the first table, the related column from the second table (rubric_discount) is never filled out (it is returned as DBNull) - even when the related value exists.
The query looks like this:
SELECT rub.id_rubric, rub.description, rub.abrev_description, rub.t031id, rub.t420id, rub.edit_by_prod, rub.state, rub.date_state, PackRub.rubric_discount
FROM (SELECT r.id_rubric, r.description, r.abrev_description, r.t031id, r.t420id, r.edit_by_prod, r.state, r.date_state
FROM pub.K_CRE_RUBRICS r
WHERE r.id_rubric >= 2000
AND r.id_rubric <= 2999) rub
LEFT OUTER JOIN (SELECT rubric_discount,id_rubric,id_cs_pack
FROM PUB.K_CRE_CS_PACKS_RUB
WHERE id_cs_pack = 1) PackRub
ON PackRub.id_rubric = rub.id_rubric
ORDER by rub.id_rubric
When run against an MsSql manager linked server pointing to the progress database this query works fine (with a little tweaking on table name qualification) and returns rubric_discount on lines where it is avaiable; when I run it in my code, rubric_discount is always null.
I've been running laps around this one for a while now and am on the verge of dropping it and looking up the rubric_discount manually on a row by row basis, so if anyone there has any thoughts that might help, I'd appreciate it.
Thanks.
I'm trying to execute a left outer join on two tables - it's a bit convoluted, but what I'm trying to achieve is something like all rows in a subset of the first table and, where available, the value of a single column in a related table (also constrained by a condition). This is to be executed under ADO.Net using an ODBC provider (05.20.0039 (b0034, u0022)) and running against an OpenEdge Progress 10.1b database.
The problem is that although the query returns all values for the relevant rows of the first table, the related column from the second table (rubric_discount) is never filled out (it is returned as DBNull) - even when the related value exists.
The query looks like this:
SELECT rub.id_rubric, rub.description, rub.abrev_description, rub.t031id, rub.t420id, rub.edit_by_prod, rub.state, rub.date_state, PackRub.rubric_discount
FROM (SELECT r.id_rubric, r.description, r.abrev_description, r.t031id, r.t420id, r.edit_by_prod, r.state, r.date_state
FROM pub.K_CRE_RUBRICS r
WHERE r.id_rubric >= 2000
AND r.id_rubric <= 2999) rub
LEFT OUTER JOIN (SELECT rubric_discount,id_rubric,id_cs_pack
FROM PUB.K_CRE_CS_PACKS_RUB
WHERE id_cs_pack = 1) PackRub
ON PackRub.id_rubric = rub.id_rubric
ORDER by rub.id_rubric
When run against an MsSql manager linked server pointing to the progress database this query works fine (with a little tweaking on table name qualification) and returns rubric_discount on lines where it is avaiable; when I run it in my code, rubric_discount is always null.
I've been running laps around this one for a while now and am on the verge of dropping it and looking up the rubric_discount manually on a row by row basis, so if anyone there has any thoughts that might help, I'd appreciate it.
Thanks.
