Populate view with misc field info

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

Table 1 loan_misc_fields

Table 2 misc_fields

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
Question by:vacusysprog
  • 2
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24391822
Create VIEW vw_YourViewNameGoesHere


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 ...

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

Accepted Solution

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
                  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

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.

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
The purpose of this video is to demonstrate how to reset a WordPress password if you are locked out and cannot reset the password. A typical use would be if you cannot access the email to which WordPress would send the password recovery email to…

756 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