Home » SQL & PL/SQL » SQL & PL/SQL » union query not returning expected result (Oracle 10g)
union query not returning expected result [message #348336] Tue, 16 September 2008 08:00 Go to next message
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

Re: union query not returning expected result [message #348341 is a reply to message #348336] Tue, 16 September 2008 08:08 Go to previous messageGo to next message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
First try UNION ALL instead of UNION as it does not show duplicates.

Do you want to return all rows? If so, don't use a WHERE clause.

If you don't want to return all rows, run the separate SELECTs and show the results.
Re: union query not returning expected result [message #348342 is a reply to message #348341] Tue, 16 September 2008 08:13 Go to previous message
jigarnaik@interactcrm.com
Messages: 2
Registered: September 2008
Location: India
Junior Member

yeah thanks a lot... UNION ALL worked.

Thanks...
Previous Topic: PL/SQL
Next Topic: joining conditions
Goto Forum:
  


Current Time: Thu Dec 08 16:18:52 CST 2016

Total time taken to generate the page: 0.14664 seconds