Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Inner Join with SubQuery Expand / Collapse
Author
Message
Posted Tuesday, June 11, 2013 9:11 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 25, 2014 5:40 AM
Points: 150, Visits: 393
I am running a Inner Join to the same table I am creating a Subquery on and I don't get the results I am expecting.

This is my query:
Select *
FROM (
Select S1.*, S2.Cnt , rn = ROW_NUMBER() OVER (PARTITION BY InvAmt_Sum ORDER BY ChkNbr DESC)
From FSW_NZ_JDE_Apr05_Sep12_C.dbo.StdPmt as S1
Inner join ( Select VndNbr, InvNbr, Sum(InvAmt) as InvAmt_Sum, Count(*) as Cnt
From FSW_NZ_JDE_Apr05_Sep12_C.dbo.StdPmt
Group By VndNbr, InvNbr) as S2
ON S1.InvNbr = S2.InvNbr
AND S1.VndNbr = S2.VndNbr
AND S1.InvAmt = S2.InvAmt_Sum
Where S2.Cnt > 2
AND InvAmt_Sum < 0

) NegQry
Where rn = 1

I get strange results. In my source table, I have these 6 records:

vndnbr | InvNbr | InvAmt
687840 | 423099 | -23.000000
687840 | 423099 | -23.000000
687840 | 423099 | 23.000000
603031 | 106541 | -781.410000
603031 | 106541 | -781.410000
603031 | 106541 | 781.410000

When I run the query,
I only get this record return in the table:
603031 | 106541 | -781.410000

But when i run the query to test it with this filter, it returns the 1 missing record:
Select *
-- Into AS_Workspace.dbo.StdPmt_InvAmt_SUM_TEST
FROM (
Select S1.*, S2.Cnt , rn = ROW_NUMBER() OVER (PARTITION BY InvAmt_Sum ORDER BY ChkNbr DESC)
From FSW_NZ_JDE_Apr05_Sep12_C.dbo.StdPmt as S1
Inner join ( Select VndNbr, InvNbr, Sum(InvAmt) as InvAmt_Sum, Count(*) as Cnt
From FSW_NZ_JDE_Apr05_Sep12_C.dbo.StdPmt
Group By VndNbr, InvNbr) as S2
ON S1.InvNbr = S2.InvNbr
AND S1.VndNbr = S2.VndNbr
AND S1.InvAmt = S2.InvAmt_Sum
Where S2.Cnt > 2
AND InvAmt_Sum < 0
and S2.VndNbr = '687840' and S2.InvNbr = '423099'
) NegQry
WHERE rn = 1

Results:
vndnbr | InvNbr | InvAmt
687840 | 423099 | -23.000000


Why does it do that?



Post #1462214
Posted Tuesday, June 11, 2013 9:27 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 11:56 AM
Points: 7,161, Visits: 13,231
here's a test scenario in a ready to use format, returning the expected(?) result.
So, I don't see anything wrong with the code.
declare @tbl table(vndnbr int, InvNbr int, InvAmt numeric(18,6),ChkNbr int)
insert into @tbl
SELECT 687840 , 423099 , -23.000000,1 union all
SELECT 687840 , 423099 , -23.000000,2 union all
SELECT 687840 , 423099 , 23.000000,3 union all
SELECT 603031 , 106541 , -781.410000,1 union all
SELECT 603031 , 106541 , -781.410000,1 union all
SELECT 603031 , 106541 , 781.410000,1


Select *
FROM (
Select S1.*, S2.Cnt , rn = ROW_NUMBER() OVER (PARTITION BY InvAmt_Sum ORDER BY ChkNbr DESC)
From @tbl as S1
Inner join ( Select VndNbr, InvNbr, Sum(InvAmt) as InvAmt_Sum, Count(*) as Cnt
From @tbl
Group By VndNbr, InvNbr) as S2
ON S1.InvNbr = S2.InvNbr
AND S1.VndNbr = S2.VndNbr
AND S1.InvAmt = S2.InvAmt_Sum
Where S2.Cnt > 2
AND InvAmt_Sum < 0

) NegQry
Where rn = 1





Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #1462229
Posted Tuesday, June 11, 2013 9:42 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 25, 2014 5:40 AM
Points: 150, Visits: 393
Here's the full code. I don't think the rest of it would affect the results, but you tell me?

Select S1.*, S2.Cnt, Cast(Null as Integer) as rn
Into AS_Workspace.dbo.StdPmt_InvAmt_SUM7
From FSW_NZ_JDE_Apr05_Sep12_C.dbo.StdPmt as S1
Inner join ( Select VndNbr, InvNbr, Sum(InvAmt) as InvAmt_Sum, Count(*) as Cnt
From FSW_NZ_JDE_Apr05_Sep12_C.dbo.StdPmt
Group By VndNbr, InvNbr) as S2
ON S1.InvNbr = S2.InvNbr
AND S1.VndNbr = S2.VndNbr
AND S1.InvAmt <> S2.InvAmt_Sum
AND S2.InvAmt_Sum > 0

Where S2.Cnt = 2

UNION

Select S1.*, S2.Cnt, Cast(Null as Integer) as rn
From FSW_NZ_JDE_Apr05_Sep12_C.dbo.StdPmt as S1
Inner join ( Select VndNbr, InvNbr, Sum(InvAmt) as InvAmt_Sum, Count(*) as Cnt
From FSW_NZ_JDE_Apr05_Sep12_C.dbo.StdPmt
Group By VndNbr, InvNbr) as S2
ON S1.InvNbr = S2.InvNbr
AND S1.VndNbr = S2.VndNbr
AND S1.InvAmt <> S2.InvAmt_Sum
AND S2.InvAmt_Sum > 0
Where S2.Cnt > 2
AND NOT EXISTS(Select InvAmt
From FSW_NZ_JDE_Apr05_Sep12_C.dbo.StdPmt as S3
Where S2.InvNbr = S3.InvNbr
AND S2.VndNbr = S3.VndNbr
AND S2.InvAmt_Sum = S3.InvAmt )


UNION

Select S1.*, S2.Cnt, Cast(Null as Integer) as rn
From FSW_NZ_JDE_Apr05_Sep12_C.dbo.StdPmt as S1
Inner join ( Select VndNbr, InvNbr, Sum(InvAmt) as InvAmt_Sum, Count(*) as Cnt
From FSW_NZ_JDE_Apr05_Sep12_C.dbo.StdPmt
Group By VndNbr, InvNbr) as S2
ON S1.InvNbr = S2.InvNbr
AND S1.VndNbr = S2.VndNbr
AND S1.InvAmt = S2.InvAmt_Sum
AND S2.InvAmt_Sum > 0
Where S2.Cnt > 0

UNION
-- Insert records to get Invoices with more than 2 records that don't equal 0 and have a negative amount of the sum of invamt.
--Pull in 1 of the 2 Negative records.
--Insert Into AS_Workspace.dbo.StdPmt_InvAmt_SUM2(VndNbr, VndName, InvNbr, InvDt, InvAmt, NetAmt, FInvAmt, DiscAmt, FDiscAmt, TaxBaseAmt, GSTAmt, FGstAmt, InvCurCd, InvStat, PmtTrms, BatNbr, PONbr, CstCntr, Cmpy, VchNbr, TrTyp, Descr, TaxCd, AdjDoc, Void, chknbr, ChkAmt, ChkCurr, ChkDt, ChkClearDt, ChkTyp, VndBnkAcct, AmtPaid, Chk_payId, ChkBatNbr, void_gl_date, VatPct, DiscPct, Cnt, rn)
Select *
FROM (
Select S1.*, S2.Cnt , rn = ROW_NUMBER() OVER (PARTITION BY InvAmt_Sum ORDER BY ChkNbr DESC)
From FSW_NZ_JDE_Apr05_Sep12_C.dbo.StdPmt as S1
Inner join ( Select VndNbr, InvNbr, Sum(InvAmt) as InvAmt_Sum, Count(*) as Cnt
From FSW_NZ_JDE_Apr05_Sep12_C.dbo.StdPmt
Group By VndNbr, InvNbr) as S2
ON S1.InvNbr = S2.InvNbr
AND S1.VndNbr = S2.VndNbr
AND S1.InvAmt = S2.InvAmt_Sum
Where S2.Cnt > 2
AND InvAmt_Sum < 0

) NegQry
WHERE rn = 1



Post #1462242
Posted Tuesday, June 11, 2013 9:51 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 11:56 AM
Points: 7,161, Visits: 13,231
I tried your code (with replaced table name) and it still return the same data as before (2 rows).




Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #1462246
Posted Tuesday, June 11, 2013 5:06 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 25, 2014 5:40 AM
Points: 150, Visits: 393
Ok, I think I found the issue, but don't know how to change this to fix it.

The problem seems to be with this code:
Select S1.*, S2.Cnt , rn = ROW_NUMBER() OVER (PARTITION BY InvAmt_Sum ORDER BY batNbr DESC)

specifically this:
rn = ROW_NUMBER() OVER (PARTITION BY InvAmt_Sum ORDER BY batNbr DESC)


I think this is creating an issue. The rn should count incrementally by VndNbr, InvNbr.



Post #1462424
Posted Tuesday, June 11, 2013 11:14 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 11:56 AM
Points: 7,161, Visits: 13,231
If you want to restart with 1 for each new combination of VndNbr, InvNbr then you should change it to
PARTITION BY VndNbr, InvNbr ORDER BY batNbr DESC




Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #1462452
Posted Wednesday, June 12, 2013 6:15 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 25, 2014 5:40 AM
Points: 150, Visits: 393
Thanks!
that fixed my problem.



Post #1462581
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse