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_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 | 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 | ||||||
| 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 | ||||||
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 16, 2015, 3:28 AM
I am doing this activity from front-end (aspx.cs) page.
I have added a dynamic datacolumn at position 0.
table = new Datatable();
table = Reports(); //return type of method is datatabe.
#region AddSRNOColumnAtPositionZeroAndFillITWithSerialNumbers
DataColumn Col = table.Columns.Add("SrNo", typeof(System.Int32));
Col.SetOrdinal(0);
for (int index = 1; index <= table.Rows.Count; index++)
{
if (table.Rows[index]["SrNo"].ToString().Trim().Length == 0)
{
table.Rows[index]["SrNo"] = index;
}
}
#endregion
#region GetColumnValues
DataView dv = new DataView(table);
DataTable dt = dv.ToTable(true, "IN_NUMBER");
#endregion
----------
I got this column values.
------
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 ???
How to go further ??
Please guide...
Thanks in advance..
Jignesh TrivediPosted Mar 16, 2015, 12:07 AM
Hi,
distinct will work with all column with select statement.
Is it possible to split this query?
I really don't know what is relations between tables. Try to write multiple query and result will be join using inner join or outer join...
Joginder BangerPosted Mar 15, 2015, 11:22 AM
if you success the get the records on front end then you have lots option there like set the first and last position
DataTable dt = new DataTable();
dt.Columns["column name"].SetOrdinal("Set Int value");
set the column value null ......dt.columns["column Name"]=null
Riddhi ValechaPosted Mar 14, 2015, 5:49 AM
Tried dear.....
The only thing is ... I want that last columns -
D_Amt, WH_Amt, Remarks and EmployeeCnt should appear once in for multiple IN_NUMBER..
Is there any way in which we can make those columns null values in oracle (toad) ??
I searched all built-in functions also... but no option...
Hi...
Do you know how to use Datatable ?? I am getting this records in datatable in front-end.
Can I apply some logic there ?? Can I access IN_NUMBERS from datatable ?? and make column values null ???
Piyush PansuriyaPosted Mar 14, 2015, 4:33 AM
http://www.c-sharpcorner.com/Blogs/11763/
Riddhi ValechaPosted Mar 14, 2015, 4:10 AM
But, I have to show the company names also... along with order values...
I cannot remove any columns....
Can I make a function for this ?? If yes, then how ??
Piyush PansuriyaPosted Mar 14, 2015, 3:48 AM
You are getting different result in OrderValue Column of IN_NUMBER = 12345.
And Company Name Also Different.
If you remove this column from select statement. or make it same value, you will get result as you aspect.