Modification in the query - SQL Server Forums

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 in the query

Author

Topic

Trupthi2124
Starting Member

5 Posts

Posted - 2011-02-07 : 00:59:51

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;

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-02-07 : 03:11:54

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.

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

Trupthi2124
Starting Member

5 Posts

Posted - 2011-02-07 : 03:49:30

Thanks a lot PESO.It worked..I was behind this from days together..You saved my day..Thanks a lot

Trupthi.TS

Trupthi2124
Starting Member

5 Posts

Posted - 2011-02-09 : 04:56:29

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?

Trupthi.TS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources