Query problems [message #231561] |
Tue, 17 April 2007 09:18  |
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  |
skooman
Messages: 913 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>
|
|
|