Home » SQL & PL/SQL » SQL & PL/SQL » format result of query
format result of query [message #269183] Fri, 21 September 2007 00:13 Go to next message
sonalshastry
Messages: 52
Registered: September 2007
Member
Hi all

i want to format my result of this query



SELECT cnm.cnm_ent_id,
       MAX(CNM.CNM_DATE)eq_last_date,
       sum(CNM.CNM_BROKERAGE_AMT)eq_brk
  FROM CONTRACT_NOTE_MASTER        CNM
 WHERE CNM.cnm_date >= '01-JAN-2007' and CNM.cnm_date <= '30-jun-2007' AND
       cnm.cnm_ent_id IN ('340449')
 GROUP BY cnm.cnm_ent_id
          
Union all 

SELECT dcm.dcn_ent_id,
       max(dcm.dcn_date),
       sum(dcm.dcn_brokerage_amt)
 FROM dtm_contract_note_master dcm
 WHERE dcm.dcn_date >= '1-jan-2007' and dcm.dcn_date <= '30-jun-2007'
       and dcm.dcn_ent_id IN ('340449')
       
        GROUP BY dcm.dcn_ent_id



result of the query comes like this

   CNM_ENT_ID   eq_last_date     eq_brk
1  340449	6/4/2007	 2572.4
2  340449	6/18/2007	 807.135



But i want the same result in below shown format

   CNM_ENT_ID eq_last_date  eq_brk   drv_last_date  drv_brk
1  340449     6/4/2007	    2572.4    6/18/2007      807.135



please help in this. & if posible please give me the code

because i have been trying it with case statement & decode function too but i am not getting the result


Best Regards
sonal
Re: format result of query [message #269190 is a reply to message #269183] Fri, 21 September 2007 01:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64124
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And did you search for "pivot"?

Regards
Michel
Re: format result of query [message #269202 is a reply to message #269190] Fri, 21 September 2007 02:40 Go to previous messageGo to next message
sonalshastry
Messages: 52
Registered: September 2007
Member
Hi Michel,

I am using 8i i think its not allowing me to use union all together with pivot

regards
sonal
Re: format result of query [message #269206 is a reply to message #269202] Fri, 21 September 2007 02:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64124
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is no problem with union all and pivot in 8i, iirc.
Post what you tried till now.

Regards
Michel
Re: format result of query [message #269256 is a reply to message #269206] Fri, 21 September 2007 06:41 Go to previous messageGo to next message
sonalshastry
Messages: 52
Registered: September 2007
Member


SELECT   e.ent_id ,
sum(case when CNM.cnm_date >= '01-JAN-2007' and CNM.cnm_date <= '30-jun-2007'
         then  CNM.CNM_BROKERAGE_AMT end)eq_brk,
sum(case when DCM.DCN_DATE >= '01-JAN-2007' and DCM.DCN_DATE <= '30-jun-2007'
         then  dcm.dcn_brokerage_amt end)drv_brk,
MAX(CNM.CNM_DATE)"Eq_last_date",
max(dcm.dcn_date)"drv_last_date"
FROM CONTRACT_NOTE_MASTER  CNM, dtm_contract_note_master dcm, entity_master e
where e.ent_id = cnm.cnm_ent_id
and e.ent_id = dcm.dcn_ent_id
AND cnm.cnm_ent_id IN ('340449')
GROUP BY e.ent_id



this query gives me righ format but data is wrong
the table that i am using is having maltiple records for every client id
as you suggested to use pivot it is not working out for me
it showa me error
so except pivot any other advice can you give

i will be very thankfull to you

Best Regards
sonal

[Updated on: Fri, 21 September 2007 07:01] by Moderator

Report message to a moderator

Re: format result of query [message #269272 is a reply to message #269256] Fri, 21 September 2007 07:40 Go to previous message
joy_division
Messages: 4641
Registered: February 2005
Location: East Coast USA
Senior Member
sonalshastry wrote on Fri, 21 September 2007 07:41
sum(case when CNM.cnm_date >= '01-JAN-2007' and CNM.cnm_date <= '30-jun-2007'
...
sum(case when DCM.DCN_DATE >= '01-JAN-2007' and DCM.DCN_DATE <= '30-jun-2007'




Invalid, invalid, invalid. Please read the concepts manual on how to properly deal with DATEs. You do not compare DATE columns to character strings.

Just because it worked in this one case, does not mean it will work for anyone else or for you when you try a idfferent client or the DBA changes an NLS parameter.
FOO SCOTT>l
  1* select 1 from dual where sysdate > '01-JAN-2007'
FOO SCOTT>/
select 1 from dual where sysdate > '01-JAN-2007'
                                   *
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected
Previous Topic: life after COMMIT??
Next Topic: insert into hints
Goto Forum:
  


Current Time: Wed Dec 07 06:42:03 CST 2016

Total time taken to generate the page: 0.06180 seconds