Home » SQL & PL/SQL » SQL & PL/SQL » SQL Issue to generate report for shopping store. (Oracle 10g.)
SQL Issue to generate report for shopping store. [message #580462] Mon, 25 March 2013 01:13 Go to next message
Manoj.Gupta.91
Messages: 194
Registered: March 2008
Location: Delhi
Senior Member
Hi All,

I've below test case

CREATE TABLE MYMERCHANT
(
	SRNO		NUMBER(5),
	PARENTSRNO	NUMBER(5),
	LEVELNO		NUMBER(5),
	LEVELNAME	VARCHAR2(25)
) ;

INSERT INTO MYMERCHANT(SRNO, PARENTSRNO, LEVELNO, LEVELNAME ) VALUES ( 1, NULL, 101, 'HQ1' ) ;
INSERT INTO MYMERCHANT(SRNO, PARENTSRNO, LEVELNO, LEVELNAME ) VALUES ( 2, 1, 102, 'DIV1' ) ;
INSERT INTO MYMERCHANT(SRNO, PARENTSRNO, LEVELNO, LEVELNAME ) VALUES ( 3, 1, 103, 'DIV2' ) ;
INSERT INTO MYMERCHANT(SRNO, PARENTSRNO, LEVELNO, LEVELNAME ) VALUES ( 4, 2, 104, 'LOC1' ) ;
INSERT INTO MYMERCHANT(SRNO, PARENTSRNO, LEVELNO, LEVELNAME ) VALUES ( 5, 2, 105, 'LOC2' ) ;
INSERT INTO MYMERCHANT(SRNO, PARENTSRNO, LEVELNO, LEVELNAME ) VALUES ( 6, 3, 106, 'LOC3' ) ;
COMMIT ;

CREATE TABLE MYMERCHANTTXN
(
	TXNNO		NUMBER(10),
	TXNTYPE		VARCHAR2(3),
	LEVELNAME	VARCHAR2(5),
	TXNAMT		NUMBER(14,2)
) ;

INSERT INTO MYMERCHANTTXN( TXNNO, TXNTYPE, LEVELNAME, TXNAMT ) VALUES( 1, 'SD', 'LOC1', 100 ) ;
INSERT INTO MYMERCHANTTXN( TXNNO, TXNTYPE, LEVELNAME, TXNAMT ) VALUES( 2, 'CA', 'LOC1', 200 ) ;
INSERT INTO MYMERCHANTTXN( TXNNO, TXNTYPE, LEVELNAME, TXNAMT ) VALUES( 3, 'CH', 'LOC1', 150 ) ;
INSERT INTO MYMERCHANTTXN( TXNNO, TXNTYPE, LEVELNAME, TXNAMT ) VALUES( 4, 'SD', 'LOC2', 100 ) ;
INSERT INTO MYMERCHANTTXN( TXNNO, TXNTYPE, LEVELNAME, TXNAMT ) VALUES( 5, 'CH', 'LOC2', 300 ) ;
INSERT INTO MYMERCHANTTXN( TXNNO, TXNTYPE, LEVELNAME, TXNAMT ) VALUES( 6, 'SD', 'LOC3', 100 ) ;
INSERT INTO MYMERCHANTTXN( TXNNO, TXNTYPE, LEVELNAME, TXNAMT ) VALUES( 7, 'CA', 'LOC3', 500 ) ;

COMMIT ;

SELECT M.LEVELNAME, SUM(T.TXNAMT)
FROM MYMERCHANT M, MYMERCHANTTXN T
WHERE M.LEVELNAME = T.LEVELNAME
GROUP BY M.LEVELNAME ;

            HQ1
          /    \
         /      \
       DIV1    DIV2
       /  \       \
      /    \       \
     LOC1  LOC2    LOC3



Structure is like above. There is a Head Quarter (HQ1) under which we have different division like (DIV1 and DIV2). Under division we have different location where our shopping stores are operating and transactions are done (LOC1, LOC2 for DIV1) and (LOC3 for DIV2) respectively.

DESIRED OUTPUT
User can generate reports at two levels i.e. Head Quarter level and Division Level.

When user wants report at Head Quarter Level :
Page 1 (Should Display as below)
HQ1   DIV1   LOC1   450
HQ1   DIV1   LOC2   400
HQ1   DIV2   LOC3   600

Page 2 (Should Display as below)
HQ1   DIV1   LOC1   450
HQ1   DIV1   LOC2   400

Page 3 (Should Display as below)
HQ1   DIV2   LOC3   600


When user wants report at Division Level : eg DIV1
Page 1 (Should Display as below)
HQ1   DIV1   LOC1   450
HQ1   DIV1   LOC2   400

Thanks & Regards
Manoj
Re: SQL Issue to generate report for shopping store. [message #580489 is a reply to message #580462] Mon, 25 March 2013 07:05 Go to previous messageGo to next message
bhat.veeresh@gmail.com
Messages: 88
Registered: July 2012
Location: Bangalore
Member
Manoj,

In MYMERCHANT table, How do you relate HQ1 with DIV1, DIV2 (What if there exists a HQ2 with DIV1, DIV2)
(Relationship between HQ & DIV ? and that of DIV and LOC)

Thanks & Regards,
Veeresh
Re: SQL Issue to generate report for shopping store. [message #580490 is a reply to message #580462] Mon, 25 March 2013 07:18 Go to previous message
Michel Cadot
Messages: 58861
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What is your client tool? "Page" is dependent on the tool, so what is yours?

Regards
Michel
Previous Topic: rollback after commit
Next Topic: BULK COLLECT and return a RECORD as OUT param
Goto Forum:
  


Current Time: Sat Aug 23 00:59:40 CDT 2014

Total time taken to generate the page: 0.11366 seconds