0

In my dimension table for abandoned calls I have the ID 1 Code NO , ID 2 Code YES I am wanting to load these ID's into the fact table based on whether or not the call was abandoned using a join.

How ever the problem I'm having it that the Abandoned value in my database is NULL for NO and 1 for YES.

So when i join 

 INNER JOIN datamartend.dbo.Abandoned_Call_Dim 
         ON incoming_measure.Abandoned = Abandoned_Call_Dim.abandoned_code

It's pulling no results?

Any ideas around this?

Basically what is needed is:

I want the abandoned ID from the abandoned dimension to be 2 if the abandonded value in measure is null and abandoned id 2 if not null

Thanks

Richard C
  • 389
  • 2
  • 5
  • 16
  • are you saying there are NULLs in both the tables `incoming_measure` and `Abandoned_Call_Dim`? Can you show more of the query as other sections of the query might be responsible for this behaviour – whytheq Mar 29 '13 at 11:40
  • No theres just a null in the maincall table if the call is not abandoned. In the abandoned dim the id for not abandoned is 3 In the main table theres a 1 if its abandoned and in the dimension the is is 2. I cant work out how to match the value in the maincall to the dimension id? – Richard C Mar 29 '13 at 11:48
  • can you not just replace sections of your joins with `ISNULL(MAINCALLTABLE.Abandoned,3)`? – whytheq Mar 29 '13 at 12:08
  • hmm i tried that but i didn't seem to work, was a good idea tho. – Richard C Mar 29 '13 at 15:18
  • can you add the whole query to the post - I suspect another part of the query is to blame. Could be NULLs in the parts of the SELECT that are grouped – whytheq Mar 29 '13 at 15:27

2 Answers2

0

You can use a CASE WHEN clause to get around this (or ISNULL, but case when is more portable across different DB engines)

 INNER JOIN datamartend.dbo.Abandoned_Call_Dim 
         ON case when incoming_measure.Abandoned is null then '0' 
                 else  incoming_measure.Abandoned end 
          = case when Abandoned_Call_Dim.abandoned_code is null then '0' 
                 else Abandoned_Call_Dim.abandoned_code end

This will replace nulls with 0. As long as you don't have a 0 code, you should be fine. If you do, try -1, or some other value you know is not in the possible set of codes.

Another thing to do if you have an unknown set of codes would be to do the join and add:

 OR (incoming_measure.Abandoned is null and Abandoned_Call_Dim.abandoned_code is null)

Which doesn't technically join - it cross joins the null records (and as long as there's only one null that matters on the abandoned call dim, you're fine).

N West
  • 6,768
  • 25
  • 40
  • Thanks for that, i run the top code and its saying "Conversion failed when converting the varchar value 'Unknown' to data type int" Which i don't understand as both are varchar? – Richard C Mar 29 '13 at 11:32
  • See my edit. I added quotes around the 0 since you 're storing as a varchar. Modify as needed, if, say, the dimension stores NO as 0 but the fact table column is just a null. – N West Mar 29 '13 at 19:13
0

Can you check whether it is possible for you to use Decode function for the ID before doing Join.

Decode(value) = joining column

or try using COALESCE(REPLACE(COL, VAL_TO_B_REPLACE_IF_NOT_NULL), VALUE_TO_REPLCE_WHEN_NULL)

Santhosh
  • 1,771
  • 1
  • 15
  • 25