question

sql123456 avatar image
sql123456 asked

T-SQL join logic issue

What is wrong with this view and all the joins? The records in the stage table, which I want to get, are excluded from the result....can anyone find out what is wrong with the logic? Select d.* FROM stage_table d left join (select base_item, max(cons_item) as cons_item from dim_itemmaster_table group by base_upc) e on CAST(ltrim(rtrim(stage_table.base_item)) AS varchar(40)) = e.base_item where (CAST(ltrim(rtrim(e.cons_item)) AS varchar(40)) +CAST(ltrim(rtrim(stage_table.div_no))+ltrim(rtrim(stage_table.store_no)) as varchar(40)) +CAST(CAST(ltrim(rtrim(stage_table.inventory_date)) AS date) as varchar(12)) +CAST(CAST(ltrim(rtrim(stage_table.inventory_date)) AS date) as varchar(12)) +CAST(CAST(Nullif(ltrim(rtrim(stage_table.inventory_qty)),'NULL') AS numeric(17,3)) as varchar(17))) NOT IN (SELECT prodt_id+locn_id+cast(date_from as varchar(12))+cast(date_to as varchar(12))+cast(stock_qty as varchar(17)) FROM Target_table where location_type = '1040')) a left join (select base_item, max(cons_item) as cons_item from dim_itemmaster_table group by base_item) c on CAST(ltrim(rtrim(a.base_item)) AS varchar(40)) = c.base_upc left join Target_table b on CAST(ltrim(rtrim(c.cons_item)) AS varchar(40)) = b.product_id and CAST(ltrim(rtrim(a.div_no)) + ltrim(rtrim(a.store)) as varchar(40)) = b.locn_id and CAST(Nullif(ltrim(rtrim(a.inv_date)),'NULL') AS date) = b.date_from and b.locn_type = '1000'
joinsviewslogic
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
David Wimbush avatar image
David Wimbush answered
I can't understand what you're trying to do here but the way I tackle complex queries is to do it one step at a time. Try commenting out most of your query. Just make sure the select from dtage_table works. Then uncomment one bit at a time and test each time. When it stops returning rows you know where to investigate.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.