September 30, 2011 at 9:06 am
this is a challenge from my manager.
could I get some help ?
SELECT NULL "lnObjVer", NULL "docTypeName", NULL "lnUtIds", NULL "utizType", SUM(utiz.amount) "amount", NULL "docDate", NULL "lnUtId", utiz.actOfferType "actOfferType", NULL "oldLineAmt", NULL "docType", NULL "claimLineAmt", SUM(utiz.acctd_amount_remaining) "acctdAmRem", NULL "itemId", SUM(utiz.amount_remaining) "amountRem", utiz.actType "actType", 'F' "selectFlag", custAcctId "custAcctId", NULL "lineAmt", NULL "docNum", lk.meaning "actTypeName", NULL "period", NULL "actCode", NULL "documentId", NULL "lnUtObjVer", NULL "itemType", NULL "lnId", NULL "utId", NULL "utObjVer", utiz.actId "actId", NULL "custName", NULL "itemTypeName", curr.name "currency", utiz.offerCustomSetupId "offerCustomSetupId", NULL "itemName", NULL "groupBy"
FROM
(SELECT 'OFFR' actType , fu.plan_id actId , offer.offer_type actOfferType , offer.custom_setup_id offerCustomSetupId , fu.cust_account_id custAcctId , fu.product_level_type , fu.product_id , fu.object_type object_type , fu.object_id object_id , fu.amount amount , fu.amount_remaining amount_remaining , fu.acctd_amount_remaining acctd_amount_remaining , fu.currency_code currency_code , fu.creation_date
FROM ozf_funds_utilized_all_b fu , ozf_offers offer , qp_list_headers_b qp
WHERE fu.plan_type = 'OFFR' AND fu.org_id = TO_NUMBER(SUBSTRB(userenv('CLIENT_INFO'),1,10)) AND fu.plan_id = offer.qp_list_header_id AND offer.qp_list_header_id = qp.list_header_id AND offer.offer_type = :1 AND (fu.reference_type IS NULL OR fu.reference_type NOT IN ('SOFT_FUND', 'SPECIAL_PRICE')) AND (offer.confidential_flag ='N' OR offer.confidential_flag IS NULL OR ( NVL(offer.budget_offer_yn,'N') ='N' AND EXISTS (
SELECT 1
FROM ams_act_access_denorm act
WHERE act.object_id = offer.qp_list_header_id AND act.object_type = 'OFFR' AND act.resource_id = :2 )) OR ( NVL(offer.budget_offer_yn,'N') ='Y' AND EXISTS (
SELECT 1
FROM ams_act_access_denorm act
WHERE act.object_id = fu.fund_id AND act.object_type = 'FUND' AND act.resource_id = :3 ))) AND ( (fu.acctd_amount_remaining <> 0 ) OR ( NVL(qp.end_date_active, trunc(sysdate)) >= trunc(sysdate) AND offer.user_status_id NOT IN (1607,1608,1609) ) OR fu.utilization_id IN
(SELECT utilization_id
FROM ozf_claim_lines_util
WHERE claim_line_id = :4)) AND fu.utilization_type IN ('ACCRUAL', 'ADJUSTMENT') AND fu.cust_account_id = :5 AND fu.plan_id = :6 AND fu.gl_posted_flag = 'Y' ) utiz , fnd_currencies_vl curr , ozf_lookups lk
WHERE (utiz.currency_code = curr.currency_code AND utiz.actType = lk.lookup_code AND lk.lookup_type = 'OZF_CLAIM_ASSO_ACT_TYPE' ) GROUP BY utiz.actType, utiz.actId, utiz.actOfferType, utiz.offerCustomSetupId, lk.meaning, curr.name, utiz.custAcctId
September 30, 2011 at 9:08 am
No.
September 30, 2011 at 9:11 am
That isn't t-sql, it is oracle.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 30, 2011 at 9:14 am
Thanks and sorry for posting it in the wrong place
September 30, 2011 at 9:16 am
change the FROM statments to use inner joins and you''l find it a few seconds...
i'm guessing that the table alaised as qp is not mentioned in the where/join criteria.
FROM
ozf_funds_utilized_all_b fu,
ozf_offers offer,
qp_list_headers_b qp
...utiz,
fnd_currencies_vl curr,
ozf_lookups lk
so if tehre is no explicit WHERE statement joining those groups of tables, you get a cartesian produc.
here it is for readability :
SELECT
NULL "lnObjVer",
NULL "docTypeName",
NULL "lnUtIds",
NULL "utizType",
SUM(utiz.amount) "amount",
NULL "docDate",
NULL "lnUtId",
utiz.actOfferType "actOfferType",
NULL "oldLineAmt",
NULL "docType",
NULL "claimLineAmt",
SUM(utiz.acctd_amount_remaining) "acctdAmRem",
NULL "itemId",
SUM(utiz.amount_remaining) "amountRem",
utiz.actType "actType",
'F' "selectFlag",
custAcctId "custAcctId",
NULL "lineAmt",
NULL "docNum",
lk.meaning "actTypeName",
NULL "period",
NULL "actCode",
NULL "documentId",
NULL "lnUtObjVer",
NULL "itemType",
NULL "lnId",
NULL "utId",
NULL "utObjVer",
utiz.actId "actId",
NULL "custName",
NULL "itemTypeName",
curr.name "currency",
utiz.offerCustomSetupId "offerCustomSetupId",
NULL "itemName",
NULL "groupBy"
FROM
(SELECT
'OFFR' actType,
fu.plan_id actId,
offer.offer_type actOfferType,
offer.custom_setup_id offerCustomSetupId,
fu.cust_account_id custAcctId,
fu.product_level_type,
fu.product_id,
fu.object_type object_type,
fu.object_id object_id,
fu.amount amount,
fu.amount_remaining amount_remaining,
fu.acctd_amount_remaining acctd_amount_remaining,
fu.currency_code currency_code,
fu.creation_date
FROM
ozf_funds_utilized_all_b fu,
ozf_offers offer,
qp_list_headers_b qp
WHERE
fu.plan_type = 'OFFR'
AND fu.org_id = TO_NUMBER(SUBSTRB(USERENV('CLIENT_INFO'), 1, 10))
AND fu.plan_id = offer.qp_list_header_id
AND offer.qp_list_header_id = qp.list_header_id
AND offer.offer_type = :1
AND ( fu.reference_type IS NULL
OR fu.reference_type NOT IN ( 'SOFT_FUND', 'SPECIAL_PRICE' ) )
AND ( offer.confidential_flag = 'N'
OR offer.confidential_flag IS NULL
OR ( NVL(offer.budget_offer_yn, 'N') = 'N'
AND EXISTS (SELECT
1
FROM
ams_act_access_denorm act
WHERE
act.object_id = offer.qp_list_header_id
AND act.object_type = 'OFFR'
AND act.resource_id = :2) )
OR ( NVL(offer.budget_offer_yn, 'N') = 'Y'
AND EXISTS (SELECT
1
FROM
ams_act_access_denorm act
WHERE
act.object_id = fu.fund_id
AND act.object_type = 'FUND'
AND act.resource_id = :3) ) )
AND ( ( fu.acctd_amount_remaining <> 0 )
OR ( NVL(qp.end_date_active, TRUNC(sysdate)) >= TRUNC(sysdate)
AND offer.user_status_id NOT IN ( 1607, 1608, 1609 ) )
OR fu.utilization_id IN (SELECT
utilization_id
FROM
ozf_claim_lines_util
WHERE
claim_line_id = :4) )
AND fu.utilization_type IN ( 'ACCRUAL', 'ADJUSTMENT' )
AND fu.cust_account_id = :5
AND fu.plan_id = :6
AND fu.gl_posted_flag = 'Y') utiz,
fnd_currencies_vl curr,
ozf_lookups lk
WHERE
( utiz.currency_code = curr.currency_code
AND utiz.actType = lk.lookup_code
AND lk.lookup_type = 'OZF_CLAIM_ASSO_ACT_TYPE' )
GROUP BY
utiz.actType,
utiz.actId,
utiz.actOfferType,
utiz.offerCustomSetupId,
lk.meaning,
curr.name,
utiz.custAcctId
Lowell
September 30, 2011 at 9:18 am
Realize the reason nobody is willing to help is the same reason you don't want to deal with it. That code is a mess. We don't have the tables or knowledge of the system that you have. We don't get paid for our help on here. Would you be willing to parse that for free? As I was typing this it seems that Lowell was willing to have a go at it. 😉
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 30, 2011 at 9:19 am
I worked with Oracle for a year, and I wouldn't touch this code. Why? I don't want to spend the time reformatting it so that it is readable. When posting code it really helps if you take the time to format it so others can easily read it.
September 30, 2011 at 9:25 am
thanks very much and sorry again:(
September 30, 2011 at 9:27 am
I made the necessary syntax changes to get it to lay out using Red Gate's SQL Prompt, but it really didn't help much. Made it more readable, but not enough to tell what's going on with it.
First, lay it out for readability.
Next, change to ANSI-standard Joins, with "ON" statements instead of having all that in the Where clause.
Finally, follow the data.
A lot of the confusion on this one is it's impossible for anyone not familiar with the tables to determine which parts of the Where clause are defining the join math and which parts are limiting the final output after the joins are done. Without separating those two things, you'll probably never find the cartesian issue. Only someone with access to the tables, the data in them, and the business definitions that they are abstracting can really do this. That's what I mean by "follow the data".
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply