Subject Re: Re: [firebird-support] How To Display Null Values At the End For More Than One Columns Specified in Order By Clause?
Author liviuslivius
Post date 2015-03-13T07:32:28Z
Hi,
 
this is not possible to sort by SQL itself - because db sort "records by columns" not columns itself.
You must write stored proc and inside it fill temp table with values - its simple
 
regards,
Karol Bieniaszewski
 
 
 
W dniu 2015-03-12 14:36:50 użytkownik Vishal Tiwari vishualsoft@... [firebird-support] <firebird-support@yahoogroups.com> napisał:
 
My sample SQL is as below:
 
Select T.EmpCode, EM.EmpName, 
 
Case When 
(Select <NumFieldName1> from TransactionTable T1 where T1.EmpCode = T.EmpCode And T1.CompCode = AVSP1.S_EarnCompCodeColumn1) Is Not Null Then 
S_EarnCompCodeColumn1 End As S_EarnCompCodeColumn1, 
 
(Select <NumFieldName1> from TransactionTable T1 where T1.Empcode = T.empcode And T1.CompCode = AVSP1.S_EarnCompCodeColumn1),
 
Case When 
(Select <NumFieldName1> from TransactionTable T2 where T2.EmpCode = T.EmpCode And T2.CompCode = AVSP1.S_EarnCompCodeColumn2) Is Not Null Then 
S_EarnCompCodeColumn2 End As S_EarnCompCodeColumn2, 
 
(Select <NumFieldName1> from TransactionTable T2 where T2.Empcode = T.empcode And T2.CompCode = AVSP1.S_EarnCompCodeColumn2),
 
Case When 
(Select <NumFieldName1> from TransactionTable T3 where T3.EmpCode = T.EmpCode And T3.CompCode = AVSP1.S_DedCompCodeColumn1) Is Not Null Then 
S_DedCompCodeColumn1 End As S_DedCompCodeColumn1, 
 
(Select <NumFieldName1> from TransactionTable T3 where T3.Empcode = T.empcode And T3.CompCode = AVSP1.S_DedCompCodeColumn1),
 
Case When 
(Select <NumFieldName1> from TransactionTable T4 where T4.EmpCode = T.EmpCode And T4.CompCode = AVSP1.S_DedCompCodeColumn2) Is Not Null Then 
S_DedCompCodeColumn2 End As S_DedCompCodeColumn2, 
 
(Select <NumFieldName1> from TransactionTable T4 where T4.Empcode = T.empcode And T4.CompCode = AVSP1.S_DedCompCodeColumn1),
 
from AVS_Payslip AVSP1 
Join TransactionTable T on AVSP1.S_EarnCompCodeColumn1 = T.CompCode 
join EmployeeMaster EM On EM.EmpCode = T.EmpCode 
 
 
order by T.EmpCode, S_EarnCompCodeColumn1 Nulls Last, S_EarnCompCodeColumn2 Nulls Last, S_DedCompCodeColumn1 Nulls Last, S_DedCompCodeColumn2 Nulls Last 
 
 
In this sql table "AVS_Payslip" has four columns, in that the values in the table are as below:
 
 
S_EarnCompCodeColumn1 S_EarnCompCodeColumn2 S_DedCompCodeColumn1 S_DedCompCodeColumn2
ADHOC CONVY PF PT
ADJADD DA RD RPLI
ATTNS_BNS DRIVE ADVANCE STORE
BASIC EXTRA_DUTY ESI  
BATH_ALW   INC_TAX  
BONUS   BKRD_ADV  
CCA      
MONT_SAL      
PRD      


On Thursday, 12 March 2015 6:53 PM, "Vishal Tiwari vishualsoft@... [firebird-support]" <firebird-support@yahoogroups.com> wrote: