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 |
|
rajasekhar857
Constraint Violating Yak Guru
396 Posts |
 Posted - 2009-06-17 : 02:09:16
|
| [code]CREATE VIEW EMRVWINSURANCELIST (INSURANCE_NAME_ID, INSURANCE_NAME, INSURANCE_ADDRESS, INSURANCE_CITY, STATE_ID, STATE_CODE, STATE_NAME, INSURANCE_ZIP, INSURANCE_PHONE, INSURANCE_PAYER_ID, HEALTH_PLAN_ID) AS SELECT INSURANCE_NAME_ID, INSURANCE_NAME, INSURANCE_ADDRESS, INSURANCE_CITY, EMRSTATESLKUP.STATE_ID, EMRSTATESLKUP.STATE_CODE, EMRSTATESLKUP.STATE_NAME, INSURANCE_ZIP, INSURANCE_PHONE, INSURANCE_PAYER_ID, NULL AS HEALTH_PLAN_ID FROM EMRINSURANCENAMESLKUP INNER JOIN EMRSTATESLKUP ON EMRINSURANCENAMESLKUP.STATE_ID = EMRSTATESLKUP.STATE_ID WHERE (EMRINSURANCENAMESLKUP.STATUS = 1 OR EMRINSURANCENAMESLKUP.STATUS IS NULL) UNION ALL SELECT EZEMRX_HEALTH_PLAN_ID AS INSURANCE_NAME_ID, HEALTH_PLAN_NAME AS INSURANCE_NAME, COALESCE(HEALTH_PLAN_ADDRESS1,HEALTH_PLAN_ADDRESS2) AS INSURANCE_ADDRESS, HEALTH_PLAN_CITY AS INSURANCE_CITY, EMRSTATESLKUP.STATE_ID,EMRSTATESLKUP.STATE_CODE,EMRSTATESLKUP.STATE_NAME, COALESCE(HEALTH_PLAN_ZIP, HEALTH_PLAN_ZIP4) AS INSURANCE_ZIP, HEALTH_PLAN_PHONE AS INSURANCE_PHONE, ORGANIZATION_ID AS INSURANCE_PAYER_ID, EMRHEALTHPLANSTPLKUP.HEALTH_PLAN_ID FROM EMRHEALTHPLANSTPLKUP INNER JOIN EMRSTATESLKUP ON EMRHEALTHPLANSTPLKUP.HEALTH_PLAN_STATE_CODE = EMRSTATESLKUP.STATE_CODE WHERE STATUS = 'A'GO[/code]now i want the above query using coalesce for desired o/p like thishi which nvl functinality i have to use if expr1 is having value and expr is not then result shoud be expr1.can anyone give me the example for above scenarios.expr1 expr2 resultvalue null nullnull value valuenull null nullvalue value value , value |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-06-17 : 10:33:44
|
| can you show required output with actual data? |
 |
|
|
|
|
|
|
|
view problem