Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: VB6 SQL Server2008
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 6:45am
Comments
richcb at 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 at 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 at 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 at 13-Feb-13 13:02pm
   
DIM RSL AS NEW ADODB.RECORDSET
Maciej Los at 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 at 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 at 13-Feb-13 16:23pm
   
Just test above query in SSMS (SQL Studio Management Studio).
khan2010 at 13-Feb-13 16:51pm
   
I tried.. it's not giving me desired result.
Maciej Los at 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. Smile | :)
 
Submitting solution for future reference.
 
Thanks for Helping me. Smile | :)
 

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)



Advertise | Privacy | Mobile
Web03 | 2.8.141220.1 | Last Updated 16 Feb 2013
Copyright © CodeProject, 1999-2014
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