?
Solved

SQL Command in Crystal Reports

Posted on 2010-01-12
6
Medium Priority
?
182 Views
Last Modified: 2012-05-08
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

 




 

0
Comment
Question by:EvesterR
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
6 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 26299147
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
 
LVL 27

Expert Comment

by:Chris Luttrell
ID: 26299155
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
 

Author Comment

by:EvesterR
ID: 26299412
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
 
LVL 27

Accepted Solution

by:
Chris Luttrell earned 2000 total points
ID: 26299533
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
 

Author Closing Comment

by:EvesterR
ID: 31676431
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

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to shrink a transaction log file down to a reasonable size.

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question