Home » SQL & PL/SQL » SQL & PL/SQL » Doubt in SQL query (Oracle 10g)
Doubt in SQL query [message #395042] Tue, 31 March 2009 02:54 Go to next message
sr_orcl
Messages: 82
Registered: January 2009
Location: mumbai
Member

Hi To All Experts,
SELECT CASE WHEN (COUNT(aa.pass) >= 1) THEN 'F'
            ELSE 'P' END
FROM   (SELECT CASE WHEN f.dsum <= f.psum THEN 'P'
                    ELSE 'F' END pass,
               f.dsum,
               f.psum
        FROM   (SELECT a.id,
                       SUM(a.disb_sum) dsum,
                       SUM(b.pymt_sum) psum
                FROM   (SELECT drawdownscheduledtlid id,
                               nvl(SUM(fngettargetcurramt(lbd.disb_amt,
                                                          lbd.cg_currency,
                                                          llt1.cg_currency,
                                                          lbd.companyid)), 0) disb_sum,
                               dense_rank() over(PARTITION BY drawdownscheduledtlid ORDER BY NULL) top
                        FROM   lot_drawdownbreakup_dtl lbd,
                               lot_loan_t              llt1
                        WHERE  llt1.comp_appl_id = lbd.compapplid
                               AND compapplid = 48
                               AND lbd.status <> 'X'
                        GROUP  BY drawdownscheduledtlid) a,
                       (SELECT id,
                               nvl(SUM(fngettargetcurramt(paymentamt,
                                                          ldd.cg_currency,
                                                          llt.cg_currency,
                                                          ldd.companyid)), 0) pymt_sum,
                               dense_rank() over(PARTITION BY id ORDER BY NULL) t1
                        FROM   lot_loan_t               llt,
                               lot_drawdownschedule_dtl ldd
                        WHERE  llt.comp_appl_id = ldd.compapplid
                               AND llt.comp_appl_id = 48
                        GROUP  BY id) b
                WHERE  a.id = b.id
                GROUP  BY a.id) f) aa
WHERE  aa.pass = 'F'



The above query work fine but in oracle11g it has some problem with dense_rank().It shows error message not a gropu by function.
If i remove the dense_rank() then it runs in oracle11g.

Can you plese tell me what is the problem with this query?

Thanks And Regards,


[Added code tags]

[Updated on: Tue, 31 March 2009 03:23] by Moderator

Report message to a moderator

Re: Doubt in SQL query [message #395043 is a reply to message #395042] Tue, 31 March 2009 02:59 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Can you show you us the actual error message?

It sounds like an ORA-00979 Not a GROUP BY expression, but I'd be very suprised if that was release dependant.

Can you show us (cut and paste from SQL*PLus) the query working on 10g and not working on 11g?
Re: Doubt in SQL query [message #395045 is a reply to message #395042] Tue, 31 March 2009 03:09 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I've been looking at this inner query:
SELECT drawdownscheduledtlid id,
       nvl(SUM(fngettargetcurramt(lbd.disb_amt,
               lbd.cg_currency,
               llt1.cg_currency,
               lbd.companyid)), 0) disb_sum,
       dense_rank() over(PARTITION BY drawdownscheduledtlid ORDER BY NULL) top
FROM   lot_drawdownbreakup_dtl lbd,
       lot_loan_t llt1
WHERE  llt1.comp_appl_id = lbd.compapplid
AND    compapplid = 48
AND    lbd.status <> 'X'
GROUP BY drawdownscheduledtlid


And I'm pretty sure you've got this wrong.
Your DENSE_RANK function will simply return 1 for every row - You are grouping the rows by DrawDownScheduleId, and this Grouping operation will be performed before the Analytic function, resulting in the Dense_Rank only having a single row to look at in each set of partitioned values.

I've tried structures like yours on 11g, and I get no error:
create table test_169 (col_1  number, col_2  number);

insert into test_169 values (1,1);
insert into test_169 values (1,2);
insert into test_169 values (1,3);

insert into test_169 values (2,2);
insert into test_169 values (2,2);
insert into test_169 values (3,2);

select col_1
      ,max(col_2)
      ,dense_rank() over (partition by col_1 order by null) top
from test_169 
group by col_1;
Re: Doubt in SQL query [message #395046 is a reply to message #395045] Tue, 31 March 2009 03:16 Go to previous messageGo to next message
sr_orcl
Messages: 82
Registered: January 2009
Location: mumbai
Member

It shows following message
"ORA-00937: not a single-group group function"

All the inner queries run properly
Re: Doubt in SQL query [message #395048 is a reply to message #395046] Tue, 31 March 2009 03:19 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Can you please run the query in SQL*Plus and cut and paste the query and the error message.
Re: Doubt in SQL query [message #395052 is a reply to message #395048] Tue, 31 March 2009 03:34 Go to previous messageGo to next message
sr_orcl
Messages: 82
Registered: January 2009
Location: mumbai
Member


Error at this line
GROUP BY lbd.drawdownscheduledtlid) a,
*
ERROR at line 12:
ORA-00933: SQL command not properly ended


SQL>
Re: Doubt in SQL query [message #395054 is a reply to message #395052] Tue, 31 March 2009 03:49 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
I think JRowbottom is probably taking a few deep breaths at the moment.
He asked you to:
Quote:
Can you please run the query in SQL*Plus and cut and paste the query and the error message.
You have failed to do this. Please re-read his request and repost with the information that he requested.
Re: Doubt in SQL query [message #395059 is a reply to message #395054] Tue, 31 March 2009 03:57 Go to previous messageGo to next message
sr_orcl
Messages: 82
Registered: January 2009
Location: mumbai
Member

SQL> (SELECT drawdownscheduledtlid id,
2 nvl(SUM(fngettargetcurramt(lbd.disb_amt,
3 lbd.cg_currency,
4 llt1.cg_currency,
5 lbd.companyid)), 0) disb_sum,
6 dense_rank() over(PARTITION BY drawdownscheduledtlid ORDER BY NU
LL) top
7 FROM lot_drawdownbreakup_dtl lbd,
8 lot_loan_t llt1
9 WHERE llt1.comp_appl_id = lbd.compapplid
10 AND lbd.compapplid = 680
11 AND lbd.status <> 'X'
12 GROUP BY drawdownscheduledtlid)a,
13 (SELECT id iid,
14 nvl(SUM(fngettargetcurramt(paymentamt,
15 ldd.cg_currency,
16 llt.cg_currency,
17 ldd.companyid)), 0) pymt_sum,
18 dense_rank() over(PARTITION BY id ORDER BY NULL) t1
19 FROM lot_loan_t llt,
20 lot_drawdownschedule_dtl ldd
21 WHERE llt.comp_appl_id = ldd.compapplid
22 AND llt.comp_appl_id = 680
23 AND ldd.status<>'X'
24 GROUP BY id) b
25 WHERE a.id = b.iid
26 GROUP BY a.id) f) aa
27 WHERE aa.pass = 'F'
28 ;
GROUP BY drawdownscheduledtlid)a,
*
ERROR at line 12:
ORA-00933: SQL command not properly ended


SQL>
Re: Doubt in SQL query [message #395060 is a reply to message #395059] Tue, 31 March 2009 04:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Maybe if you add code tags like Moderator did it for you in your first post and explain in read OraFAQ Forum Guide, especially "How to format your post?" section, your query should be more readable.

Regards
Michel
Re: Doubt in SQL query [message #395061 is a reply to message #395059] Tue, 31 March 2009 04:03 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You're aware that this is a completely different error to the one that you reported?

What you've got there isn't a valid piece of SQL - it's two sub-queries with a where clause and some un-matched brackets attached.

Go away and try again.
Run your query in SQL*Plus, and cut and paste the query and the results into this forum.
Re: Doubt in SQL query [message #395086 is a reply to message #395061] Tue, 31 March 2009 04:51 Go to previous messageGo to next message
sr_orcl
Messages: 82
Registered: January 2009
Location: mumbai
Member

Hi,
Please check the attached file for the sample data and table structure.
  • Attachment: test.sql
    (Size: 3.27KB, Downloaded 121 times)
Re: Doubt in SQL query [message #395129 is a reply to message #395086] Tue, 31 March 2009 07:22 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Please look at this from my point of view.

I have a day job, and lots of other posts besides yours that I can look at.
The people who get the most help from me are:
1) People with interesting problems where I get to practice bits of SQL that I don't frequently use
2) People who make it easy for me to help them.

Your problem does not fall into category 1.

Therefore, if you want any more help you need to get yourself into category 2. Posting the DDL you just did helps, (or will if it runs without me having to edit it), but mainly, will you PLEASE do what I ask and run your query in SQL*Plus, cutting and pasting the query and the results into this forum.

Thank you.

[The DDL works, but you forgot the table LOT_LOAN.]

[Updated on: Tue, 31 March 2009 07:26]

Report message to a moderator

Re: Doubt in SQL query [message #395434 is a reply to message #395129] Wed, 01 April 2009 07:52 Go to previous messageGo to next message
sr_orcl
Messages: 82
Registered: January 2009
Location: mumbai
Member

Hi,
I am sorry that i am not following the guide lines.


please check this query that I run in sql*plus.
SQL> SELECT CASE
  2             WHEN (COUNT(aa.pass) >= 1) THEN
  3              'F'
  4             ELSE
  5              'P'
  6         END
  7  FROM   (SELECT CASE
  8                     WHEN f.dsum <= f.psum THEN
  9                      'P'
 10                     ELSE
 11                      'F'
 12                 END pass,
 13                 f.dsum,
 14                 f.psum
 15          FROM   (SELECT a.id,
 16                         SUM(a.disb_sum) dsum,
 17                         SUM(b.pymt_sum) psum
 18                  FROM   (SELECT drawdownscheduledtlid id,
 19                                 nvl(SUM(fngettargetcurramt(lbd.disb_amt,
 20                                                            lbd.cg_currency,
 21                                                            llt1.cg_currency,
 22                                                            lbd.companyid)), 0) disb_sum,
 23                                 dense_rank() over(PARTITION BY drawdownscheduledtlid ORDER BY NU
LL) top
 24                          FROM   lot_drawdownbreakup_dtl lbd,
 25                                 lot_loan_t              llt1
 26                          WHERE  llt1.comp_appl_id = lbd.compapplid
 27                                 AND compapplid = 680
 28                                 AND lbd.status <> 'X'
 29                          GROUP  BY drawdownscheduledtlid) a,
 30                         (SELECT id,
 31                                 nvl(SUM(fngettargetcurramt(paymentamt,
 32                                                            ldd.cg_currency,
 33                                                            llt.cg_currency,
 34                                                            ldd.companyid)), 0) pymt_sum,
 35                                 dense_rank() over(PARTITION BY id ORDER BY NULL) t1
 36                          FROM   lot_loan_t               llt,
 37                                 lot_drawdownschedule_dtl ldd
 38                          WHERE  llt.comp_appl_id = ldd.compapplid
 39                                 AND llt.comp_appl_id = 680
 40                          GROUP  BY id) b
 41                  WHERE  a.id = b.id
 42                  GROUP  BY a.id) f) aa
 43  WHERE  aa.pass = 'F'
 44  ;
                               dense_rank() over(PARTITION BY drawdownscheduledtlid ORDER BY NULL) top
                                                              *
ERROR at line 23:
ORA-00937: not a single-group group function



[Mod-edit: Frank added [code] and [/code] around the code part to improve readability]

[Updated on: Wed, 01 April 2009 08:05] by Moderator

Report message to a moderator

Re: Doubt in SQL query [message #395481 is a reply to message #395434] Wed, 01 April 2009 10:43 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Here's a stripped down version of your query that runs on the tables you gave us, and still produces the error:
create table test_169 (col_1  number, col_2  number);

insert into test_169 values (1,1);
insert into test_169 values (1,2);
insert into test_169 values (1,3);

insert into test_169 values (2,2);
insert into test_169 values (2,2);
insert into test_169 values (3,2);

select col_1
      ,nvl(sum(f_val(col_2)),0)
      ,dense_rank() over (partition by col_1 order by null) top
from test_169 
group by col_1;


select case when count(*) > 1 then 'A' else 'B' end
from  (select * from test_169 where col_1=1);

create or replace function f_val (p_in  number) return number as
begin
  return p_in;
end;
/

select * from user_tables;

create table LOT_DRAWDOWNBREAKUP_DTL
(
  ID                    NUMBER(8) not null,
  MAKERID               VARCHAR2(8 CHAR) not null,
  STATUS                VARCHAR2(1 CHAR) not null,
  MAKERDATE             DATE not null,
  BENEFICIARYID         NUMBER(8),
  DRAWDOWNSCHEDULEDTLID NUMBER(8),
  CG_DISB_CURRENCY      NUMBER(8),
  CG_CURRENCY           NUMBER(8),
  DRAWDOWN              NUMBER(8),
  COMPAPPLID            NUMBER(8),
  COMPANYID             NUMBER(8),
  CG_TYPE_OF_PAYMENT    NUMBER(8),
  LOANAMOUNT            NUMBER(16,2),
  DISB_AMT              NUMBER(16,2),
  RLSACCNO              VARCHAR2(80),
  FACILITYTYPECODE      VARCHAR2(32),
  LOANREFNO             VARCHAR2(200)
);


insert into lot_drawdownbreakup_dtl (ID, MAKERID, STATUS, MAKERDATE, BENEFICIARYID, DRAWDOWNSCHEDULEDTLID, CG_DISB_CURRENCY, CG_CURRENCY, DRAWDOWN, COMPAPPLID, COMPANYID, CG_TYPE_OF_PAYMENT, LOANAMOUNT, DISB_AMT, RLSACCNO, FACILITYTYPECODE, LOANREFNO)
values (314, 'DDOWNM', 'A', to_date('05-05-2009', 'dd-mm-yyyy'), 300, 348, 241, 241, null, 680, 5000, 710, 360000.00, 360000.00, '', '', '');

insert into lot_drawdownbreakup_dtl (ID, MAKERID, STATUS, MAKERDATE, BENEFICIARYID, DRAWDOWNSCHEDULEDTLID, CG_DISB_CURRENCY, CG_CURRENCY, DRAWDOWN, COMPAPPLID, COMPANYID, CG_TYPE_OF_PAYMENT, LOANAMOUNT, DISB_AMT, RLSACCNO, FACILITYTYPECODE, LOANREFNO)
values (315, 'DDOWNM', 'A', to_date('05-05-2009', 'dd-mm-yyyy'), 300, 348, 241, 241, null, 680, 5000, 710, 360000.00, 360000.00, '', '', '');
----------
create table LOT_DRAWDOWNSCHEDULE_DTL
(
  ID                     NUMBER(10) not null,
  HDRID                  NUMBER(8),
  COMPAPPLID             NUMBER(8),
  DRAWDOWN               NUMBER(8),
  CG_TYPE_OF_PAYMENT     NUMBER(20),
  PROPOSEDDATE           DATE,
  BENEFICIARYID          NUMBER,
  DRAWDOWNAMT            NUMBER(16,2),
  PAYMENTSTATUS          VARCHAR2(1 CHAR),
  PAYMENTAMT             NUMBER(16,2),
  PAYMENTDATE            DATE,
  PAYMENTTOBENEFICIARYID NUMBER,
  STATUS                 VARCHAR2(1 CHAR),
  COMPANYID              NUMBER(8),
  MAKERID                VARCHAR2(8 CHAR),
  MAKERDATE              DATE,
  CG_EVENT               NUMBER(8),
  PAYMENTBY              VARCHAR2(1 CHAR),
  PAYMENTSCHEDULEID      NUMBER(8),
  DISBREFNO              VARCHAR2(16 CHAR),
  CG_CURRENCY            NUMBER(8),
  APPLID                 NUMBER(8),
  CAPITALIZEYN           VARCHAR2(1),
  ADJUSTEDTOID           NUMBER(8),
  UNUSED_AMT             NUMBER(16,2),
  CG_UNUSED_AMT_CURRENCY NUMBER(8),
  APPR_FLAG              VARCHAR2(4),
  LOAN_NO                VARCHAR2(200),
  TENURE                 NUMBER(3)
);

insert into lot_drawdownschedule_dtl (ID, HDRID, COMPAPPLID, DRAWDOWN, CG_TYPE_OF_PAYMENT, PROPOSEDDATE, BENEFICIARYID, DRAWDOWNAMT, PAYMENTSTATUS, PAYMENTAMT, PAYMENTDATE, PAYMENTTOBENEFICIARYID, STATUS, COMPANYID, MAKERID, MAKERDATE, CG_EVENT, PAYMENTBY, PAYMENTSCHEDULEID, DISBREFNO, CG_CURRENCY, APPLID, CAPITALIZEYN, ADJUSTEDTOID, UNUSED_AMT, CG_UNUSED_AMT_CURRENCY, APPR_FLAG, LOAN_NO, TENURE)
values (348, 297, 680, null, 710, to_date('13-05-2009', 'dd-mm-yyyy'), 300, null, 'P', 360000.00, null, null, 'A', 5000, '', to_date('30-03-2009 19:09:49', 'dd-mm-yyyy hh24:mi:ss'), null, '', null, '', 241, 631, '', null, -360000.00, 241, '', '', null);

SELECT CASE
             WHEN (COUNT(aa.pass) >= 1) THEN
              'F'
             ELSE
              'P'
         END
  FROM   (SELECT CASE
                     WHEN f.dsum <= f.psum THEN
                      'P'
                     ELSE
                      'F'
                 END pass,
                 f.dsum,
                 f.psum
          FROM   (SELECT a.id,
                         SUM(a.disb_sum) dsum,
                         SUM(b.pymt_sum) psum
                  FROM   (SELECT drawdownscheduledtlid id,
                                 nvl(SUM(lbd.disb_amt), 0) disb_sum,
                                 dense_rank() over(PARTITION BY drawdownscheduledtlid ORDER BY NULL) top
                          FROM   lot_drawdownbreakup_dtl lbd
                          WHERE   compapplid = 680
                                 AND lbd.status <> 'X'
                          GROUP  BY drawdownscheduledtlid) a,
                         (SELECT id,
                                 nvl(SUM(paymentamt), 0) pymt_sum,
                                 dense_rank() over(PARTITION BY id ORDER BY NULL) t1
                          FROM   lot_drawdownschedule_dtl ldd
                          GROUP  BY id) b
                  WHERE  a.id = b.id
                  GROUP  BY a.id) f) aa
  WHERE  aa.pass = 'F'
  ;


Step 1) Raise an SR with Oracle - the same query gives different results on the same data on different versions - BUG

Step 2) As an interim fix, get rid of the dense_rank lines in the query - they serve no purpose, and will simply return 1 for each row. Plus you don't use them in the next level of SQL up from them, so they're just eating memory and Cpu time.
Previous Topic: execution problem
Next Topic: Performance issue with name value pairs
Goto Forum:
  


Current Time: Mon Dec 05 14:43:32 CST 2016

Total time taken to generate the page: 0.11629 seconds