7 Replies Latest reply: Jan 25, 2013 3:29 AM by chris227 RSS

    Reg Sql statement

    User444780-OC
      Hi Gurus,

      Here we are having problem with the below querey. Which is working in 11.2.0.3. But not working in 10.2.0.4

      Out put in 11.2.0.3
      SQL> Select Distinct Bbesd.Order_Id Order_Id, Bbesd.Acct_Id Acct_Id,
        2  Bbesd.Element_Id Element_Id,
        3  Vsd.Activity,
        4  --Bbe_Service_Entries.Bbe_Services Bbe_Service_Entries ,
        5  Decode('52' , '52','52','') Product_Category,
        6  Sd.Location_Indicator Location_Indicator,Sd.Service_State Service_State,
        7  SUBSTR(SD.ORDER_TYPE,1,1) ORDER_TYPE,VSD.SERVICE_TYPE
        8  FROM SSP_FLOW.BBE_SERVICE_DETAILS BBESD, SSP_FLOW.SSP_SERVICE_DETAILS SD,SSP_FLOW.VAS_SERVICE_D
      ETAILS VSD,
        9  (SELECT BBE_INNER.BBE_ORDER_ID, LISTAGG(BBE_INNER.BBE_SERVICE_ENTRIES, '') WITHIN GROUP
       10  (ORDER BY BBE_INNER.BBE_SERVICE_ENTRIES ) BBE_SERVICES
       11  FROM (SELECT '<Service object="' || DECODE(PRODUCT_CATEGORY, 53, 'VASBundle', 54, 'VASItem', 55
      , 'VASPromo')
       12  || '" BundleType="' || BUNDLE_TYPE
       13  || '" elementID="' || VAS_ELEMENT_ID
       14  || '" RequestType="' || Service_Type
       15  || '" provisioningTrigger="' || PROVISION_TRIGGER_FLAG
       16  || '" Level="' || Service_Level
       17  || '" billingTrigger="' || BILLING_TRIGGER_FLAG
       18  || '" fulfilmentId= "' || FULFILLMENT_ID
       19  || '" contentVendorId="' || CONTENT_VENDOR_ID
       20  || '"/>' As Bbe_Service_Entries, Order_Id As Bbe_Order_Id
       21  FROM SSP_FLOW.VAS_SERVICE_DETAILS WHERE /*[getOrderId]*/ ORDER_ID = ''
       22  and SERVICE_LEVEL=2) BBE_INNER
       23  Group By Bbe_Inner.Bbe_Order_Id) Bbe_Service_Entries
       24  WHERE /*[getOrderId]*/ BBESD.ORDER_ID = ''
       25  AND BBESD.ORDER_ID = BBE_SERVICE_ENTRIES.BBE_ORDER_ID
       26  And Sd.Order_Id = Bbe_Service_Entries.Bbe_Order_Id AND VSD.ORDER_ID = BBE_SERVICE_ENTRIES.BBE_O
      RDER_ID 
       27  ;
      
      no rows selected
      OUT PUT in 10.2.0.4
      SQL> Select Distinct Bbesd.Order_Id Order_Id, Bbesd.Acct_Id Acct_Id,
        2  Bbesd.Element_Id Element_Id,
        3  Vsd.Activity,
        4  --Bbe_Service_Entries.Bbe_Services Bbe_Service_Entries ,
        5  Decode('52' , '52','52','') Product_Category,
        6  Sd.Location_Indicator Location_Indicator,Sd.Service_State Service_State,
        7  SUBSTR(SD.ORDER_TYPE,1,1) ORDER_TYPE,VSD.SERVICE_TYPE
        8  FROM SSP_FLOW.BBE_SERVICE_DETAILS BBESD, SSP_FLOW.SSP_SERVICE_DETAILS SD,SSP_FLOW.VAS_SERVICE_D
      ETAILS VSD,
        9  (SELECT BBE_INNER.BBE_ORDER_ID, LISTAGG(BBE_INNER.BBE_SERVICE_ENTRIES, '') WITHIN GROUP
       10  (ORDER BY BBE_INNER.BBE_SERVICE_ENTRIES ) BBE_SERVICES
       11  FROM (SELECT '<Service object="' || DECODE(PRODUCT_CATEGORY, 53, 'VASBundle', 54, 'VASItem', 55
      , 'VASPromo')
       12  || '" BundleType="' || BUNDLE_TYPE
       13  || '" elementID="' || VAS_ELEMENT_ID
       14  || '" RequestType="' || Service_Type
       15  || '" provisioningTrigger="' || PROVISION_TRIGGER_FLAG
       16  || '" Level="' || Service_Level
       17  || '" billingTrigger="' || BILLING_TRIGGER_FLAG
       18  || '" fulfilmentId= "' || FULFILLMENT_ID
       19  || '" contentVendorId="' || CONTENT_VENDOR_ID
       20  || '"/>' As Bbe_Service_Entries, Order_Id As Bbe_Order_Id
       21  FROM SSP_FLOW.VAS_SERVICE_DETAILS WHERE /*[getOrderId]*/ ORDER_ID = ''
       22  and SERVICE_LEVEL=2) BBE_INNER
       23  Group By Bbe_Inner.Bbe_Order_Id) Bbe_Service_Entries
       24  WHERE /*[getOrderId]*/ BBESD.ORDER_ID = ''
       25  AND BBESD.ORDER_ID = BBE_SERVICE_ENTRIES.BBE_ORDER_ID
       26  And Sd.Order_Id = Bbe_Service_Entries.Bbe_Order_Id AND VSD.ORDER_ID = BBE_SERVICE_ENTRIES.BBE_O
      RDER_ID 
       27  ;
      (SELECT BBE_INNER.BBE_ORDER_ID, LISTAGG(BBE_INNER.BBE_SERVICE_ENTRIES, '') WITHIN GROUP
                                                                                        *
      ERROR at line 9:
      ORA-00923: FROM keyword not found where expected
      
      
      SQL> 
      Can any one please help how to solve my problem.

      Thanks & Regards,
      Venkata Poorna Prasad.S