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 [email protected]
[firebird-support]" <[email protected]> wrote:
Hi SET,
It is not table values, the table you shown is the result of my SQL i.e. my sql
gives the result as your table. Sorry for last wrong explanation.
On Thursday, 12 March 2015 6:47 PM, "Vishal Tiwari [email protected]
[firebird-support]" <[email protected]> wrote:
Table values are as you shown:
MyTable
FieldA FieldB FieldC
1 2 3
1 2 <null>
1 <null> 2
2 3 4
2 <null> <null>
<null> 2 3
On Thursday, 12 March 2015 6:32 PM, Vishal Tiwari
<[email protected]> wrote:
Hi SET,
I have already tried but still not getting the result. Last column displays
Null value in middle of some rows.
With Best Regards.
Vishal
On Thursday, 12 March 2015 6:26 PM, "Svein Erling Tysvær
[email protected] [firebird-support]"
<[email protected]> wrote:
>I am using Firebird database with 2.1 version. I have one issue in Order
By clause.
>I have SQL which gives multile records for multiple employees, i.e. one
>employee has multiple records as an output in SQL.
>I have Four columns in Order By Clause and I want every columns Null value
>should be displayed after Not Null value in every column.
>I googled and found that if I Use
>Order By Column1 Asc Nulls Last
>It works very well for the first columns but for the remaining two columns it
>doesn't work i.e. for these two remaining columns the
>Null value comes in middle i.e. first some values Not Null, then Null Values
>and then again Not Null Values.
>I need to display Null Values at the end of every Not Null value for every
>Four columns specified in Order By Clause for every employee.
>I tried Case When, then Asc for every column in Order By clause, but didn't
>get the expected result.
This type of question is often best asked with an example. Let's say you have
these data:
MyTable
FieldA FieldB FieldC
1 2 3
1 2 <null>
1 <null> 2
2 3 4
2 <null> <null>
<null> 2 3
To get the result in the order it has above, I would use
ORDER BY FieldA NULLS LAST, FieldB NULLS LAST, FieldC NULLS LAST
If you for some strange reason want THIS order (the columns with the largest
number of nulls should be last):
FieldA FieldB FieldC
1 2 3
2 3 4
1 2 <null>
1 <null> 2
<null> 2 3
2 <null> <null>
then you need your CASE construct to be something like:
ORDER BY IIF(FieldA IS NULL, 2, 0)+IIF(FieldB IS NULL, 3, 0)+IIF(FieldC IS
NULL, 4, 0), FieldA, FieldB, FieldC
If there are four fields to order by, you have to use minimum 3, 4, 5 and 6
rather than 2, 3 and 4.
HTH,
Set
#yiv3759800554 #yiv3759800554 -- #yiv3759800554ygrp-mkp {border:1px solid
#d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv3759800554
#yiv3759800554ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv3759800554
#yiv3759800554ygrp-mkp #yiv3759800554hd
{color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px
0;}#yiv3759800554 #yiv3759800554ygrp-mkp #yiv3759800554ads
{margin-bottom:10px;}#yiv3759800554 #yiv3759800554ygrp-mkp .yiv3759800554ad
{padding:0 0;}#yiv3759800554 #yiv3759800554ygrp-mkp .yiv3759800554ad p
{margin:0;}#yiv3759800554 #yiv3759800554ygrp-mkp .yiv3759800554ad a
{color:#0000ff;text-decoration:none;}#yiv3759800554 #yiv3759800554ygrp-sponsor
#yiv3759800554ygrp-lc {font-family:Arial;}#yiv3759800554
#yiv3759800554ygrp-sponsor #yiv3759800554ygrp-lc #yiv3759800554hd {margin:10px
0px;font-weight:700;font-size:78%;line-height:122%;}#yiv3759800554
#yiv3759800554ygrp-sponsor #yiv3759800554ygrp-lc .yiv3759800554ad
{margin-bottom:10px;padding:0 0;}#yiv3759800554 #yiv3759800554actions
{font-family:Verdana;font-size:11px;padding:10px 0;}#yiv3759800554
#yiv3759800554activity
{background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv3759800554
#yiv3759800554activity span {font-weight:700;}#yiv3759800554
#yiv3759800554activity span:first-child
{text-transform:uppercase;}#yiv3759800554 #yiv3759800554activity span a
{color:#5085b6;text-decoration:none;}#yiv3759800554 #yiv3759800554activity span
span {color:#ff7900;}#yiv3759800554 #yiv3759800554activity span
.yiv3759800554underline {text-decoration:underline;}#yiv3759800554
.yiv3759800554attach
{clear:both;display:table;font-family:Arial;font-size:12px;padding:10px
0;width:400px;}#yiv3759800554 .yiv3759800554attach div a
{text-decoration:none;}#yiv3759800554 .yiv3759800554attach img
{border:none;padding-right:5px;}#yiv3759800554 .yiv3759800554attach label
{display:block;margin-bottom:5px;}#yiv3759800554 .yiv3759800554attach label a
{text-decoration:none;}#yiv3759800554 blockquote {margin:0 0 0
4px;}#yiv3759800554 .yiv3759800554bold
{font-family:Arial;font-size:13px;font-weight:700;}#yiv3759800554
.yiv3759800554bold a {text-decoration:none;}#yiv3759800554 dd.yiv3759800554last
p a {font-family:Verdana;font-weight:700;}#yiv3759800554 dd.yiv3759800554last p
span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv3759800554
dd.yiv3759800554last p span.yiv3759800554yshortcuts
{margin-right:0;}#yiv3759800554 div.yiv3759800554attach-table div div a
{text-decoration:none;}#yiv3759800554 div.yiv3759800554attach-table
{width:400px;}#yiv3759800554 div.yiv3759800554file-title a, #yiv3759800554
div.yiv3759800554file-title a:active, #yiv3759800554
div.yiv3759800554file-title a:hover, #yiv3759800554 div.yiv3759800554file-title
a:visited {text-decoration:none;}#yiv3759800554 div.yiv3759800554photo-title a,
#yiv3759800554 div.yiv3759800554photo-title a:active, #yiv3759800554
div.yiv3759800554photo-title a:hover, #yiv3759800554
div.yiv3759800554photo-title a:visited {text-decoration:none;}#yiv3759800554
div#yiv3759800554ygrp-mlmsg #yiv3759800554ygrp-msg p a
span.yiv3759800554yshortcuts
{font-family:Verdana;font-size:10px;font-weight:normal;}#yiv3759800554
.yiv3759800554green {color:#628c2a;}#yiv3759800554 .yiv3759800554MsoNormal
{margin:0 0 0 0;}#yiv3759800554 o {font-size:0;}#yiv3759800554
#yiv3759800554photos div {float:left;width:72px;}#yiv3759800554
#yiv3759800554photos div div {border:1px solid
#666666;height:62px;overflow:hidden;width:62px;}#yiv3759800554
#yiv3759800554photos div label
{color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv3759800554
#yiv3759800554reco-category {font-size:77%;}#yiv3759800554
#yiv3759800554reco-desc {font-size:77%;}#yiv3759800554 .yiv3759800554replbq
{margin:4px;}#yiv3759800554 #yiv3759800554ygrp-actbar div a:first-child
{margin-right:2px;padding-right:5px;}#yiv3759800554 #yiv3759800554ygrp-mlmsg
{font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv3759800554
#yiv3759800554ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv3759800554
#yiv3759800554ygrp-mlmsg select, #yiv3759800554 input, #yiv3759800554 textarea
{font:99% Arial, Helvetica, clean, sans-serif;}#yiv3759800554
#yiv3759800554ygrp-mlmsg pre, #yiv3759800554 code {font:115%
monospace;}#yiv3759800554 #yiv3759800554ygrp-mlmsg *
{line-height:1.22em;}#yiv3759800554 #yiv3759800554ygrp-mlmsg #yiv3759800554logo
{padding-bottom:10px;}#yiv3759800554 #yiv3759800554ygrp-msg p a
{font-family:Verdana;}#yiv3759800554 #yiv3759800554ygrp-msg
p#yiv3759800554attach-count span {color:#1E66AE;font-weight:700;}#yiv3759800554
#yiv3759800554ygrp-reco #yiv3759800554reco-head
{color:#ff7900;font-weight:700;}#yiv3759800554 #yiv3759800554ygrp-reco
{margin-bottom:20px;padding:0px;}#yiv3759800554 #yiv3759800554ygrp-sponsor
#yiv3759800554ov li a {font-size:130%;text-decoration:none;}#yiv3759800554
#yiv3759800554ygrp-sponsor #yiv3759800554ov li
{font-size:77%;list-style-type:square;padding:6px 0;}#yiv3759800554
#yiv3759800554ygrp-sponsor #yiv3759800554ov ul {margin:0;padding:0 0 0
8px;}#yiv3759800554 #yiv3759800554ygrp-text
{font-family:Georgia;}#yiv3759800554 #yiv3759800554ygrp-text p {margin:0 0 1em
0;}#yiv3759800554 #yiv3759800554ygrp-text tt {font-size:120%;}#yiv3759800554
#yiv3759800554ygrp-vital ul li:last-child {border-right:none
!important;}#yiv3759800554
