Home » SQL & PL/SQL » SQL & PL/SQL » Query problems
Query problems [message #231561] Tue, 17 April 2007 09:18 Go to next message
dlong
Messages: 1
Registered: April 2007
Junior Member
I was hoping someone could help me modify two query's I have created . Both query's work independently of each other but I need the top query to run if a field on a separate table is greater than zero and the bottom query to run if it is zero.

The psuedo code would be somthing like this

If PPLE_T_USER_DEFINED.USER_DEF_TEXT1 >'0' -- Completely different table

Then


SELECT DISTINCT
recipientDetails.CK_RMS_ID AS PK_RMS_ID,
recipientDetails.ix_Student_Number,
recipientDetails.PK_APPLICATION_NO,
recipientDetails.ix_First_Name,
recipientDetails.ix_Last_Name,
recipientDetails.ix_Middle_Name,
recipientDetails.E_mail,
recipientDetails.ix_First_Name || ' ' || recipientDetails.ix_Last_Name as FULL_NAME,
recipientDetails.Address_1 || ' ' || recipientDetails.Address_1b as ADDRESS,
recipientDetails.Address_2 || ' ' || recipientDetails.fk_State || ' ' || recipientDetails.postcode as CITY_STATE_ZIP,
BUILDINGS_NAME as BUILDING_NAME,
pple_t_address.Address_1 as OTHER_ADDRESS,
TTR16.ix_First_Name_1 || ' ' || TTR16.ix_Last_Name_1 || ' ' || TTR16.Location_Phone_1 as ROOMMATE_NAME_PHONE_1,
TTR16.ix_First_Name_2 || ' ' || TTR16.ix_Last_Name_2 || ' ' || TTR16.Location_Phone_2 as ROOMMATE_NAME_PHONE_2,
TTR16.ix_First_Name_3 || ' ' || TTR16.ix_Last_Name_3 || ' ' || TTR16.Location_Phone_3 as ROOMMATE_NAME_PHONE_3
FROM recipientDetails, temp_t_roommates_1to6 TTR16, pple_t_address
WHERE (recipientDetails.ck_rms_id = TTR16.RECIPIENT_RMS_ID(+)) AND (recipientDetails.ck_rms_id = pple_t_address.ck_rms_id)
AND (pple_t_address.ck_address_type = 'Other')
and (rownum <= 50)

ELSE

SELECT DISTINCT
recipientDetails.CK_RMS_ID AS PK_RMS_ID,
recipientDetails.ix_Student_Number,
recipientDetails.PK_APPLICATION_NO,
recipientDetails.ix_First_Name,
recipientDetails.ix_Last_Name,
recipientDetails.ix_Middle_Name,
recipientDetails.E_mail,
recipientDetails.ix_First_Name || ' ' || recipientDetails.ix_Last_Name as FULL_NAME,
recipientDetails.Address_1 || ' ' || recipientDetails.Address_1b as ADDRESS,
recipientDetails.Address_2 || ' ' || recipientDetails.fk_State || ' ' || recipientDetails.postcode as CITY_STATE_ZIP,
BUILDINGS_NAME as BUILDING_NAME,
RMGT_T_ROOM_CONFIGS.Rooms_Address_1 as Other_ADDRESS,
TTR16.ix_First_Name_1 || ' ' || TTR16.ix_Last_Name_1 || ' ' || TTR16.Location_Phone_1 as ROOMMATE_NAME_PHONE_1,
TTR16.ix_First_Name_2 || ' ' || TTR16.ix_Last_Name_2 || ' ' || TTR16.Location_Phone_2 as ROOMMATE_NAME_PHONE_2,
TTR16.ix_First_Name_3 || ' ' || TTR16.ix_Last_Name_3 || ' ' || TTR16.Location_Phone_3 as ROOMMATE_NAME_PHONE_3
FROM recipientDetails, temp_t_roommates_1to6 TTR16, RMGT_T_ROOM_CONFIGS, RMGT_T_ROOM_PERSON
WHERE (recipientDetails.ck_rms_id = TTR16.RECIPIENT_RMS_ID(+)) AND (recipientDetails.ck_rms_id = RMGT_T_ROOM_PERSON.ck_rms_id) AND
(RMGT_T_ROOM_PERSON.ck_bed_space = RMGT_T_ROOM_CONFIGS.ck_bed_space)
AND (RMGT_T_ROOM_PERSON.Room_Person_Move_Out_Date > CURRENT_DATE) AND (RMGT_T_ROOM_PERSON.Ck_Move_In_Date <= CURRENT_DATE)
and (rownum <= 50)

END

Re: Query problems [message #231600 is a reply to message #231561] Tue, 17 April 2007 12:27 Go to previous message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
You can merge them using a CASE WHEN THEN ELSE END. So, keep the "common" where clauses (if any) down and alter the part of the select to something like:
CASE WHEN <your first where clause> 
     THEN <your first address line>  
     WHEN <your second where clause> 
     THEN <your second address line>
     ELSE 'unknown'
END <name your column, ie address>
Previous Topic: Sql query
Next Topic: Regarding Trigger
Goto Forum:
  


Current Time: Mon Dec 05 15:14:31 CST 2016

Total time taken to generate the page: 0.16238 seconds