dcsimg
Results 1 to 4 of 4

Thread: Convert SQL to MDX query

  1. #1
    Join Date
    Feb 2009
    Posts
    2

    Convert SQL to MDX query

    Hello All,

    I am new to MDX and need help in converting a SQL to MDX query. Could somebody please help me?

    SELECT
    COUNT(DISTINCT INVOICE.INVOICE_NUMBER),
    VENDOR.VENDOR_NAME,
    nvl(Dispute_Origin.DESCRIPTION, 'Not Applicable'),
    (FLOOR(CEIL(SYSDATE- DISPUTE_LOG.DISPUTE_FILED_DATE)/30) * 30 || ' - ' || (FLOOR(CEIL(SYSDATE-DISPUTE_LOG.DISPUTE_FILED_DATE)/30)+ 1) * 30),
    SUM(DISPUTE_LOG.DISPUTE_OPEN),
    COUNT(DISTINCT DISPUTE_LOG.CLAIM_NUMBER)
    FROM
    BILLING_ACCOUNT,
    REFVAL Dispute_Origin,
    DISPUTE_LOG,
    INVOICE,
    VENDOR_REMITTANCE,
    VENDOR
    WHERE
    ( Dispute_Origin.REFVAL_CODE(+)=DISPUTE_LOG.DISPUTE_ ORIGIN )
    AND ( INVOICE.BILLING_ACCOUNT_SYS_ID=BILLING_ACCOUNT.BIL LING_ACCOUNT_SYS_ID )
    AND ( VENDOR_REMITTANCE.VENDOR_SYS_ID=VENDOR.VENDOR_SYS_ ID(+) )
    AND ( VENDOR_REMITTANCE.VENDOR_REMITTANCE_SYS_ID(+)=BILL ING_ACCOUNT.VENDOR_REMITTANCE_SYS_ID )
    AND ( DISPUTE_LOG.INVOICE_SYS_ID=INVOICE.INVOICE_SYS_ID )
    AND ( Dispute_Origin.REFVAL_OBJ_NAME(+) = 'DISPUTE_ORIGIN' )
    AND ( DISPUTE_LOG.DISPUTE_FILED_FLAG = 'Y' )
    GROUP BY
    VENDOR.VENDOR_NAME,
    nvl(Dispute_Origin.DESCRIPTION, 'Not Applicable'),
    (FLOOR(CEIL(SYSDATE- DISPUTE_LOG.DISPUTE_FILED_DATE)/30) * 30 || ' - ' || (FLOOR(CEIL(SYSDATE-DISPUTE_LOG.DISPUTE_FILED_DATE)/30)+ 1) * 30)

    Basically, I am trying to display ageing information in terms of days such as 0-30, 31-60 etc. I need to display the same in MDX could somebody help me?

    Thanks,
    Deepak

  2. #2
    Join Date
    Sep 2002
    Posts
    5,938
    There's no direct conversion between them, need know cube and dimensions to mdx query.

  3. #3
    Join Date
    Feb 2009
    Posts
    2
    specifically i want to replace this code :

    (FLOOR(CEIL(SYSDATE- DISPUTE_LOG.DISPUTE_FILED_DATE)/30) * 30 || ' - ' || (FLOOR(CEIL(SYSDATE-DISPUTE_LOG.DISPUTE_FILED_DATE)/30)+ 1) * 30)

    which gives the days in this manner 0-30,30-60,60-90 etc.

    with an mdx equivalent. Can I use ParallelPeriods, ClosingPeriods to achieve the same? If somebody has a code snippet for this it would be great.

    Thanks,
    Deepak

  4. #4
    Join Date
    Jun 2004
    Location
    Atlanta and Manhattan
    Posts
    607

    No Snippets, but ...

    We would need to know your dimensional structure to suggest doing what you appear to want to do in MDX. If you have access to the Analysis Services AdventureWorks AS DB sample, and could state what you are attempting in terms of that structure, I could possibly assist.

    HTH,

    Bill

Posting Permissions

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