| table Data [message #625032] |
Tue, 30 September 2014 08:27  |
shahzad-ul-hasan
Messages: 615
Registered: August 2002
|
Senior Member |
|
|
Table Structure
SQL> DESC EMP
Name Null? Type
----------------------------------------- -------- ------------------------
EMPID NOT NULL NUMBER(6)
STAT VARCHAR2(30)
DOL DATE
NAME VARCHAR2(250)
FH_NAME VARCHAR2(250)
CNIC VARCHAR2(25)
GENDER VARCHAR2(20)
DOJ DATE
DOB DATE
DESIG VARCHAR2(130)
QUALI VARCHAR2(80)
ADDRES VARCHAR2(300)
SQL> desc tatt
Name Null? Type
----------------------------------------- -------- ----------------------------
ADATE DATE
AEMPID NUMBER(6)
ABTYPE VARCHAR2(50)
master table: Emp
Table Emp has 76 Records.
Detail table Tatt
This query shows only those record which are in tatt table. i want to show all records of emp table which is in tatt and which are not in tatt table.
select E.EMPID,SUBSTR(LAST_DAY(SYSDATE),1,2),count(AEMPID),nvl(BSAL,0),NVL(MAX(SNO),0)+1
from EMP E,TATT S,SAL_DET,SAL
where E.EMPID=S.AEMPID(+) AND E.STAT='ACTIVE' AND TO_CHAR(:SAL.SDATE,'MON-YY')=TO_CHAR(ADATE,'MON-YY')
AND SAL.SDATE=SAL_DET.SDATE
GROUP BY E.EMPID,nvl(bsal,0)
order by e.EMPID;
|
|
|
|
|
|
|
|
| Re: table Data [message #625053 is a reply to message #625032] |
Tue, 30 September 2014 12:52  |
 |
Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Quote:SUBSTR(LAST_DAY(SYSDATE),1,2)
SUBSTR on a DATE is wrong, you can only SUBSTR on a string; so you have to use TO_CHAR with the appropriate format mask.
Quote:TO_CHAR(:SAL.SDATE,'MON-YY')=TO_CHAR(ADATE,'MON-YY')
This is not correct.
The correct way is
TRUNC(:SAL.SDATE,'MONTH') = TRUNC(S.ADATE(+),'MONTH')
You tell nothing about SAL_DET and SAL, so first try to get the result you want from the 2 other tables, then add these ones.
|
|
|
|
| Re: table Data [message #625059 is a reply to message #625053] |
Tue, 30 September 2014 22:48 |
shahzad-ul-hasan
Messages: 615
Registered: August 2002
|
Senior Member |
|
|
Respected Sir,
SAL---Table
Sdate Date primary key
---------------------------
SAL_DET-------Table
sdate date
sempid number(6)
bsal number (5).
Gndsal number(6)
i want to fetch the (Emp, Tatt) data through cursor and insert it into sal_det.But it only insert the data which are in (emp,tatt). but some emp records are not fetch.please advised.
|
|
|
|
| Re: table Data [message #625076 is a reply to message #625059] |
Wed, 01 October 2014 03:49 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
|
If you apply Michels suggested fixes, which include a missing outer-join then you'll probably get what you want.
|
|
|
|
|
|
| Re: table Data [message #625145 is a reply to message #625080] |
Wed, 01 October 2014 15:28 |
Bill B
Messages: 1971
Registered: December 2004
|
Senior Member |
|
|
|
please show your solved code. This will help the next person who is looking up this problem. Thank you.
|
|
|
|
| Re: table Data [message #625148 is a reply to message #625145] |
Wed, 01 October 2014 22:25 |
shahzad-ul-hasan
Messages: 615
Registered: August 2002
|
Senior Member |
|
|
SELECT E.empid,
To_char(Last_day(SYSDATE), 'DD'),
Count(aempid),
Nvl(bsal, 0),
Nvl(Max(:SNO), 0) + 1
FROM emp E,
tatt S
WHERE E.empid = S.aempid(+)
AND E.stat = 'ACTIVE'
AND To_char(:SAL.sdate, 'MON-YY') = To_char(adate(+), 'MON-YY')
GROUP BY E.empid,
Nvl(bsal, 0)
ORDER BY e.empid;
Thanks Michels
*BlackSwan formatted the SQL using following URL http://www.dpriver.com/pp/sqlformat.htm?ref=g_wangz
[Updated on: Wed, 01 October 2014 22:28] by Moderator Report message to a moderator |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
