Solved

# SQL Statment

Posted on 2009-07-07
310 Views
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
``````
0
Question by:ezaldeen

LVL 6

Expert Comment

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 142

Assisted Solution

Guy Hengel [angelIII / a3] earned 150 total points
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
``````
0

LVL 4

Assisted Solution

JonasMalmsten earned 20 total points
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

LVL 6

Assisted Solution

divyeshhdoshi earned 20 total points
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

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 142

Assisted Solution

Guy Hengel [angelIII / a3] earned 150 total points
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
``````
0

Author Comment

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 142

Assisted Solution

Guy Hengel [angelIII / a3] earned 150 total points
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

Thanks
But still not solve it , it showing error , pls see the attachment
untitled.bmp
0

LVL 142

Assisted Solution

Guy Hengel [angelIII / a3] earned 150 total points
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
``````
0

Author Comment

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 142

Assisted Solution

Guy Hengel [angelIII / a3] earned 150 total points
sorry. ms access it's iif and not if in the query:
``````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
``````
0

Author Comment

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 142

Assisted Solution

Guy Hengel [angelIII / a3] earned 150 total points
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

ezaldeen earned 0 total points
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

fine
0

## Join & Write a Comment Already a member? Login.

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…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

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

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

#### Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!