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_Amt | WH_Amt | Remarks | EmployeesCnt | |
Should have value in only 1 row.
How to get this ??
Please help... its urgent...
Riddhi ValechaPosted Mar 15, 2015, 3:57 AM
Please help me with the logic...
I am trying out in the following way -
1. I am getting result from database in datatable in front-end.
2. I got all distinct values of "IN_NUMBER" in dataview.
3. Now, I want to check how many times 1 IN_NUMBER is present.
4. If it is present only 1 time, then no changes.
5. If more than 1 time (i.e. multiple times), then make the last columns values blank.
Can you please guide me in doing steps from 3 to 5 ???
Pradeep ShetPosted Mar 15, 2015, 2:32 AM
It is not possible to have a part of columns shown in single row from query, in that case you need to write a logic where you will iterate through each row and check whether previous row column is having match data, if yes show blank in next row else show as it is. Hope you are getting my point.