SQL

[Henry Olive 5]

I wish everyone a healthy day

SELECT EMPNO,EMPNAME, PRIORTAXBASE, BRUTSALARY,

  Case
    When SHAREHOLDER='Y' then
             (PRIORTAXBASE + BRUTSALARY)

    When RETIRED='Y' then
             (PRIORTAXBASE + BRUTSALARY)-
            ((PRIORTAXBASE + BRUTSALARY) * 4 /100) +
  When (SHAREHOLDER IS NULL or SHAREHOLDER='N') then
       (PRIORTAXBASE + BRUTSALARY) -
       ((PRIORTAXBASE + BRUTSALARY) * 0.18)
 end as TAXBASE

 

/* Upper SQL works fine,  Here below i want to use  TAXBASE (upper result)   like below */

Case

When TAXBASE <= 30000 then TAXBASE * 20

When TAXBASE >30000 and TAXBASE <=50000 then TAXBASE * 30

....

end as TAXTOTAL

 

from EMPLOYEE

 

Thank You

 

 

[Stano 139]

Using WITH. Somehow like this. If the unknown DB supports it

WITH TB
AS
  (SELECT EMPNO,EMPNAME, PRIORTAXBASE, BRUTSALARY,
    Case
      When SHAREHOLDER='Y' then
               (PRIORTAXBASE + BRUTSALARY)
      When RETIRED='Y' then
               (PRIORTAXBASE + BRUTSALARY)-
              ((PRIORTAXBASE + BRUTSALARY) * 4 /100) +
    When (SHAREHOLDER IS NULL or SHAREHOLDER='N') then
         (PRIORTAXBASE + BRUTSALARY) -
         ((PRIORTAXBASE + BRUTSALARY) * 0.18)
   end as TAXBASE
  )
SELECT
  Case
  When TAXBASE <= 30000 then TAXBASE * 20
  When TAXBASE >30000 and TAXBASE <=50000 then TAXBASE * 30
  ....
  end as TAXTOTAL
FROM TB

 

[Henry Olive 5]

Thank you so much Stano, 

I'm so sorry, i forgot to add my message that i use IB-2007

and IB-2007 doesnt have Common Table Expression
In this case i think there is no chance WITHOUT a view or S.PROC
Again i'm so sorry.

[Stano 139]

So use it as a subselect.