OraFAQ Forum: Reports & Discoverer » It is showing a symbol like C/D in data model.

Home » Developer & Programmer » Reports & Discoverer » It is showing a symbol like C/D in data model. (Report Builder 9.0.4.0.33,Windows XP)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

It is showing a symbol like C/D in data model. [message #295998]

Thu, 24 January 2008 03:01

sasipalarivattom
Messages: 121
Registered: June 2007
Location: Cochin ( INDIA )

Senior Member

Hi all,

I am not much familiar with reports.
Now when i try to create a report,
it is showing a symbol in the 'query' box in the data model.
I didn't see something like this earlier.
Can you please tell me what it means and when it comes.
I shall attach the screen shot .

Thanks & Regards
Sasi...

Attachment: report.JPG
(Size: 77.76KB, Downloaded 707 times)

Report message to a moderator

Re: It is showing a symbol like C/D in data model. [message #296031 is a reply to message #295998]

Thu, 24 January 2008 04:52

sasipalarivattom
Messages: 121
Registered: June 2007
Location: Cochin ( INDIA )

Senior Member

Report message to a moderator

Re: It is showing a symbol like C/D in data model. [message #296046 is a reply to message #296031]

Thu, 24 January 2008 06:21

misragopal
Messages: 125
Registered: June 2005
Location: New Delhi, india

Senior Member

Can you please send your query.?

Report message to a moderator

Re: It is showing a symbol like C/D in data model. [message #296153 is a reply to message #296046]

Thu, 24 January 2008 21:36

sasipalarivattom
Messages: 121
Registered: June 2007
Location: Cochin ( INDIA )

Senior Member

SELECT X.JOBDATE,X.YEARCODE,X.TYPEDESC,X.NAME,X.COMP_CODE,X.DIV_CODE,MAX(X.JANVOL)JANVOL,MAX(X.FEBVOL)FEBVOL,
MAX(X.MARVOL)MARVOL,MAX(X.APRVOL)APRVOL,MAX(X.MAYVOL)MAYVOL,MAX(X.JUNVOL)JUNVOL,MAX(X.JULVOL)JULVOL,
MAX(X.AUGVOL)AUGVOL,MAX(X.SEPVOL)SEPVOL,MAX(X.OCTVOL)OCTVOL,MAX(X.NOVVOL)NOVVOL,MAX(X.DECVOL)DECVOL,
MAX(X.JANACTPROFIT)JANACTPROFIT,MAX(X.FEBACTPROFIT)FEBACTPROFIT,MAX(X.MARACTPROFIT)MARACTPROFIT,
MAX(X.APRACTPROFIT)APRACTPROFIT,MAX(X.MAYACTPROFIT)MAYACTPROFIT,
MAX(X.JUNACTPROFIT)JUNACTPROFIT,MAX(X.JULACTPROFIT)JULACTPROFIT,MAX(X.AUGACTPROFIT)AUGACTPROFIT,
MAX(X.SEPACTPROFIT)SEPACTPROFIT,MAX(X.OCTACTPROFIT)OCTACTPROFIT,MAX(X.NOVACTPROFIT)NOVACTPROFIT,
MAX(X.DECACTPROFIT)DECACTPROFIT

FROM (SELECT  TO_CHAR(b.JOBDATE,'yyyy') jobdate,
b.YEARCODE,t.typedesc,
s.saleman,s.name,b.COMP_CODE,b.DIV_CODE,
DECODE(TO_CHAR(b.jobdate,'MON'),'JAN',SUM(b.VOLUME)) JANVOL,
DECODE(TO_CHAR(b.jobdate,'MON'),'FEB',SUM(b.VOLUME)) FEBVOL,
DECODE(TO_CHAR(b.jobdate,'MON'),'MAR',SUM(b.VOLUME)) MARVOL,
DECODE(TO_CHAR(b.jobdate,'MON'),'APR',SUM(b.VOLUME)) APRVOL,
DECODE(TO_CHAR(b.jobdate,'MON'),'MAY',SUM(b.VOLUME)) MAYVOL,
DECODE(TO_CHAR(b.jobdate,'MON'),'JUN',SUM(b.VOLUME)) JUNVOL,
DECODE(TO_CHAR(b.jobdate,'MON'),'JUL',SUM(b.VOLUME)) JULVOL,
DECODE(TO_CHAR(b.jobdate,'MON'),'AUG',SUM(b.VOLUME)) AUGVOL,
DECODE(TO_CHAR(b.jobdate,'MON'),'SEP',SUM(b.VOLUME)) SEPVOL,
DECODE(TO_CHAR(b.jobdate,'MON'),'OCT',SUM(b.VOLUME)) OCTVOL,
DECODE(TO_CHAR(b.jobdate,'MON'),'NOV',SUM(b.VOLUME)) NOVVOL,
DECODE(TO_CHAR(b.jobdate,'MON'),'DEC',SUM(b.VOLUME)) DECVOL,
DECODE(TO_CHAR(b.jobdate,'MON'),'JAN',SUM(NVL(j.SALAMT,0))) - 
DECODE(TO_CHAR(b.jobdate,'MON'),'JAN',SUM(NVL(j.provision,0))) JANPROVPROFIT,
DECODE(TO_CHAR(b.jobdate,'MON'),'FEB',SUM(NVL(j.SALAMT,0))) -
DECODE(TO_CHAR(b.jobdate,'MON'),'FEB',SUM(NVL(j.provision,0))) FEBPROVPROFIT,
DECODE(TO_CHAR(b.jobdate,'MON'),'MAR',SUM(NVL(j.SALAMT,0))) -
DECODE(TO_CHAR(b.jobdate,'MON'),'MAR',SUM(NVL(j.provision,0))) MARPROVPROFIT,
DECODE(TO_CHAR(b.jobdate,'MON'),'APR',SUM(NVL(j.SALAMT,0))) - 
DECODE(TO_CHAR(b.jobdate,'MON'),'APR',SUM(NVL(j.provision,0))) APRPROVPROFIT,
DECODE(TO_CHAR(b.jobdate,'MON'),'MAY',SUM(NVL(j.SALAMT,0))) -
DECODE(TO_CHAR(b.jobdate,'MON'),'MAY',SUM(NVL(j.provision,0))) MAYPROVPROFIT,
DECODE(TO_CHAR(b.jobdate,'MON'),'JUN',SUM(NVL(j.SALAMT,0))) - 
DECODE(TO_CHAR(b.jobdate,'MON'),'JUN',SUM(NVL(j.provision,0))) JUNPROVPROFIT,
DECODE(TO_CHAR(b.jobdate,'MON'),'JUL',SUM(NVL(j.SALAMT,0))) -
DECODE(TO_CHAR(b.jobdate,'MON'),'JUL',SUM(NVL(j.provision,0))) JULPROVPROFIT,
DECODE(TO_CHAR(b.jobdate,'MON'),'AUG',SUM(NVL(j.SALAMT,0))) - 
DECODE(TO_CHAR(b.jobdate,'MON'),'AUG',SUM(NVL(j.provision,0))) augPROVPROFIT,
DECODE(TO_CHAR(b.jobdate,'MON'),'SEP',SUM(NVL(j.SALAMT,0))) - 
DECODE(TO_CHAR(b.jobdate,'MON'),'SEP',SUM(NVL(j.provision,0))) SEPPROVPROFIT,
DECODE(TO_CHAR(b.jobdate,'MON'),'OCT',SUM(NVL(j.SALAMT,0))) - 
DECODE(TO_CHAR(b.jobdate,'MON'),'OCT',SUM(NVL(j.provision,0))) OCTPROVPROFIT,
DECODE(TO_CHAR(b.jobdate,'MON'),'NOV',SUM(NVL(j.SALAMT,0))) -
DECODE(TO_CHAR(b.jobdate,'MON'),'NOV',SUM(NVL(j.provision,0))) NOVPROVPROFIT,
DECODE(TO_CHAR(b.jobdate,'MON'),'DEC',SUM(NVL(j.SALAMT,0))) - 
DECODE(TO_CHAR(b.jobdate,'MON'),'DEC',SUM(NVL(j.provision,0))) DECPROVPROFIT,
DECODE(TO_CHAR(b.jobdate,'MON'),'JAN',SUM(NVL(j.invamt,0)))+DECODE(TO_CHAR(b.jobdate,'MON'),'JAN',SUM(NVL(j.dnamt,0)))- 
DECODE(TO_CHAR(b.jobdate,'MON'),'JAN',SUM(NVL(j.expamt,0)))+DECODE(TO_CHAR(b.jobdate,'MON'),'JAN',SUM(NVL(j.cnamt,0))) JANACTPROFIT,
DECODE(TO_CHAR(b.jobdate,'MON'),'FEB',SUM(NVL(j.invamt,0)))+DECODE(TO_CHAR(b.jobdate,'MON'),'FEB',SUM(NVL(j.dnamt,0)))- 
DECODE(TO_CHAR(b.jobdate,'MON'),'FEB',SUM(NVL(j.expamt,0)))+DECODE(TO_CHAR(b.jobdate,'MON'),'FEB',SUM(NVL(j.cnamt,0))) FEBACTPROFIT,
DECODE(TO_CHAR(b.jobdate,'MON'),'MAR',SUM(NVL(j.invamt,0)))+DECODE(TO_CHAR(b.jobdate,'MON'),'MAR',SUM(NVL(j.dnamt,0)))- 
DECODE(TO_CHAR(b.jobdate,'MON'),'MAR',SUM(NVL(j.expamt,0)))+DECODE(TO_CHAR(b.jobdate,'MON'),'MAR',SUM(NVL(j.cnamt,0))) MARACTPROFIT,
DECODE(TO_CHAR(b.jobdate,'MON'),'APR',SUM(NVL(j.invamt,0)))+DECODE(TO_CHAR(b.jobdate,'MON'),'APR',SUM(NVL(j.dnamt,0)))- 
DECODE(TO_CHAR(b.jobdate,'MON'),'APR',SUM(NVL(j.expamt,0)))+DECODE(TO_CHAR(b.jobdate,'MON'),'APR',SUM(NVL(j.cnamt,0))) APRACTPROFIT,
DECODE(TO_CHAR(b.jobdate,'MON'),'MAY',SUM(NVL(j.invamt,0)))+DECODE(TO_CHAR(b.jobdate,'MON'),'MAY',SUM(NVL(j.dnamt,0)))- 
DECODE(TO_CHAR(b.jobdate,'MON'),'MAY',SUM(NVL(j.expamt,0)))+DECODE(TO_CHAR(b.jobdate,'MON'),'MAY',SUM(NVL(j.cnamt,0))) MAYACTPROFIT,
DECODE(TO_CHAR(b.jobdate,'MON'),'JUN',SUM(NVL(j.invamt,0)))+DECODE(TO_CHAR(b.jobdate,'MON'),'JUN',SUM(NVL(j.dnamt,0)))- 
DECODE(TO_CHAR(b.jobdate,'MON'),'JUN',SUM(NVL(j.expamt,0)))+DECODE(TO_CHAR(b.jobdate,'MON'),'JUN',SUM(NVL(j.cnamt,0))) JUNACTPROFIT,
DECODE(TO_CHAR(b.jobdate,'MON'),'JUL',SUM(NVL(j.invamt,0)))+DECODE(TO_CHAR(b.jobdate,'MON'),'JUL',SUM(NVL(j.dnamt,0)))- 
DECODE(TO_CHAR(b.jobdate,'MON'),'JUL',SUM(NVL(j.expamt,0)))+DECODE(TO_CHAR(b.jobdate,'MON'),'JUL',SUM(NVL(j.cnamt,0))) JULACTPROFIT,
DECODE(TO_CHAR(b.jobdate,'MON'),'AUG',SUM(NVL(j.invamt,0)))+DECODE(TO_CHAR(b.jobdate,'MON'),'AUG',SUM(NVL(j.dnamt,0)))- 
DECODE(TO_CHAR(b.jobdate,'MON'),'AUG',SUM(NVL(j.expamt,0)))+DECODE(TO_CHAR(b.jobdate,'MON'),'AUG',SUM(NVL(j.cnamt,0))) augACTPROFIT,
DECODE(TO_CHAR(b.jobdate,'MON'),'SEP',SUM(NVL(j.invamt,0)))+DECODE(TO_CHAR(b.jobdate,'MON'),'SEP',SUM(NVL(j.dnamt,0)))- 
DECODE(TO_CHAR(b.jobdate,'MON'),'SEP',SUM(NVL(j.expamt,0)))+DECODE(TO_CHAR(b.jobdate,'MON'),'SEP',SUM(NVL(j.cnamt,0))) SEPACTPROFIT,
DECODE(TO_CHAR(b.jobdate,'MON'),'OCT',SUM(NVL(j.invamt,0)))+DECODE(TO_CHAR(b.jobdate,'MON'),'OCT',SUM(NVL(j.dnamt,0)))- 
DECODE(TO_CHAR(b.jobdate,'MON'),'OCT',SUM(NVL(j.expamt,0)))+DECODE(TO_CHAR(b.jobdate,'MON'),'OCT',SUM(NVL(j.cnamt,0))) OCTACTPROFIT,
DECODE(TO_CHAR(b.jobdate,'MON'),'NOV',SUM(NVL(j.invamt,0)))+DECODE(TO_CHAR(b.jobdate,'MON'),'NOV',SUM(NVL(j.dnamt,0)))- 
DECODE(TO_CHAR(b.jobdate,'MON'),'NOV',SUM(NVL(j.expamt,0)))+DECODE(TO_CHAR(b.jobdate,'MON'),'NOV',SUM(NVL(j.cnamt,0))) NOVACTPROFIT,
DECODE(TO_CHAR(b.jobdate,'MON'),'DEC',SUM(NVL(j.invamt,0)))+DECODE(TO_CHAR(b.jobdate,'MON'),'DEC',SUM(NVL(j.dnamt,0)))- 
DECODE(TO_CHAR(b.jobdate,'MON'),'DEC',SUM(NVL(j.expamt,0)))+DECODE(TO_CHAR(b.jobdate,'MON'),'DEC',SUM(NVL(j.cnamt,0))) DECACTPROFIT


FROM prtjobhd b,actjobledsumv j,fnmcostcat f,
          fnmcosttype t,msalesman  s,
		   (SELECT b1.comp_code,b1.div_code,decode(nvl(b1.mainjob,-1),-1,b1.intjobno,b1.mainjob) intjobno,
			              decode(nvl(MAX(b1.chrgweight),0),0,1,MAX(b1.chrgweight)) chrgweight,
			              decode(nvl(MAX(b1.weight),0),0,1,  MAX(b1.weight)) Weight,
                                                              decode(nvl(MAX(b1.volume),0),0,1,MAX(b1.volume)) volume,
			               SUM(j1.salamt) prvsales, SUM(NVL(j1.invamt,0)) SALES,
			               SUM(nvl(j1.provision,0)) prvcost,SUM(NVL(j1.expamt,0)) cost,
			               SUM(j1.cnamt) cnissu,SUM(j1.dnamt) cnrcv,
			               (SUM(j1.salamt)-SUM(j1.provision)) provprofit,
			               (SUM(NVL(j1.invamt,0)+ NVL(j1.dnamt,0)))-
			               (SUM(NVL(j1.expamt,0)+NVL(j1.cnamt,0))) actprofit
				   FROM prtjobhd b1,actjobledsumv j1
				WHERE b1.comp_code=:pcomp_code
				AND b1.div_code=:pdiv_code
		        AND TO_CHAR(B1.JOBDATE,'YYYY') = :curentyear 
				AND (b1.typecode  = :p_jtype OR :p_jtype ='0')
				AND b1.status =2
				AND j1.jobno  (+)= b1.intjobno
				GROUP BY b1.comp_code,b1.div_code,decode(nvl(b1.mainjob,-1),-1,b1.intjobno,b1.mainjob)) m
WHERE b.comp_code=:pcomp_code
AND b.div_code=:pdiv_code
AND b.status =2
AND b.mainjob IS NOT NULL
AND b.mainjob= m.intjobno 
AND s.comp_code(+) = b.comp_code
AND s.div_code(+) = b.div_code
AND s.saleman(+) = b.salesman
AND b.typecode= t.typecode 
AND j.comp_code (+) = b.comp_code
AND j.div_code  (+)= b.div_code
AND j.jobno  (+)= b.intjobno
AND f.CATCODE = t.JOBCATCODE
AND s.SALEMAN = :msaleman
and to_char(jobdate,'yyyy') = :curentyear
and f.CATDESC = :CATDESC
GROUP BY TO_CHAR(b.JOBDATE,'yyyy'),
b.YEARCODE,t.typedesc,
s.saleman,s.name,b.COMP_CODE,b.DIV_CODE,(TO_CHAR(b.jobdate,'MON')) 
ORDER BY 1) X  
GROUP BY X.JOBDATE,X.YEARCODE,X.TYPEDESC,X.NAME,X.COMP_CODE,X.DIV_CODE

This is my query..

Report message to a moderator

Previous Topic:	how to call a report from sql
Next Topic:	Repeating frame in oracle report

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Jul 02 08:34:59 CDT 2024