Home » Developer & Programmer » Reports & Discoverer » My JOB is in DANGER....Please give some time to my probs.
|
|
|
|
| Re: My JOB is in DANGER....Please give some time to my probs. [message #170883 is a reply to message #170130] |
Fri, 05 May 2006 14:33   |
Shaila.Mehra
Messages: 88
Registered: July 2005
Location: mumbai
|
Member |
 
|
|
Hi it is nice to see your reply..
Please find the correct report as an attachment and also find the table structure which is written below...
SQL> desc salary_master
Name Null? Type
----------------------------------------- -------- -------------
ENO NOT NULL VARCHAR2(10)
BP NUMBER(9,2)
DA NUMBER(9,2)
SP NUMBER(9,2)
TA NUMBER(9,2)
CCA NUMBER(9,2)
HRA NUMBER(9,2)
DP NUMBER(9,2)
WA NUMBER(9,2)
MISC NUMBER(9,2)
REMARK VARCHAR2(500)
TOT_CREDIT NUMBER(9,2)
GPFS NUMBER(9,2)
GPFR NUMBER(9,2)
PLI NUMBER(9,2)
CGHS NUMBER(9,2)
CGEGIS NUMBER(9,2)
LF NUMBER(9,2)
FA NUMBER(9,2)
IT NUMBER(9,2)
HBA NUMBER(9,2)
MCA NUMBER(9,2)
MISC1 NUMBER(9,2)
REM1 VARCHAR2(500)
MISC2 NUMBER(9,2)
REM2 VARCHAR2(500)
TOT_DEBIT NUMBER(9,2)
NET_PAY NUMBER(9,2)
FEST_INST NUMBER(3)
FEST_BAL NUMBER(9,2)
FEST_TOT_INST NUMBER(3)
GPF_INST NUMBER(3)
GPF_BAL NUMBER(9,2)
GPF_TOT_INST NUMBER(3)
HBA_ADV_INST NUMBER(3)
HBA_ADV_BAL NUMBER(9,2)
HBA_ADV_TOT_INST NUMBER(3)
MCA_ADV_INST NUMBER(3)
MCA_ADV_BAL NUMBER(9,2)
MCA_ADV_TOT_INST NUMBER(3)
HBA_INT_INST NUMBER(3)
HBA_INT_BAL NUMBER(9,2)
HBA_INT_TOT_INST NUMBER(3)
MCA_INT_INST NUMBER(3)
MCA_INT_BAL NUMBER(9,2)
MCA_INT_TOT_INST NUMBER(3)
MONTH NOT NULL VARCHAR2(15)
CRTN_ID VARCHAR2(10)
CRTN_DT DATE
MOD_ID VARCHAR2(10)
MOD_DT DATE
STAGNATION_AMT NUMBER(9,2)
GPFCL NUMBER(9,2)
COMPU_ADV_INST NUMBER(3)
COMPU_ADV_BAL NUMBER(9,2)
COMPU_ADV_TOT_INST NUMBER(3)
COMPU_INT_INST NUMBER(3)
COMPU_INT_BAL NUMBER(9,2)
COMPU_INT_TOT_INST NUMBER(3)
COM_ADV NUMBER(9,2)
DEARNESS_PAY NUMBER(9,2)
MISCC NUMBER(9,2)
MISCD NUMBER(9,2)
SQL> desc emp_master
Name Null? Type
----------------------------------------- -------- ------------- ENO NOT NULL VARCHAR2(10)
FNAME NOT NULL VARCHAR2(50)
SNAME VARCHAR2(100)
SEX CHAR(1)
DOB NOT NULL DATE
DT_OF_JOIN NOT NULL DATE
ADDS1 VARCHAR2(250)
ADDS2 VARCHAR2(250)
QUALIFICATION VARCHAR2(100)
RADESG VARCHAR2(50)
MARITAL_STAT VARCHAR2(10)
BP_SLAB_NO NOT NULL NUMBER(3)
BP NOT NULL NUMBER(9,2)
AWARD VARCHAR2(100)
CATEGORY VARCHAR2(50)
GROU CHAR(1)
GAZATTED CHAR(1)
DEPUTED CHAR(1)
DT_OF_INC DATE
ACCO_CODE CHAR(1)
TYPE_OF_ACCO VARCHAR2(10)
DT_OF_RET DATE
CGHS CHAR(1)
GPF_NO VARCHAR2(40)
TRANSFERRED CHAR(1)
TRANS_DATE DATE
CRTN_ID VARCHAR2(10)
CRTN_DT DATE
MOD_ID VARCHAR2(10)
MOD_DT DATE
STAGNATION NUMBER(2)
INCREMENT_STATUS CHAR(1)
STAGNATION_AMOUNT NUMBER(9,2)
DEP_CODE NUMBER(2)
AAN VARCHAR2(7)
DEARNESS_PAY NUMBER(9,2)
SPECIAL_PAY NUMBER(9,2)
SPECIAL_PAY_AVAIL CHAR(1)
SQL> desc balance_master
Name Null? Type
----------------------------------------- -------- -------------
ENO NOT NULL VARCHAR2(10)
GPF_TOT_AMT NUMBER(9,2)
GPF_PAID NUMBER(9,2)
GPF_BAL NUMBER(9,2)
HBA_TOT_AMT NUMBER(9,2)
HBA_PAID NUMBER(9,2)
HBA_BAL NUMBER(9,2)
HBA_INST_TOT NUMBER(9,2)
HBA_INST_PAID NUMBER(9,2)
HBA_INST_BAL NUMBER(9,2)
MCA_TOT_AMT NUMBER(9,2)
MCA_PAID NUMBER(9,2)
MCA_BAL NUMBER(9,2)
MCA_INST_TOT NUMBER(9,2)
MCA_INST_PAID NUMBER(9,2)
MCA_INST_BAL NUMBER(9,2)
FEST_TOT_AMT NUMBER(9,2)
FEST_PAID NUMBER(9,2)
FEST_BAL NUMBER(9,2)
CRTN_ID VARCHAR2(10)
CRTN_DT DATE
MOD_ID VARCHAR2(10)
MOD_DT DATE
COMPU_TOT_AMT NUMBER(9,2)
COMPU_PAID NUMBER(9,2)
COMPU_BAL NUMBER(9,2)
COMPU_INST_TOT NUMBER(9,2)
COMPU_INST_PAID NUMBER(9,2)
COMPU_INST_BAL NUMBER(9,2)
SQL> |
|
|
|
|
|
| Re: My JOB is in DANGER....Please give some time to my probs. [message #171630 is a reply to message #169805] |
Wed, 10 May 2006 13:40 |
Shaila.Mehra
Messages: 88
Registered: July 2005
Location: mumbai
|
Member |
|
|
Hi,
I wrote a separate query in which i gave
| Quote: |
SUBSTR(A.MONTH,4) BETWEEN 'MAR/2006' AND :MON
|
To get the summary in between 'MAR/2006' AND :MON.
But it is not working.
And i am confused too.
Please correct my query
SELECT
A.ENO,RTRIM(B.FNAME)||' '||RTRIM( B.SNAME),RTRIM(B.RADESG),B.DOB,B.DT_OF_INC,B.GROU,B.GPF_NO,
A.BP,A.DA,A.SP,A.TA,A.CCA,A.HRA,A.DP,A.WA,
nvl(A.MISC,0) MISC,A.TOT_CREDIT,A.GPFS,A.GPFR,A.PLI,
A.CGHS,A.CGEGIS,A.LF,A.FA,A.IT,A.HBA,A.MCA,
NVL(A.MISC1,0) MISC1,NVL(A.MISC2,0) MISC2,A.TOT_DEBIT,A.NET_PAY,
A.FEST_INST,A.FEST_BAL,A.FEST_TOT_INST,
A.GPF_INST,A.GPF_BAL,A.GPF_TOT_INST,
A.HBA_ADV_INST,A.HBA_ADV_BAL,A.HBA_ADV_TOT_INST,
A.MCA_ADV_INST,A.MCA_ADV_BAL,A.MCA_ADV_TOT_INST,
A.HBA_INT_INST,A.HBA_INT_BAL,A.HBA_INT_TOT_INST,
A.MCA_INT_INST,A.MCA_INT_BAL,A.MCA_INT_TOT_INST,
A.REMARK,A.REM1,A.REM2,A.MONTH,
A.COMPU_INT_INST,A.COMPU_INT_BAL,A.COMPU_INT_TOT_INST,A.COMPU_ADV_INST,A.COM_ADV,
A.COMPU_ADV_BAL,A.COMPU_ADV_TOT_INST,a.stagnation_amt,
A.gpfcl,C.GPFCL,C.GPFSUBS,C.GPFSUBS_STDT,
C.GPFWD,C.GPFWD_DT,
C.GPFSUBS2,C.GPFSUBS_STDT2,NVL(A.DEARNESS_PAY,0) DEARNESS_PAY,NVL(A.MISCC,0) MISCC,NVL(A.MISCD,0) MISCD
FROM SALARY_MASTER A,EMP_MASTER B,
GPF_CL_WD C
WHERE A.ENO = B.ENO AND A.ENO=C.ENO
AND SUBSTR(A.MONTH,4) BETWEEN 'MAR/2006' AND :MON
AND B.GAZATTED like :G
AND A.eno like :EN
AND A.ENO NOT LIKE 'DRN%'
ORDER BY A.BP DESC
AND when i change the datatype of :MON from character to Date
then it is showing thios error
| Quote: |
ORA-01858: a non-numeric character was found where a
nummeric was expected
AND TO_DATE(==>
SUBSTR(a.MONTH,4))=TO_DATE(:MON)
|
How it will be achieved.
I am totally confused.
Please do something.
Shaila ......
|
|
|
|
|
|
| Re: My JOB is in DANGER....Please give some time to my probs. [message #171780 is a reply to message #171686] |
Thu, 11 May 2006 07:56 |
skooman
Messages: 913
Registered: March 2005
Location: Netherlands
|
Senior Member |
|
|
Shaila,
Month in table A is a varchar(15) column. So, in order to compare it to a date field (or between date fields), you have to explain sql what format to expect.
First question therefor is: what order/type of data do you expect in Month? And can you absolutely rely on that format? No exceptions?
Let's say you know for sure that all values are like 'MAR-2005' etc (so, English abbrevation 3 characters, line, 4-digit year). Test the contents of a.month by typing: select to_date(month,'MON-YYYY') from a;
Then your where clause should be:
where trunc(to_date(a.month,'MON-YYYY'),'MON') between to_date('MAR-2005','MON-YYYY') and to_date(:¶m,'MON-YYYY')
Regards,
Sabine
[Updated on: Thu, 11 May 2006 07:57] Report message to a moderator |
|
|
|
|
|
| Re: My JOB is in DANGER....Please give some time to my probs. [message #172155 is a reply to message #171980] |
Mon, 15 May 2006 04:56 |
skooman
Messages: 913
Registered: March 2005
Location: Netherlands
|
Senior Member |
|
|
Shaila,
There are some values in the column month, that are not in the format you expect. Best way to solve this, is to contact some developer of the source system to help you. If necessary (if they claim it's all in that format, and you still get this error, so you have to prove that there is something else in there), this is a way to get the values that are in a different format:
create a function which is something like:
FUNCTION any_to_date(inp VARCHAR2) RETURN DATE IS
tmp_date DATE;
BEGIN
tmp_date := to_date(inp,'MON-YYYY');
RETURN tmp_date;
EXCEPTION
WHEN OTHERS THEN
BEGIN
RETURN NULL;
END;
END any_to_date;
This function tries to convert the value to date, but if that fails, it returns a null.
Now you can find the "different" values by using a statement like:
select month
from salary_master
where any_to_date(month) is null;
Regards,
Sabine
PS only now I see that you state that month is in the format of '1/JAN/2005', so everything in my reply now is maybe unnecessary. Just change your statement from
to_date(month,'MON-YYYY')
to
to_date(month,'dd-mon-yyyy'
[Updated on: Mon, 15 May 2006 05:01] Report message to a moderator |
|
|
|
|
|
|
|
| Re: My JOB is in DANGER....Please give some time to my probs. [message #172934 is a reply to message #169805] |
Thu, 18 May 2006 12:28 |
Shaila.Mehra
Messages: 88
Registered: July 2005
Location: mumbai
|
Member |
|
|
Hi,
Problem is same because in my report i have two queries
in other query this code become again hurdle.
| Quote: |
SUBSTR(A.MONTH,4)=:MON
|
it is giving error :
| Quote: |
ORA-01858: a non-numeric character was found where a
numeric was expected
AND to_date(==>SUBSTR(A.MONTH,4))=:MON
|
I use same query two times 'Q1' and 'Q2' one for salary detail and other for its summary.
QUERY Q1 is:
SELECT
A.ENO,RTRIM(B.FNAME)||' '||RTRIM( B.SNAME),RTRIM(B.RADESG),B.DOB,B.DT_OF_INC,B.GROU,B.GPF_NO,
A.BP,A.DA,A.SP,A.TA,A.CCA,A.HRA,A.DP,A.WA,
nvl(A.MISC,0) MISC,A.TOT_CREDIT,A.GPFS,A.GPFR,A.PLI,
A.CGHS,A.CGEGIS,A.LF,A.FA,A.IT,A.HBA,A.MCA,
NVL(A.MISC1,0) MISC1,NVL(A.MISC2,0) MISC2,A.TOT_DEBIT,A.NET_PAY,
A.FEST_INST,A.FEST_BAL,A.FEST_TOT_INST,
A.GPF_INST,A.GPF_BAL,A.GPF_TOT_INST,
A.HBA_ADV_INST,A.HBA_ADV_BAL,A.HBA_ADV_TOT_INST,
A.MCA_ADV_INST,A.MCA_ADV_BAL,A.MCA_ADV_TOT_INST,
A.HBA_INT_INST,A.HBA_INT_BAL,A.HBA_INT_TOT_INST,
A.MCA_INT_INST,A.MCA_INT_BAL,A.MCA_INT_TOT_INST,
A.REMARK,A.REM1,A.REM2,A.MONTH,
A.COMPU_INT_INST,A.COMPU_INT_BAL,A.COMPU_INT_TOT_INST,A.COMPU_ADV_INST,A.COM_ADV,
A.COMPU_ADV_BAL,A.COMPU_ADV_TOT_INST,a.stagnation_amt,
A.gpfcl,C.GPFCL,C.GPFSUBS,C.GPFSUBS_STDT,
C.GPFWD,C.GPFWD_DT,
C.GPFSUBS2,C.GPFSUBS_STDT2,NVL(A.DEARNESS_PAY,0) DEARNESS_PAY,NVL(A.MISCC,0) MISCC,NVL(A.MISCD,0) MISCD
FROM SALARY_MASTER A,EMP_MASTER B,
GPF_CL_WD C
WHERE A.ENO = B.ENO AND A.ENO=C.ENO
AND TO_DATE(SUBSTR(A.MONTH,4))=:MON
AND B.GAZATTED like :G
AND A.eno like :EN
AND A.ENO NOT LIKE 'DRN%'
ORDER BY A.BP DESC
QUERY Q2 is:
SELECT
A.ENO,RTRIM(B.FNAME)||' '||RTRIM( B.SNAME),RTRIM(B.RADESG),B.DOB,B.DT_OF_INC,B.GROU,B.GPF_NO,
A.BP,A.DA,A.SP,A.TA,A.CCA,A.HRA,A.DP,A.WA,
nvl(A.MISC,0) MISC,A.TOT_CREDIT,A.GPFS,A.GPFR,A.PLI,
A.CGHS,A.CGEGIS,A.LF,A.FA,A.IT,A.HBA,A.MCA,
NVL(A.MISC1,0) MISC1,NVL(A.MISC2,0) MISC2,A.TOT_DEBIT,A.NET_PAY,
A.FEST_INST,A.FEST_BAL,A.FEST_TOT_INST,
A.GPF_INST,A.GPF_BAL,A.GPF_TOT_INST,
A.HBA_ADV_INST,A.HBA_ADV_BAL,A.HBA_ADV_TOT_INST,
A.MCA_ADV_INST,A.MCA_ADV_BAL,A.MCA_ADV_TOT_INST,
A.HBA_INT_INST,A.HBA_INT_BAL,A.HBA_INT_TOT_INST,
A.MCA_INT_INST,A.MCA_INT_BAL,A.MCA_INT_TOT_INST,
A.REMARK,A.REM1,A.REM2,A.MONTH,
A.COMPU_INT_INST,A.COMPU_INT_BAL,A.COMPU_INT_TOT_INST,A.COMPU_ADV_INST,A.COM_ADV,
A.COMPU_ADV_BAL,A.COMPU_ADV_TOT_INST,a.stagnation_amt,
A.gpfcl,C.GPFCL,C.GPFSUBS,C.GPFSUBS_STDT,
C.GPFWD,C.GPFWD_DT,
C.GPFSUBS2,C.GPFSUBS_STDT2,NVL(A.DEARNESS_PAY,0) DEARNESS_PAY,NVL(A.MISCC,0) MISCC,NVL(A.MISCD,0) MISCD
FROM SALARY_MASTER A,EMP_MASTER B,
GPF_CL_WD C
WHERE A.ENO = B.ENO AND A.ENO=C.ENO
and to_date(month,'dd-mon-yyyy') BETWEEN to_date('01-MAR-2006','DD-MON-YYYY') AND :MON
AND B.GAZATTED like :G
AND A.eno like :EN
AND A.ENO NOT LIKE 'DRN%'
ORDER BY A.BP DESC
So for summary the problem has been solved.
But for detail salary, i think here that the old story of apples and oranges taking turn.
But hope to get result soon.
Shaila.
|
|
|
|
|
|
|
|
|
|
| Re: My JOB is in DANGER....Please give some time to my probs. [message #205417 is a reply to message #205381] |
Fri, 24 November 2006 11:07 |
skooman
Messages: 913
Registered: March 2005
Location: Netherlands
|
Senior Member |
|
|
Shaila,
Eeeh, just make sure (probably I should put on my glasses or something  ): I asked on May 22 what exactly was the remaining problem, and on November 24 you tell me some little problems can become a hurdle. A problem that I have been struggling with for 6 months... I wouldn't call that a Little Problem!
So, is it actually true that you're still trying to solve that same query?? Wow, I have to admire your persistence...
But hey, we are here to help, right? So, please refresh my memory: could you please explain what exactly is the problem now remaining?
Regards,
Sabine
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Sat Nov 30 05:42:48 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
I am posting same message again sorry for this. Because i do not have any other options .
