March 13, 2008 at 9:36 am
The following sql runs fine directly on sql server 2005, but when ran through powerbuilder 10.5 I get the above message. Each select statement runs fine through powerbuilder 10.5, but when unioned they do not. Any suggestions on how to solve this problem?
SELECT '-------Keys-------' as ring_type,
r.code,
ktr.key_code,
ktr.key_count,
ktr.key_count as key_total,
0 as sub_key_total
FROM "dbo"."ring" r
LEFT OUTER JOIN "dbo"."key_to_ring" ktr ON r.code = ktr.ring_code
AND r.institution_code = ktr.institution_code
AND r.location_id = ktr.location_id
WHERE r.code = :as_ring_code
AND r.institution_code = :as_inst_code
AND r.location_id = :an_location_id
UNION ALL
SELECT '----Sub Rings----' as ring_type,
rsr.ring_code,
ktr.key_code,
ktr.key_count,
0 as key_total,
ktr.key_count as sub_key_total
FROM "dbo"."ring_sub_ring" rsr
LEFT OUTER JOIN "dbo"."key_to_ring" ktr ON rsr.ring_code = ktr.ring_code
AND rsr.institution_code = ktr.institution_code
AND rsr.location_id = ktr.location_id
WHERE rsr.to_ring = :as_ring_code
AND rsr.institution_code = :as_inst_code
AND rsr.location_id = :an_location_id
ORDER BY 1
March 13, 2008 at 2:03 pm
hi...
see if this helps...
SELECT '-------Keys-------' as ring_type,
r.code as key_code,
ktr.key_code,
ktr.key_count,
ktr.key_count as key_total,
0 as sub_key_total
FROM "dbo"."ring" r
LEFT OUTER JOIN "dbo"."key_to_ring" ktr ON r.code = ktr.ring_code
AND r.institution_code = ktr.institution_code
AND r.location_id = ktr.location_id
WHERE r.code = :as_ring_code
AND r.institution_code = :as_inst_code
AND r.location_id = :an_location_id
UNION ALL
SELECT '----Sub Rings----' as ring_type,
rsr.ring_code as key_code ,
ktr.key_code,
ktr.key_count,
0 as key_total,
ktr.key_count as sub_key_total
FROM "dbo"."ring_sub_ring" rsr
LEFT OUTER JOIN "dbo"."key_to_ring" ktr ON rsr.ring_code = ktr.ring_code
AND rsr.institution_code = ktr.institution_code
AND rsr.location_id = ktr.location_id
WHERE rsr.to_ring = :as_ring_code
AND rsr.institution_code = :as_inst_code
AND rsr.location_id = :an_location_id
ORDER BY 1
March 13, 2008 at 2:11 pm
adding the as ring_code did not work. i still get the error message
select error: sqlstate = 37000
the multi-part identifier "rsr.location_id" could not be bound
i forgot to add the column name to the original post.
Deb
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply