Announcement

Collapse
No announcement yet.
X
Collapse
  • Page of 1
  • Filter
  • Time
Clear All
new posts
Previous template Next
    #1

    Difference in criteria construction between ListGrid and DataSource

    Isomorphic,

    I have a list grid which fetches data from different tables with joins. I have a situation where the same table need to be joined multiple times with different aliases.

    So for list grid we write fields with Aliases and define them as customSQL and then use 'customCriteriaFields' parameter for the OperationBinding to generate the appropriate whereClause.

    Example:
    Code:
    SELECT DB.ORIGIN, DB.DESTINATION, A1.COUNTRY ORIGIN_COUNTRY, A2.COUNTRY DESTINATION_COUNTRY, SUM(BOOKINGS) FROM DATATABLE DB, AIRPORT A1, AIRPORT A2 WHERE DB.ORIGIN = A1.AIRPORT AND DB.DEST=A2.AIRPORT AND DB.ORIG ='LHR' AND A2.COUNTRY='GB' 
GROUP BY DB.ORIGIN, DB.DESTINATION, A1.COUNTRY , A2.COUNTRY
    In the above sample we define, A2.COUNTRY as custom field in the ds.xml file and include it in 'customCriteriaFields' for the operationBinding. We then put it in criteria.

    It works fine with the query constructed on a ListGrid.

    We generate chart by doing a fetch on the DataSource, which ignores the custom field in the constructed whereClause.

    I see the following in the log:

    Code:
    === 2012-02-15 16:15:15,363 [0-61] WARN  DSRequest - In criteria, definition 'A2.COUNTRY' refers to a related DataSource ('A2') that does not exist.  Ignoring this criteria entry.
    1) Any reason why the behavior is different in both the cases and how to resolve it or is this a bug.
    2) Is there any shortcoming in this approach of joining tables. I have been hearing a lot on 'includeFrom' feature in the forums. Can this be used when you have to join same table multiple times like in the case we have above. If yes, which would be the better approach, the one we have or the includeFrom approach.


    Version: SC_SNAPSHOT-2012-01-17_v8.2p/Enterprise Deployment 2012-01-17

    Thanks
    Last edited by harsha.galla; 15 Feb 2012, 03:28.
    Tags: None
    #2
    Server-side execution can only differ based on differences in the request, so, can you guess what we need to see? The complete server logs for the request.

    Comment

      #3
      Isomorphic,

      Please find the complete log for the request.

      Code:
      === 2012-02-16 02:37:13,176 [l0-4] INFO  RequestContext - URL: '/MarketIntelligence/sc/IDACall', User-Agent: 'Mozilla/5.0 (Windows NT 6.1; WOW64; rv:9.0.1) Gecko/20100101 Firefox/9.0.1': Moz (Gecko) with Accept-Encoding header
=== 2012-02-16 02:37:13,360 [l0-4] DEBUG XML - Parsed XML from (in memory stream): 5ms
=== 2012-02-16 02:37:13,367 [l0-4] DEBUG XML - Parsed XML from C:\Users\sg0209257\.IntelliJIdea11\system\gwt\mi-implementation.mi-implementation525b4c71\mi-implementation.97be1ef4\run\www\MarketIntelligence\sc\system\schema\List.ds.xml: 4ms
=== 2012-02-16 02:37:13,378 [l0-4] DEBUG RPCManager - Processing 1 requests.
=== 2012-02-16 02:37:13,407 [l0-4] DEBUG RPCManager - Request #1 (DSRequest) payload: {
    criteria:{
        ORIG:[
            "BOM"
        ],
        "A2.COUNTRY_CODE":[
            "IN"
        ],
        fromDate:200901,
        toDate:200912,
        ORIGIN_LEVEL:"Airport",
        DESTINATION_LEVEL:"Country",
        AIRLINE_TYPE:"Marketing",
        REPORT_TYPE:"AGGREGATE"
    },
    operationConfig:{
        dataSource:"graphsodmidt",
        operationType:"fetch"
    },
    appID:"builtinApplication",
    operation:"summaryDirectionalAggregateGraph",
    oldValues:{
        ORIG:[
            "BOM"
        ],
        "A2.COUNTRY_CODE":[
            "IN"
        ],
        fromDate:200901,
        toDate:200912,
        ORIGIN_LEVEL:"Airport",
        DESTINATION_LEVEL:"Country",
        AIRLINE_TYPE:"Marketing",
        REPORT_TYPE:"AGGREGATE"
    }
}
=== 2012-02-16 02:37:13,429 [l0-4] INFO  IDACall - Performing 1 operation(s)
=== 2012-02-16 02:37:13,445 [l0-4] WARN  DSRequest - In criteria, definition 'A2.COUNTRY_CODE' refers to a related DataSource ('A2') that does not exist.  Ignoring this criteria entry.
=== 2012-02-16 02:37:13,465 [l0-4] DEBUG AppBase - [builtinApplication.summaryDirectionalAggregateGraph] No userTypes defined, allowing anyone access to all operations for this application
=== 2012-02-16 02:37:13,465 [l0-4] DEBUG AppBase - [builtinApplication.summaryDirectionalAggregateGraph] No public zero-argument method named '_summaryDirectionalAggregateGraph' found, performing generic datasource operation
=== 2012-02-16 02:37:13,466 [l0-4] INFO  SQLDataSource - [builtinApplication.summaryDirectionalAggregateGraph] Performing fetch operation with
	criteria: {ORIG:["BOM"],fromDate:200901,toDate:200912,ORIGIN_LEVEL:"Airport",DESTINATION_LEVEL:"Country",AIRLINE_TYPE:"Marketing",REPORT_TYPE:"AGGREGATE"}	values: {ORIG:["BOM"],fromDate:200901,toDate:200912,ORIGIN_LEVEL:"Airport",DESTINATION_LEVEL:"Country",AIRLINE_TYPE:"Marketing",REPORT_TYPE:"AGGREGATE"}
=== 2012-02-16 02:37:13,540 [l0-4] INFO  SQLDataSource - [builtinApplication.summaryDirectionalAggregateGraph] derived query: SELECT 
                #if($criteria.REPORT_TYPE == "TREND")
                    DATES
                    #if ($criteria.CLASS_TYPE)
                        , CLASS
                    #elseif ($criteria.AIRLINE_TYPE)
                        , AIRLINE
                    #else
                        , MARKET
                    #end
                #else
                    #if($criteria.CLASS_TYPE && $criteria.AIRLINE_TYPE)
                        CLASS, AIRLINE
                    #elseif(!$criteria.CLASS_TYPE && !$criteria.AIRLINE_TYPE)
                        MARKET
                    #elseif($criteria.CLASS_TYPE && !$criteria.AIRLINE_TYPE)
                        CLASS, MARKET
                    #elseif(!$criteria.CLASS_TYPE && $criteria.AIRLINE_TYPE)
                        AIRLINE, MARKET
                    #end
                #end
                , BOOKINGS
                FROM ( SELECT
                #if($criteria.REPORT_TYPE == "TREND")
                    DATES
                    #if ($criteria.CLASS_TYPE)
                        , CLASS
                    #elseif ($criteria.AIRLINE_TYPE)
                        , AIRLINE
                    #else
                        , MARKET
                    #end
                #else
                    #if($criteria.CLASS_TYPE && $criteria.AIRLINE_TYPE)
                            CLASS, AIRLINE
                    #elseif(!$criteria.CLASS_TYPE && !$criteria.AIRLINE_TYPE)
                            MARKET
                    #elseif($criteria.CLASS_TYPE && !$criteria.AIRLINE_TYPE)
                            CLASS, MARKET
                    #elseif(!$criteria.CLASS_TYPE && $criteria.AIRLINE_TYPE)
                            AIRLINE, MARKET
                    #end
                #end
                #if($criteria.REPORT_TYPE == "AGGREGATE")
                    , DENSE_RANK() OVER (ORDER BY TOTALBOOKINGS DESC ) AS RANK1
                #end
                , BOOKINGS
                FROM ( SELECT
                #if($criteria.REPORT_TYPE == "TREND")
                        MB.DEPARTURE_DATES DATES
                    #if ($criteria.CLASS_TYPE)
                        #if ($criteria.CLASS_TYPE == "Cabin Class")
                            , BCD.YIELD_CLASS_CODE CLASS
                        #else
                            , MB.BOOK_CLASS CLASS
                        #end
                    #elseif ($criteria.AIRLINE_TYPE)
                        #if ($criteria.AIRLINE_TYPE == "Marketing")
                            , MB.DOMAIRL AIRLINE
                        #else
                            , MB.OPAIRL AIRLINE
                        #end
                    #else
                        #if($criteria.ORIGIN_LEVEL == "Airport" )
                            , MB.ORIG
                        #elseif($criteria.ORIGIN_LEVEL == "City")
                            , A1.CITY_CODE
                        #elseif($criteria.ORIGIN_LEVEL == "Country")
                            , A1.COUNTRY_NAME
                        #elseif($criteria.ORIGIN_LEVEL == "Region")
                            , A1.WORLD_AREA_NAME
                        #end
                        || '-' ||
                        #if($criteria.DESTINATION_LEVEL == "Airport" )
                            MB.DEST
                        #elseif($criteria.DESTINATION_LEVEL == "City")
                            A2.CITY_CODE
                        #elseif($criteria.DESTINATION_LEVEL == "Country")
                            A2.COUNTRY_NAME
                        #elseif($criteria.DESTINATION_LEVEL == "Region")
                            A2.WORLD_AREA_NAME
                        #end
                        MARKET
                    #end
                #else
                    #if($criteria.CLASS_TYPE && $criteria.AIRLINE_TYPE)
                        #if ($criteria.CLASS_TYPE == "Cabin Class")
                            BCD.YIELD_CLASS_CODE CLASS
                        #else
                            MB.BOOK_CLASS CLASS
                        #end
                        #if ($criteria.AIRLINE_TYPE == "Marketing")
                            , MB.DOMAIRL AIRLINE
                        #else
                            , MB.OPAIRL AIRLINE
                        #end
                    #elseif(!$criteria.CLASS_TYPE && !$criteria.AIRLINE_TYPE)
                        #if($criteria.ORIGIN_LEVEL == "Airport" )
                            MB.ORIG
                        #elseif($criteria.ORIGIN_LEVEL == "City")
                            A1.CITY_CODE
                        #elseif($criteria.ORIGIN_LEVEL == "Country")
                            A1.COUNTRY_NAME
                        #elseif($criteria.ORIGIN_LEVEL == "Region")
                            A1.WORLD_AREA_NAME
                        #end
                        || '-' ||
                        #if($criteria.DESTINATION_LEVEL == "Airport" )
                            MB.DEST
                        #elseif($criteria.DESTINATION_LEVEL == "City")
                            A2.CITY_CODE
                        #elseif($criteria.DESTINATION_LEVEL == "Country")
                            A2.COUNTRY_NAME
                        #elseif($criteria.DESTINATION_LEVEL == "Region")
                            A2.WORLD_AREA_NAME
                        #end
                        MARKET
                    #elseif($criteria.CLASS_TYPE && !$criteria.AIRLINE_TYPE)
                        #if ($criteria.CLASS_TYPE == "Cabin Class")
                            BCD.YIELD_CLASS_CODE CLASS
                        #else
                            MB.BOOK_CLASS CLASS
                        #end
                        #if($criteria.ORIGIN_LEVEL == "Airport" )
                            , MB.ORIG
                        #elseif($criteria.ORIGIN_LEVEL == "City")
                            , A1.CITY_CODE
                        #elseif($criteria.ORIGIN_LEVEL == "Country")
                            , A1.COUNTRY_NAME
                        #elseif($criteria.ORIGIN_LEVEL == "Region")
                            , A1.WORLD_AREA_NAME
                        #end
                        || '-' ||
                        #if($criteria.DESTINATION_LEVEL == "Airport" )
                            MB.DEST
                        #elseif($criteria.DESTINATION_LEVEL == "City")
                            A2.CITY_CODE
                        #elseif($criteria.DESTINATION_LEVEL == "Country")
                            A2.COUNTRY_NAME
                        #elseif($criteria.DESTINATION_LEVEL == "Region")
                            A2.WORLD_AREA_NAME
                        #end
                        MARKET
                    #elseif(!$criteria.CLASS_TYPE && $criteria.AIRLINE_TYPE)
                        #if ($criteria.AIRLINE_TYPE == "Marketing")
                            MB.DOMAIRL AIRLINE
                        #else
                            MB.OPAIRL AIRLINE
                        #end
                        #if($criteria.ORIGIN_LEVEL == "Airport" )
                            , MB.ORIG
                        #elseif($criteria.ORIGIN_LEVEL == "City")
                            , A1.CITY_CODE
                        #elseif($criteria.ORIGIN_LEVEL == "Country")
                            , A1.COUNTRY_NAME
                        #elseif($criteria.ORIGIN_LEVEL == "Region")
                            , A1.WORLD_AREA_NAME
                        #end
                        || '-' ||
                        #if($criteria.DESTINATION_LEVEL == "Airport" )
                            MB.DEST
                        #elseif($criteria.DESTINATION_LEVEL == "City")
                            A2.CITY_CODE
                        #elseif($criteria.DESTINATION_LEVEL == "Country")
                            A2.COUNTRY_NAME
                        #elseif($criteria.DESTINATION_LEVEL == "Region")
                            A2.WORLD_AREA_NAME
                        #end
                        MARKET
                    #end
                #end
                , SUM(MB.UPAX) BOOKINGS,  RANK() OVER (PARTITION BY(
                #if($criteria.REPORT_TYPE == "TREND")
                    MB.DEPARTURE_DATES
                #else
                    #if($criteria.CLASS_TYPE && $criteria.AIRLINE_TYPE)
                        MB.DOMAIRL
                    #else
                        #if($criteria.ORIGIN_LEVEL == "Airport" )
                            MB.ORIG
                        #elseif($criteria.ORIGIN_LEVEL == "City")
                            A1.CITY_CODE
                        #elseif($criteria.ORIGIN_LEVEL == "Country")
                            A1.COUNTRY_NAME
                        #elseif($criteria.ORIGIN_LEVEL == "Region")
                            A1.WORLD_AREA_NAME
                        #end
                        || '-' ||
                        #if($criteria.DESTINATION_LEVEL == "Airport" )
                            MB.DEST
                        #elseif($criteria.DESTINATION_LEVEL == "City")
                            A2.CITY_CODE
                        #elseif($criteria.DESTINATION_LEVEL == "Country")
                            A2.COUNTRY_NAME
                        #elseif($criteria.DESTINATION_LEVEL == "Region")
                            A2.WORLD_AREA_NAME
                        #end
                    #end
                #end

                ) ORDER BY SUM(MB.UPAX) DESC) AS RANK
                #if($criteria.REPORT_TYPE == "AGGREGATE")
                    , SUM(SUM(MB.UPAX)) OVER (PARTITION BY
                    #if($criteria.CLASS_TYPE && $criteria.AIRLINE_TYPE)
                        MB.DOMAIRL
                    #else
                        #if($criteria.ORIGIN_LEVEL == "Airport" )
                            MB.ORIG
                        #elseif($criteria.ORIGIN_LEVEL == "City")
                            A1.CITY_CODE
                        #elseif($criteria.ORIGIN_LEVEL == "Country")
                            A1.COUNTRY_NAME
                        #elseif($criteria.ORIGIN_LEVEL == "Region")
                            A1.WORLD_AREA_NAME
                        #end
                            || '-' ||
                        #if($criteria.DESTINATION_LEVEL == "Airport" )
                            MB.DEST
                        #elseif($criteria.DESTINATION_LEVEL == "City")
                            A2.CITY_CODE
                        #elseif($criteria.DESTINATION_LEVEL == "Country")
                            A2.COUNTRY_NAME
                        #elseif($criteria.DESTINATION_LEVEL == "Region")
                            A2.WORLD_AREA_NAME
                        #end
                    #end
                    ) AS TOTALBOOKINGS
                #end

             FROM 
                #if($criteria.CLASS_TYPE == "Cabin Class" || $criteria.CLASS_TYPE == "Booking Class")
                MONTHLY_OD_DETAIL_NA_CLASS MB
                #elseif($criteria.CONNECT1  || $criteria.CONNECT2  || $criteria.CONNECT3 || $criteria.CONNECTAIRLINE1   ||
                    $criteria.CONNECTAIRLINE2 || $criteria.CONNECTAIRLINE3 ||  $criteria.CONNECTAIRLINE4   || $criteria.VIA)
                MONTHLY_BOOKINGS_OD_DETAIL_NA MB
                #else
                MONTHLY_BOOKINGS_OD_SUMMARY_NA MB
                #end
                #if($criteria.CLASS_TYPE == "Cabin Class")
                , CLIENT_BOOKING_CLASS_DIM BCD
                #end
                , AIRPORT_DIM A1, AIRPORT_DIM A2

             WHERE 
                MB.ORIG = A1.AIRPORT_IATA_CODE AND MB.DEST = A2.AIRPORT_IATA_CODE
                AND MB.DEPARTURE_DATES BETWEEN $criteria.fromDate AND $criteria.toDate
                #if($criteria.CLASS_TYPE == "Cabin Class")
                    AND MB.BOOK_CLASS = BCD.BOOKING_CLA_CLASS_CODE AND MB.DOMAIRL = BCD.BOOKING_CLA_CARRIER_CODE AND BCD.CLIENT_ID=999
                #end
                #if($criteria.DOMINTERNATIONAL == "DOMESTIC")
                AND A1.COUNTRY_CODE = A2.COUNTRY_CODE
                #elseif($criteria.DOMINTERNATIONAL ==  "INTERNATIONAL")
                AND A1.COUNTRY_CODE <> A2.COUNTRY_CODE
                #end
                AND $defaultWhereClause
             GROUP BY 
               #if($criteria.REPORT_TYPE == "TREND")
                    MB.DEPARTURE_DATES
                    #if ($criteria.CLASS_TYPE)
                        #if ($criteria.CLASS_TYPE == "Cabin Class")
                            , BCD.YIELD_CLASS_CODE
                        #else
                            , MB.BOOK_CLASS
                        #end
                    #elseif ($criteria.AIRLINE_TYPE)
                        #if ($criteria.AIRLINE_TYPE == "Marketing")
                            , MB.DOMAIRL
                        #else
                            , MB.OPAIRL
                        #end
                    #else
                        #if($criteria.ORIGIN_LEVEL == "Airport" )
                            , MB.ORIG
                        #elseif($criteria.ORIGIN_LEVEL == "City")
                            , A1.CITY_CODE
                        #elseif($criteria.ORIGIN_LEVEL == "Country")
                            , A1.COUNTRY_NAME
                        #elseif($criteria.ORIGIN_LEVEL == "Region")
                            , A1.WORLD_AREA_NAME
                        #end
                        #if($criteria.DESTINATION_LEVEL == "Airport" )
                            , MB.DEST
                        #elseif($criteria.DESTINATION_LEVEL == "City")
                            , A2.CITY_CODE
                        #elseif($criteria.DESTINATION_LEVEL == "Country")
                            , A2.COUNTRY_NAME
                        #elseif($criteria.DESTINATION_LEVEL == "Region")
                            , A2.WORLD_AREA_NAME
                        #end
                    #end
                #else
                    #if($criteria.CLASS_TYPE && $criteria.AIRLINE_TYPE)
                        #if ($criteria.CLASS_TYPE == "Cabin Class")
                            BCD.YIELD_CLASS_CODE
                        #else
                            MB.BOOK_CLASS
                        #end
                        #if ($criteria.AIRLINE_TYPE == "Marketing")
                            , MB.DOMAIRL
                        #else
                            , MB.OPAIRL
                        #end
                    #elseif(!$criteria.CLASS_TYPE && !$criteria.AIRLINE_TYPE)
                        #if($criteria.ORIGIN_LEVEL == "Airport" )
                            MB.ORIG
                        #elseif($criteria.ORIGIN_LEVEL == "City")
                            A1.CITY_CODE
                        #elseif($criteria.ORIGIN_LEVEL == "Country")
                            A1.COUNTRY_NAME
                        #elseif($criteria.ORIGIN_LEVEL == "Region")
                            A1.WORLD_AREA_NAME
                        #end
                        #if($criteria.DESTINATION_LEVEL == "Airport" )
                            , MB.DEST
                        #elseif($criteria.DESTINATION_LEVEL == "City")
                            , A2.CITY_CODE
                        #elseif($criteria.DESTINATION_LEVEL == "Country")
                            , A2.COUNTRY_NAME
                        #elseif($criteria.DESTINATION_LEVEL == "Region")
                            , A2.WORLD_AREA_NAME
                        #end
                    #elseif($criteria.CLASS_TYPE && !$criteria.AIRLINE_TYPE)
                        #if ($criteria.CLASS_TYPE == "Cabin Class")
                            BCD.YIELD_CLASS_CODE
                        #else
                            MB.BOOK_CLASS CLASS
                        #end
                        #if($criteria.ORIGIN_LEVEL == "Airport" )
                            , MB.ORIG
                        #elseif($criteria.ORIGIN_LEVEL == "City")
                            , A1.CITY_CODE
                        #elseif($criteria.ORIGIN_LEVEL == "Country")
                            , A1.COUNTRY_NAME
                        #elseif($criteria.ORIGIN_LEVEL == "Region")
                            , A1.WORLD_AREA_NAME
                        #end
                        #if($criteria.DESTINATION_LEVEL == "Airport" )
                            , MB.DEST
                        #elseif($criteria.DESTINATION_LEVEL == "City")
                            , A2.CITY_CODE
                        #elseif($criteria.DESTINATION_LEVEL == "Country")
                            , A2.COUNTRY_NAME
                        #elseif($criteria.DESTINATION_LEVEL == "Region")
                            , A2.WORLD_AREA_NAME
                        #end
                    #elseif(!$criteria.CLASS_TYPE && $criteria.AIRLINE_TYPE)
                        #if ($criteria.AIRLINE_TYPE == "Marketing")
                            MB.DOMAIRL
                        #else
                            MB.OPAIRL
                        #end
                        #if($criteria.ORIGIN_LEVEL == "Airport" )
                            , MB.ORIG
                        #elseif($criteria.ORIGIN_LEVEL == "City")
                            , A1.CITY_CODE
                        #elseif($criteria.ORIGIN_LEVEL == "Country")
                            , A1.COUNTRY_NAME
                        #elseif($criteria.ORIGIN_LEVEL == "Region")
                            , A1.WORLD_AREA_NAME
                        #end
                        #if($criteria.DESTINATION_LEVEL == "Airport" )
                            , MB.DEST
                        #elseif($criteria.DESTINATION_LEVEL == "City")
                            , A2.CITY_CODE
                        #elseif($criteria.DESTINATION_LEVEL == "Country")
                            , A2.COUNTRY_NAME
                        #elseif($criteria.DESTINATION_LEVEL == "Region")
                            , A2.WORLD_AREA_NAME
                        #end
                    #end
                #end
                 ) WHERE RANK <=10    )
                #if($criteria.REPORT_TYPE == "AGGREGATE")
                    WHERE RANK1 <=15
                #end
             ORDER BY 
                #if($criteria.REPORT_TYPE == "TREND")
                    DATES
                #else
                    BOOKINGS DESC
                #end
            
=== 2012-02-16 02:37:13,867 [l0-4] INFO  SQLDataSource - [builtinApplication.summaryDirectionalAggregateGraph] Executing SQL query on 'Oracle': SELECT 
                                                            AIRLINE, MARKET
                                                    , BOOKINGS
                FROM ( SELECT
                                                                AIRLINE, MARKET
                                                                        , DENSE_RANK() OVER (ORDER BY TOTALBOOKINGS DESC ) AS RANK1
                                , BOOKINGS
                FROM ( SELECT
                                                                                        MB.DOMAIRL AIRLINE
                                                                            , MB.ORIG
                                                || '-' ||
                                                    A2.COUNTRY_NAME
                                                MARKET
                                                    , SUM(MB.UPAX) BOOKINGS,  RANK() OVER (PARTITION BY(
                                                                                        MB.ORIG
                                                || '-' ||
                                                    A2.COUNTRY_NAME
                                                            
                ) ORDER BY SUM(MB.UPAX) DESC) AS RANK
                                    , SUM(SUM(MB.UPAX)) OVER (PARTITION BY
                                                                        MB.ORIG
                                                    || '-' ||
                                                    A2.COUNTRY_NAME
                                                                ) AS TOTALBOOKINGS
                
             FROM 
                                MONTHLY_BOOKINGS_OD_SUMMARY_NA MB
                                                , AIRPORT_DIM A1, AIRPORT_DIM A2

             WHERE 
                MB.ORIG = A1.AIRPORT_IATA_CODE AND MB.DEST = A2.AIRPORT_IATA_CODE
                AND MB.DEPARTURE_DATES BETWEEN 200901 AND 200912
                                                AND (((ORIG='BOM')))
             GROUP BY 
                                                                                       MB.DOMAIRL
                                                                            , MB.ORIG
                                                                            , A2.COUNTRY_NAME
                                                                             ) WHERE RANK <=10    )
                                    WHERE RANK1 <=15
                             ORDER BY 
                                    BOOKINGS DESC
                            
=== 2012-02-16 02:37:14,301 [l0-4] DEBUG PoolableSQLConnectionFactory - [builtinApplication.summaryDirectionalAggregateGraph] Initializing SQL config for 'Oracle' from system config - using DataSource:  oracle.jdbc.pool.OracleDataSource
=== 2012-02-16 02:37:18,268 [l0-4] DEBUG PoolableSQLConnectionFactory - [builtinApplication.summaryDirectionalAggregateGraph] Returning pooled Connection
=== 2012-02-16 02:37:19,188 [l0-4] INFO  SQLDriver - [builtinApplication.summaryDirectionalAggregateGraph] Executing SQL query on 'Oracle': SELECT 
                                                            AIRLINE, MARKET
                                                    , BOOKINGS
                FROM ( SELECT
                                                                AIRLINE, MARKET
                                                                        , DENSE_RANK() OVER (ORDER BY TOTALBOOKINGS DESC ) AS RANK1
                                , BOOKINGS
                FROM ( SELECT
                                                                                        MB.DOMAIRL AIRLINE
                                                                            , MB.ORIG
                                                || '-' ||
                                                    A2.COUNTRY_NAME
                                                MARKET
                                                    , SUM(MB.UPAX) BOOKINGS,  RANK() OVER (PARTITION BY(
                                                                                        MB.ORIG
                                                || '-' ||
                                                    A2.COUNTRY_NAME
                                                            
                ) ORDER BY SUM(MB.UPAX) DESC) AS RANK
                                    , SUM(SUM(MB.UPAX)) OVER (PARTITION BY
                                                                        MB.ORIG
                                                    || '-' ||
                                                    A2.COUNTRY_NAME
                                                                ) AS TOTALBOOKINGS
                
             FROM 
                                MONTHLY_BOOKINGS_OD_SUMMARY_NA MB
                                                , AIRPORT_DIM A1, AIRPORT_DIM A2

             WHERE 
                MB.ORIG = A1.AIRPORT_IATA_CODE AND MB.DEST = A2.AIRPORT_IATA_CODE
                AND MB.DEPARTURE_DATES BETWEEN 200901 AND 200912
                                                AND (((ORIG='BOM')))
             GROUP BY 
                                                                                       MB.DOMAIRL
                                                                            , MB.ORIG
                                                                            , A2.COUNTRY_NAME
                                                                             ) WHERE RANK <=10    )
                                    WHERE RANK1 <=15
                             ORDER BY 
                                    BOOKINGS DESC
                            
=== 2012-02-16 02:37:27,652 [l0-4] INFO  DSResponse - [builtinApplication.summaryDirectionalAggregateGraph] DSResponse: List with 153 items
=== 2012-02-16 02:37:27,979 [l0-4] DEBUG RPCManager - Content type for RPC transaction: text/plain; charset=UTF-8
=== 2012-02-16 02:37:27,984 [l0-4] DEBUG RPCManager - non-DMI response, dropExtraFields: false

      Comment

        #4
        That is one hideous SQL template and we would advise moving more of the logic into Java. Remember you can compute whatever you want in Java and make it available to the SQL template via dsRequest.addToTemplateContext(). So there is no need to do horrific Velocity if..elseif..end stuff when you could just be using lookup tables.

        Whatever is wrong here is likely some kind of logic bug in your code, because if you are submitting the same criteria to the same DataSource with the same operationId, the same SQL query will result.

        Please take a look at the logs for the working and non-working requests. If you believe that the requests are identical and yet our server code is behaving differently, let us know.

        Comment

          #5
          Isomorphic,

          1) I'm comparing the defaultWhereClause generated by calling a fetch on ListGrid v/s calling a fetch on DataSource directly. So datasource files, operationId are different for both of the scenarios. We divided them into different files for the sake of convenience, but the underneath logic is the same for both like I mentioned in my first post.


          2) Why is A2.COUNTRY_CODE IGNORED IN THE defaultWhereClause when I have it in the customCriteriaFields. It is present in the Criteria object also, if you look at the log I posted. I had used the same approach for ListGrid and it had worked.


          Ya, need to move if/else logic to JAVA :).

          Thanks.

          Comment

            #6
            About A2.COUNTRY_CODE, field names must be valid identifiers (no dots).

            Comment

              #7
              I have the following doubts with respect to your previous response,

              1) Why did this work for ListGrid? Surprising :) !!!

              2) If these identifiers are invalid what is the approach I need to take in my case where I'm joining same table with mulitple aliases to construct the defaultwhereClause appropriately.

              Thanks.

              Comment

                #8
                1) It doesn't, although you may not immediately hit the various things that don't work if you only test lightly.

                2) If you need to provide a tableName in the SQL name for some field, DataSourceField.tableName does this.

                Comment

                  #9
                  Isomorphic,

                  In answer 2) , how do I distinguish between the two columns, when I push in criteria values.

                  ex: two tables Airport A1, Airport A2

                  Since both tables have same column names how do I distinguish between columns belonging to these tables. So If I want to push into criteria to create a where clause on A2, how do I do it.

                  Earlier I started off with this approach, but because of the above question I took the approach of custom fields with '.' in it.

                  Thanks.
                  Last edited by harsha.galla; 15 Feb 2012, 22:11.

                  Comment

                    #10
                    Isomorphic,

                    Can you elaborate this:

                    So there is no need to do horrific Velocity if..elseif..end stuff when you could just be using lookup tables.

                    I have so many if-else because the select clause is extremely dynamic in nature. Which results in extremely dynamic groupby clause too :)
                    I would like to know what you meant by 'lookup tables' here. Other than just pushing this if..else logic to java is there anything better I can do.

                    Thanks.

                    Comment

                      #11
                      Create two distinct DataSourceFields with different tableNames and use DataSourceField.nativeName to set them both to use the same column name. Specifically, set nativeName to just the column name, not the table name plus column name.

                      Comment

                        #12
                        Isomorphic,

                        This is exactly what i wanted. Some how I missed this method when I read the API's. :)

                        Thanks.

                        Comment

                        Previous template Next
                        Working...
                        X