Home » SQL & PL/SQL » SQL & PL/SQL » view modification (oracle 10g)
view modification [message #408584] Wed, 17 June 2009 00:16 Go to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
hi,
i am having a view.
CREATE OR REPLACE FORCE 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
	LEFT OUTER 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, 
			CASE WHEN NVL(HEALTH_PLAN_ADDRESS1,HEALTH_PLAN_ADDRESS2) AS INSURANCE_ADDRESS, 	
			HEALTH_PLAN_CITY AS INSURANCE_CITY, 
			EMRSTATESLKUP.STATE_ID,EMRSTATESLKUP.STATE_CODE,EMRSTATESLKUP.STATE_NAME,
			NVL(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 
	LEFT OUTER JOIN EMRSTATESLKUP ON EMRHEALTHPLANSTPLKUP.HEALTH_PLAN_STATE_CODE = EMRSTATESLKUP.STATE_CODE
		WHERE STATUS = 'A';
/


now can anyone help me at nl function used two times i want the result to be as like this.

address1 address2 result
cannotnull null null
null cannot null cannot null
null null null
cann null cann null result seperated by comma result

how can i do this
Re: view modification [message #408590 is a reply to message #408584] Wed, 17 June 2009 00:43 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
hi 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 result
value null null
null value value
null null null
value value value , value
Re: view modification [message #408597 is a reply to message #408590] Wed, 17 June 2009 01:11 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
rajasekhar857 wrote on Wed, 17 June 2009 06:43
hi 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 result
value null null
null value value
null null null
value value value , value

Show us what you have tried. Look into the NVL, NVL2, DECODE, CASE, COALESCE (et al) functions
Re: view modification [message #408608 is a reply to message #408597] Wed, 17 June 2009 01:19 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
(CASE WHEN HEALTH_PLAN_ADDRESS1 IS NULL THEN HEALTH_PLAN_ADDRESS2 ELSE END) AS INSURANCE_ADDRESS



this i am trying but if i want all scenarios then how should i have to go by

[Updated on: Wed, 17 June 2009 01:22]

Report message to a moderator

Re: view modification [message #408632 is a reply to message #408608] Wed, 17 June 2009 02:44 Go to previous message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Well, based on the info that yopu have supplied, it look like you always want to return expression2 unless both expression1 and expression2 are not null. Is this the case?
If not, then supply a proper test case (as you have been asked to do before on many of your threads, and also post the correct ruleset. I'm not going to guess for you. Also, please edit your original post so that your code does not exceed 80 characters per line, it is exceptionally irritating to have have to scroll hrizontally when working with a thread.
Previous Topic: Help in Converting Minutes of Difference Between Dates to Decimal
Next Topic: Multiple insert for the same row
Goto Forum:
  


Current Time: Fri Dec 02 19:02:38 CST 2016

Total time taken to generate the page: 0.21337 seconds