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

 




 

EvesterRAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Aneesh RetnakaranDatabase AdministratorCommented:
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
0
Chris LuttrellSenior Database ArchitectCommented:
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.
0
EvesterRAuthor Commented:
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
0
Chris LuttrellSenior Database ArchitectCommented:
In the inner query add an alias like this:
(SELECT DET.CONTRACTING_PARTY OPERATOR,        dbo.OFP_NOT_ALLOCABLE(det.u2_id) ColumnAliasName,        NAME.NAME1 OPERATOR_NAME,
...
Then in the outer query reference it like this:
SELECT DETAIL.OPERATOR,     DETAIL.ColumnAliasName,       DETAIL.OPERATOR_NAME,
...
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
EvesterRAuthor Commented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.