"baaul" <baaul@discussions.microsoft.com> wrote:

> How do I make this to work. Optimize the current query. Should I move all the
> joins from FROM to the WHERE clause?? My query isn't working as is. Any
> suggestion. Thanks!!
>
>
> ************************************************** ****************
> *
> * TRANSFORMATION 1A
> * Description: This Transformation will be ran for Contract Level Invoices
> * (i.e. Commodity, Reservation)
> *
> ************************************************** ****************
> ************************************************** ***************/
>
>
> SELECT
> STGInvoice.companyNumber,
> ContractDim.contractKey,
> ShipperDim.shipperKey,
> PayerDim.payerKey,
> PayeeDim.payeeKey,
> PointDimRec.pointKey,
> PointDimDel.pointKey,
> DateDim.dateKey,
> MonthDimProd.monthKey,
> MonthDimAcct.monthKey,
> STGInvoice.invoiceTypeCode,
> STGInvoice.invoiceNumber,
> STGInvoice.invoiceDate,
> STGInvoice.invoiceDueDate,
> STGBaseChargeLineItem.lineItemAmount,
> STGBaseChargeLineItem.lineItemQuantity,
> (CASE STGBaseChargeLineItem.updateUserId WHEN 'SYSTEM' THEN 'S' ELSE 'M' END),
> STGBaseChargeLineItem.chargeCode,
> STGChargeType.LongDescription,
> STGBaseChargeLineItem.transactionTypeCode,
> STGRateVolumeType.longDescription,
> STGBaseChargeLineItem.chargeRate,
> STGBaseChargeLineItem.discountRate,
> (STGBaseChargeLineItem.chargeRate - STGBaseChargeLineItem.discountRate),
> STGBaseChargeLineItem.reversalFlag,
> STGBaseChargeLineItem.sequenceId,
> STGBaseChargeLineItem.relatedContractNum,
> (CASE WHEN STGBaseChargeLineItem.lineItemQuantity >= 0 THEN 'A' ELSE 'R' END),
> (CASE STGInvoice.invoiceTypeCode WHEN 'COMMODITY' THEN 'C' WHEN
> 'RESERVATION' THEN 'R' ELSE 'O' END),
> STGBaseChargeLineItem.volumeSourceCode,
> STGBaseChargeLineItem.baseChargeLineItemSID,
> 'GAS',
> STGInvoice.finanicalInvoiceId,
> STGInvoice.invoiceStatusTypeCode,
> GETDATE(),
> 'A'
> FROM
> STGInvoice INNER JOIN STGBaseChargeLineItem ON
> STGInvoice.invoiceNumber = STGBaseChargeLineItem.invoiceNumber
> INNER JOIN STGChargeType ON
> STGChargeType.chargeCode = STGBaseChargeLineItem.chargeCode AND
> STGChargeType.companyNumber = STGBaseChargeLineItem.companyNumber
> INNER JOIN STGRateVolumeType ON
> STGRateVolumeType.transactionTypeCode =
> STGBaseChargeLineItem.transactionTypeCode AND
> STGRateVolumeType.companyNumber = STGBaseChargeLineItem.companyNumber
> INNER JOIN STGAccountingMonth ON
> STGAccountingMonth.accountingMonth = STGInvoice.accountingMonth AND
> STGAccountingMonth.companyNumber = STGInvoice.companyNumber
> INNER JOIN ContractDim ON
> ContractDim.contractNum = STGInvoice.primaryEntityId AND
> ContractDim.companyNum = STGInvoice.companyNumber AND
> STGInvoice.primaryEntityTypeCode = 'CONTRACT' AND
> ContractDim.recordStatusCode = 'A'
> INNER JOIN ShipperDim ON
> ShipperDim.legalEntityNum = ContractDim.legalEntityNum AND
> ShipperDim.contactNum = ContractDim.shipperContactNum AND
> ShipperDim.companyNum = ContractDim.companyNum AND
> ShipperDim.recordStatusCode = 'A'
>
>
>
> /************************************************** ******
> *Need to JOIN ShipperDim from above to PayerDim below using (WHERE (case
> * How would I join HERE with a WHERE clause
> *NEED help with joins !!
> ************************************************** ******/
>
>
>
> INNER JOIN PayerDim ON
> PayerDim.legalEntityNum = ContractDim.payerLegalEntityNum AND
> PayerDim.companyNum = STGBaseChargeLineItem.companyNumber AND
> PayerDim.purposeCode = 'IN' AND
> PayerDim.recordStatusCode = 'A'
> INNER JOIN PayeeDim ON
> PayeeDim.companyNum = STGBaseChargeLineItem.companyNumber AND
> PayeeDim.recordStatusCode = 'A'
> INNER JOIN PointDim PointDimRec ON
> PointDimRec.pointNumber = STGBaseChargeLineItem.receiptPointNumber AND
> PointDimRec.companyNum = STGBaseChargeLineItem.companyNum AND
> PointDimRec.recordStatusCode = 'A'
> INNER JOIN PointDim PointDimDel ON
> PointDimDel.pointNumber = STGBaseChargeLineItem.deliveryPointNumber AND
> PointDimDel.companyNum = STGBaseChargeLineItem.companyNum AND
> PointDimDel.recordStatusCode = 'A'
> INNER JOIN DateDim ON
> DateDim.dateStamp = STGBaseChargeLineItem.startDate
> INNER JOIN MonthDim MonthDimProd ON
> MonthDimProd.monthNumber = MONTH(STGInvoice.startDate) AND
> MonthDimProd.yearNumber = YEAR(STGInvoice.startDate)
> INNER JOIN MonthDim MonthDimAcct ON
> MonthDimAcct.monthNumber = MONTH(STGAccountingMonth.startDate) AND
> MonthDimAcct.yearNumber = YEAR(STGAccountingMonth.startDate)
>
>
> --appreciate any help!