Home » SQL & PL/SQL » SQL & PL/SQL » Please kindly help in this SQL
Please kindly help in this SQL [message #208662] Mon, 11 December 2006 19:14 Go to next message
kumarvk
Messages: 211
Registered: July 2004
Senior Member
Hi Experts,

I have the result from the sql

select distinct a.lic_no "REG NO",a.INV_NO "INV NO", a.INV_AMT "INV AMT",b.LBR_AMT "LBR",b.MTL_AMT "MTL",
b.PARTS_AMT "PARTS"
FROM MSS_FMP_HDR A,MSS_FMP_ITEMS B,MSS_RO_HDR C,MSS_RO_JOBS D,MSS_INV_HDR E,MSS_INV_LINES F
WHERE a.INV_DTIME between '01-MAR-00' AND '30-NOV-06' AND E.STS='I' AND a.ID=b.INHD_ID AND a.LIC_NO='GU8788P'  
GROUP BY a.lic_NO,a.INV_NO,a.INV_AMT,b.LBR_AMT,b.MTL_AMT, b.PARTS_AMT ORDER BY 1,2;


REG NO	INV NO	        INV AMT	LBR	MTL	PARTS
GU8788P	SAI029600	6.4		6.4	0
GU8788P	SAI016392	97.7	35	43.1	0
GU8788P	SAI016392	97.7		0	19.6
GU8788P	SAI017677	301	38	0	0
GU8788P	SAI017677	301	41	42	0
GU8788P	SAI017677	301		0	180
GU8788P	SAI018052	19.5		19.5	0
GU8788P	SAI019881	183.95	32.3	0	0
GU8788P	SAI019881	183.95	73.1	38.3	0
GU8788P	SAI019881	183.95		0	40.25
GU8788P	SAI020422	122.15	34.85	38.3	0
GU8788P	SAI020422	122.15		0	49
GU8788P	SAI021788	89.15	29.75	39.8	0
GU8788P	SAI021788	89.15		0	19.6
GU8788P	SAI023193	249.35	25.5	0	0
GU8788P	SAI023193	249.35	25.5	2.6	0
GU8788P	SAI023193	249.35	42.5	0	0
GU8788P	SAI023193	249.35	55.25	0	0
GU8788P	SAI023193	249.35		0	98


I want to display the output in one line for inv nos having the same nos like the following.

REG NO	INV NO	        INV AMT	LBR	MTL	PARTS
GU8788P	SAI029600	6.4		6.4	0
GU8788P	SAI016392	97.7	35	43.1	19.6
GU8788P	SAI017677	301	71	42	180
GU8788P	SAI018052	19.5		19.5	0
GU8788P	SAI019881	183.95	105.4	38.3	40.25
GU8788P	SAI020422	122.15	34.85	38.3	49
GU8788P	SAI021788	89.15	29.75	39.8	19.6
GU8788P	SAI023193	249.35	148.75   2.6	98


Re: Please kindly help in this SQL [message #208782 is a reply to message #208662] Tue, 12 December 2006 04:35 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
You are looking for MAX and GROUP BY REG_NO,INV_NO I think.

MHE
Re: Please kindly help in this SQL [message #209016 is a reply to message #208662] Tue, 12 December 2006 23:20 Go to previous messageGo to next message
janacallsu
Messages: 6
Registered: December 2006
Junior Member
I think this should work for you. I didnt check for the output anyways.


SELECT   a.lic_No "REG NO",
         a.Inv_No "INV NO",
         a.Inv_Amt "INV AMT",
         sum(b.lbr_Amt) "LBR",
         sum(b.mtl_Amt) "MTL",
         sum(b.Parts_Amt) "PARTS"
FROM     mss_fmp_hdr a,
         mss_fmp_Items b,
         mss_ro_hdr c,
         mss_ro_Jobs d,
         mss_Inv_hdr e,
         mss_Inv_Lines f
WHERE    a.Inv_dTime BETWEEN '01-MAR-00'
                             AND '30-NOV-06'
         AND e.sts = 'I'
         AND a.Id = b.Inhd_Id
         AND a.lic_No = 'GU8788P'
GROUP BY a.lic_No,
         a.Inv_No,
         a.Inv_Amt,
ORDER BY 1,
         2;


->Jana
Re: Please kindly help in this SQL [message #209134 is a reply to message #208662] Wed, 13 December 2006 08:00 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
kumarvk wrote on Mon, 11 December 2006 20:14

WHERE a.INV_DTIME between '01-MAR-00' AND '30-NOV-06'




Tsk, tsk, poor code.
Re: Please kindly help in this SQL [message #209143 is a reply to message #209134] Wed, 13 December 2006 08:53 Go to previous messageGo to next message
janacallsu
Messages: 6
Registered: December 2006
Junior Member
Sorry Joy. As I have mentioned earlier, I just gave an idea for his required output. I just copied and pasted his own code and modified only in necessary places. I did not notice this date thing at all.

SELECT   a.lic_No "REG NO",
         a.Inv_No "INV NO",
         a.Inv_Amt "INV AMT",
         sum(b.lbr_Amt) "LBR",
         sum(b.mtl_Amt) "MTL",
         sum(b.Parts_Amt) "PARTS"
FROM     mss_fmp_hdr a,
         mss_fmp_Items b,
         mss_ro_hdr c,
         mss_ro_Jobs d,
         mss_Inv_hdr e,
         mss_Inv_Lines f
WHERE    a.Inv_dTime BETWEEN TO_DATE('01-MAR-00','DD-MON-YY')
                             AND TO_DATE('30-NOV-06','DD-MON-YY')
         AND e.sts = 'I'
         AND a.Id = b.Inhd_Id
         AND a.lic_No = 'GU8788P'
GROUP BY a.lic_No,
         a.Inv_No,
         a.Inv_Amt,
ORDER BY 1,
         2;



Hope now it will work fine.
Re: Please kindly help in this SQL [message #209157 is a reply to message #209143] Wed, 13 December 2006 09:23 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Just to be a nitpicker:
A year contains four digits..
Re: Please kindly help in this SQL [message #209187 is a reply to message #209143] Wed, 13 December 2006 12:01 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
It wasn't you Janacallsu. You just added the SUMs to the original mess. But...adding the TO_DATE with a two-digit year has had the unwanted side-effect of setting off Frank's warranted alarm Wink

[Updated on: Wed, 13 December 2006 12:03]

Report message to a moderator

Re: Please kindly help in this SQL [message #209189 is a reply to message #208662] Wed, 13 December 2006 12:10 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
More nits to pick.
1) Only two tables contribute to the SELECT columns & therefore should be only two tables in FROM clause.
2) SIX tables in from clause but only FOUR conditions in WHERE clause; means cartesian product that has negative performance implications.
Re: Please kindly help in this SQL [message #209230 is a reply to message #209157] Wed, 13 December 2006 22:02 Go to previous messageGo to next message
janacallsu
Messages: 6
Registered: December 2006
Junior Member
Hey Frank...We can have two digits of year too, as we specify the format as YY in TO_DATE function

For Example:TO_DATE('01-MAR-07','DD-MON-YY') refers to year 2007.

Correct me if I am wrong.

->Jana
Re: Please kindly help in this SQL [message #209255 is a reply to message #209230] Thu, 14 December 2006 00:08 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
and what about 01-MAR-96 ?
What year is that?

OK, you could use RR instead of YY, but that is not the point. Good coding is about being precise and unambiguous.
Hey, it is only 4 extra characters!
Compare it to writing u instead of you. Most people will understand, but:
a) it irritates a lot of people
b) the gain is almost zero (compare it to i18n, now there is a good reason to abbreviate)
c) it is plain sloppy and lazy.

Writing dates as strings or using only two digits for the year, well each point above applies, I think Wink
Re: Please kindly help in this SQL [message #209257 is a reply to message #209255] Thu, 14 December 2006 00:15 Go to previous messageGo to next message
janacallsu
Messages: 6
Registered: December 2006
Junior Member
What you have said is 100% right Frank. I will follow YYYY format itself, which gives more clarity.

Thanks Frank.

->Jana
Re: Please kindly help in this SQL [message #209265 is a reply to message #209257] Thu, 14 December 2006 00:32 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Cool!

Another true believer gained! ./fa/1725/0/
Previous Topic: Displaying work hours for a pay period
Next Topic: UTL_FILE usage
Goto Forum:
  


Current Time: Fri Dec 02 16:58:51 CST 2016

Total time taken to generate the page: 0.19328 seconds