Won't display row when values are Null 2

[nikolas916]

I'm using MS Query, and want to display all rows of a certain column even if the other columns associated with it are Null. For example...

SELECT SO_LN.MINOR_CD,
SUM(DECODE(ORD_TP_CD,'CRM',-QTY,'SAL',QTY,0)),
SUM(DECODE(ORD_TP_CD,'CRM',-QTY,'MCR',-QTY,QTY)*UNIT_PRC),
FROM SALES.SO SO, SALES.SO_LN SO_LN
WHERE SO.DEL_DOC_NUM = SO_LN.DEL_DOC_NUM
GROUP BY SO_LN.MINOR_CD

I want it to show sales by minor code even if no sales were done in a particular minor code. I want to just show zeros. I've tried using nvl(x,0) with no success. Anyone have any suggestions?

 

[rac2]

Possibly one of the tables does not have a row when there are no sales. In order to produce a row in the query you must use an outer join. The query you posted is a simple join, also called an inner join. It could be written like this -

SELECT SO_LN.MINOR_CD,
       SUM(DECODE(ORD_TP_CD,'CRM',-QTY,'SAL',QTY,0)),
       SUM(DECODE(ORD_TP_CD,'CRM',-QTY,'MCR',-QTY,QTY)*UNIT_PRC)

FROM SALES.SO_LN SO_LN
JOIN SALES.SO SO  ON  SO.DEL_DOC_NUM = SO_LN.DEL_DOC_NUM

GROUP BY SO_LN.MINOR_CD

Assuming that SALES.SO is the sales table which may not have any rows for some of the MINOR_CDs, the outer join would be -

SELECT SO_LN.MINOR_CD,
       SUM(DECODE(ORD_TP_CD,'CRM',-QTY,'SAL',QTY,0)),
       SUM(DECODE(ORD_TP_CD,'CRM',-QTY,'MCR',-QTY,QTY)*UNIT_PRC)

FROM SALES.SO_LN SO_LN
LEFT JOIN SALES.SO SO  ON  SO.DEL_DOC_NUM = SO_LN.DEL_DOC_NUM

GROUP BY SO_LN.MINOR_CD

You may also use the keywords INNER JOIN in the simple join, and LEFT OUTER JOIN in the outer join.

I am not familiar with the MS Query application you are using. Would that be Access or SQL Server?

 

[digitalparts]

Is there a way, by changing this SQL slightly, that you can put a 0 in each place there is no value (null value)?

Thanks,

Paul

 

[rac2]

SQL Server has a function ISNULL(column_name, alt_value) which returns the alt_value if there is no value for the column in a particular row. Other RDBMS will have a comparable function. I dont know the Oracle function. The DECODE(ORD_TP_CD,'CRM',-QTY,'SAL',QTY,0) might return 0 when ORD_TP_CD is null. Access has the IIf(condition, value_when_true, value_when_false) function which could do that.

 

[graabein]

I have the same problem and have tried different variations of left-right inner-outer joins with no luck... Here are my tables:

Sale
Date       | ProductID
2005-01-10 | 1
2005-01-12 | 4
2005-01-12 | 1
2005-01-11 | 2

Product
ID | Name
1  | Chocolate
2  | Soda
3  | Pizza
4  | Cigarettes

The intended result is:

Result
ProductID | Count
1         | 2
2         | 1
3         | 0
4         | 1

I want to fully outer join the product table even though there are no sale rows for a given period *sigh* ... Anyone?


graabein

 

[r937]

graabein, a full outer join would include

- matched rows
- rows from Product that aren't matched in Sale
- rows from Sale that aren't matched in Product

since the third of these is impossible (unless you forgot to declare relational integrity, or have sloppy application code), therefore all you need is a left outer join

select Product.ID as ProductID
     , count(Sale.ProductID)
  from Product
left outer
  join Sale
    on Product.ID  
     = Sale.ProductID
group
    by Product.ID

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)

 

[digitalparts]

I finally got it working - all you need is a left outer join. A full outer join would work the other way as well - which is not possible with referencial integrity enforced I don't think.

 

[graabein]

Thanks guys, but that's exactly the query I'm using and I still get it wrong! I'll post my real table defs and query...

Stryk
  StrykID (varchar(14), Not Null)
  Dato (datetime, Null)
  AvdID (int, Null)
  SekID (int, Null)
  ArsakNr (int, Null)
  Kommentar (varchar(80), Null)
  AntElektiv (int, Null)

Arsak
  ArsakNr (int, Not Null)
  Arsak (varchar(80), Null)
  ArsakKort (varchar(50), Null)

My query and result:

select
	a.arsaknr, 
	count(s.arsaknr) as antall 
from 
	Arsak a left outer join
	Stryk s on a.arsaknr=s.arsaknr
where 
	year(s.dato) = 2002 and 
	s.avdid = 1 
group by 
	a.arsaknr 
order by 
	a.arsaknr

arsaknr     antall      
----------- ----------- 
1           4
2           2
5           2
8           2
10          4
13          3
14          1
16          3
21          7
22          8
23          2
25          5

select arsaknr from arsak order by arsaknr

arsaknr     
----------- 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

 

[PHV]

Replace this:
where
year(s.dato) = 2002 and
s.avdid = 1
By this:
where
(year(s.dato) = 2002 Or s.dato Is Null) and
(s.avdid = 1 Or s.avdid Is Null)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[graabein]

Nope, still the same...

select
    a.arsaknr,
    count(s.arsaknr) as antall
from
    Arsak a left outer join
    Stryk s on a.arsaknr=s.arsaknr
where
    (year(s.dato) = 2002 or s.dato is null) and
    (s.avdid = 1 or s.avdid is null)
group by
    a.arsaknr
order by
    a.arsaknr

arsaknr     antall      
----------- ----------- 
1           4
2           2
5           2
8           2
10          4
13          3
14          1
16          3
21          7
22          8
23          2
25          5

 

[PHV]

select
a.arsaknr,
(SELECT Count(*) FROM Stryk s
WHERE s.arsaknr=a.arsaknr And year(s.dato)=2002 and s.avdid=1
) as antall
from
Arsak a
order by
a.arsaknr


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[r937]

the reason your left outer join isn't working is because you hvae conditions on the right table in the WHERE clause

conditions on the right table need to be moved into the ON clause

select a.arsaknr
     , count(s.arsaknr) as antall
  from Arsak a 
left outer 
  join Stryk s 
    on a.arsaknr = s.arsaknr
   [b]and year(s.dato) = 2002 
   and s.avdid = 1 [/b]
group 
    by a.arsaknr
order 
    by a.arsaknr

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)

 

[graabein]

Ah, I see! Excellent! Thank you very much!