We help IT Professionals succeed at work.

VB.NET and SQL 2000 - how to fill datagrid by results of 3 SQL queries

Terrace
Terrace asked
on
Medium Priority
360 Views
Last Modified: 2012-05-06
Trying to combine 3 querie results into 1 datagrid.
This is VB.NET Web app and SQL 2000 combined question I guess.

Basically Ideal data grid would look like this:

DateFund     MaxVol     SchVol     WireVol
01/01/09     20000     15000            0
01/02/09     0              10000         5000
01/03/09     12000       0                  0
01/04/09     12000       9000           6000
etc (listing ALL days in a year)

MaxVol, SchedVol, WireAmt are actually sums that come from 2 different tables.
(tblDTA and Lock).

I tried to run 1 query, where appropriate dates from both tables would be equal to each other, but I get a very limited result that actually does not show any rows if any of the 3 sums does not exists for a certain date.
Example.
Instead of getting all 4 rows like above, I get only last row (01/04/09) where ALL 3 sums exist for that certain date.  I understand that is the way SQL works, but I am trying to find a workaround.
I think I could run a separate, individual query for each table to get 3 results, 3 individual sums - but HOW to merge them into one, same datagrid and if a sum does not exist for a certain date, how to show 0 there????

All details in the code below.

Thanks




The combined query used to fill mu datagrid currently:
 
SELECT transID, DateFund, MaxVol, SUM(ln_amount) As SchVol, 
 SUM(a_wire_amt) As WireVol  
FROM tblDTA D, lock L where D.Datefund= L.date_fund 
and D.datefund=L.a_wire_date 
and Datefund between '01/01/2009'  and '02/12/2009'  
group by datefund, maxvol, transid
 
The query above produces ONLY 3 rows for Datefund between '01/01/2009'  and '02/12/2009'. It should show 43 rows with results for each of 43 dates
 
If I separate this query into 3 basic ones:
Query 1:
select datefund, maxvol from tblDTA where datefund between '01/01/2009'  and '02/12/2009'  
(this query produces 43 rows showing 43 days since Jan 1 and MaxVol = 0 - by default)
 
Query 2:
select a_wire_dat, sum(a_wire_amt) as WireVol from lock where a_wire_dat between '01/01/2009' and '02/12/2009'
group by a_wire_dat
(this query produces 29 rows showing 29 days where a_wire_dat  actually exists)
In datagrid I must somehow show the other 14 days where a_wire_dat does not exist as WireVol  = 0
 
Query 3:
select date_fund, sum(ln_amount)SchVol from lock where date_fund between '01/01/2009' and '02/12/2009'
group by date_fund
(this query produces 35 rows showing 35 days where date_fund actually exists)
In datagrid I must somehow show the other 8 days where date_fund does not exist as WireVol  = 0

Open in new window

Comment
Watch Question

Software Applications Developer / Integrator
CERTIFIED EXPERT
Commented:
Give a try to some outer joins on the table with the missing rows like follows...
I don't have your table, but it should go something like the following.

SELECT transID, DateFund, MaxVol, SUM(ln_amount) As SchVol,
 SUM(a_wire_amt) As WireVol  
FROM tblDTA D
LEFT OUTER JOIN lock L ON  D.Datefund= L.date_fund
and D.datefund=L.a_wire_date
and D.Datefund between '01/01/2009'  and '02/12/2009'  
group by D.datefund, maxvol, transid

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
Thank you for the fast reponse.

I tried Outer Joins, and seems to work, so SQL side of my problem seems to be resolved.
I can fill datagrid with just 1 query, as I initially set it up.

The values for SchVol and WireVol that don't exist for certain dates are shown as NULL.
Now I need to see how to show Null as 0 in datagrid.

Thanks
Daniel ReynoldsSoftware Applications Developer / Integrator
CERTIFIED EXPERT

Commented:
In your select, try using isnull() or coalesce() to return a value.
like so
SELECT transID, DateFund, IsNull(MaxVol,0), IsNull(Sum(ln_amount),0) as SchoVol, IsNull(Sum(a_wire_amt),0) AS WireVol
Daniel ReynoldsSoftware Applications Developer / Integrator
CERTIFIED EXPERT

Commented:
The isnull() function returns the value of the first item in parethesis unless it is null, in the case it is null, it returns the second value

coalesce goes one further, it will check each value in order for null and return the next value...
Coalesce(myfield, yourfield, 0)

Author

Commented:
Thank you very much.

Author

Commented:
Actually, I was wrong.
On the first glance, everything seems OK, but those SUMS are not working properly. It seems like instead of SUM of few recoreds, I get only 1 record per date.
I'll play with it more now, but if you know what the reason is, let me know
Thanks
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.