Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

SQL Command in Crystal Reports

Posted on 2010-01-12
6
Medium Priority
?
184 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
  • 2
  • 2
5 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
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
Suggested Courses

580 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