HnD | oracle view rownum problem, Page 1

oracle view rownum problem

Posts

vikramka
User

Posts: 42
Joined: 08-Sep-2004

# Posted on: 18-Jan-2006 23:28:55

Has anyone come across this problem?

I have an entity based on a view. I use fetchentitycollection paging feature and found a problem when I was filtering that the row count which I get using getdbcount said 863, but rows returned were only 10 on first page when pagesize is 100. So I looked at the query llblgen sends over and it was:


SELECT *
  FROM (SELECT a.*, ROWNUM r___
          FROM (SELECT   "PCPACP"."SOMEVIEW"."COL1",
                         "PCPACP"."SOMEVIEW"."COL2",
                         "PCPACP"."SOMEVIEW"."COL3",
                         "PCPACP"."SOMEVIEW"."COL4",
                         "PCPACP"."SOMEVIEW"."COL5",
                         "PCPACP"."SOMEVIEW"."COL6",
                         "PCPACP"."SOMEVIEW"."COL7",
                         "PCPACP"."SOMEVIEW"."COL8",
                         "PCPACP"."SOMEVIEW"."COL9",
                         "PCPACP"."SOMEVIEW"."COL10",
                         "PCPACP"."SOMEVIEW"."COL11",
                         "PCPACP"."SOMEVIEW"."COL12",
                         "PCPACP"."SOMEVIEW"."COL13"
                    FROM "PCPACP"."SOMEVIEW"
                   WHERE (((   "PCPACP"."SOMEVIEW"."COL13" = :col131
                            OR "PCPACP"."SOMEVIEW"."COL13" = :col132  
                           )
                          )
                         )
                ORDER BY "PCPACP"."SOMEVIEW"."COL3" ASC) a
         WHERE ROWNUM < :rownostart__)
 WHERE r___ >= :rownoend__

Now what I did was take this query and run it without any filtering on rownums and I got back 863 rows. I modified the query thus


          SELECT a.*, ROWNUM r___
          FROM (SELECT   "PCPACP"."SOMEVIEW"."COL1",
                         "PCPACP"."SOMEVIEW"."COL2",
                         "PCPACP"."SOMEVIEW"."COL3",
                         "PCPACP"."SOMEVIEW"."COL4",
                         "PCPACP"."SOMEVIEW"."COL5",
                         "PCPACP"."SOMEVIEW"."COL6",
                         "PCPACP"."SOMEVIEW"."COL7",
                         "PCPACP"."SOMEVIEW"."COL8",
                         "PCPACP"."SOMEVIEW"."COL9",
                         "PCPACP"."SOMEVIEW"."COL10",
                         "PCPACP"."SOMEVIEW"."COL11",
                         "PCPACP"."SOMEVIEW"."COL12",
                         "PCPACP"."SOMEVIEW"."COL13"
                    FROM "PCPACP"."SOMEVIEW"
                   WHERE (((   "PCPACP"."SOMEVIEW"."COL13" = :col131
                            OR "PCPACP"."SOMEVIEW"."COL13" = :col132  
                           )
                          )
                         )
                ORDER BY "PCPACP"."SOMEVIEW"."COL3" ASC) a
         WHERE ROWNUM < 800

I get only 15 rows running the above query. Did anyone face this problem before. I am at my wits end now. Maybe rownum and views do not make a good combination?

Otis
LLBLGen Pro Team

Posts: 39930
Joined: 17-Aug-2003

# Posted on: 19-Jan-2006 00:01:48

strange...

rownum is in caps in your query btw? It should be lowercase.

Frans Bouma | Lead developer LLBLGen Pro

vikramka
User

Posts: 42
Joined: 08-Sep-2004

# Posted on: 19-Jan-2006 00:32:15

Otis wrote:

strange...

rownum is in caps in your query btw? It should be lowercase.

I dont think the case matters. I was using the TOAD session browser to see the query and it must have gotten formatted.

bclubb
User

Posts: 934
Joined: 12-Feb-2004

# Posted on: 19-Jan-2006 02:20:20

Can you post your code to generate the query? I'm not seeing the same behavior, but could very well be doing the query differently.

vikramka
User

Posts: 42
Joined: 08-Sep-2004

# Posted on: 19-Jan-2006 05:24:01

bclubb, the original code is pretty much mundane like the countless times I have written them.

manager code*******


        public static int GetPPCount(IPredicateExpression filter, DataAccessAdapter adapterToUse)
        {
            int toReturn = 0;

            IRelationPredicateBucket bucket = new RelationPredicateBucket();
            bucket.PredicateExpression.Add(filter);

            object result = adapterToUse.GetDbCount(new PPEntityFactory().CreateFields(), bucket);
            if( result != null )
            {
                toReturn = (int)result;
            }
            return toReturn;
        }
        public static EntityCollection FetchPPCollection(IPredicateExpression filter, ISortExpression sorter, int page, int pageSize, DataAccessAdapter adapterToUse)
        {
            EntityCollection toReturn = new EntityCollection(new PPEntityFactory());

            IRelationPredicateBucket bucket = new RelationPredicateBucket();
            bucket.PredicateExpression.Add(filter);

            adapterToUse.FetchEntityCollection(toReturn, bucket, 0, sorter, page, pageSize);

            return toReturn;
        }

*************data grid bind code ******************


        private void BindGrid()
        {
            EntityCollection list;

            listPageSize.Visible = false;
            lbl1.Visible = false;
            lbl2.Visible = false;
            dlSortOrder.Visible = false;
            dlSortField.Visible = false;
            btnUpdate.Visible = false;
            DG.Visible = false;
            
            DG.PageSize = Convert.ToInt32(listPageSize.SelectedValue);
            try
            {
                IPredicateExpression A = (IPredicateExpression)this.SearchCriteria;
                ISortExpression sorter = this.GetSortExpression();

                using (DataAccessAdapter adapter = DataAccessAdapterFactory.CreateAdapter(Request["net"],true))
                {
                    adapter.CommandTimeOut = 600;
                    list = ReportManager.FetchPPCollection(
                        A,
                        sorter,
                        DG.CurrentPageIndex+1,
                        Convert.ToInt32(listPageSize.SelectedValue),
                        adapter);
                    adapter.CloseConnection();
                }
            }
            catch(ORMQueryExecutionException E)
            {
                lblError.Text = E.Message+"<br>**<br>"+E.QueryExecuted+"<br>**<br>"+E.Source+"<br>**<br>"+E.StackTrace;
                return;
            }
            catch(Exception E)
            {
                lblError.Text = E.Message+"<br>**<br>"+E.InnerException+"<br>**<br>"+E.Source+"<br>**<br>"+E.StackTrace;
                return;
            }
            if (list.Count > 0) 
            {
                DG.VirtualItemCount = (int)this.TotalCountOfRows;
                listPageSize.Visible = true;
                lbl1.Visible = true;
                lbl2.Visible = true;
                dlSortOrder.Visible = true;
                dlSortField.Visible = true;
                btnUpdate.Visible = true;
                DG.Visible = true;
                DG.DataSource = list;
                DG.DataBind();
            }

        }

code to create filter*


        public IPredicateExpression GetPredicateExpression()
        {
            IPredicateExpression A = new PredicateExpression();
            if (txtID.Text.Trim() != String.Empty)
            {
                string[] keywords = txtID.Text.Split();
                for (int i = 0; i < keywords.Length; i++)
                {
                    if(i==0)
                    {
                        A.Add(PredicateFactory.CompareValue(PPFieldIndex.PAT_ID, ComparisonOperator.Equal,keywords[i]));
                    }
                    else
                    {
                        A.AddWithOr(PredicateFactory.CompareValue(PPFieldIndex.PAT_ID, ComparisonOperator.Equal,keywords[i]));
                    }
                }
            }

            IPredicateExpression B = new PredicateExpression();
            if (txtName.Text.Trim() != String.Empty)
            {
                string[] keywords = txtName.Text.Split();
                for (int i = 0; i < keywords.Length; i++)
                {
                    if(i==0)
                    {
                        FieldLikePredicate likeFilter=PredicateFactory.Like(PPFieldIndex.NAME,"%" + keywords[i].ToUpper() + "%");
                        likeFilter.CaseSensitiveCollation = true;
                        B.Add(likeFilter);
                    }
                    else
                    {
                        FieldLikePredicate likeFilter=PredicateFactory.Like(PPFieldIndex.NAME,"%" + keywords[i].ToUpper() + "%");
                        likeFilter.CaseSensitiveCollation = true;
                        B.AddWithOr(likeFilter);
                    }
                }
            }

            IPredicateExpression C = new PredicateExpression();
            if (txtProviderID.Text.Trim() != String.Empty)
            {
                C.Add(PredicateFactory.CompareValue(PPFieldIndex.PCP_ID, ComparisonOperator.Equal, txtProviderID.Text.Trim()));
                C.AddWithOr(PredicateFactory.CompareValue(PPFieldIndex.ACP_ID, ComparisonOperator.Equal, txtProviderID.Text.Trim()));
                C.AddWithOr(PredicateFactory.CompareValue(PPFieldIndex.AP_ID, ComparisonOperator.Equal, txtProviderID.Text.Trim()));
                C.AddWithOr(PredicateFactory.CompareValue(PPFieldIndex.BP_ID, ComparisonOperator.Equal, txtProviderID.Text.Trim()));
            }

            IPredicateExpression D = new PredicateExpression();
            if (txtProviderName.Text.Trim() != String.Empty)
            {
                string[] keywords = txtProviderName.Text.Split();
                for (int i = 0; i < keywords.Length; i++)
                {
                    if(i==0)
                    {
                        FieldLikePredicate likeFilter1=PredicateFactory.Like(PPFieldIndex.PCP_NAME,"%" + keywords[i].ToUpper() + "%");
                        likeFilter1.CaseSensitiveCollation = true;
                        FieldLikePredicate likeFilter2=PredicateFactory.Like(PPFieldIndex.ACP_NAME,"%" + keywords[i].ToUpper() + "%");
                        likeFilter2.CaseSensitiveCollation = true;
                        FieldLikePredicate likeFilter3=PredicateFactory.Like(PPFieldIndex.AP_NAME,"%" + keywords[i].ToUpper() + "%");
                        likeFilter3.CaseSensitiveCollation = true;
                        FieldLikePredicate likeFilter4=PredicateFactory.Like(PPFieldIndex.BP_NAME,"%" + keywords[i].ToUpper() + "%");
                        likeFilter4.CaseSensitiveCollation = true;
                        D.Add(likeFilter1);
                        D.AddWithOr(likeFilter2);
                        D.AddWithOr(likeFilter3);
                        D.AddWithOr(likeFilter4);
                    }
                    else
                    {
                        FieldLikePredicate likeFilter1=PredicateFactory.Like(PPFieldIndex.PCP_NAME,"%" + keywords[i].ToUpper() + "%");
                        likeFilter1.CaseSensitiveCollation = true;
                        FieldLikePredicate likeFilter2=PredicateFactory.Like(PPFieldIndex.ACP_NAME,"%" + keywords[i].ToUpper() + "%");
                        likeFilter2.CaseSensitiveCollation = true;
                        FieldLikePredicate likeFilter3=PredicateFactory.Like(PPFieldIndex.AP_NAME,"%" + keywords[i].ToUpper() + "%");
                        likeFilter3.CaseSensitiveCollation = true;
                        FieldLikePredicate likeFilter4=PredicateFactory.Like(PPFieldIndex.BP_NAME,"%" + keywords[i].ToUpper() + "%");
                        likeFilter4.CaseSensitiveCollation = true;
                        D.AddWithOr(likeFilter1);
                        D.AddWithOr(likeFilter2);
                        D.AddWithOr(likeFilter3);
                        D.AddWithOr(likeFilter4);
                    }
                }
            }

            IPredicateExpression E = new PredicateExpression();
            if (dlLocation.SelectedValue != "-1")
            {
                E.Add(PredicateFactory.CompareValue(PPFieldIndex.LOCATION_ID, ComparisonOperator.Equal,Int32.Parse(dlLocation.SelectedValue)));
            }

            IPredicateExpression F = new PredicateExpression();
            if (txtDeptCode.Text.Trim() != String.Empty)
            {
                string[] keywords = txtDeptCode.Text.Split();
                for (int i = 0; i < keywords.Length; i++)
                {
                    if(i==0)
                    {
                        F.Add(PredicateFactory.CompareValue(PPFieldIndex.DEPT_CODE, ComparisonOperator.Equal,keywords[i]));
                    }
                    else
                    {
                        F.AddWithOr(PredicateFactory.CompareValue(PPFieldIndex.DEPT_CODE, ComparisonOperator.Equal,keywords[i]));
                    }
                }
            }


            IPredicateExpression toReturn = new PredicateExpression();
            toReturn.AddWithAnd(A)
                .AddWithAnd(B)
                .AddWithAnd(C)
                .AddWithAnd(D)
                .AddWithAnd(E)
                .AddWithAnd(F);
            return toReturn;
        }

It will great if you can give this a look over.

Otis
LLBLGen Pro Team

Posts: 39930
Joined: 17-Aug-2003

# Posted on: 19-Jan-2006 10:00:39

Do you have an order by clause in your view? If so, could you try (for testing) to remove it and try again? Also, try without the sortexpression and try again. Though it is strange...

Frans Bouma | Lead developer LLBLGen Pro

vikramka
User

Posts: 42
Joined: 08-Sep-2004

# Posted on: 19-Jan-2006 16:01:43

I dont have any orderby in the view.

I did more testing and the results are: 1. Removing sortexpression got everything working properly again. Results are clearly unordered.

Through the application, when using sorting, if on a particular field I have 2 conditions, I observe strange behavior; i.e only 10 records returned although rowcount is 1500 etc.
Through the application, when using sorting, if on a particular field I have only 1 condition, things work fine. So I was ready to restrict the application to use only one condition on that particular field. But, just to make sure that I dont have any future problems, I ran the following in toad, which seems to be the queries that llblgen sends to oracle:


SELECT COUNT (*) AS numberofrows
  FROM (SELECT "PCPACP"."PP"."PP_ID",
               "PCPACP"."PP"."PP_NAME",
               "PCPACP"."PP"."PP_ID2",
               "PCPACP"."PP"."PP_DOB",
               "PCPACP"."PP"."PCP_ID",
               "PCPACP"."PP"."PCP_NAME",
               "PCPACP"."PP"."PCP_TITLE",
               "PCPACP"."PP"."PCP_UPD_DT",
               "PCPACP"."PP"."ACP_ID",
               "PCPACP"."PP"."ACP_NAME",
               "PCPACP"."PP"."ACP_TITLE",
               "PCPACP"."PP"."ACP_UPD_DT",
               "PCPACP"."PP"."BP_ID",
               "PCPACP"."PP"."BP_NAME",
               "PCPACP"."PP"."BP_TITLE",
               "PCPACP"."PP"."BP_DATE_TIME",
               "PCPACP"."PP"."BP_VISIT_ID",
               "PCPACP"."PP"."AP_ID",
               "PCPACP"."PP"."AP_NAME",
               "PCPACP"."PP"."AP_TITLE",
               "PCPACP"."PP"."AP_VISIT_ID",
               "PCPACP"."PP"."AP_DATE_TIME",
               "PCPACP"."PP"."LOCATION_ID",
               "PCPACP"."PP"."DEPT_CODE",
               "PCPACP"."PP"."LOCATION_NAME"
          FROM "PCPACP"."PP"
         WHERE ((("PCPACP"."PP"."DEPT_CODE" = '000')
                )
               )) tmpresult

I got 8791 as the answer. Then I ran the foll. in toad:


SELECT *
    FROM (SELECT a.*, ROWNUM r___
          FROM (SELECT   "PCPACP"."PP"."PP_ID",
                         "PCPACP"."PP"."PP_NAME",
                         "PCPACP"."PP"."PP_ID2",
                         "PCPACP"."PP"."PP_DOB",
                         "PCPACP"."PP"."PCP_ID",
                         "PCPACP"."PP"."PCP_NAME",
                         "PCPACP"."PP"."PCP_TITLE",
                         "PCPACP"."PP"."PCP_UPD_DT",
                         "PCPACP"."PP"."ACP_ID",
                         "PCPACP"."PP"."ACP_NAME",
                         "PCPACP"."PP"."ACP_TITLE",
                         "PCPACP"."PP"."ACP_UPD_DT",
                         "PCPACP"."PP"."BP_ID",
                         "PCPACP"."PP"."BP_NAME",
                         "PCPACP"."PP"."BP_TITLE",
                         "PCPACP"."PP"."BP_DATE_TIME",
                         "PCPACP"."PP"."BP_VISIT_ID",
                         "PCPACP"."PP"."AP_ID",
                         "PCPACP"."PP"."AP_NAME",
                         "PCPACP"."PP"."AP_TITLE",
                         "PCPACP"."PP"."AP_VISIT_ID",
                         "PCPACP"."PP"."AP_DATE_TIME",
                         "PCPACP"."PP"."LOCATION_ID",
                         "PCPACP"."PP"."DEPT_CODE",
                         "PCPACP"."PP"."LOCATION_NAME"
                FROM "PCPACP"."PP"
                WHERE ("PCPACP"."PP"."DEPT_CODE" = '000')
                ORDER BY "PCPACP"."PP"."BP_ID" ASC
                ) a
         WHERE ROWNUM < 1000
         )
 WHERE r___ >= 350

I got back nothing.

Strange isn't it? Am I doing something wrong? Why would application using llblgen + sorting work and not the same queries in toad?

My feeling is that sorting is the culprit somehow. Any thoughts? How do you think I should approach this now?

Walaa
Support Team

Posts: 14995
Joined: 21-Aug-2005

# Posted on: 20-Jan-2006 08:02:49

What's the Oracle version number you are using?

Otis
LLBLGen Pro Team

Posts: 39930
Joined: 17-Aug-2003

# Posted on: 20-Jan-2006 10:51:43

I've formatted the second query a little.

The weird thing is: the order by is in the query from which rows are selected. So ROWNUM should be based on that order.

if you instead do:


SELECT a.*, ROWNUM r___
FROM (SELECT   "PCPACP"."PP"."PP_ID",
         "PCPACP"."PP"."PP_NAME",
         "PCPACP"."PP"."PP_ID2",
         "PCPACP"."PP"."PP_DOB",
         "PCPACP"."PP"."PCP_ID",
         "PCPACP"."PP"."PCP_NAME",
         "PCPACP"."PP"."PCP_TITLE",
         "PCPACP"."PP"."PCP_UPD_DT",
         "PCPACP"."PP"."ACP_ID",
         "PCPACP"."PP"."ACP_NAME",
         "PCPACP"."PP"."ACP_TITLE",
         "PCPACP"."PP"."ACP_UPD_DT",
         "PCPACP"."PP"."BP_ID",
         "PCPACP"."PP"."BP_NAME",
         "PCPACP"."PP"."BP_TITLE",
         "PCPACP"."PP"."BP_DATE_TIME",
         "PCPACP"."PP"."BP_VISIT_ID",
         "PCPACP"."PP"."AP_ID",
         "PCPACP"."PP"."AP_NAME",
         "PCPACP"."PP"."AP_TITLE",
         "PCPACP"."PP"."AP_VISIT_ID",
         "PCPACP"."PP"."AP_DATE_TIME",
         "PCPACP"."PP"."LOCATION_ID",
         "PCPACP"."PP"."DEPT_CODE",
         "PCPACP"."PP"."LOCATION_NAME"
FROM "PCPACP"."PP"
WHERE ("PCPACP"."PP"."DEPT_CODE" = '000')
ORDER BY "PCPACP"."PP"."BP_ID" ASC
) a

What's the result?

Frans Bouma | Lead developer LLBLGen Pro

vikramka
User

Posts: 42
Joined: 08-Sep-2004

# Posted on: 20-Jan-2006 20:29:35

Walaa wrote:

What's the Oracle version number you are using?

Oracle 9.2.0 environment

vikramka
User

Posts: 42
Joined: 08-Sep-2004

# Posted on: 20-Jan-2006 21:08:09

Otis wrote:

I've formatted the second query a little.

The weird thing is: the order by is in the query from which rows are selected. So ROWNUM should be based on that order.

if you instead do:


SELECT a.*, ROWNUM r___
FROM (SELECT   "PCPACP"."PP"."PP_ID",
         "PCPACP"."PP"."PP_NAME",
         "PCPACP"."PP"."PP_ID2",
         "PCPACP"."PP"."PP_DOB",
         "PCPACP"."PP"."PCP_ID",
         "PCPACP"."PP"."PCP_NAME",
         "PCPACP"."PP"."PCP_TITLE",
         "PCPACP"."PP"."PCP_UPD_DT",
         "PCPACP"."PP"."ACP_ID",
         "PCPACP"."PP"."ACP_NAME",
         "PCPACP"."PP"."ACP_TITLE",
         "PCPACP"."PP"."ACP_UPD_DT",
         "PCPACP"."PP"."BP_ID",
         "PCPACP"."PP"."BP_NAME",
         "PCPACP"."PP"."BP_TITLE",
         "PCPACP"."PP"."BP_DATE_TIME",
         "PCPACP"."PP"."BP_VISIT_ID",
         "PCPACP"."PP"."AP_ID",
         "PCPACP"."PP"."AP_NAME",
         "PCPACP"."PP"."AP_TITLE",
         "PCPACP"."PP"."AP_VISIT_ID",
         "PCPACP"."PP"."AP_DATE_TIME",
         "PCPACP"."PP"."LOCATION_ID",
         "PCPACP"."PP"."DEPT_CODE",
         "PCPACP"."PP"."LOCATION_NAME"
FROM "PCPACP"."PP"
WHERE ("PCPACP"."PP"."DEPT_CODE" = '000')
ORDER BY "PCPACP"."PP"."BP_ID" ASC
) a

What's the result?

Works as it should, displaying rownum from 1 to count(*). This problem might be be an undocumented oracle feature

Otis
LLBLGen Pro Team

Posts: 39930
Joined: 17-Aug-2003

# Posted on: 21-Jan-2006 11:32:39

heh well, I hope you'll find what that 'feature' might be so this gets solved. I'm afraid I'm running out of options.

Frans Bouma | Lead developer LLBLGen Pro

vikramka
User

Posts: 42
Joined: 08-Sep-2004

# Posted on: 21-Jan-2006 17:33:27

thanks. maybe oracle needs to be patched or something. I had a talk with the dba, he was stumped too.