Convert SQL to MDX query

[deepakp]

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

[rmiao]

There's no direct conversion between them, need know cube and dimensions to mdx query.

[deepakp]

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

[Island1]

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