Results 1 to 1 of 1
  1. #1
    Join Date
    Sep 2004
    Posts
    3

    Question Unanswered: ETL Transformation Query JOINS

    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
    * How would I join HERE with a WHERE clause
    *NEED help with joins and optimization!!
    ************************************************** ******/



    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!
    Last edited by baaul; 09-29-04 at 00:29. Reason: To clarify

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •