Help- SQL*PLUS Reports
From: <analyst_user_at_my-dejanews.com>
Date: Tue, 13 Apr 1999 22:11:48 GMT
Message-ID: <7f0feu$pur$1_at_nnrp1.dejanews.com>
Hello I'm having a problem creating a SQL*PLUS report. There are two tables EMP and DETAIL.
FROM DETAIL,EMP
WHERE EMP.EMP_CD=DETAIL.EMP_NUM
GROUP BY EMP_NUM
ORDER BY 2; [Quoted] The problem with that SELECT is that I cannot use the first two items, S_DATE [Quoted] and the LNAME, FNAME because of the GROUP BY CLAUSE.
FROM
(SELECT COUNT(DECODE(DETAIL.CAR_TYPE,'N',0)) cnt_n,
DETAIL,EMP
WHERE EMP.EMP_CD=DETAIL.EMP_NUM
ORDER BY 2; With this SELECT statement I am able to use the first two items since the group by is in a sub-query, however the result is wrong since I do not get one row for each person.
Date: Tue, 13 Apr 1999 22:11:48 GMT
Message-ID: <7f0feu$pur$1_at_nnrp1.dejanews.com>
Hello I'm having a problem creating a SQL*PLUS report. There are two tables EMP and DETAIL.
EMP contains the following columns.
EMP_CD (Employee Code) F_NAME (First Name) L_NAME (Last Name)
DETAIL contains the following columns.
EMP_NUM (Employee Code matches EMP_CD off of EMP table) S_DATE (Sale Date) CAR_TYPE (such as N, T, P which stands for Nissan, Toyota, Porsche)
I want to create a report like this.
S_DATE Person N T P Total
10/24/74 Smith, Joe 0 0 1 1
10/24/74 Lee, John 1 0 1 2
TOTAL ---------------------------
1 0 2 3
The problem I am having is my SELECT statement. This was my first select statement.
SELECT TO_CHAR(DETAIL.S_DATE,'MM/DD/YY') S_DATE,
EMP.LNAME||','||EMP.FNAME PERSON,
COUNT(DECODE(DETAIL.CAR_TYPE,'N',0)) N,
COUNT(DECODE(DETAIL.CAR_TYPE,'T',0)) T,
COUNT(DECODE(DETAIL.CAR_TYPE,'P',0)) P,
COUNT(DETAIL.CAR_TYPE) TOTAL
FROM DETAIL,EMP
WHERE EMP.EMP_CD=DETAIL.EMP_NUM
GROUP BY EMP_NUM
ORDER BY 2; [Quoted] The problem with that SELECT is that I cannot use the first two items, S_DATE [Quoted] and the LNAME, FNAME because of the GROUP BY CLAUSE.
The modified statement I came up with is as follows:
SELECT TO_CHAR(DETAIL.S_DATE,'MM/DD/YY') S_DATE,
EMP.LNAME||','||EMP.FNAME PERSON,
a.cnt_n N,
a.cnt_t T,
a.cnt_p P,
a.cnt_total TOTAL
FROM
(SELECT COUNT(DECODE(DETAIL.CAR_TYPE,'N',0)) cnt_n,
COUNT(DECODE(DETAIL.CAR_TYPE,'T',0)) cnt_t, COUNT(DECODE(DETAIL.CAR_TYPE,'P',0)) cnt_p, COUNT(DETAIL.CAR_TYPE) cnt_total FROM DETAIL GROUP BY EMP_NUM) a,
DETAIL,EMP
WHERE EMP.EMP_CD=DETAIL.EMP_NUM
ORDER BY 2; With this SELECT statement I am able to use the first two items since the group by is in a sub-query, however the result is wrong since I do not get one row for each person.
Please help.
-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Wed Apr 14 1999 - 00:11:48 CEST