Home » RDBMS Server » Server Administration » Help required on following query
| Help required on following query [message #372720] |
Mon, 05 March 2001 05:01  |
Ashesh Dave
Messages: 2
Registered: March 2001
|
Junior Member |
|
|
In following query, I am getting the answer "No data found" in case of any one criteria (TYPE_DEST=1) fails for any ND. Actually what I need is in all summation columns in each row, summation from COM_AUT table for given criteria should come. In case of no record in COM_AUT table for the given criteria 0.0 should be added instead of current result "No data found'
Can anybody help me?
***************************************************
select a.NCLI,a.NDOS,a.ND,b.NOM,b.PRENOM1,b.PRENOM2,a.MNT_CONSOCPT,(a.MNT_ABO+a.
MNT_REGABO),a.MNT_TAXE_FACT,(a.MNT_HT_FACT+a.MNT_TAXE_FACT),sum(c.MNT_COM),sum(d
.MNT_COM),sum(e.MNT_COM),sum(f.MNT_COM)
from H_EXPEDITION a,ADRESSE b, COM_AUT c, COM_AUT d, COM_AUT e, COM_AUT f
where a.NCLI=b.NCLI
and b.NADR=(select NADR_FACT from DOSSIER
where NCLI=a.NCLI
and NDOS=a.NDOS)
and a.AN_FACT=1
and a.PER_FACT = 3
and a.GROUPE_FACT='A'
and a.NDOS=c.NDOS
and a.NCLI=c.NCLI
and a.ND=c.ND
and a.AN_FACT=c.AN_FACT
and a.PER_FACT=c.PER_FACT
and a.GROUPE_FACT=c.GROUPE_FACT
and c.TYPE_dEST IN ('3','6')
and a.NDOS=d.NDOS
and a.NCLI=d.NCLI
and a.ND=d.ND
and a.AN_FACT=d.AN_FACT
and a.PER_FACT=d.PER_FACT
and d.TYPE_DEST IN ('1')
and a.NDOS=e.NDOS
and a.NCLI=e.NCLI
and a.ND=e.ND
and a.AN_FACT=e.AN_FACT
and a.PER_FACT=e.PER_FACT
and a.GROUPE_FACT=e.GROUPE_FACT
and e.ND_APPELE LIKE ('079%')
and a.NDOS=f.NDOS
and a.NCLI=f.NCLI
and a.ND=f.ND
and a.AN_FACT=f.AN_FACT
and a.PER_FACT=f.PER_FACT
and f.ND_APPELE LIKE ('077%')
and a.GROUPE_FACT=f.GROUPE_FACT
GROUP BY a.NCLI,a.NDOS,a.ND,b.NOM,b.PRENOM1,b.PRENOM2,a.MNT_CONSOCPT,(a.MNT_ABO+
a.MNT_REGABO),a.MNT_TAXE_FACT,(a.MNT_HT_FACT+a.MNT_TAXE_FACT)
/
|
|
|
|
| Re: Help required on following query [message #372721 is a reply to message #372720] |
Mon, 05 March 2001 09:20  |
Madhav Kasojjala
Messages: 42
Registered: November 2000
|
Member |
|
|
If you have an sql like
select col_a cA,col_b cB,col_c cC
from table_1
where....
group by... that gives no data and you need at least a row with '0's then
you can add union like this.
select col_a cA,col_b cB,col_c cC
from table_1
where....
group by ....
union
select 0 cA, 0 cB, 0 cC
from dual
It was quite confusing to get a grasp of your problem the way it was presented hope this helps
else rephrase the question
Thanks
Madhav
|
|
|
|
| Re: Help required on following query [message #372722 is a reply to message #372721] |
Mon, 05 March 2001 09:42 |
Ashesh Dave
Messages: 2
Registered: March 2001
|
Junior Member |
|
|
Dear madhav,
Thanks for help.
My porblem is I do not have data with 0 values and still I want to fetch the rows. The real problem is in the same table COM_AUT, there are various calls with different kind of type_dest. I have to get the summation for lets say all records of COM_AUT for type_dest =1 in one column and at the same time in same row, in another column, summation of all records of COM_AUT for type_dest=3.
Will it be possible?
|
|
|
|
| Re: Help required on following query [message #372728 is a reply to message #372722] |
Tue, 06 March 2001 08:08 |
Madhav Kasojjala
Messages: 42
Registered: November 2000
|
Member |
|
|
Hi Ashesh,
Here's the solution.
Say you need to find count(*) for each type_dest.
Build on this idea OK.
Say you have type_dest values as 0,1,2,3,4.
And you need count of each records for each type_dest in one row horizontally.
Then the query will be:
select a.cnt_type_dest_0,
b.cnt_type_dest_1,
c.cnt_type_dest_2,
d.cnt_type_dest_3
from
(Select count(*) cnt_type_dest_0
from Com_Aut
where type_dest = 0
group by type_dest) a,
(Select count(*) cnt_type_dest_1
from Com_Aut
where type_dest = 1
group by type_dest) b,
(Select count(*) cnt_type_dest_2
from Com_Aut
where type_dest = 2
group by type_dest) c,
(Select count(*) cnt_type_dest_3
from Com_Aut
where type_dest = 3
group by type_dest) d;
This usage of giving SELECT statement in FROM clause is called in-line views where the main query is operated on the result sets of each of SELECT statements which we named as Table a,b,c and d.
I think this will solve your problem.
As of to avoid no data found situation
you need to use the same UNION clause which I gave you yesterday.
So above query will be like this:
select a.cnt_type_dest_0,
b.cnt_type_dest_1,
c.cnt_type_dest_2,
d.cnt_type_dest_3
from.......
UNION
SELECT 0 a.cnt_type_dest_0,
0 b.cnt_type_dest_1,
0 c.cnt_type_dest_2,
0 d.cnt_type_dest_3
from dual a,dual b,dual c, dual d;
Hope this helps
Madhav
|
|
|
|
|
|
Goto Forum:
Current Time: Tue Jun 18 15:51:23 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
