Ron
|
| Posted: 11/11/2003, 1:45 PM |
|
Background:
I am building a custom WHERE clause within a CCS Grid. Unfortunately, although the SQL works fine for the grid. It is playing havoc with the CountSQL. Here is an example of the final SQL.
SELECT USER_TRANSACTION, MANUFACTURE_DATE, RESOURCE, SUM(ACTUAL_USAGE) AS SUM_ACTUAL_USAGE, MIN(CURRENT_QUANTITY) AS MIN_CURRENT_QUANTITY FROM INGREDIENT_LOG WHERE USER_TRANSACTION IN ('AA','BC','BU','CH') GROUP BY MANUFACTURE_DATE, USER_TRANSACTION, RESOURCE ORDER BY MANUFACTURE_DATE
The WHERE and GROUP components are modified into the x.DataSource.Where variable depending on selections from previous CCS pages/selection forms. This is in fact one of the simpler queries. If I can get this one right, then the rest should be comparatively easy.
HERE'S THE PROBLEM:
The Record Count is showing up as '1'. As a result, the first page of the grid shows up as page 1 of 2. The second page shows up as page 2 of 3, etc. until the end of the road (which could be from 10 to hundreds of pages). I believe that the problem is that the SELECT statement prior to the WHERE clause is invalid without the GROUP BY clause. Given how CCS splits this up, I have not found a work around to get the Count function to work correctly.
It is interesting to show the RecordSet count on the page and see it diplay '1' with a whole page of records displaying in the grid.
Unfortunately, this is the SQL that I need to use for the client (I'm replacing a fat app). It is beginning to look like my only option will be to edit the ExecuteCount() function in classes.asp .... which I would rather not do.
I was hoping that minds greater than mine around here could give me some ideas.
Thanks in advance.
|
|
|
 |
RonB
|
| Posted: 11/11/2003, 2:01 PM |
|
You do not state wich database you are using. If it's a database that supports views you might consider creating a view with the above mentioned statement and use thatas a source for the grid. I think the count sql will work fine after that.
Ron(no pun intended it's my name as well
|
|
|
|
Ron
|
| Posted: 11/11/2003, 2:14 PM |
|
Ron (good name BTW),
I'm using DB2, so yes VIEW might be possible ... assuming that I had more control ... Without putting you to sleep with the sorted details, this is not an option open to me at this time. I'm working on some reports that had been estimated to take four months to develop as fat applications. This is my last major hurdle to be able to show what CodeChargeStudio can do (so far I have invested 12 days part-time on the project and almost have a beta of everything ready to go). This last little hickup might be my undoing <grin>. in short, for now I have to use the database as it is since it is being used to run 12 plant floors.
I did see the entry by Steve Kitchen from a few days ago, but I have not been able to use his suggestions to my benfit either. I am a little surprised that CCS does not split out the GROUP BY clause in the same way it does the WHERE and ORDER BY clauses. ... but that is another soap box.
Thanks for your idea ... I just can't use it here (if I succeed at my little demo ... that may be another story).
|
|
|
|
RonB
|
| Posted: 11/12/2003, 6:31 AM |
|
Did you use the query builder to recreate the query or did you get the query from the existing app and pasted it into the sql query box?
try and paste the query in the sql box in the query wizzard and set the parameters for the where clause and group by clause using the parameter window. Maybe that will help?
Ron
|
|
|
|
Ron
|
| Posted: 11/12/2003, 8:47 AM |
|
>Did you use the query builder to recreate the query or did you get
>the query from the existing app and pasted it into the sql query box?
The basic query is defined within the Query Builder so that all of the proper variables are in place for the Grid (I hate having to build them by hand). The WHERE claus is left blank since I build that by hand.
>try and paste the query in the sql box in the query wizzard and
>set the parameters for the where clause and group by clause using
>the parameter window. Maybe that will help?
I don't believe that this is where the issue is. The query and the grid data works fine. The problem is that the record count that CCS is determining from default is either picking up the count from the first grouping (this seems to be the thinking from similar posts in this forum), or the group by clause is causing a query error that is not exposing itself and causing my IngBinByDayRpt.DataSource.Recordset.RecordCount to be set to '1'.
I need to find some way of setting IngBinByDayRpt.DataSource.Recordset.RecordCount to the real value other than '1'. CCS seems to set a Count(*) against the default select statement ... which in most GROUP BY statements will fail if the GROUP BY clause is not included.
I am using a "Before Build Select - Custom Code" to define the WHERE clause. I have not put all of the logic in yet, since if I can't get this top work at this point, there is not point in creating the rest of the logic.
>===============included code===============================<
Function IngBinByDayRpt_DataSource_BeforeBuildSelect() 'IngBinByDayRpt_DataSource_BeforeBuildSelect @104-D28D69D0
'Custom Code @129-73254650
' -------------------------
' Write your own code here.
' -------------------------
Dim x
Dim LoopCount
Dim TempBin
Dim TempType
Dim TypeParse
LoopCount = 0
TempBin = request.QueryString("s_ING_BIN_NUMBER")
TempType = request.QueryString("s_TRANSACTION_TYPE")
Do While InStr(TempType,",") > 0
If LoopCount > 0 Then TypeParse = TypeParse & ","
LoopCount = LoopCount + 1
x = InStr(TempType,",")
TypeParse = TypeParse & "'" & Trim(Left(TempType,x-1) & "'")
TempType = Right(TempType,Len(TempType)-x)
Loop
If Len(TempType) > 0 Then TypeParse = TypeParse & ",'" & Trim(TempType & "'")
TempType = TypeParse
' INGREDIENT BIN NUMBER CRITERIA WILL BE BUILT HERE using TempBin
If IngBinByDayRpt.DataSource.Where <> Empty Then
IngBinByDayRpt.DataSource.Where = IngBinByDayRpt.DataSource.Where & " AND "
end if
IngBinByDayRpt.DataSource.Where = IngBinByDayRpt.DataSource.Where & " USER_TRANSACTION IN (" & TempType & ") "
IngBinByDayRpt.DataSource.Where = IngBinByDayRpt.DataSource.Where & " GROUP BY MANUFACTURE_DATE, USER_TRANSACTION, RESOURCE "
' -------------------------
'End Custom Code
End Function 'Close IngBinByDayRpt_DataSource_BeforeBuildSelect @104-54C34B28
|
|
|
|
RonB
|
| Posted: 11/13/2003, 12:25 AM |
|
I just thought that when keeping the where, orderby and group by in the query builder... the countSQL might work as it should. The problem could be that you are adding variables outside of the query builder structure wich might/or might not result in bits of the query being left out of the count construction.
Man I shoould be in politics
|
|
|
|
Ron
|
| Posted: 11/18/2003, 8:37 AM |
|
I was finally able to figure this out with the assistance of the Code Charge Support team (Thanks Peter!) I thought that I would share the simple but elegant solution here in case it will be of benefit to others.
Background:
Basically, CCS does not have a means to handle 'GROUP BY' as a unique portion of the SQL statement in the same way that it does by have a *.SQL, *.WHERE , and *.ORDER variable. In most cases this is not a big deal, you can just include a GROUP BY clause within the WHERE portion of the statement. In other cases this causes a huge problem because CCS is unable to figure out the proper number of records that are returned by a given SQL query. As you can imaging this plays havoc with the navigation section when it tries to figure out how many records per page, how many pages, etc.
Scenaio:
In the scenario that I was working with, I had one page with a form and several multi-select fields. The form would send this to a second page which had no display capabilities but only included logic to determine which secondary selection page would be displayed based on the results of the first page input. This would display a second page with a form containing various fields (based on what was selected in the first form). Based on the input from this second display page, the informaiton was sent to a second 'redirect' page which, based on the input from the second display page, would direct the user to the proper report page. Here is an outline of what I am talking about ...
Main Page (with multi-select fields)
|
First Redirect Page (user never sees ...
| only logic to choose secondary page)
|
Secondary Selection Page (based on input from Main Page)
|
Second Redirect Page (user never sees, selects proper report output page)
|
|
Report Page (proper page choosed by input parameters from first 2 pages)
Here's the rub. By the time that I get to the Report page, this could have been one of several dozen pages. I was having to build the SQL, Where and Order variables by hand. Within the Where claus I was having to place a GROUP BY section which was based on some of the inputs from the two selection pages.
When trying to display the report pages I would either get a value of '0', '1' or the count of the first GROUP BY group. For CCS CountSQL variable would constantly put the counting query together incorrectly because of the way that the GROUP BY functions where used.
SOLUTION:
What was needed was a means to get CCS to handle the entire SQL string as a whole ... and not deal with the WHERE and ORDER sections separately. Of course now that I know the answer it is one of those Homer Simpson "DOH!!!" moments.
What I had been doing was using the Before Build Select Custom Code section to build my custom variables for the SQL, Where and Order Clauses. I still did the clause builds, but right before leaving the Before Build Select, I changed how they were used by using the follow code structure.
' Suggested Code from Peter at CCSSupport
Element.DataSource.SQL = Element.DataSource.SQL & " WHERE " & Element.DataSource.Where
Element.DataSource.Where = ""
Element.DataSource.CountSQL = "SELECT Count(*) FROM(" & Element.DataSource.SQL & ") cnt"
' End Suggested Code Structure
In this case I left the Element.DataSource.Order values alone.
By moving the WHERE clause into the SQL variable for this scenario, the CountSQL works properly. Without the Where clause being NULL. The code in the classes.asp attempts to add teh WHERE clause to the CountSQL .... and things just get ugly real fast.
I realize that this was a little long for such a simple solution, but hopefully it will help someone else and the details above will help explain why this stupid little move is necessary.
Hopefully, with the next major release of CCS, the development team will look into a better way of handling the record counting issues as they relate to GROUP BY clauses.
|
|
|
|
|
