SQL Query - PLEASE HELP... URGENT !!

Question

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

123

567

10-Mar-15

Company 2

Div-2

Vendor1

100

Done.

10-Mar-15

WORKORDERS - 0004050775,0004050776,0004050777

Dec-14

Jan-15

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

123

567

10-Mar-15

Company 2

Div-2

Vendor1

100

Done.

10-Mar-15

WORKORDERS - 0004050775,0004050776,0004050777

Dec-14

Jan-15

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 Valecha · Answer

Hi.. 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 { 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 Trivedi · Answer

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 Banger · Answer

Hi 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 Valecha · Answer

Hi.. 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 Pansuriya · Answer

Than you can do it with comma separated values. Use COALESCE() for that. http://www.c-sharpcorner.com/Blogs/11763/

Riddhi Valecha · Answer

Hi... 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 Pansuriya · Answer

Hello 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.