SQL query without UNION

Question

Can any one help me in rewriting the following query without using UNION statement?

(
  SELECT
    A.QRYNAME0 "Query Name",
    A.OPRID,
    A.DESCR,
    A.QRYTYPE,
    TO_CHAR(A.CREATEDTTM,'DD-MON-YYYY HH24:MI:SS') "Created On",
    TO_CHAR(A.LASTUPDDTTM,'DD-MON-YYYY HH24:MI:SS') "Last Updated On",
    B.EXECCOUNT "No of Times Executed",
    B.AVGEXECTIME,
    TO_CHAR(B.LASTEXECDTTM,'DD-MON-YYYY HH24:MI:SS') "Last Executed On"
    --TO_CHAR(SUBSTR(A.DESCRLONG,1,50))"Long Description"
  FROM
    PSQRYDEFN A,
    PSQRYSTATS B
  WHERE
    A.QRYNAME = B.QRYNAME and
    a.oprid = b.oprid
)
UNION
(
  SELECT
    A.QRYNAME "Query Name",
    A.OPRID,
    A.DESCR,
    A.QRYTYPE,
    TO_CHAR(A.CREATEDTTM,'DD-MON-YYYY HH24:MI:SS') "Created On",
    TO_CHAR(A.LASTUPDDTTM,'DD-MON-YYYY HH24:MI:SS') "Last Updated On",
    0 "No of Times Executed",
    0,
    NULL "Last Executed On"
    --TO_CHAR(SUBSTR(A.DESCRLONG,1,50)) "Long Description"
  FROM
    PSQRYDEFN A
  WHERE 
    NOT EXISTS (
      SELECT 1 
      FROM   PSQRYSTATS B
      WHERE  a.qryname = b.qryname and a.oprid = b.oprid
    )
)

Answer 1

It looks like you are simulating a left outer join. Try

A.QRYNAME *= B.QRYNAME and
a.oprid *= b.oprid

or even better, make it ANSI-compliant

FROM PSQRYDEFN A
LEFT OUTER JOIN PSQRYSTATS B ON A.QRYNAME=B.QRYNAME AND A.oprid=B.oprid

Answer 2

I think you are looking for left outer join and isnull/nvl functions but without knowing the dialect of SQL and the tables cannot be exact

SELECT
    A.QRYNAME0 "Query Name",
    A.OPRID,
    A.DESCR,
    A.QRYTYPE,
    TO_CHAR(A.CREATEDTTM,'DD-MON-YYYY HH24:MI:SS') "Created On",
    TO_CHAR(A.LASTUPDDTTM,'DD-MON-YYYY HH24:MI:SS') "Last Updated On",
    nvl( B.EXECCOUNT, 0 ) "No of Times Executed",
    nvl( B.AVGEXECTIME, 0)
    nvl( TO_CHAR(B.LASTEXECDTTM,'DD-MON-YYYY HH24:MI:SS'), null) "Last Executed On"
    --TO_CHAR(SUBSTR(A.DESCRLONG,1,50))"Long Description"
FROM
    PSQRYDEFN A left outer join   PSQRYSTATS B
     on A.QRYNAME = B.QRYNAME and
         a.oprid = b.oprid

Answer 3

Looks like you'll need a LEFT OUTER JOIN of the two tables, plus you'll need to mangle the EXECCOUNT and EXECAVGTIME values with NVL to convert nulls to zeroes.