I have a VIEW that queries my E-A-V model which uses datatype tables to store the values. I have an entity that contains many attributes, each attribute can be a different datatype. The DataType tables are all identically structured except for storage type they have 4 columns, required, Min, Max and Value (of the respective datatype for the table).

I would like simply get something like

MyAttributeName, Min, Max, Value, Required

I have tables like this..

  • [Attribute:: Attribute_Id, Name] [Machine:: Machine_Id, Name]
  • [Attribute_Val_Int :: Id, Attribute_Id, Machine_Id, Min, Max, Value,Required]
  • [Attribute_Val_Dec :: Id, Attribute_Id, Machine_Id, Min,Max, Value, Required]
  • [Attribute_Val_Dat :: Id, Attribute_Id, Machine_Id, Min, Max, Value, Required]

While I can get all of the Attribute Type Values (in this example I get 12 columns back - I want only the Attribute_Val that has non-null values. I would like the Column Names to then be aliased Min, Max, Val, Required

So instead of receiving this:

MachineName, MyAttributeName, IntMin, IntMax, IntValue, IntRequired, DecMin, DecMax, DecValue, DecRequired, DatMin, DatMax, DatValue, DatRequired.

I want to get:

[MachineName, MyAttributeName, Min, Max, Value, Required]


Think about

SELECT MachineName, 
       COALESCE(IntMin, DecMin, DatMin) ValMin,
       COALESCE(IntMax, DecMax, DatMax) ValMax, 
       COALESCE(IntValue, DecValue, DatValue) ValValue,
       COALESCE(IntRequired, DecRequired, DatRequired) ValRerquired

But it seems you must to convert all separate values to the same datatype (string?) to avoid an error.

  • yes the string issue creates somewhat of a problem, but gives me an idea. When comparing to determine if within the range a string of 10242018 and 11182020 has to be reconverted to date and I then need the datatype for the column. The Int and Decimal fields as decimal is easy enough because 1 and 1.0 is straight forward.So my options are to store the date as a non-date more like milliseconds value in which case it is numeric type. I can then convert string back to Decimal for comparing. I am going to mark this as answer because it gives me another idea to simplify storage.
    – StixO
    Jan 16 '20 at 10:34
  • Is there any way to know which column is the non-null and get datatype so as to create a temp table with that column and insert values ?
    – StixO
    Jan 16 '20 at 10:39
  • Maybe... but I don't know how to do it.
    – Akina
    Jan 16 '20 at 10:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.