?
Solved

SQL Statment

Posted on 2009-07-07
16
Medium Priority
?
319 Views
Last Modified: 2012-05-07
I have 2 Tabels , one showing the clinc data and oher there is amout of invoice I want to run report shwoing all records of clnc table and the total amount of invoice even null value during period time ... pls check my code which is correct but it showing only the records have figures and the clnc which have nill values it not appearing ... pls help us
SELECT a.clnna, a.clnc, SUM(b.total) AS total
FROM clnc a, invmaster b
WHERE a.clnc = b.clnc  AND 
    b.inv_date BETWEEN ? AND ?
GROUP BY a.clnna, a.clnc

Open in new window

0
Comment
Question by:ezaldeen
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
16 Comments
 
LVL 6

Expert Comment

by:jwenting
ID: 24792225
It doesn't show those records because they don't have any child records.
The where clause excludes any records in clnc for which no records exist in invmaster (assuming no records exist in invmaster for which the clnc column is NULL, which would be a logical assumption).

You'd have to use something like a union on clnc to get records there with no child records as well.
0
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 600 total points
ID: 24792238
what database?
SELECT a.clnna, a.clnc, SUM(b.total) AS total
FROM clnc a
LEFT JOIN invmaster b
  ON a.clnc = b.clnc  AND 
 AND b.inv_date BETWEEN ? AND ?
GROUP BY a.clnna, a.clnc

Open in new window

0
 
LVL 4

Assisted Solution

by:JonasMalmsten
JonasMalmsten earned 80 total points
ID: 24792239
You need to do an "outer left join", not sure which database you use, but syntax for oracle would be:

SELECT a.clnna, a.clnc, SUM(b.total) AS total
FROM clnc a, invmaster b
WHERE a.clnc = b.clnc(+)  AND
    (b.clnc is null or b.inv_date BETWEEN ? AND ?)
GROUP BY a.clnna, a.clnc
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 6

Assisted Solution

by:divyeshhdoshi
divyeshhdoshi earned 80 total points
ID: 24792247
SELECT a.clnna, a.clnc, SUM(b.total) AS total
FROM clnc a, invmaster b
WHERE a.clnc = b.clnc(+)  AND
    b.inv_date BETWEEN ? AND ?
GROUP BY a.clnna, a.clnc
0
 

Author Comment

by:ezaldeen
ID: 24792479
Thanks for multi answers ...
This Access Database ....
I try to run SQl Statment which is given by JONAS & DIVYE.. but it not work fine
I use dataenvirment tool with VB6 ...
can any one give best statment work fine ..
0
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 600 total points
ID: 24792508
ms access:
SELECT a.clnna, a.clnc, SUM(b.total) AS total
FROM clnc a
LEFT OUTER JOIN invmaster b
  ON ( a.clnc = b.clnc  AND 
 AND b.inv_date BETWEEN ? AND ? )
GROUP BY a.clnna, a.clnc

Open in new window

0
 

Author Comment

by:ezaldeen
ID: 24792801
Thanks ange....
It's work fine , but the field of (Total) showing NULL if there is no data , I need to show it as (0) Zero
0
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 600 total points
ID: 24792850
SELECT a.clnna, a.clnc, ISNULL(SUM(b.total),0) AS total
FROM clnc a
LEFT OUTER JOIN invmaster b
  ON ( a.clnc = b.clnc  AND
 AND b.inv_date BETWEEN ? AND ? )
GROUP BY a.clnna, a.clnc
0
 

Author Comment

by:ezaldeen
ID: 24793233
Thanks
But still not solve it , it showing error , pls see the attachment
untitled.bmp
0
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 600 total points
ID: 24793317
sorry. ..
SELECT a.clnna, a.clnc, IF(ISNULL(SUM(b.total),0,SUM(b.total)) AS total
FROM clnc a
LEFT OUTER JOIN invmaster b
  ON ( a.clnc = b.clnc  AND 
 AND b.inv_date BETWEEN ? AND ? )
GROUP BY a.clnna, a.clnc

Open in new window

0
 

Author Comment

by:ezaldeen
ID: 24793419
Dear ...
I use Dataenvirment with VB6 ...
your statment to work with this >>> I try many times still there is a problem ... still error coming
the IF statment not work into select statment with dataenvirment in VB6 ... pls search another way
0
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 600 total points
ID: 24793519
sorry. ms access it's iif and not if in the query:
http://www.techonthenet.com/access/functions/advanced/iif.php
SELECT a.clnna, a.clnc, IIF(ISNULL(SUM(b.total),0,SUM(b.total)) AS total
FROM clnc a
LEFT OUTER JOIN invmaster b
  ON ( a.clnc = b.clnc  AND 
 AND b.inv_date BETWEEN ? AND ? )
GROUP BY a.clnna, a.clnc

Open in new window

0
 

Author Comment

by:ezaldeen
ID: 24793596
Thanks ...
I think this will work into access application but not with data envirment in VB6 ... I take it copy and paste on our vb6 application but not work and still there is an error
0
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 600 total points
ID: 24793658
I never worked with dataenvironment, so I don't know what the problem could be, at least not without seeing the EXACT ERROR !
remember, we are not sitting in front of your screen. I don't have any crystal balls ...
0
 

Accepted Solution

by:
ezaldeen earned 0 total points
ID: 24793677
Thanks Alot ...
I found this and it's work fine Now :
***********************************************************
SELECT a.clnna, a.clnc, SUM(IIf(isnull(b.total), 0, b.total))
    AS total
FROM clnc a LEFT OUTER JOIN
    invmaster b ON (a.clnc = b.clnc AND b.inv_date BETWEEN
    ? AND ?)
GROUP BY a.clnna, a.clnc
***********************************************************************
Thanks ...
0
 

Author Comment

by:ezaldeen
ID: 25003483
fine
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses

801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question