Modification of query without using Inner join

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

Other Topics

Modification of query without using Inner join

Author

Topic

Trupthi2124
Starting Member

5 Posts

Posted - 2011-02-10 : 02:11:00

SELECT
orderInfo.ROS_ORDER_NUMBER, TYPE.CODE AS REQUEST_TYPE, payProcStatus.CODE AS STATUS,
orderInfo.ROS_ORDER_DATE AS ORDER_DATE, orderInfo.ORDER_ID AS EPAY_ID,
orderInfo.ROS_TOTAL_AMOUNT AS ORDER_TOTAL, customer.ROS_CUSTOMER_IDENTIFIER AS CUSTOMER_ID,
ros.CODE AS ROS, item.ITEM_ID, item.UNIT_PRICE, contactInfo.LAST_NAME,
address.STATE, address.CITY, currency.CODE_ALPHA3 AS CURRENCY,
country.CODE_ALPHA3 AS COUNTRY, paymentSubType.CODE AS PAYMENT_SUB_TYPE,
contactInfo.FIRST_NAME, itemTax.COUNTRY_TAX_AMOUNT AS COUNTRY_TAX_AMOUNT,
address.POSTAL_CODE, rosGroup.CODE AS ROS_GROUP, trans.TRANSACTION_ID
FROM EPAYMENT.TRANSACTION trans
LEFT OUTER JOIN EPAYMENT.ORDER_INFO orderInfo ON trans.ORDER_ID = orderInfo.ORDER_ID
LEFT OUTER JOIN EPAYMENT_LOOKUP.ROS_REQUEST_TYPE TYPE ON trans.ROS_REQUEST_TYPE_ID = TYPE.ROS_REQUEST_TYPE_ID
LEFT OUTER JOIN EPAYMENT_LOOKUP.REQUESTING_ORDER_SYSTEM ros ON trans.ROS_ID = ros.ROS_ID
LEFT OUTER JOIN EPAYMENT_LOOKUP.ROS_GROUP rosGroup ON ros.ROS_GROUP_ID = rosGroup.ROS_GROUP_ID
LEFT OUTER JOIN EPAYMENT_LOOKUP.CURRENCY currency ON orderInfo.currency_id = currency.CURRENCY_ID
LEFT OUTER JOIN EPAYMENT.CUSTOMER customer ON orderInfo.CUSTOMER_ID = customer.CUSTOMER_ID
LEFT OUTER JOIN EPAYMENT.CONTACT_INFO contactInfo ON customer.CONTACT_INFO_ID = contactInfo.CONTACT_INFO_ID
LEFT OUTER JOIN EPAYMENT.ADDRESS address ON customer.ADDRESS_ID = address.ADDRESS_ID
LEFT OUTER JOIN EPAYMENT_LOOKUP.COUNTRY country ON address.COUNTRY_ID = country.COUNTRY_ID
LEFT OUTER JOIN EPAYMENT_LOOKUP.PAYMENT_PROCESSING_STATUS payProcStatus ON orderInfo.PAYMENT_PROC_STATUS_ID = payProcStatus.PAYMENT_PROC_STATUS_ID
LEFT OUTER JOIN EPAYMENT.ITEM item ON orderInfo.ORDER_ID = item.ORDER_ID
LEFT OUTER JOIN EPAYMENT.ITEM_TAX itemTax ON item.ITEM_ID = itemTax.ITEM_ID
LEFT OUTER JOIN EPAYMENT.PAYMENT_SPECIFICATION paymentSpec ON ORDERInfo.ORDER_ID = paymentSpec.ORDER_ID
LEFT OUTER JOIN EPAYMENT_LOOKUP.PAYMENT_SUBTYPE paymentSubType ON paymentSubType.PAYMENT_SUBTYPE_ID = paymentSpec.PAYMENT_SUBTYPE_ID
LEFT OUTER JOIN EPAYMENT.CREDIT_CARD_PAYMENT_SPEC creditCardPymtSpec ON creditCardPymtSpec.PAYMENT_SPEC_ID = paymentSpec.PAYMENT_SPEC_ID
WHERE ( trans.LAST_CHANGE_DATE BETWEEN sysdate-180 AND sysdate ) AND
( orderInfo.ROS_ORDER_NUMBER LIKE 'NP25292867%' OR orderInfo.ORIGINAL_ROS_ORDER_NUMBER LIKE 'NP25292867%' )
AND ROWNUM <= 100;

PESO:
Since you have put the WHERE clause at the end for an OUTER table, the OUTER is not used.
Change LEFT OUTER JOIN orderInfo to INNER JOIN orderInfo.

Trupthi:

I tried with Inner join the result was fetched within no time but i started facing the problem for the orders of type Bank transfers. When i use the INNER JOIN, only the Credit card orders were fetched and not the Bank transfer orders. So is there any other way to optimise this query using some UNION or something like that?

Please help me with this.

Trupthi.TS

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-02-10 : 02:42:42

[code]SELECT orderInfo.ROS_ORDER_NUMBER,
TYPE.CODE AS REQUEST_TYPE,
payProcStatus.CODE AS STATUS,
orderInfo.ROS_ORDER_DATE AS ORDER_DATE,
orderInfo.ORDER_ID AS EPAY_ID,
orderInfo.ROS_TOTAL_AMOUNT AS ORDER_TOTAL,
customer.ROS_CUSTOMER_IDENTIFIER AS CUSTOMER_ID,
ros.CODE AS ROS,
item.ITEM_ID,
item.UNIT_PRICE,
contactInfo.LAST_NAME,
address.STATE,
address.CITY,
currency.CODE_ALPHA3 AS CURRENCY,
country.CODE_ALPHA3 AS COUNTRY,
paymentSubType.CODE AS PAYMENT_SUB_TYPE,
contactInfo.FIRST_NAME,
itemTax.COUNTRY_TAX_AMOUNT AS COUNTRY_TAX_AMOUNT,
address.POSTAL_CODE,
rosGroup.CODE AS ROS_GROUP,
trans.TRANSACTION_ID
FROM EPAYMENT.TRANSACTION AS trans
LEFT JOIN EPAYMENT.ORDER_INFO AS orderInfo ON trans.ORDER_ID = orderInfo.ORDER_ID
AND (
orderInfo.ROS_ORDER_NUMBER LIKE 'NP25292867%'
OR
orderInfo.ORIGINAL_ROS_ORDER_NUMBER LIKE 'NP25292867%'
)
/* Try this part instead of the above part if need more speed
AND (
SUBSTRING(orderInfo.ROS_ORDER_NUMBER, 1, 10) = 'NP25292867'
OR
SUBSTRING(orderInfo.ORIGINAL_ROS_ORDER_NUMBER, 1, 10) = 'NP25292867'
)
*/
LEFT JOIN EPAYMENT_LOOKUP.ROS_REQUEST_TYPE AS TYPE ON TYPE.ROS_REQUEST_TYPE_ID = trans.ROS_REQUEST_TYPE_ID
LEFT JOIN EPAYMENT_LOOKUP.REQUESTING_ORDER_SYSTEM AS ros ON ros.ROS_ID = trans.ROS_ID
LEFT JOIN EPAYMENT_LOOKUP.ROS_GROUP AS rosGroup ON rosGroup.ROS_GROUP_ID = ros.ROS_GROUP_ID
LEFT JOIN EPAYMENT_LOOKUP.CURRENCY AS currency ON currency.CURRENCY_ID = orderInfo.currency_id
LEFT JOIN EPAYMENT.CUSTOMER AS customer ON customer.CUSTOMER_ID = orderInfo.CUSTOMER_ID
LEFT JOIN EPAYMENT.CONTACT_INFO AS contactInfo ON contactInfo.CONTACT_INFO_ID = customer.CONTACT_INFO_ID
LEFT JOIN EPAYMENT.ADDRESS AS address ON address.ADDRESS_ID = customer.ADDRESS_ID
LEFT JOIN EPAYMENT_LOOKUP.COUNTRY AS country ON country.COUNTRY_ID = address.COUNTRY_ID
LEFT JOIN EPAYMENT_LOOKUP.PAYMENT_PROCESSING_STATUS AS payProcStatus ON payProcStatus.PAYMENT_PROC_STATUS_ID = orderInfo.PAYMENT_PROC_STATUS_ID
LEFT JOIN EPAYMENT.ITEM AS item ON item.ORDER_ID = orderInfo.ORDER_ID
LEFT JOIN EPAYMENT.ITEM_TAX AS itemTax ON itemTax.ITEM_ID = item.ITEM_ID
LEFT JOIN EPAYMENT.PAYMENT_SPECIFICATION AS paymentSpec ON paymentSpec.ORDER_ID = ORDERInfo.ORDER_ID
LEFT JOIN EPAYMENT_LOOKUP.PAYMENT_SUBTYPE AS paymentSubType ON paymentSubType.PAYMENT_SUBTYPE_ID = paymentSpec.PAYMENT_SUBTYPE_ID
LEFT JOIN EPAYMENT.CREDIT_CARD_PAYMENT_SPEC AS creditCardPymtSpec ON creditCardPymtSpec.PAYMENT_SPEC_ID = paymentSpec.PAYMENT_SPEC_ID
WHERE trans.LAST_CHANGE_DATE BETWEEN sysdate - 180 AND sysdate
AND ROWNUM <= 100;[/code]

N 56°04'39.26"
E 12°55'05.63"

Trupthi2124
Starting Member

5 Posts

Posted - 2011-02-10 : 07:14:06

Thanks Peso..But my concern over here is the cost. I would like to minimise it.The original query takes around 3MB (cost) which is not acceptable. So i am trying to reduce it. The new query which u mentioned today takes more than 5MB..

Trupthi.TS

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-02-10 : 08:18:22

What 3MB cost?

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-02-10 : 08:18:51

Is this even a Microsoft SQL Server query?

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources