March 23, 2015 at 4:06 am
I have Two tables @master and @child
Master Table :
MasterID EntryNumber BranchId IsstockIn
1 1 1 1
2 1 1 0
Child Table:
CEntryNumber CBranchID EntryQty
1 1 10
1 1 20
1 1 -5
1 1 -4
My Query:
Select SEC.EntryQty from Item.StockEntryChild SEC
where SEC.CEntryNo =
(
select SEM.EntryNumber from item.StockEntryMaster SEM
where SEC.CBranchID=SEM.BranchID and SEC.CEntryNo=SEM.EntryNumber and SEM.MasterID=1 and SEM.isStockIn=1
)
My Result:
EntryQty
10
20
-5
-4
Expected Result:
10
20
March 23, 2015 at 4:20 am
It would be useful if you could post the table schema and script to populate it with your test data. Reading your code the results look to be correct. All 4 rows in the child table meet the condition in the where clause. The only condition its working with is CEntryNo = 1 and all child table rows meet trat criteria.
Perhaps it will not based on your data but in theory this could error as the sub select could return more than one row. Try using a JOIN rather than the sub select. It wont return a different result as you are asking for all child rows with a CEntryNo = 1 but it might help you to spot the problem.
March 23, 2015 at 5:39 am
roger.price-1150775 (3/23/2015)
It would be useful if you could post the table schema and script to populate it with your test data. Reading your code the results look to be correct. All 4 rows in the child table meet the condition in the where clause. The only condition its working with is CEntryNo = 1 and all child table rows meet trat criteria.Perhaps it will not based on your data but in theory this could error as the sub select could return more than one row. Try using a JOIN rather than the sub select. It wont return a different result as you are asking for all child rows with a CEntryNo = 1 but it might help you to spot the problem.
CREATE TABLE [dbo].[master](
[masterid] [int] IDENTITY(1,1) NOT NULL,
[entrynumber] [int] NULL,
[branchid] [int] NULL,
[isstockin] [bit] NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[master] ON
INSERT [dbo].[master] ([masterid], [entrynumber], [branchid], [isstockin]) VALUES (1, 1, 1, 1)
INSERT [dbo].[master] ([masterid], [entrynumber], [branchid], [isstockin]) VALUES (2, 1, 1, 0)
SET IDENTITY_INSERT [dbo].[master] OFF
CREATE TABLE [dbo].[child](
[centrynumber] [int] NULL,
[cbranchid] [int] NULL,
[entryqty] [int] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[child] ([centrynumber], [cbranchid], [entryqty]) VALUES (1, 1, 10)
INSERT [dbo].[child] ([centrynumber], [cbranchid], [entryqty]) VALUES (1, 1, 20)
INSERT [dbo].[child] ([centrynumber], [cbranchid], [entryqty]) VALUES (1, 1, -5)
INSERT [dbo].[child] ([centrynumber], [cbranchid], [entryqty]) VALUES (1, 1, -4)
Query with Join:
Select SEC.EntryQty
from child SEC
inner join master SEM on SEC.CBranchID=SEM.BranchID and SEC.centrynumber=SEM.EntryNumber
where SEM.MasterID=1 and SEM.isStockIn=1
Actual Result :
10
20
-5
-4
March 23, 2015 at 6:19 am
Why should the two negative values not appear in the results?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 23, 2015 at 6:57 am
Because it belongs to isstockin = 0 for your reference I have one more column in @child table named as childMasterID where associated masterid in master tables will be inserted accordingly
centrynumbercbranchidentryqty childMasterID
1 1 10 1
1 1 20 1
1 1 -5 2
1 1 -4 2
But the team, we have decided not to use childMasterId in join condition because in local server it will match with @master table but when we are pushing to root server where childMasterID will not match will @master table so only i have ignored that column in script ....
March 23, 2015 at 7:45 am
pradeep.joy21 (3/23/2015)
for your reference I have one more column in @child table named as childMasterID where associated masterid in master tables will be inserted accordingly
And we were supposed to guess that?
Without that column, there's no way to ignore negatives short of filtering for entryqty > 0, which I suspect is not what you want. This is because all four rows that you've posted for the child table have the same centrynumber and cbranchid, so in a join all four join to both rows in master.
You can't say that the row with entryqty = -5 belongs to isstockin because the only columns available to link to the master table are centrynumber and cbranchid, and the row with entryqty = 20 has exactly the same centrynumber and cbranchid values
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 23, 2015 at 11:16 pm
Thanks for your valuable time,Based upon your suggestion i have just modified my query as follows
For SEM.IsStockIn=1
Select SEC.EntryQty
from child SEC
inner join master SEM on SEC.CBranchID=SEM.BranchID and SEC.centrynumber=SEM.EntryNumber
where SEM.MasterID=1 and SEM.isStockIn=1 and SEC.entryQty >0
For SEM.IsStockIn=0
Select SEC.EntryQty
from child SEC
inner join master SEM on SEC.CBranchID=SEM.BranchID and SEC.centrynumber=SEM.EntryNumber
where SEM.MasterID=2 and SEM.isStockIn=0 and SEC.entryQty < 0
As per your guideline the query works perfectly for IsStockIn=0 always @child entryqty will be negative values and for IsstockIn =1 always @child entryqty will be positive values
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply