I need help with the following logic:
I have two tables
- inv_corp
- hst_ref
LOGIC:
When inv_corp.btlr_dlvr_pnt_no = hst_ref.old_dlvr_pnt_no Then select hst_ref.new_dlvr_pnt_no Else inv_corp.btlr_dlvr_pnt_no
I feel i could easily do this in the SELECT statement had there been a static field I could join to of the table. But being that the joining of these two tables is dependent on a criteria first it make this a little trickier for me.
Any help would be much appreciated
If I understand correctly, then something like this:
SELECT ..., ISNULL(hr.new_dlvr_pnt_no, ic.btlr_dlvr_pnt_no) AS hst_ref_lookup, ...
FROM inv_corp ic
LEFT OUTER JOIN hst_ref hr ON hr.old_dlvr_pnt_no = ic.btlr_dlvr_pnt_no
maybe this?
create table inv_corp(btlr_dlvr_pnt_no int);
insert into inv_corp
select top 10 object_id from sys.objects
create table hst_ref(old_dlvr_pnt_no int, new_dlvr_pnt_no int)
insert into hst_ref
select top 10 object_id, object_id * 2 from sys.objects
select *,
case
when i.btlr_dlvr_pnt_no = h.old_dlvr_pnt_no then h.new_dlvr_pnt_no
else i.btlr_dlvr_pnt_no
end as boomBam
from inv_corp i, hst_ref h
How would I join i and h table though? I don't have a particular column to just do the JOIN on. The join is determined by rather the btlr_dlvr_pnt_no from table "i" equals table's "h" column or not
Did you try out the recommended solutions ?
You're not at all clear on what you want to do, but this is my best guess for now. If you're trying to match on one, and if no match, then match on the other, a CASE statement can't easily do that for you.
SELECT ..., ISNULL(hr1.new_dlvr_pnt_no, h2.new_dlvr_pnt_no) AS hst_ref_lookup, ...
FROM inv_corp ic
LEFT OUTER JOIN hst_ref hr1 ON hr1.old_dlvr_pnt_no = ic.btlr_dlvr_pnt_no
LEFT OUTER JOIN hst_ref hr2 ON hr1.old_dlvr_pnt_no IS NULL AND hr1.new_dlvr_pnt_no = ic.btlr_dlvr_pnt_no