Trying to display the null values

[shaleen7]

Scenerio:
When we enter an application, the date that the app is entered is automatically assigned to the app. I'm trying to display all employees name regardless of whether they submitted an application for the month of March.

Desired Results for March
Hunt 5
Jaso 10
Kimb 13
Robb


Current Results for March
Hunt 5
Jaso 10
Kimb 13

Here is my SQL:
SELECT TABLENAMES "NAME", COUNT(TABLEAPPNO) "APP NO"

FROM TABLENAMES, TABLEAPPNO

WHERE TABLENAMES.IDEING=TABLEAPPNO.IDERES(+) AND

(VRPT_TABPRO.DATOUV BETWEEN TO_DATE('20040301','YYYYMMDD') AND TO_DATE('20040331','YYYYMMDD')

Any suggestions?
Thanks

 

[Krishna751204]

Hi Shaleen,

Is the query is working without group by function? If not add the goup by clause in the query.

Regards
Krishna Reddy
mckreddy@visualsoft-tech.com

 

[elam68]

hi,
as krisha said it will not work without "group by".

add "group by" into that
good luck
elam

http://www.iteq.com.au

 

[shaleen7]

The group by is there. I neglected to copy it over.

 

[Krishna751204]

Your sql query seems to be correct if the group by clause is present. Test the following three cases to identify the problem.

1. Modify the Where condition as follows and check either the desired records are coming.

WHERE TABLENAMES.IDEING(+)=TABLEAPPNO.IDERES

2. Modify the select statement as follows and check either the desired records are coming.

SELECT TABLENAMES "NAME", nvl(COUNT(TABLEAPPNO),0) "APP NO"

3. Remove the date checking condition in the where clause and check it.

Please note that in all the three cases the remaining sql statement is same, only modify the specific conditions and test it.

Regards
Krishna Reddy
mckreddy@visualsoft-tech.com

 

[shaleen7]

Thanks for the advice. I applied your suggestions to my query.

By using the query below I was able display all employees name regardless of whether they submitted an application.

SELECT TABLENAMES "NAME", NVL(COUNT(TABLEAPPNO),0) "APP NO"
FROM TABLENAMES, TABLEAPPNO
WHERE TABLENAMES.IDEING=TABLEAPPNO.IDERES(+)

however I have to place this date range on the query

AND (VRPT_TABPRO.DATOUV BETWEEN TO_DATE('20040301','YYYYMMDD') AND TO_DATE('20040331','YYYYMMDD')

Whenever I add the date range my query goes back to displaying the names of the employees who have submitted apps in March. I want to display all employee names regardless of whether they submitted an application for the month of March.

Any more suggestions? Many thanks....

 

[sem]

When you need outer join, all conditions should be "outered":

...AND (VRPT_TABPRO.DATOUV(+) BETWEEN TO_DATE('20040301','YYYYMMDD') AND TO_DATE('20040331','YYYYMMDD')

Regards, Dima

http://seminihin.narod.ru

 

[Krishna751204]

If the above solution given by Dima is not working try the following query.

SELECT TABLENAMES "NAME", NVL(COUNT(TABLEAPPNO),0) "APP NO"
FROM TABLENAMES, TABLEAPPNO
WHERE TABLENAMES.IDEING=TABLEAPPNO.IDERES(+)
AND (VRPT_TABPRO.DATOUV BETWEEN TO_DATE('20040301','YYYYMMDD') AND TO_DATE('20040331','YYYYMMDD')
Union
SELECT TABLENAMES "NAME", NVL(COUNT(TABLEAPPNO),0) "APP NO"
FROM TABLENAMES, TABLEAPPNO
WHERE TABLENAMES.IDEING=TABLEAPPNO.IDERES(+)
AND VRPT_TABPRO.DATOUV is Null

Here I used the union clause and changed the where condition in the second sql statement, both statements are same, only the where condition is changed in the second conditon.

Hope this will solve your problem.

Regards
Krishna Reddy M
mckreddy@visualsoft-tech.com