0

Overview

I have an inner join statement that links 5 tables together which is working fantastically if i do say so myself.

Now one of the values returned is in Hex and it is in a column named Value however, the Value column also contains other values and so i cannot simply convert the whole column.

Every Hex entry into column Value has an identifier of 18 in a column called DataTypeID.

The Question

How can i convert just the Hex entry in column Value if DataTypeID=18.

I know this needs a function with: 

SELECT CONVERT(INT, CONVERT(VARBINARY

But I am unsure of how to write it and where to place it, is it before my inner join statement or after?

SELECT Data.Value AS Value, Entry_Data.DataTypeID AS DataTypeID
From Database.Entry
INNER JOIN Database.Data on Entry_Data.DataTypeID=DataTypeID
INNER Join Database.Data on Data.Value=Value

Please note that this is not an accurate example of the script its just for example purposes

Now i assume that i would create my function above this query and then add a where function below it.

The names of the columns and identifier are accurate.

Vadim Kotov
  • 8,084
  • 8
  • 48
  • 62
Oliver843
  • 25
  • 1
  • 8

1 Answers1

0

You can use the case expression.

Your query is a little difficult to interpret intent, so I am going to make an assumption that you have two tables, (TableA and TableB) and you want to join one to the other like this: TableA.Value = TableB.Value, but TableB stores its Value column as a mixture of ints and hex(ints) where the hex(insts) are identified by value of TableB.VDataTypeID = 18 and anyt other DataTypeID indicates that TableB.Value a regular int.

If this is the case, to the following:

SELECT
    ...
FROM 
    TableA 
    INNER JOIN TableB ON TableA.Value 
        = CASE WHEN TableB.DataTypeID = 18 THEN Convert(...
            ELSE TableB.DataTypeID
        END

So you are using the CASE expression effectively as an in-place function on which to join.

It reads, Join TableA to TableB on the Values directly if TableB.DataTypeID is not 18, but if it is 18, then convert TableB.Value first and then do the join.

Hope this gives you what you need :-)

Alan
  • 1,378
  • 2
  • 19
  • 24
  • Case _expression_, not _statement_. – jarlh Oct 04 '17 at 09:51
  • Fair cop. Changed. – Alan Oct 04 '17 at 09:52
  • @Alan I'm unsure when to include your CASE statement. I attempted to add it after the last inner join but the convert failed to change the correct value field – Oliver843 Oct 04 '17 at 12:31
  • @Oliver843, if you are trying to join on this converted value instead of Value (is that right?), you would replace the Value with the case statement. - I have amended my answer to address this assumption.Let me know if that is what you are after. – Alan Oct 04 '17 at 21:29
  • @Alan Thanks for your reply. The tables are set out in a very weird way. TableA contains the columns DataID,DataTypeID and TableB contains the columns DataID, Value. My understanding is that i will have to join on the DataID columns in both tables? Hope this clarifies things but your assumptions are indeed correct. – Oliver843 Oct 05 '17 at 09:45