Link to home
Start Free TrialLog in
Avatar of EvesterR
EvesterR

asked on

SQL Command in Crystal Reports

I am trying to add this column back to the SQL code.  However I keep getting this error message:

I am trying to add this column back to the SQL code - dbo.OFP_NOT_ALLOCABLE(det.u2_id),

Msg 8155, Level 16, State 2, Line 1
No column was specified for column 2 of 'DETAIL'.

Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "det.u2_id" could not be bound.

Here is my code, any help would be greatly appreciated.
Thanks
SELECT DETAIL.OPERATOR,

       dbo.OFP_NOT_ALLOCABLE(det.u2_id),

       DETAIL.OPERATOR_NAME,

       DETAIL.ANALYST,

       DETAIL.SALE_ACCTG_PERIOD,

       SUM(DETAIL.VOLUME_CURR) VOLUME_CURR,  

       SUM(DETAIL.DOLLARS_CURR) DOLLARS_CURR,

       CASE WHEN SUM(DETAIL.VOLUME_CURR) = 0

            THEN 0

            ELSE ((SUM(DETAIL.DOLLARS_CURR))/(SUM(DETAIL.VOLUME_CURR))) END PRICE_PER_BARREL_CURR,

       SUM(DETAIL.VOLUME_LAST) VOLUME_LAST,  

       SUM(DETAIL.DOLLARS_LAST) DOLLARS_LAST,

       CASE WHEN SUM(DETAIL.VOLUME_LAST) = 0

            THEN 0

            ELSE ((SUM(DETAIL.DOLLARS_LAST))/(SUM(DETAIL.VOLUME_LAST))) END PRICE_PER_BARREL_LAST,

       SUM(DETAIL.VOLUME_NEXT) VOLUME_NEXT,  

       SUM(DETAIL.DOLLARS_NEXT) DOLLARS_NEXT,

       CASE WHEN SUM(DETAIL.VOLUME_NEXT) = 0

            THEN 0

            ELSE ((SUM(DETAIL.DOLLARS_NEXT))/(SUM(DETAIL.VOLUME_NEXT))) END PRICE_PER_BARREL_NEXT,

       '' VS_KS,

       '' VS_NYMEX,

       DETAIL.PROPERTY,

       DETAIL.PROPERTY_NAME,

       DETAIL.METHOD_TYPE,

       DETAIL.PRICE_BASIS,

       DETAIL.FORMULA,

       DETAIL.EFF_DATE,

       DETAIL.COUNTY,

       DETAIL.BONUS,

       DETAIL.MILEAGE,

       AVG(DETAIL.GRAVITY_CURR) GRAVITY_CURR,

       AVG(DETAIL.GRAVITY_LAST) GRAVITY_LAST,

       AVG(DETAIL.GRAVITY_NEXT) GRAVITY_NEXT

FROM

(SELECT DET.CONTRACTING_PARTY OPERATOR,

        dbo.OFP_NOT_ALLOCABLE(det.u2_id),

        NAME.NAME1 OPERATOR_NAME,

        PROP.ANALYST ANALYST,

        DET.SALE_ACCTG_PERIOD,

        DET.GROSS_VOLUME_VOL1 VOLUME_CURR,  

        DET.GROSS_VALUE       DOLLARS_CURR,

        0                     VOLUME_LAST,

        0                     DOLLARS_LAST,

        0                     VOLUME_NEXT,

        0                     DOLLARS_NEXT,

        DET.PROPERTY,

        PROP.NAME PROPERTY_NAME,

        PCE.PRICE_PARAMS_METHOD_TYPES METHOD_TYPE,

        PCE.PRICE_PARAMS_BASIS PRICE_BASIS,

        DET.PRICE_METHOD FORMULA,

        PCE.PRICE_PARAMS_EFF_DATES EFF_DATE,

        CTY.NAME COUNTY,

        DET.BONUS_PRICE_ADJ BONUS,

        MILES.INJ_PT_MILES MILEAGE,

        GRAVITY        GRAVITY_CURR,

        0              GRAVITY_LAST,

        0              GRAVITY_NEXT

   FROM OG_SALE_DET DET,

        NAME,

        OGP_PROP PROP,

        CTY, PRICE_CTRC_EFF_DT PCE,

        RUN_TICKET RT,

        OGP_PROP_INJ INJ,

        OGP_PROP_MILES MILES

  WHERE DET.SALE_ACCTG_PERIOD = '2009-09-30'

  --AND DET.CONTRACTING_PARTY = 'OP88'

  --AND DET.PROPERTY IN ('151*175197','151*175154','151*175156')

    AND DET.GROSS_VOLUME_VOL1 > 0

    AND DET.CONTRACTING_PARTY = NAME.U2_ID

    AND DET.PROPERTY = PROP.U2_ID

  AND dbo.OFP_NOT_ALLOCABLE(DET.U2_ID) = '151'

    AND PROP.COUNTY = CTY.U2_ID

    AND DET.PRICE_CTRC = PCE.u2_id

    AND (PCE.EFF_DATES <='2009-09-30' AND (PCE.PRICE_PARAMS_EXP_DATES >= '2009-09-30' OR PCE.PRICE_PARAMS_EXP_DATES IS NULL))

    AND DET.PROPERTY = INJ.U2_ID

    AND DET.RUN_TICKET_ID = RT.U2_ID

    AND INJ.INJ_PTS = RT.INJ_PT

    AND INJ.OGP_PROP_INJ_ID = MILES.OGP_PROP_INJ_ID

UNION ALL

SELECT DET.CONTRACTING_PARTY OPERATOR,

        dbo.OFP_NOT_ALLOCABLE(det.u2_id),

        NAME.NAME1 OPERATOR_NAME,

        PROP.ANALYST ANALYST,

        DET.SALE_ACCTG_PERIOD,

        0                     VOLUME_CURR,  

        0                     DOLLARS_CURR,

        DET.GROSS_VOLUME_VOL1 VOLUME_LAST,

        DET.GROSS_VALUE       DOLLARS_LAST,

        0                     VOLUME_NEXT,

        0                     DOLLARS_NEXT,

        DET.PROPERTY,

        PROP.NAME PROPERTY_NAME,

        PCE.PRICE_PARAMS_METHOD_TYPES METHOD_TYPE,

        PCE.PRICE_PARAMS_BASIS PRICE_BASIS,

        DET.PRICE_METHOD FORMULA,

        PCE.PRICE_PARAMS_EFF_DATES EFF_DATE,

        CTY.NAME COUNTY,

        DET.BONUS_PRICE_ADJ BONUS,

        MILES.INJ_PT_MILES MILEAGE,

        0              GRAVITY_CURR,

        GRAVITY        GRAVITY_LAST,

        0              GRAVITY_NEXT

   FROM OG_SALE_DET DET,

        NAME,

        OGP_PROP PROP,

        CTY, PRICE_CTRC_EFF_DT PCE,

        RUN_TICKET RT,

        OGP_PROP_INJ INJ,

        OGP_PROP_MILES MILES

  WHERE DET.SALE_ACCTG_PERIOD = '2009-08-31'

  --AND DET.CONTRACTING_PARTY = 'OP88'

  --AND DET.PROPERTY IN ('151*175197','151*175154','151*175156')

    AND DET.GROSS_VOLUME_VOL1 > 0

    AND DET.CONTRACTING_PARTY = NAME.U2_ID

    AND DET.PROPERTY = PROP.U2_ID

  AND dbo.OFP_NOT_ALLOCABLE(DET.U2_ID) = '151'

    AND PROP.COUNTY = CTY.U2_ID

    AND DET.PRICE_CTRC = PCE.u2_id

    AND (PCE.EFF_DATES <= '2009-08-31' AND (PCE.PRICE_PARAMS_EXP_DATES >= '2009-08-31' OR PCE.PRICE_PARAMS_EXP_DATES IS NULL))

    AND DET.PROPERTY = INJ.U2_ID

    AND DET.RUN_TICKET_ID = RT.U2_ID

    AND INJ.INJ_PTS = RT.INJ_PT

    AND INJ.OGP_PROP_INJ_ID = MILES.OGP_PROP_INJ_ID

UNION ALL

SELECT DET.CONTRACTING_PARTY OPERATOR,

        dbo.OFP_NOT_ALLOCABLE(det.u2_id),

        NAME.NAME1 OPERATOR_NAME,

        PROP.ANALYST ANALYST,

        DET.SALE_ACCTG_PERIOD,

        0                     VOLUME_CURR,  

        0                     DOLLARS_CURR,

        0                     VOLUME_LAST,

        0                     DOLLARS_LAST,

        DET.GROSS_VOLUME_VOL1 VOLUME_NEXT,

        DET.GROSS_VALUE       DOLLARS_NEXT,

        DET.PROPERTY,

        PROP.NAME PROPERTY_NAME,

        PCE.PRICE_PARAMS_METHOD_TYPES METHOD_TYPE,

        PCE.PRICE_PARAMS_BASIS PRICE_BASIS,

        DET.PRICE_METHOD FORMULA,

        PCE.PRICE_PARAMS_EFF_DATES EFF_DATE,

        CTY.NAME COUNTY,

        DET.BONUS_PRICE_ADJ BONUS,

        MILES.INJ_PT_MILES MILEAGE,

        0              GRAVITY_CURR,

        0              GRAVITY_LAST,

        GRAVITY        GRAVITY_NEXT

   FROM OG_SALE_DET DET,

        NAME,

        OGP_PROP PROP,

        CTY, PRICE_CTRC_EFF_DT PCE,

        RUN_TICKET RT,

        OGP_PROP_INJ INJ,

        OGP_PROP_MILES MILES

  WHERE DET.SALE_ACCTG_PERIOD = '2009-07-31'

  --AND DET.CONTRACTING_PARTY = 'OP88'

  --AND DET.PROPERTY IN ('151*175197','151*175154','151*175156')

    AND DET.GROSS_VOLUME_VOL1 > 0

    AND DET.CONTRACTING_PARTY = NAME.U2_ID

    AND DET.PROPERTY = PROP.U2_ID

  AND dbo.OFP_NOT_ALLOCABLE(DET.U2_ID) = '151'

    AND PROP.COUNTY = CTY.U2_ID

    AND DET.PRICE_CTRC = PCE.u2_id

    AND (PCE.EFF_DATES <= '2009-07-31' AND (PCE.PRICE_PARAMS_EXP_DATES >= '2009-07-31' OR PCE.PRICE_PARAMS_EXP_DATES IS NULL))

    AND DET.PROPERTY = INJ.U2_ID

    AND DET.RUN_TICKET_ID = RT.U2_ID

    AND INJ.INJ_PTS = RT.INJ_PT

    AND INJ.OGP_PROP_INJ_ID = MILES.OGP_PROP_INJ_ID

) DETAIL

GROUP BY DETAIL.PROPERTY,

         DETAIL.SALE_ACCTG_PERIOD,

         DETAIL.OPERATOR,

         DETAIL.OPERATOR_NAME,

         DETAIL.ANALYST,

         

       DETAIL.PROPERTY_NAME,

       DETAIL.METHOD_TYPE,

       DETAIL.PRICE_BASIS,

       DETAIL.FORMULA,

       DETAIL.EFF_DATE,

       DETAIL.COUNTY,

       DETAIL.BONUS,

       DETAIL.MILEAGE

 




 

Avatar of Aneesh
Aneesh
Flag of Canada image
SELECT  DETAIL.OPERATOR, dbo.OFP_NOT_ALLOCABLE(det.u2_id) SomeColumnName ,
        DETAIL.OPERATOR_NAME, DETAIL.ANALYST, DETAIL.SALE_ACCTG_PERIOD,
        SUM(DETAIL.VOLUME_CURR) VOLUME_CURR,
        SUM(DETAIL.DOLLARS_CURR) DOLLARS_CURR,
        CASE WHEN SUM(DETAIL.VOLUME_CURR) = 0 THEN 0
             ELSE ( ( SUM(DETAIL.DOLLARS_CURR) ) / ( SUM(DETAIL.VOLUME_CURR) ) )
        END PRICE_PER_BARREL_CURR, SUM(DETAIL.VOLUME_LAST) VOLUME_LAST,
        SUM(DETAIL.DOLLARS_LAST) DOLLARS_LAST,
        CASE WHEN SUM(DETAIL.VOLUME_LAST) = 0 THEN 0
             ELSE ( ( SUM(DETAIL.DOLLARS_LAST) ) / ( SUM(DETAIL.VOLUME_LAST) ) )
        END PRICE_PER_BARREL_LAST, SUM(DETAIL.VOLUME_NEXT) VOLUME_NEXT,
        SUM(DETAIL.DOLLARS_NEXT) DOLLARS_NEXT,
        CASE WHEN SUM(DETAIL.VOLUME_NEXT) = 0 THEN 0
             ELSE ( ( SUM(DETAIL.DOLLARS_NEXT) ) / ( SUM(DETAIL.VOLUME_NEXT) ) )
        END PRICE_PER_BARREL_NEXT, '' VS_KS, '' VS_NYMEX, DETAIL.PROPERTY,
        DETAIL.PROPERTY_NAME, DETAIL.METHOD_TYPE, DETAIL.PRICE_BASIS,
        DETAIL.FORMULA, DETAIL.EFF_DATE, DETAIL.COUNTY, DETAIL.BONUS,
        DETAIL.MILEAGE, AVG(DETAIL.GRAVITY_CURR) GRAVITY_CURR,
        AVG(DETAIL.GRAVITY_LAST) GRAVITY_LAST,
        AVG(DETAIL.GRAVITY_NEXT) GRAVITY_NEXT
FROM    ( SELECT    DET.CONTRACTING_PARTY OPERATOR,
                    dbo.OFP_NOT_ALLOCABLE(det.u2_id), NAME.NAME1 OPERATOR_NAME,
                    PROP.ANALYST ANALYST, DET.SALE_ACCTG_PERIOD,
                    DET.GROSS_VOLUME_VOL1 VOLUME_CURR,
                    DET.GROSS_VALUE DOLLARS_CURR, 0 VOLUME_LAST,
                    0 DOLLARS_LAST, 0 VOLUME_NEXT, 0 DOLLARS_NEXT,
                    DET.PROPERTY, PROP.NAME PROPERTY_NAME,
                    PCE.PRICE_PARAMS_METHOD_TYPES METHOD_TYPE,
                    PCE.PRICE_PARAMS_BASIS PRICE_BASIS,
                    DET.PRICE_METHOD FORMULA,
                    PCE.PRICE_PARAMS_EFF_DATES EFF_DATE, CTY.NAME COUNTY,
                    DET.BONUS_PRICE_ADJ BONUS, MILES.INJ_PT_MILES MILEAGE,
                    GRAVITY GRAVITY_CURR, 0 GRAVITY_LAST, 0 GRAVITY_NEXT
          FROM      OG_SALE_DET DET
                   ,NAME
                   ,OGP_PROP PROP
                   ,CTY
                   ,PRICE_CTRC_EFF_DT PCE
                   ,RUN_TICKET RT
                   ,OGP_PROP_INJ INJ
                   ,OGP_PROP_MILES MILES
          WHERE     DET.SALE_ACCTG_PERIOD = '2009-09-30'

  --AND DET.CONTRACTING_PARTY = 'OP88'

  --AND DET.PROPERTY IN ('151*175197','151*175154','151*175156')
                    AND DET.GROSS_VOLUME_VOL1 > 0
                    AND DET.CONTRACTING_PARTY = NAME.U2_ID
                    AND DET.PROPERTY = PROP.U2_ID
                    AND dbo.OFP_NOT_ALLOCABLE(DET.U2_ID) = '151'
                    AND PROP.COUNTY = CTY.U2_ID
                    AND DET.PRICE_CTRC = PCE.u2_id
                    AND ( PCE.EFF_DATES <= '2009-09-30'
                          AND ( PCE.PRICE_PARAMS_EXP_DATES >= '2009-09-30'
                                OR PCE.PRICE_PARAMS_EXP_DATES IS NULL ) )
                    AND DET.PROPERTY = INJ.U2_ID
                    AND DET.RUN_TICKET_ID = RT.U2_ID
                    AND INJ.INJ_PTS = RT.INJ_PT
                    AND INJ.OGP_PROP_INJ_ID = MILES.OGP_PROP_INJ_ID
          UNION ALL
          SELECT    DET.CONTRACTING_PARTY OPERATOR,
                    dbo.OFP_NOT_ALLOCABLE(det.u2_id), NAME.NAME1 OPERATOR_NAME,
                    PROP.ANALYST ANALYST, DET.SALE_ACCTG_PERIOD, 0 VOLUME_CURR,
                    0 DOLLARS_CURR, DET.GROSS_VOLUME_VOL1 VOLUME_LAST,
                    DET.GROSS_VALUE DOLLARS_LAST, 0 VOLUME_NEXT,
                    0 DOLLARS_NEXT, DET.PROPERTY, PROP.NAME PROPERTY_NAME,
                    PCE.PRICE_PARAMS_METHOD_TYPES METHOD_TYPE,
                    PCE.PRICE_PARAMS_BASIS PRICE_BASIS,
                    DET.PRICE_METHOD FORMULA,
                    PCE.PRICE_PARAMS_EFF_DATES EFF_DATE, CTY.NAME COUNTY,
                    DET.BONUS_PRICE_ADJ BONUS, MILES.INJ_PT_MILES MILEAGE,
                    0 GRAVITY_CURR, GRAVITY GRAVITY_LAST, 0 GRAVITY_NEXT
          FROM      OG_SALE_DET DET
                   ,NAME
                   ,OGP_PROP PROP
                   ,CTY
                   ,PRICE_CTRC_EFF_DT PCE
                   ,RUN_TICKET RT
                   ,OGP_PROP_INJ INJ
                   ,OGP_PROP_MILES MILES
          WHERE     DET.SALE_ACCTG_PERIOD = '2009-08-31'

  --AND DET.CONTRACTING_PARTY = 'OP88'

  --AND DET.PROPERTY IN ('151*175197','151*175154','151*175156')
                    AND DET.GROSS_VOLUME_VOL1 > 0
                    AND DET.CONTRACTING_PARTY = NAME.U2_ID
                    AND DET.PROPERTY = PROP.U2_ID
                    AND dbo.OFP_NOT_ALLOCABLE(DET.U2_ID) = '151'
                    AND PROP.COUNTY = CTY.U2_ID
                    AND DET.PRICE_CTRC = PCE.u2_id
                    AND ( PCE.EFF_DATES <= '2009-08-31'
                          AND ( PCE.PRICE_PARAMS_EXP_DATES >= '2009-08-31'
                                OR PCE.PRICE_PARAMS_EXP_DATES IS NULL ) )
                    AND DET.PROPERTY = INJ.U2_ID
                    AND DET.RUN_TICKET_ID = RT.U2_ID
                    AND INJ.INJ_PTS = RT.INJ_PT
                    AND INJ.OGP_PROP_INJ_ID = MILES.OGP_PROP_INJ_ID
          UNION ALL
          SELECT    DET.CONTRACTING_PARTY OPERATOR,
                    dbo.OFP_NOT_ALLOCABLE(det.u2_id), NAME.NAME1 OPERATOR_NAME,
                    PROP.ANALYST ANALYST, DET.SALE_ACCTG_PERIOD, 0 VOLUME_CURR,
                    0 DOLLARS_CURR, 0 VOLUME_LAST, 0 DOLLARS_LAST,
                    DET.GROSS_VOLUME_VOL1 VOLUME_NEXT,
                    DET.GROSS_VALUE DOLLARS_NEXT, DET.PROPERTY,
                    PROP.NAME PROPERTY_NAME,
                    PCE.PRICE_PARAMS_METHOD_TYPES METHOD_TYPE,
                    PCE.PRICE_PARAMS_BASIS PRICE_BASIS,
                    DET.PRICE_METHOD FORMULA,
                    PCE.PRICE_PARAMS_EFF_DATES EFF_DATE, CTY.NAME COUNTY,
                    DET.BONUS_PRICE_ADJ BONUS, MILES.INJ_PT_MILES MILEAGE,
                    0 GRAVITY_CURR, 0 GRAVITY_LAST, GRAVITY GRAVITY_NEXT
          FROM      OG_SALE_DET DET
                   ,NAME
                   ,OGP_PROP PROP
                   ,CTY
                   ,PRICE_CTRC_EFF_DT PCE
                   ,RUN_TICKET RT
                   ,OGP_PROP_INJ INJ
                   ,OGP_PROP_MILES MILES
          WHERE     DET.SALE_ACCTG_PERIOD = '2009-07-31'

  --AND DET.CONTRACTING_PARTY = 'OP88'

  --AND DET.PROPERTY IN ('151*175197','151*175154','151*175156')
                    AND DET.GROSS_VOLUME_VOL1 > 0
                    AND DET.CONTRACTING_PARTY = NAME.U2_ID
                    AND DET.PROPERTY = PROP.U2_ID
                    AND dbo.OFP_NOT_ALLOCABLE(DET.U2_ID) = '151'
                    AND PROP.COUNTY = CTY.U2_ID
                    AND DET.PRICE_CTRC = PCE.u2_id
                    AND ( PCE.EFF_DATES <= '2009-07-31'
                          AND ( PCE.PRICE_PARAMS_EXP_DATES >= '2009-07-31'
                                OR PCE.PRICE_PARAMS_EXP_DATES IS NULL ) )
                    AND DET.PROPERTY = INJ.U2_ID
                    AND DET.RUN_TICKET_ID = RT.U2_ID
                    AND INJ.INJ_PTS = RT.INJ_PT
                    AND INJ.OGP_PROP_INJ_ID = MILES.OGP_PROP_INJ_ID ) DETAIL
GROUP BY DETAIL.PROPERTY, DETAIL.SALE_ACCTG_PERIOD, DETAIL.OPERATOR,
        DETAIL.OPERATOR_NAME, DETAIL.ANALYST, DETAIL.PROPERTY_NAME,
        DETAIL.METHOD_TYPE, DETAIL.PRICE_BASIS, DETAIL.FORMULA,
        DETAIL.EFF_DATE, DETAIL.COUNTY, DETAIL.BONUS, DETAIL.MILEAGE
Avatar of Chris Luttrell
Chris Luttrell
Flag of United States of America image
You need to put a column alias on that new column that is using a function to get the value.  Then you can just reference the alias in the outer query.
Avatar of EvesterR
EvesterR

ASKER

Can you show me how to create a column name and then reference the alias in the rest of the SQL code I tried this and still get the same error messages:  

Msg 8155, Level 16, State 2, Line 1
No column was specified for column 2 of 'DETAIL'.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "det.u2_id" could not be bound.


SELECT  DETAIL.OPERATOR, dbo.OFP_NOT_ALLOCABLE(det.u2_id) as PropID ,
        DETAIL.OPERATOR_NAME, DETAIL.ANALYST, DETAIL.SALE_ACCTG_PERIOD,
        SUM(DETAIL.VOLUME_CURR) VOLUME_CURR,
        SUM(DETAIL.DOLLARS_CURR) DOLLARS_CURR,
        CASE WHEN SUM(DETAIL.VOLUME_CURR) = 0 THEN 0
             ELSE ( ( SUM(DETAIL.DOLLARS_CURR) ) / ( SUM(DETAIL.VOLUME_CURR) ) )
        END PRICE_PER_BARREL_CURR, SUM(DETAIL.VOLUME_LAST) VOLUME_LAST,
        SUM(DETAIL.DOLLARS_LAST) DOLLARS_LAST,
        CASE WHEN SUM(DETAIL.VOLUME_LAST) = 0 THEN 0
             ELSE ( ( SUM(DETAIL.DOLLARS_LAST) ) / ( SUM(DETAIL.VOLUME_LAST) ) )
        END PRICE_PER_BARREL_LAST, SUM(DETAIL.VOLUME_NEXT) VOLUME_NEXT,
        SUM(DETAIL.DOLLARS_NEXT) DOLLARS_NEXT,
        CASE WHEN SUM(DETAIL.VOLUME_NEXT) = 0 THEN 0
             ELSE ( ( SUM(DETAIL.DOLLARS_NEXT) ) / ( SUM(DETAIL.VOLUME_NEXT) ) )
        END PRICE_PER_BARREL_NEXT, '' VS_KS, '' VS_NYMEX, DETAIL.PROPERTY,
        DETAIL.PROPERTY_NAME, DETAIL.METHOD_TYPE, DETAIL.PRICE_BASIS,
        DETAIL.FORMULA, DETAIL.EFF_DATE, DETAIL.COUNTY, DETAIL.BONUS,
        DETAIL.MILEAGE, AVG(DETAIL.GRAVITY_CURR) GRAVITY_CURR,
        AVG(DETAIL.GRAVITY_LAST) GRAVITY_LAST,
        AVG(DETAIL.GRAVITY_NEXT) GRAVITY_NEXT
FROM    ( SELECT    DET.CONTRACTING_PARTY OPERATOR,
                    dbo.OFP_NOT_ALLOCABLE(det.u2_id), NAME.NAME1 OPERATOR_NAME,
                    PROP.ANALYST ANALYST, DET.SALE_ACCTG_PERIOD,
                    DET.GROSS_VOLUME_VOL1 VOLUME_CURR,
                    DET.GROSS_VALUE DOLLARS_CURR, 0 VOLUME_LAST,
                    0 DOLLARS_LAST, 0 VOLUME_NEXT, 0 DOLLARS_NEXT,
                    DET.PROPERTY, PROP.NAME PROPERTY_NAME,
                    PCE.PRICE_PARAMS_METHOD_TYPES METHOD_TYPE,
                    PCE.PRICE_PARAMS_BASIS PRICE_BASIS,
                    DET.PRICE_METHOD FORMULA,
                    PCE.PRICE_PARAMS_EFF_DATES EFF_DATE, CTY.NAME COUNTY,
                    DET.BONUS_PRICE_ADJ BONUS, MILES.INJ_PT_MILES MILEAGE,
                    GRAVITY GRAVITY_CURR, 0 GRAVITY_LAST, 0 GRAVITY_NEXT
          FROM      OG_SALE_DET DET
                   ,NAME
                   ,OGP_PROP PROP
                   ,CTY
                   ,PRICE_CTRC_EFF_DT PCE
                   ,RUN_TICKET RT
                   ,OGP_PROP_INJ INJ
                   ,OGP_PROP_MILES MILES
          WHERE     DET.SALE_ACCTG_PERIOD = '2009-09-30'

  --AND DET.CONTRACTING_PARTY = 'OP88'

  --AND DET.PROPERTY IN ('151*175197','151*175154','151*175156')
                    AND DET.GROSS_VOLUME_VOL1 > 0
                    AND DET.CONTRACTING_PARTY = NAME.U2_ID
                    AND DET.PROPERTY = PROP.U2_ID
                    AND dbo.OFP_NOT_ALLOCABLE(DET.U2_ID) = '151'
                    AND PROP.COUNTY = CTY.U2_ID
                    AND DET.PRICE_CTRC = PCE.u2_id
                    AND ( PCE.EFF_DATES <= '2009-09-30'
                          AND ( PCE.PRICE_PARAMS_EXP_DATES >= '2009-09-30'
                                OR PCE.PRICE_PARAMS_EXP_DATES IS NULL ) )
                    AND DET.PROPERTY = INJ.U2_ID
                    AND DET.RUN_TICKET_ID = RT.U2_ID
                    AND INJ.INJ_PTS = RT.INJ_PT
                    AND INJ.OGP_PROP_INJ_ID = MILES.OGP_PROP_INJ_ID
          UNION ALL
          SELECT    DET.CONTRACTING_PARTY OPERATOR,
                    dbo.OFP_NOT_ALLOCABLE(det.u2_id), NAME.NAME1 OPERATOR_NAME,
                    PROP.ANALYST ANALYST, DET.SALE_ACCTG_PERIOD, 0 VOLUME_CURR,
                    0 DOLLARS_CURR, DET.GROSS_VOLUME_VOL1 VOLUME_LAST,
                    DET.GROSS_VALUE DOLLARS_LAST, 0 VOLUME_NEXT,
                    0 DOLLARS_NEXT, DET.PROPERTY, PROP.NAME PROPERTY_NAME,
                    PCE.PRICE_PARAMS_METHOD_TYPES METHOD_TYPE,
                    PCE.PRICE_PARAMS_BASIS PRICE_BASIS,
                    DET.PRICE_METHOD FORMULA,
                    PCE.PRICE_PARAMS_EFF_DATES EFF_DATE, CTY.NAME COUNTY,
                    DET.BONUS_PRICE_ADJ BONUS, MILES.INJ_PT_MILES MILEAGE,
                    0 GRAVITY_CURR, GRAVITY GRAVITY_LAST, 0 GRAVITY_NEXT
          FROM      OG_SALE_DET DET
                   ,NAME
                   ,OGP_PROP PROP
                   ,CTY
                   ,PRICE_CTRC_EFF_DT PCE
                   ,RUN_TICKET RT
                   ,OGP_PROP_INJ INJ
                   ,OGP_PROP_MILES MILES
          WHERE     DET.SALE_ACCTG_PERIOD = '2009-08-31'

  --AND DET.CONTRACTING_PARTY = 'OP88'

  --AND DET.PROPERTY IN ('151*175197','151*175154','151*175156')
                    AND DET.GROSS_VOLUME_VOL1 > 0
                    AND DET.CONTRACTING_PARTY = NAME.U2_ID
                    AND DET.PROPERTY = PROP.U2_ID
                    AND dbo.OFP_NOT_ALLOCABLE(DET.U2_ID) = '151'
                    AND PROP.COUNTY = CTY.U2_ID
                    AND DET.PRICE_CTRC = PCE.u2_id
                    AND ( PCE.EFF_DATES <= '2009-08-31'
                          AND ( PCE.PRICE_PARAMS_EXP_DATES >= '2009-08-31'
                                OR PCE.PRICE_PARAMS_EXP_DATES IS NULL ) )
                    AND DET.PROPERTY = INJ.U2_ID
                    AND DET.RUN_TICKET_ID = RT.U2_ID
                    AND INJ.INJ_PTS = RT.INJ_PT
                    AND INJ.OGP_PROP_INJ_ID = MILES.OGP_PROP_INJ_ID
          UNION ALL
          SELECT    DET.CONTRACTING_PARTY OPERATOR,
                    dbo.OFP_NOT_ALLOCABLE(det.u2_id), NAME.NAME1 OPERATOR_NAME,
                    PROP.ANALYST ANALYST, DET.SALE_ACCTG_PERIOD, 0 VOLUME_CURR,
                    0 DOLLARS_CURR, 0 VOLUME_LAST, 0 DOLLARS_LAST,
                    DET.GROSS_VOLUME_VOL1 VOLUME_NEXT,
                    DET.GROSS_VALUE DOLLARS_NEXT, DET.PROPERTY,
                    PROP.NAME PROPERTY_NAME,
                    PCE.PRICE_PARAMS_METHOD_TYPES METHOD_TYPE,
                    PCE.PRICE_PARAMS_BASIS PRICE_BASIS,
                    DET.PRICE_METHOD FORMULA,
                    PCE.PRICE_PARAMS_EFF_DATES EFF_DATE, CTY.NAME COUNTY,
                    DET.BONUS_PRICE_ADJ BONUS, MILES.INJ_PT_MILES MILEAGE,
                    0 GRAVITY_CURR, 0 GRAVITY_LAST, GRAVITY GRAVITY_NEXT
          FROM      OG_SALE_DET DET
                   ,NAME
                   ,OGP_PROP PROP
                   ,CTY
                   ,PRICE_CTRC_EFF_DT PCE
                   ,RUN_TICKET RT
                   ,OGP_PROP_INJ INJ
                   ,OGP_PROP_MILES MILES
          WHERE     DET.SALE_ACCTG_PERIOD = '2009-07-31'

  --AND DET.CONTRACTING_PARTY = 'OP88'

  --AND DET.PROPERTY IN ('151*175197','151*175154','151*175156')
                    AND DET.GROSS_VOLUME_VOL1 > 0
                    AND DET.CONTRACTING_PARTY = NAME.U2_ID
                    AND DET.PROPERTY = PROP.U2_ID
                    AND dbo.OFP_NOT_ALLOCABLE(DET.U2_ID) = '151'
                    AND PROP.COUNTY = CTY.U2_ID
                    AND DET.PRICE_CTRC = PCE.u2_id
                    AND ( PCE.EFF_DATES <= '2009-07-31'
                          AND ( PCE.PRICE_PARAMS_EXP_DATES >= '2009-07-31'
                                OR PCE.PRICE_PARAMS_EXP_DATES IS NULL ) )
                    AND DET.PROPERTY = INJ.U2_ID
                    AND DET.RUN_TICKET_ID = RT.U2_ID
                    AND INJ.INJ_PTS = RT.INJ_PT
                    AND INJ.OGP_PROP_INJ_ID = MILES.OGP_PROP_INJ_ID ) DETAIL
GROUP BY DETAIL.PROPERTY, DETAIL.SALE_ACCTG_PERIOD, DETAIL.OPERATOR,
        DETAIL.OPERATOR_NAME, DETAIL.ANALYST, DETAIL.PROPERTY_NAME,
        DETAIL.METHOD_TYPE, DETAIL.PRICE_BASIS, DETAIL.FORMULA,
        DETAIL.EFF_DATE, DETAIL.COUNTY, DETAIL.BONUS, DETAIL.MILEAGE
ASKER CERTIFIED SOLUTION
Avatar of Chris Luttrell
Chris Luttrell
Flag of United States of America image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of EvesterR
EvesterR

ASKER

Thank you so much, this worked.  The only problem now is the query takes 6 mins to run when it used to only take 2.  Thanks again.