Post
Ask Question
Riddhi Valecha

Riddhi Valecha

  • 420
  • 3.3k
  • 412.9k

ORACLE QUERY - PLEASE HELP - GETTING ROW VALUE JUST ONCE

Mar 14 2015 7:42 AM
Hi...

I have the following tables and query -


select distinct T2.IN_NUMBER  , to_char(T2.IN_DATE,'dd-MON-yyyy') as IN_DATE, 
 COMP_NAME_BY_COMPANYCODE(T1.COMPANY_CODE) CompanyName,T3.Divisions as Divisions ,T4.Vendors, 
 T4.VendorsCode,(select sm.Status from STATUSMASTER sm where  sm.SID = T2.Status ) as Status,
 to_char( T2.Out_Date,'dd-MON-yyyy') as Out_Date, 'WORKORDERS - '|| INWARD_WORKORDER_REPORT(T1.GRPS,T1.COMPANY_CODE ) OrderValues, 
 FN_GET_PERIOD(TO_CHAR(add_months(T2.DUE_DATE,-1),'MMYYYY')) ||'-' || substr((TO_CHAR(add_months(T2.DUE_DATE,-1),'MMYYYY')),3,4) Month, 
 FN_GET_PERIOD(T1.GROUP_CODE) ||'-' || SUBSTR(T1.GROUP_CODE,3,4) NextMonth,  T2.D_Amt,T2.WH_Amt, t2.Remarks , t2. EmployeesCnt
 from Table1 t1 inner join Table2 t2 on T1.GROUP_CODE = T2.GROUP_CODE and T2.DELETED_STATUS = 0 
 inner join Divisions_master t3 on T1.DID = T3.DID and T3.DELETED_STATUS = 0  inner join VendorsMaster  t4 on T1.VID = T4.VID
  where  T2.IN_NUMBER is not null  AND NVL(t2.IN_DATE,'01-MAR-2015') BETWEEN '01-MAR-2015'
 AND LAST_DAY('01-MAR-2015')
--------------------
Result of the query -
IN_NUMBER IN_DATE CompanyName Divisions Vendors VendorsCode Status Out_Date OrderValues Month NextMonth D_Amt WH_Amt Remarks EmployeesCnt
12345 12-Mar-15 Company 2 Div-2 Vendor 4 400 Pending
WORKORDERS - 0054004062,0054004063,0054004148 Dec-14 Jan-15 1000 0 test 123
12345 12-Mar-15 Company 2 Div-2 Vendor 4 400 Pending
WORKORDERS - 0004052189,0004052190,0004052191,0004053488,0004065808,0004071437,0004085472,0004087360,0004087621,0004087622,0004087874,0004088728,0031141935 Dec-14 Jan-15 1000 0 test 123
678
Company 1  D-3 vendor 3  300 In Progress.
WORKORDERS - 0004071922 Nov-14 Dec-14




135 2-Mar-15 Company 2 Division - 1 Vendor 2 200 Rejected
WORKORDERS - 0004039834,0004039835,0004039836 Dec-14 Jan-15 0 0
123
567 10-Mar-15 Company 2 Div-2 Vendor1 100 Done. 10-Mar-15 WORKORDERS - 0004050775,0004050776,0004050777 Dec-14 Jan-15 0 0
2
12345 12-Mar-15 Company 3 Div-2 Vendor 4 400 Pending
WORKORDERS - 0054004150 Dec-14 Jan-15 1000 0 test 123

















----------------
Expected Result -

IN_DATE CompanyName Divisions Vendors VendorsCode Status Out_Date Order Values Month NextMonth D_Amt WH_Amt Remarks EmployeesCnt
12-Mar-15 Company 2 Div-2 Vendor 4 400 Pending
WORKORDERS - 0054004062,0054004063,0054004148 Dec-14 Jan-15 1000 0 test 123
12-Mar-15 Company 2 Div-2 Vendor 4 400 Pending
WORKORDERS - 0004052189,0004052190,0004052191,0004053488,0004065808,0004071437,0004085472,0004087360,0004087621,0004087622,0004087874,0004088728,0031141935 Dec-14 Jan-15





Company 1  D-3 vendor 3  300 In Progress.
WORKORDERS - 0004071922 Nov-14 Dec-14




2-Mar-15 Company 2 Division - 1 Vendor 2 200 Rejected
WORKORDERS - 0004039834,0004039835,0004039836 Dec-14 Jan-15 0 0
123
10-Mar-15 Company 2 Div-2 Vendor1 100 Done. 10-Mar-15 WORKORDERS - 0004050775,0004050776,0004050777 Dec-14 Jan-15 0 0
2
12-Mar-15 Company 3 Div-2 Vendor 4 400 Pending
WORKORDERS - 0054004150 Dec-14 Jan-15




-------
Eg - For In_NUMBER - 12345, Columns D_Amt, WH_Amt, Remarks and EmployeesCnt should have values in just 1 single row. NOt in all 3 rows.

Eg-
If I select T2.IN_NUMBER = 12345, then the result should be -
IN_NUMBER IN_DATE CompanyName Divisions Vendors VendorsCode Status Out_Date Order Values Month NextMonth D_Amt WH_Amt Remarks EmployeesCnt
12345 12-Mar-15 Company 2 Div-2 Vendor 4 400 Pending
WORKORDERS - 0054004062,0054004063,0054004148 14-Dec 15-Jan 1000 0 test 123
12345 12-Mar-15 Company 2 Div-2 Vendor 4 400 Pending
WORKORDERS - 0004052189,0004052190,0004052191,0004053488,0004065808,0004071437,0004085472,0004087360,0004087621,0004087622,0004087874,0004088728,0031141935 14-Dec 15-Jan




12345 12-Mar-15 Company 3 Div-2 Vendor 4 400 Pending
WORKORDERS - 0054004150 14-Dec 15-Jan





Columns -
D_AmtWH_AmtRemarksEmployeesCnt

Should have value in only 1 row.

How to get this ??

Please help... its urgent...

Answers (2)