Home » SQL & PL/SQL » SQL & PL/SQL » merging select into one (oracle 10g)
merging select into one [message #429272] Mon, 02 November 2009 23:37 Go to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
SELECT   EMR_TRANSACTION_ID,  ETL.TASK_ID, TASK_NAME,     
	 '1/1/1900' AS APPOINTMENT_DATE, '' AS PATIENT_FIRSTNAME, '' AS PATIENT_LASTNAME,    
	 ET.USER_LOGIN AS CONSULTANT_ID, ET.ROLE_NAME, NULL AS LOCATION_ID, ID_VALUE AS ID_VALUE
FROM  EMRTASKLISTLKUP ETL
INNER JOIN EMRTRANSACTIONS ET ON ETL.TASK_ID = ET.TASK_ID
INNER JOIN USERS US ON ET.USER_LOGIN = US.USER_LOGIN
WHERE  ET.TASK_ID IN (34, 61, 62, 73, 109)
UNION ALL
SELECT   EMR_TRANSACTION_ID,  ETL.TASK_ID, TASK_NAME,     
	 '1/1/1900' AS APPOINTMENT_DATE, '' AS PATIENT_FIRSTNAME, '' AS PATIENT_LASTNAME,    
	 ET.USER_LOGIN AS CONSULTANT_ID, ET.ROLE_NAME, NULL AS LOCATION_ID, ID_VALUE AS ID_VALUE
FROM  EMRTASKLISTLKUP ETL
INNER JOIN EMRTRANSACTIONS ET ON ETL.TASK_ID = ET.TASK_ID
WHERE  ET.TASK_ID IN (124, 126, 147, 154) AND ET.ROLE_NAME IS NOT NULL




how to merge this into one
Re: merging select into one [message #429274 is a reply to message #429272] Mon, 02 November 2009 23:52 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
Both upper and lower queries will return different number of rows based on
INNER JOIN USERS US ON ET.USER_LOGIN = US.USER_LOGIN

Run these queries to see how many rows they return
SELECT   EMR_TRANSACTION_ID,  ETL.TASK_ID, TASK_NAME,     
	 '1/1/1900' AS APPOINTMENT_DATE, '' AS PATIENT_FIRSTNAME, '' AS PATIENT_LASTNAME,    
	 ET.USER_LOGIN AS CONSULTANT_ID, ET.ROLE_NAME, NULL AS LOCATION_ID, ID_VALUE AS ID_VALUE
FROM  EMRTASKLISTLKUP ETL
INNER JOIN EMRTRANSACTIONS ET ON ETL.TASK_ID = ET.TASK_ID
INNER JOIN USERS US ON ET.USER_LOGIN = US.USER_LOGIN

SELECT   EMR_TRANSACTION_ID,  ETL.TASK_ID, TASK_NAME,     
	 '1/1/1900' AS APPOINTMENT_DATE, '' AS PATIENT_FIRSTNAME, '' AS PATIENT_LASTNAME,    
	 ET.USER_LOGIN AS CONSULTANT_ID, ET.ROLE_NAME, NULL AS LOCATION_ID, ID_VALUE AS ID_VALUE
FROM  EMRTASKLISTLKUP ETL
INNER JOIN EMRTRANSACTIONS ET ON ETL.TASK_ID = ET.TASK_ID
AND ET.ROLE_NAME IS NOT NULL


then you will be able to say whether they can be merged or not .
And also why are you trying to merge??
Re: merging select into one [message #429275 is a reply to message #429274] Tue, 03 November 2009 00:06 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
can i achieve it through a function
Re: merging select into one [message #429276 is a reply to message #429275] Tue, 03 November 2009 00:21 Go to previous message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
SELECT   EMR_TRANSACTION_ID,  ETL.TASK_ID, TASK_NAME,     
	 '1/1/1900' AS APPOINTMENT_DATE, '' AS PATIENT_FIRSTNAME, '' AS PATIENT_LASTNAME,    
	 ET.USER_LOGIN AS CONSULTANT_ID, ET.ROLE_NAME, NULL AS LOCATION_ID, ID_VALUE AS ID_VALUE
FROM  EMRTASKLISTLKUP ETL
INNER JOIN EMRTRANSACTIONS ET ON ETL.TASK_ID = ET.TASK_ID
WHERE  ET.TASK_ID IN (34, 61, 62, 73, 109)
OR (ET.TASK_ID IN (124, 126, 147, 154) AND ET.ROLE_NAME IS NOT NULL)

why do you require this join in first query as you are not selecting anything from that table

INNER JOIN USERS US ON ET.USER_LOGIN = US.USER_LOGIN

[Updated on: Tue, 03 November 2009 00:23]

Report message to a moderator

Previous Topic: Need your help in redesign a table
Next Topic: queried data from column into rows
Goto Forum:
  


Current Time: Fri Dec 09 15:12:53 CST 2016

Total time taken to generate the page: 0.31873 seconds