Solved

Populate view with misc field info

Posted on 2009-05-14
3
251 Views
Last Modified: 2013-12-24
I have two tables as described below:

Table 1 loan_misc_fields
loan_id
misc_field_id
misc_field_value
misc_dec_value
misc_int_value
misc_dt_value

Table 2 misc_fields
misc_field_id
misc_field_name
misc_field_type

I want to populate a view with the loan_id and then the value (from the appropriate value field) in a field named the misc_field_name for each of the misc fields (there are 512 of them).   So I would end with a view that contained 513 fields.  Can it be done?

Type values are as follows
when misc_field_type = 1 then info will be in either the misc_field_value field or the misc_int_value field (I don't know how that's working, whoever programmed this must have been insane)
when misc_field_type = 3 then info will be in misc_dec_value field
when misc_field_type = 5 then info will be in misc_dt_value field
0
Comment
Question by:vacusysprog
  • 2
3 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24391822
Create VIEW vw_YourViewNameGoesHere

As

Select      t1.loan_id,
            CASE t2.misc_field_Type
                  WHEN 1 THEN COALESCE(t2.misc_field_value field, t1.misc_int_value)
                  WHEN 3 THEN t1.misc_dec_value
                  WHEN 5 THEN t1.misc_dt_value
                  WHEN ...

            END
From      Table1 t1
            Inner Join Table2 t2 On t1.misc_field_id = t2.misc_field_id
0
 

Accepted Solution

by:
vacusysprog earned 0 total points
ID: 24519814
I ended up joining the table to itself 512 times.  So, that I would not have to type all of that I used excel to concatenate the infomation using the field number in the alias.  It worked out nicely.

SELECT     t1.loan_id, mf102.misc_field_value AS var_rate_feature,
                                 mf103.misc_field_value AS prepay_penalty,
                                 mf106.misc_field_value AS haz_ins_required
FROM         t1 LEFT OUTER JOIN
                  t2 AS mf102 ON mf102.loan_id = li.loan_id AND mf102.misc_field_id = 102 LEFT OUTER JOIN
                  t2 AS mf103 ON mf103.loan_id = li.loan_id AND mf103.misc_field_id = 103 LEFT OUTER JOIN
                  t2 AS mf106 ON mf106.loan_id = li.loan_id AND mf106.misc_field_id = 106 LEFT OUTER JOIN

       
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24520060
Welcome to EE.  In future you will find experts here more than prepared to help, if you are prrepared to provide PROMPT feedback.
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
The purpose of this video is to demonstrate how to set up an RSS Feed on a WordPress Website. This will be demonstrated using a Windows 8 PC. Feedburner will be used for this demonstration. Go to your WordPress login page. This will look like the…

790 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question