February 15, 2017 at 3:16 am
Hi All,
I have a requirement is to extract only transactions records thatare type ‘01’,’17’,’18’,’19’ or ‘99’ and those with status not equal to ‘RJCT’(rejected).
The transaction type is in<Transaction><Transfer><Type> and the status is<Transaction><TransactionStatus>
I can get this to work in a 2 stage process (i.e. filter on one elementinto a variable and then do a second filter to output) using
let $transaction :=$b/Transaction[TransactionStatus != "RJCT"]
first and then follow this by
let $transaction := $b/Transaction[Transfer/Type[contains(.,"17") or contains (., "01") or contains (., "18")or contains (., "19") or contains (., "99")]]
but I’d like to do it in a single pass of the data ifpossible.
The attached shows an attempt using an ‘AND’ to concatenatethese two. I’ve tried various different syntax and also using a where, noneproduce the desired result.
Please see attached query
Any suggestions?
February 15, 2017 at 6:15 am
Found a solution, if you do the AND part first sql seems to like it. And you get the result set you would expect.
This....
let $transaction := $b/Transaction[TransactionStatus != "RJCT" and Transfer/Type[contains(., "17") or contains (., "01") or contains (., "18") or contains (., "19") or contains (., "99")]]
let $count := count($b/Transaction[TransactionStatus != "RJCT" and Transfer/Type[contains(., "17") or contains (., "01") or contains (., "18") or contains (., "19") or contains (., "99")]])
let $amount := sum($b/Transaction/Transfer/Amount[../../TransactionStatus != "RJCT" and ../Type[contains(., "17") or contains (., "01") or contains (., "18") or contains (., "19") or contains (., "99")]])
Rather than that..
let $transaction := $b/Transaction[Transfer/Type[contains(., "17") or contains (., "01") or contains (., "18") or contains (., "19") or contains (., "99")]] and $b/Transaction[TransactionStatus != "RJCT"]
let $count := count($b/Transaction/Transfer/Type[contains(., "17") or contains (., "01") or contains (., "18") or contains (., "19") or contains (., "99")])
let $amount := sum($b/Transaction/Transfer/Amount[../Type[contains(., "17") or contains (., "01") or contains (., "18") or contains (., "19") or contains (., "99")]])
February 17, 2017 at 9:31 pm
It's very hard to see it when you have it formatted this way, but the two options aren't equivalent simply because the LET isn't processing a logical statement. I just had to reboot so I lost the formatting I had done, but in short you have this happening:
The INCORRECT version looks something like
let a:= <list of transactions of one of the types> AND <list of transactions where the status isn't rejected>
The CORRECT version
let a:= <list of transactions with one of the types AND aren't rejected>
When you refer to $b/transaction TWICE on the root level of the let statement, the let evaluates each criteria separately and appends the two lists to each other. It's an iterator clause by default not a joining clause.
If you were to move one bracket:
let $transaction := $b/Transaction[Transfer/Type[contains(., "17") or contains (., "01") or contains (., "18") or contains (., "19") or contains (., "99")]
] --move this one
and $b/Transaction[TransactionStatus != "RJCT"]
-- to here
both statements are now the same
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 18, 2017 at 2:56 am
Quick thought: the query posted is very inefficient with an execution plan that has literally hundreds of operators. This can be significantly simplified, here is an example.
😎
declare @bp_Xml xml
set @bp_Xml='<Root xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<Header>
<Scheme>BACS</Scheme>
<Source>2027</Source>
<Interface>SYSTEM</Interface>
<Channel>H2H</Channel>
<Target>VOCASTS</Target>
<FileName>BACS.STD18_28122016144100.001.2.txt</FileName>
<FileDate>2017-01-11</FileDate>
<FileReference>2027.100123.12345 .17011</FileReference>
<FileCreated>2017-01-11T00:00:00.0</FileCreated>
<TransactionCount>2</TransactionCount>
<TotalDebits>60.02</TotalDebits>
<TotalCredits>0.00</TotalCredits>
</Header>
<TransactionGroup>
<Transaction Transaction_ID="5315">
<Type>BACS</Type>
<TransactionStatus>RJCT</TransactionStatus>
<Debtor>
<ID_Type>CLIENT_NAME</ID_Type>
<ID_Value>JOHN 00000000001</ID_Value>
<Name>JOHN 00000000001</Name>
<Iso_Country>GB</Iso_Country>
</Debtor>
<Debit_Account>
<Bank_ID_Type>BACS</Bank_ID_Type>
<Account_Name>JOHN 00000000001</Account_Name>
<Code>a72908</Code>
<BasicNumber>09395668</BasicNumber>
<DirectDebit>
<Mandate>
<ID>WAGES 00000000001</ID>
<SignedOn>Tue Jan 10 17:49:37 GMT 2017</SignedOn>
</Mandate>
</DirectDebit>
</Debit_Account>
<Creditor>
<ID_Type>H2H_CLINET_ID</ID_Type>
<ID_Value>2027</ID_Value>
<Name>ARATHITEST</Name>
<Iso_Country>GB</Iso_Country>
</Creditor>
<Credit_Account>
<Bank_ID_Type>BACS</Bank_ID_Type>
<Account_Name>ARATHITEST</Account_Name>
<Code>010039</Code>
<BasicNumber>01059963</BasicNumber>
</Credit_Account>
<Transfer>
<EndToEndId>WAGES 00000000001</EndToEndId>
<SourceReference>JOHN 00000000001</SourceReference>
<Beneficiary_Reference>ALLOKAY 00000001</Beneficiary_Reference>
<Amount>000000030.01</Amount>
<Currency>GBP</Currency>
<Remittance_Detail>0000</Remittance_Detail>
<Type>17</Type>
</Transfer>
<Original>a729080939566801701003901059963000000000003001ALLOKAY 00000001WAGES 00000000001JOHN 00000000001</Original>
<Original_Value_Date>2017-01-12T00:00:00</Original_Value_Date>
</Transaction>
<Transaction Transaction_ID="5316">
<Type>BACS</Type>
<TransactionStatus>PNDG</TransactionStatus>
<Debtor>
<ID_Type>CLIENT_NAME</ID_Type>
<ID_Value>JOHN 00000000001</ID_Value>
<Name>JOHN 00000000001</Name>
</Debtor>
<Debit_Account>
<Account_Name>JOHN 00000000001</Account_Name>
<Code>772908</Code>
<BasicNumber>09395668</BasicNumber>
<DirectDebit>
<Mandate>
<ID>WAGES 00000000001</ID>
<SignedOn>Tue Jan 10 17:49:37 GMT 2017</SignedOn>
</Mandate>
</DirectDebit>
</Debit_Account>
<Creditor>
<ID_Type>H2H_CLINET_ID</ID_Type>
<ID_Value>2027</ID_Value>
<Name>ARATHITEST</Name>
</Creditor>
<Credit_Account>
<Account_Name>ARATHITEST</Account_Name>
<Code>010039</Code>
<BasicNumber>01059963</BasicNumber>
</Credit_Account>
<Transfer>
<EndToEndId>WAGES 00000000001</EndToEndId>
<SourceReference>JOHN 00000000001</SourceReference>
<Beneficiary_Reference>ALLOKAY 00000001</Beneficiary_Reference>
<Amount>000000030.01</Amount>
<Remittance_Detail>0000</Remittance_Detail>
<Type>17</Type>
</Transfer>
<Original>7729080939566801701003901059963000000000003001ALLOKAY 00000001WAGES 00000000001JOHN 00000000001</Original>
<Original_Value_Date>2017-01-12T00:00:00</Original_Value_Date>
</Transaction>
</TransactionGroup>
</Root>'
;WITH BASE_DATA (TRANXML,TRANType,TransactionStatus) AS
(
SELECT
TRANSACT.DATA.query('*') AS TRANXML
,TRANSACT.DATA.value('(Transfer/Type/text())[1]','CHAR(2)') AS TRANType
,TRANSACT.DATA.value('(TransactionStatus/text())[1]','CHAR(4)') AS TransactionStatus
FROM @bp_Xml.nodes('Root/TransactionGroup/Transaction') AS TRANSACT(DATA)
)
SELECT
BD.TRANXML
FROM BASE_DATA BD
WHERE BD.TransactionStatus <> 'RJCT'
AND BD.TRANType IN ('01','17','18','19','99');
February 20, 2017 at 3:07 am
Thank you both very much 🙂
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply