Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jose1lm
Yak Posting Veteran
70 Posts |
 Posted - 2003-07-25 : 18:01:31
|
| I need help on combining the table/views into the results (view) I’m looking for.Table: jobJob_id | status133 | Complete501 | Complete502 | Complete503 | Complete504 | Complete505 | Completeetc etcView: vw_ffd_form_102Job_id | txtSection13Element3501 | 0503 | 0504 | 90506 | 288etc etcView: vw_ffd_form_47Job_id | txtSection31Element3133 | 1502 | 154505 | 192etc etcThere is a function I use that will convert those text section numbers into something else we use. Here is that select statement and the results it returns when reading from just one table/view:SELECT job_id, dbo.fn_dd_Product(txtSection13Element3) AS txtSection13Element3FROM dbo.vw_ffd_form_102ORDER BY job_idJob_id | txtSection13Element3501 | other503 | other504 | DR1000506 | Vacuum_Breakeretc etc.Now the results I would like to see should look like this:Job_id | status | Product133 | Complete | unkown501 | Complete | other502 | Complete | L5000503 | Complete | other504 | Complete | DR1000505 | Complete | NS1000E506 | Open | Vacuum Breakeretc etcHere is the query I wrote but it doesn’t work correctly at all, not to mention that I couldn’t get the function to work in this statement so I had it just return the number instead of the conversion that I would like. I feel I should’ve used a UNION somewhere. The ‘product’ columns should’ve only be listed once, not twice:SELECT a.job_id, a.status,(SELECT txtSection13Element3 FROM vw_ffd_form_102 b WHERE b.job_id = a.job_id) AS Product,(SELECT txtSection31Element3 FROM vw_ffd_form_47 b WHERE b.job_id = a.job_id) AS ProductFROM job aJob_id | status | Product | Product133 | Complete | Null | 1501 | Complete | 0 | Null502 | Complete | Null | 154503 | Complete | 0 | Null504 | Complete | 90 | Null505 | Complete | Null | 192506 | Open | 288 | NullI didn’t past the function code here because it’s awfully long, but if it is required, let me know.Thanks in advance for your help guys! This place is a great. |
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2003-07-25 : 18:53:55
|
I think that this will work: SELECT j.job_id, j.Status, dbo.fn_dd_Product(COALESCE(v1.txtSection13Element3 , v2.txtSection31Element3)) AS ProductFROM Job jLEFT JOIN dbo.vw_ffd_form_102 v1 ON v1.job_id = j.job_idLEFT JOIN vw_ffd_form_47 v2 ON v2.job_id = j.job_idORDER BY j.job_id Basically what it does is gives you an output similar to what your last query did, except it takes those two product fields, and puts them into one field. COALESCE gets the first non-null value in the list of things that you give it. It will then pass that value to your handy UDF, and you'll get the results you need.Let us know if this works or not!Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda>Edited by - michaelp on 07/25/2003 18:55:19 |
 |
|
|
jose1lm
Yak Posting Veteran
70 Posts |
Posted - 2003-07-28 : 11:00:52
|
| Thanks Michael, it works great!I had this working by doing seperate views rather than just 1 view as you have shown me.CREATE VIEW dbo.product_lineSELECT job_id, dbo.fn_dd_Product(txtSection13Element3) AS ProductFROM dbo.vw_ffd_form_102UNIONSELECT job_id, dbo.fn_dd_Product(txtSection31Element3) AS ProductFROM dbo.vw_ffd_form_47----------------CREATE VIEW dbo.product_jobsSELECT dbo.JOB.job_id, dbo.JOB.status, dbo.vw_product_line.ProductFROM dbo.JOB LEFT OUTER JOIN dbo.vw_product_line ON dbo.JOB.job_id = dbo.vw_product_line.job_idORDER BY dbo.JOB.job_idThanks again!JLM |
|
|
|
|
|
|
|
|
Combining a few results while using a function