Click here to Skip to main content
12,949,816 members (79,804 online)
Rate this:
 
Please Sign up or sign in to vote.
Hi
I am trying to get data Using Following Query but its not giving me desired result..

Set RSL = Nothing
If RSL.State = 1 Then RSL.Close
If Option2.Value = True Then
RSL.Open "Select BDD.BiltyNo, BDD.Article as Art, BDD.Consignee, BDD.Desti as Destination,  " & _
"BDD.Weight, BDD.Total AS Basic, (CASE WHEN BDD.Pymt_mode = 'TOPAY' THEN BDD.Total ELSE 0 END) as Topay,  " & _
"(CASE WHEN BDD.Pymt_mode = 'PAID' THEN BDD.Total ELSE 0 END) as Paid, " & _
"(CASE WHEN BDD.Pymt_mode = 'TBB' THEN BDD.Total ELSE 0 END) as TBB, BDD.pf as PF_Amt  " & _
"FROM Bilty_Detail_DELV as BDD, B_Detail_DELV_Crosing AS BDC  " & _
"WHERE BDD.BiltyNo <> BDC.BiltyNo AND BDD.Tmp_Weight = " & Text1(0).Text & " group by BDD.BiltyNo, BDD.Article, BDD.Consignee, BDD.Desti, BDD.Weight, BDD.Total, BDD.Pymt_mode, BDD.pf, BDD.Tmp_Weight", Con_Main, adOpenKeyset, adLockOptimistic


Some of Same records are exist in both table.

and i don't want that data which are exist in B_Detail_DELV_Crosing under Tmp_Weight = 1

Please Help..
Posted 13-Feb-13 5:45am
Comments
richcb 13-Feb-13 11:48am
   
You are trying to select from two tables without joining them. Use "Select Distinct" to weed out multiple records that are the same.
khan2010 13-Feb-13 11:52am
   
Thanks For reply so fast Richcb.
i tried Select Distinct but still its giving me that records which are exist in B_Detail_DELV_Crosing
richcb 13-Feb-13 16:59pm
   
You are selecting from that Table, if you don't want data from it, don't select from it.
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

I don't know what is RSL, but in the first line you set to RSL=Nothing. Next instructions are "ignored".

Set RSL = Nothing
If RSL.State = 1 Then RSL.Close
If Option2.Value = True Then
  Permalink  
Comments
khan2010 13-Feb-13 13:02pm
   
DIM RSL AS NEW ADODB.RECORDSET
Maciej Los 13-Feb-13 13:10pm
   
Read this: http://msdn.microsoft.com/en-us/library/office/aa141422%28v=office.10%29.aspx and you'll find an answer what to do next... ;)
khan2010 13-Feb-13 16:12pm
   
actually Records set is not my problem ,
my problem is how to in my scenario how to get desired data using posted query.
Maciej Los 13-Feb-13 16:23pm
   
Just test above query in SSMS (SQL Studio Management Studio).
khan2010 13-Feb-13 16:51pm
   
I tried.. it's not giving me desired result.
Maciej Los 13-Feb-13 17:00pm
   
Sorry, but only you can solve this problem. Try to use JOINS (LEFT, RIGHT, INNER), rather then cross join. You need to define relationships between tables.
Probably, this part: WHERE BDD.BiltyNo <> BDC.BiltyNo is an element which generate wrong result. Try to change it as fallow: FROM ... AS BDC INNER JOIN ... AS BDD ON BDC.BiltyNo = BDD.BiltyNo.
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 4

I have solved this my self. :-)

Submitting solution for future reference.

Thanks for Helping me. :-)


RSL.Open "Select Distinct(BDD.BiltyNo), BDD.Article as Art, BDD.Consignee, BDD.Desti as Destination,  " & _
"BDD.Weight, BDD.Total AS Basic, (CASE WHEN BDD.Pymt_mode = 'TOPAY' THEN BDD.Total ELSE 0 END) as Topay,  " & _
"(CASE WHEN BDD.Pymt_mode = 'PAID' THEN BDD.Total ELSE 0 END) as Paid, " & _
"(CASE WHEN BDD.Pymt_mode = 'TBB' THEN BDD.Total ELSE 0 END) as TBB, BDD.pf as PF_Amt  " & _
"FROM Bilty_Detail_DELV as BDD " & _
"WHERE BDD.BiltyNo NOT IN (SELECT DISTINCT(BDC.BiltyNo) FROM B_Detail_DELV_Crosing AS BDC) " & _
" AND BDD.Tmp_Weight = " & Text1(0).Text & _
" group by BDD.BiltyNo, BDD.Article, BDD.Consignee, BDD.Desti, BDD.Weight, BDD.Total,  " & _
" BDD.Pymt_mode, BDD.pf, BDD.Tmp_Weight", Con_Main, adOpenKeyset, adLockOptimistic
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

    Print Answers RSS
Top Experts
Last 24hrsThis month
OriginalGriff 5,749
CHill60 3,380
Maciej Los 2,953
Jochen Arndt 1,935
ppolymorphe 1,820


Advertise | Privacy | Mobile
Web02 | 2.8.170524.1 | Last Updated 16 Feb 2013
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100