union query not returning expected result [message #348336] |
Tue, 16 September 2008 08:00  |
jigarnaik@interactcrm.com
Messages: 2 Registered: September 2008 Location: India
|
Junior Member |

|
|
hi i have written a union query to return all the rows from two table
suppose tableA conains 10 records and tableB contains 20 than the query should return me 25 records, but the query is not returning all the records as expected it is returning 3 or 4 reocrds only.
is there any different logic for union in oracle.
bellow is my query
SELECT * FROM (SELECT ROWNUM serial_number,e.* FROM ((SELECT customerno as "Mobile No",RESPONSEMESSAGE AS "MESSAGE",TO_CHAR(SENTTIME,'DD/MM/YYYY HH:MI:SS') AS "DATE",
(SELECT 'INTERACTION' AS "MESSAGE TYPE" FROM DUAL) AS "MESSAGE TYPE"
FROM REPORTSMSRESPONSE WHERE
SENTTIME >= TO_TIMESTAMP('9/16/2008','mm/dd/yyyy') and
SENTTIME <= trunc(TO_TIMESTAMP('9/16/2008','mm/dd/yyyy')+ 1)
)
UNION
(SELECT MOBILE_NO as "Mobile No",CONTENT AS "MESSAGE",
TO_CHAR(ENTRYTIME,'DD/MM/YYYY HH:MI:SS') AS "DATE",(SELECT 'ALERT' AS "MESSAGE TYPE" FROM DUAL) AS "MESSAGE TYPE"
FROM OUTBOUND_SMS WHERE
ENTRYTIME >= TO_TIMESTAMP('9/16/2008','mm/dd/yyyy') and
ENTRYTIME <= trunc(TO_TIMESTAMP('9/16/2008','mm/dd/yyyy')+ 1))) e WHERE ROWNUM <= 5000) WHERE serial_number > 0
Bellow is the simple version of the union query for same table.
SQL> desc outbound_sms;
Name Null? Type
----------------------------------------- -------- ----------------------------
OUT_MSG_ID NOT NULL NUMBER(20)
MOBILE_NO VARCHAR2(15)
CONTENT VARCHAR2(160)
MESSAGE_TYPE VARCHAR2(30)
RECEIVER_NAME VARCHAR2(1000)
RECEIVER_CATEGORY VARCHAR2(30)
WORKGROUP VARCHAR2(30)
MESSAGE_CODE NUMBER(10)
DNIS NUMBER(15)
COMPLAINT_ID VARCHAR2(30)
U_ID VARCHAR2(20)
ENTRYTIME TIMESTAMP(6)
SQL> desc REPORTSMSRESPONSE;
Name Null? Type
----------------------------------------- -------- ----------------------------
PKEY NUMBER(10)
SMSID VARCHAR2(20)
RESPONSEMESSAGE VARCHAR2(400)
CUSTOMERNO VARCHAR2(20)
SENTTIME TIMESTAMP(6)
RESPONSETYPE VARCHAR2(20)
COMMENT_ VARCHAR2(100)
SQL> SELECT COUNT(*) FROM OUTBOUND_SMS;
COUNT(*)
----------
10
SQL> SELECT COUNT(*) FROM REPORTSMSRESPONSE;
COUNT(*)
----------
4
SQL> (SELECT MOBILE_NO FROM OUTBOUND_SMS)
2 UNION
3 (SELECT CUSTOMERNO FROM REPORTSMSRESPONSE);
MOBILE_NO
--------------------
919969413222
[Updated on: Tue, 16 September 2008 08:08] Report message to a moderator
|
|
|
|
|