Home » SQL & PL/SQL » SQL & PL/SQL » Strange output - Please Advise
Strange output - Please Advise [message #231566] Tue, 17 April 2007 09:50 Go to next message
madprog
Messages: 3
Registered: January 2006
Junior Member
This query is just not giving me the correct output. When I run it manually it works great. When I execute from a procedure it has strange output. See below and I will try to explain:

CREATE OR REPLACE PROCEDURE COREData_test (asofdate IN DATE)
AS
BEGIN
SELECT 
       A.Company NATIVEBANK,
       'STUC' LOANSYSTEM,
       Client.BCRNum OBLIGORNUMBER,
       A.BCRUtil OBLIGATIONNUMBER,
       TranCode,
       NativeTranCode,
       SOURCESYSTEMSIGN,
       SOURCESYSTEMAMT
FROM
    (SELECT * FROM Division.VW_W_BABC_DETAILMASTER_NA) Dmast,
    Clientinformation Client,
    (SELECT Temp.Officenum,
               Temp.Clinum,
               Temp.Loannum,
               TranCode,
               NativeTranCode,
               DECODE(SIGN(LoanAdj*RiskPerc),-1,'+','-') sourceSystemSign,
               ROUND(ABS(LoanAdj*RiskPerc),2) SourceSystemAmt,
               DECODE(NonEarningType,'WOF','162045','164045') GLAccount,
               DECODE(NonEarningType,'WOF','162045','164045') GLNativeCode
           FROM

             (SELECT Risk.Officenum,
                    Risk.Clinum,
                    Risk.Notenumber Loannum,
                    DECODE(SumC,0,0,RiskAmount/SumC) RiskPerc
               FROM
                    (SELECT OFFICENUM,
                           CLINUM,
                           SUM(ACCRINT) TotalAccr,
                           SUM(DECODE(NAType,' ',
                             DECODE(LoanType,'PT',0,'LC',0,Accrint),0)) SumA,
                           SUM(DECODE(NAType,' ',0,Accrint)) SumB,
                           SUM(DECODE(NAType,' ', DECODE(LoanType,'PT',0,'LC',0,RiskAmount),0)) SumC

                      FROM PIMS_TBLNONACCRUAL
                      group by Officenum, clinum) Total,

                    (SELECT OFFICENUM,
                           CLINUM,
                           NOTENUMBER,
                           RiskAmount,
                           NAType,
                           LoanType
                      FROM PIMS_TBLNONACCRUAL) Risk

            WHERE Risk.Officenum = Total.OFficenum
              AND Risk.Clinum = Total.Clinum
              AND Risk.NAType = ' '
              AND Risk.LoanType != 'LC') Temp,

            (SELECT
                Comdhis.Officenum,
                Comdhis.Clinum,
                Comdhis.Loannum,
                NonEarningType,
                DECODE(NonEArningType,'WOF',
                    DECODE(SIGN(SUM(nvl(Comdhis.LoanAdj,0))),-1,'CO','COREV'), 'REC',
                    DECODE(SIGN(SUM(nvl(Comdhis.LoanAdj,0))),-1,'REREV','RE')) Trancode,
                DECODE(NonEArningType,'WOF',
                    DECODE(SIGN(SUM(nvl(Comdhis.LoanAdj,0))),-1,'CO','COREV'), 'REC',
                    DECODE(SIGN(SUM(nvl(Comdhis.LoanAdj,0))),-1,'REREV','RE')) NativeTrancode,
                SUM(Comdhis.LoanAdj) LoanAdj
………(more code)


When I run using the parameter and run from a procedure line this is my output:

NATIVEBANK	LOANSYSTEM	OBLIGORNUMBER	OBLIGATIONNUMBER	TRANCODE	NATIVETRANCODE	SOURCESYSTEMSIGN	SOURCESYSTEMAMT
607	STUC	BAWE160STN	BAWE160STN0A	31-MAR-07	31-MAR-07	-	25000
607	STUC	MIDA130PSE	MIDA130PSE0E	31-MAR-07	31-MAR-07	+	1545891.81
607	STUC	MIDA130PSE	MIDA130PSE0E	31-MAR-07	31-MAR-07	-	1600242.55
607	STUC	BASE120TLK	BASE120TLK1A	31-MAR-07	31-MAR-07	-	29447.54
607	STUC	BASE120TLK	BASE120TLK00	31-MAR-07	31-MAR-07	-	61044.75
607	STUC	BASE120ASI	BASE120ASI00	31-MAR-07	31-MAR-07	-	76106.5
607	STUC	BAMA130PSC	BAMA130PSCU0	31-MAR-07	31-MAR-07	+	3970.57
607	STUC	BAMA130PSC	BAMA130PSCU0	31-MAR-07	31-MAR-07	-	4334.94
607	STUC	BAMA130PSC	BAMA130PSC0A	31-MAR-07	31-MAR-07	+	1432252.34


For some reason the AsOfDate gets put into the TRANCODE and NATIVETRANCODE Column
If I run Manually it looks like this (Replacing the AsOfDate with TO_DATE(’31-MAR-2007’) – This is how it should look

NATIVEBANK	LOANSYSTEM	OBLIGORNUMBER	OBLIGATIONNUMBER	TRANCODE	NATIVETRANCODE	SOURCESYSTEMSIGN	SOURCESYSTEMAMT
607	STUC	BAWE160STN	BAWE160STN0A	CO	CO	-	25000
607	STUC	MIDA130PSE	MIDA130PSE0E	RE	RE	+	1545891.81
607	STUC	MIDA130PSE	MIDA130PSE0E	CO	CO	-	1600242.55
607	STUC	BASE120TLK	BASE120TLK1A	CO	CO	-	29447.54
607	STUC	BASE120TLK	BASE120TLK00	RE	RE	-	61044.75
607	STUC	BASE120ASI	BASE120ASI00	RE	RE	-	76106.5
607	STUC	BAMA130PSC	BAMA130PSCU0	RE	RE	+	3970.57
607	STUC	BAMA130PSC	BAMA130PSCU0	RE	RE	-	4334.94
607	STUC	BAMA130PSC	BAMA130PSC0A	CO	CO	+	1432252.34

Has this issue ever happened to anyone – is it the complexity or is Oracle having a brain Cramp.

Thanks
S

[Moderator - added CODE tags]

[Updated on: Wed, 18 April 2007 23:12] by Moderator

Report message to a moderator

Re: Strange output - Please Advise [message #231570 is a reply to message #231566] Tue, 17 April 2007 09:56 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>Has this issue ever happened to anyone – is it the complexity or is Oracle having a brain Cramp.

What happens when below is used instead?
TO_DATE(’31-MAR-2007’,'DD-MON-YYYY')
Re: Strange output - Please Advise [message #231573 is a reply to message #231566] Tue, 17 April 2007 10:02 Go to previous messageGo to next message
madprog
Messages: 3
Registered: January 2006
Junior Member
No - I get the same result - I am using TOAD but this also happens using Embaradero RapidSQL. Never seen anything like this.....Two values go into NATIVE and TRAN CODE. Either 'CO' or 'RE'. This procedure is not too complex. I am also on 8i. I do not have access to our 10g server yet and would have loved to see it the behavior was the same
Re: Strange output - Please Advise [message #231893 is a reply to message #231566] Wed, 18 April 2007 14:48 Go to previous message
smcnulty
Messages: 2
Registered: April 2007
Junior Member
It looks like you might be getting something odd from your NonEarningType source.

I'd say you might try putting a default in the decode if the NonEarningType comes back with an unexpected value and see if you get a result that is unexpected.

You also might output your NonEarningType to see what it is coming back with as well.

Failing that, you may want to post the rest of your code.

No, I haven't seen it before either.


Previous Topic: Calling a function
Next Topic: Random Sample
Goto Forum:
  


Current Time: Fri Dec 09 21:19:40 CST 2016

Total time taken to generate the page: 0.08041 seconds