Click here to Skip to main content
15,940,550 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have a query, actually I have 8 bit fields in my table and I want to select the one that is true.
Please help me.

VB
Dim Sql As String = "SELECT [EmployeeID],([FirstName]+[LastName]) AS EmpName,[Department],[MonthlySalary], "
            Sql = Sql & " PayPeriod=(CASE WHEN WeeklyWages='1' THEN (CASE WHEN WeeklyWages='1' THEN 'Weekly' END) "
            Sql = Sql & " ELSE (CASE WHEN SemiMonthlyWages='1' THEN (CASE WHEN SemiMonthlyWages='1' THEN 'SemiMonthly' END) "
            Sql = Sql & " ELSE(CASE WHEN MonthlyWages='1' THEN (CASE WHEN MonthlyWages='1' THEN 'Monthly' END) "
            Sql = Sql & " ELSE(CASE WHEN QuarterlyWages='1' THEN (CASE WHEN QuarterlyWages='1' THEN 'Quarterly' END) "
            Sql = Sql & " ELSE(CASE WHEN HalfYearlyWages='1' THEN (CASE WHEN HalfYearlyWages='1' THEN 'HalfYearly' END) "
            Sql = Sql & " ELSE(CASE WHEN yearlyWages='1' THEN (CASE WHEN yearlyWages='1' THEN 'Yearly' END) "
            Sql = Sql & " ELSE(CASE WHEN DailyWages='1' THEN (CASE WHEN DailyWages='1' THEN 'Daily' END) "
            Sql = Sql & " ELSE(CASE WHEN MiscWages='1' THEN (CASE WHEN MiscWages='1' THEN 'Miscellaneous' END) "
            Sql = Sql & " END)END)END)END)END)END)END)END) "
            Sql = Sql & " FROM [IRLPayRollSystem].[irlPayRoll].[Employee_Details_Entry]  where [irlPayRoll].[Employee_Details_Entry].EmployeeID = '" & Session("AddPayrollEmp") & "'"


[Edit - Summarized the subject, moved the question to the base of the question, fixed spelling, removed text speak]
Posted
Updated 7-Feb-12 5:10am
v2

1 solution

VB
ELSE(CASE WHEN MonthlyWages='1' THEN (CASE WHEN MonthlyWages='1' THEN 'Monthly' END) "


Why do you check each case twice ?

I don't see why this would not work. It seems convoluted, why not one 'case' statement that iterates through all the possibilities ? Why not use 'as' to give your column a name ? If you have 8 bit fields, what happens if more than one is selected ? I think you havea design flaw and you meant one field to contain a value that maps to an enum.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900