question

krithiga avatar image
krithiga asked

can we avoid this mulitple inline select for same function

CREATE PROCEDURE [dbo].[sLoadEftRejectedPayments] AS BEGIN SET NOCOUNT ON; INSERT INTO statEFTRejectedCodes SELECT tempeftRejectedPayments.errorCode,'Unknown Error' AS errorDesc FROM tempeftRejectedPayments LEFT OUTER JOIN statEFTRejectedCodes ON tempeftRejectedPayments.errorCode = statEFTRejectedCodes.errorCode WHERE statEFTRejectedCodes.errorCode IS NULL ----------------------------------------------------------------------------------------------------------------- SELECT DISTINCT statEFTRejectedCodes.eftRejectedCodesId, tempeftRejectedPayments.vendorId, tempeftRejectedPayments.taxId, humanaAcctNbr, payeeName, paymentNbr, issueDate, amount, abaRoutingNbr, acctNbr, addressType, glCompany, glFacility, glDepartment, glAccount, glMarket, glProduct, glPlatformFuture2, glBusinessSegment, glHMNSCoCoid, glNonGAAPFuture1, glUDNAccount, coid, receivedDate, statRejectedPaymentsStatus.rejectedPaymentsStatusId, vendor.vendorKey, vendorMaster.vendorMasterId, (SELECT address1 FROM fn_getBillingAddressByVendor(vendor.vendorKey)) AS payeeAddressLine1, (SELECT city FROM fn_getBillingAddressByVendor(vendor.vendorKey)) AS payeeCity, (SELECT state FROM fn_getBillingAddressByVendor(vendor.vendorKey)) AS payeeState, (SELECT zip FROM fn_getBillingAddressByVendor(vendor.vendorKey)) AS payeeZip, getdate() updateDt FROM tempeftRejectedPayments INNER JOIN statEFTRejectedCodes ON tempeftRejectedPayments.errorCode = statEFTRejectedCodes.errorCode INNER JOIN statRejectedPaymentsStatus ON statRejectedPaymentsStatus.statusName = 'Rejected' INNER JOIN vendor ON tempeftRejectedPayments.vendorId = vendor.vendorId INNER JOIN (SELECT vendorMasterId ,vendorKey,RANK() OVER (PARTITION BY eraData.vendorKey ORDER BY eraDataID DESC) AS RankById FROM eraData) AS tempEraData ON vendor.vendorKey = tempEraData.vendorKey INNER JOIN vendorMaster ON tempEraData.vendorMasterId = vendorMaster.vendorMasterId WHERE tempEraData.RankById=1 SET NOCOUNT OFF; END
inline-table-valued-function
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Squirrel avatar image
Squirrel answered
use CROSS APPLY SELECT ... vendor.vendorKey, vendorMaster.vendorMasterId, ba.address1, ba.city ... FROM ... CROSS APPLY fn_getBillingAddressByVendor(vendor.vendorKey) ba
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.