IF THEN statement in a query

[Neozero]

How would I go about putting an IF THEN statement in a select query. Example:


SELECT Name, Age, SSN, Pay, PayType
FROM Base, Payroll
WHERE BaseID = PayrollID AND

IF paytype = '0' then bencomp3
ELSE BASE


I am confused as to how I would put that into my select statement. Any help would be appreciated.



Neozero

 

[GShen]

I think you may want a CASE statement, something like the following:

SELECT Name, Age, SSN, Pay, PayType
FROM Base, Payroll
WHERE BaseID = PayrollID AND
CASE PayType
WHEN '0' THEN Bencomp3
ELSE BASE
END

I am not sure the rest of your syntax is ok. I never did a FROM statement like that. Not sure what Bencomp3 is and BASE a table? If so what is the ELSE doing?
Maybe the following is what you want?

SELECT Name,
Age,
SSN,
Pay,
CASE PayType
WHEN '0' THEN Bencomp3
ELSE BASE
END AS txtPayType
FROM Base
INNER JOIN Payroll ON
BaseID = PayrollID

Let me know if I am confused or I confused you. Your question is a little vague, I did the best I could.

 

[niklasw]

Hi, if your not using oracle or somffin with PL/SQL, Im not sure you can use stored procedures, loops, if/then and so on.

If you want, you can put more where statements after eachother, like
SELECT *
FROM FRIENDS
WHERE NAME='sniffo' AND AGE=19
ORDER BY AGE;

-Cheers, Niklas

 

[GShen]

niklasw,
I was under the understanding it was SQL. And you sure can use loops, if/then, etc. statements in it.
Neozero, is this and MDB query or an ADP stored procedure?

 

[SQLBill]

You haven't given us enough information about your IF statement. What are you trying to do with the IF? Give us examples.

As for the FROM, while that JOIN works here's a better way to do it:

SELECT Name, Age, SSN, Pay, PayType
FROM Base B
JOIN Payroll P
ON B.BaseID = P.PayrollID

This might get you started on what you want with the IF:

IF (SELECT paytype FROM Payroll) = '0'
BEGIN
SELECT Name, Age, SSN, Pay, PayType
FROM Base B
JOIN Payroll P
ON B.BaseID = P.PayrollID
END
ELSE
BASE

Again, it's hard to help you with this since you didn't give enough information about what the IF does. What is bencomp3? What is BASE?

-SQLBill

 

[Neozero]

Here is my actual statment. What I need to do is figure out under the 'WHERE' clause. I need to pull the record for a particualr person that has a record that matches BENCOMP3 and EmAnnual is >0 otherwise I need to get the BASE record and EmAnnual amount for the employee.



QUERY:


SELECT

'EMP' AS 'RecordType',
'252' AS ClientID,
'SCP' AS ClientCode,
'O' AS DataDestination,
EBase.EbSocnumber AS SSN,
EBase.EbSocNumber AS LoginID,
RIGHT(EBase.EbSocnumber,4) AS Password,
EBase.EbLastName AS LastName,
EBase.EbFirstName AS FirstName,
EBase.EbMiddleName AS MiddleName,
LEFT (EPerson.EpSex,1) AS Gender,
CONVERT (Varchar(12), EPerson.EpDateBorn, 101) AS BirthDate,
EPerson.EpStreet1 AS PrimaryAddressLine1,
EPerson.EpStreet2 AS PrimaryAddressLine2,
EPerson.EpCity AS PrimaryCity,
EPerson.EpState AS PrimaryStateCode,
EPerson.EpZip AS PrimaryZipCode,
'' AS AlternateAddressLine1,
'' AS AlternateAddressLine2,
'' AS AlternateCity,
'' AS AlternateStateCode,
'' AS AlternateZipCode,
EPerson.EpEMail AS Email,
EPerson.EpHomePhone AS HomePhone,
EJob.EjFaxPhone AS Fax,
CONVERT (Varchar(12), EEmploy.EeDateLastHire, 101) AS HireDate,
CONVERT (Varchar(12), EEmploy.EeTermDate, 101) AS TerminationDate,
EBase.EbEmpNumber AS EmployeeNumber,
EComp.EmAnnual AS AnnualWage,
' ' AS EmployeeType,
'[Calculate]' AS OriginalEffectiveDate,
'[Calculate]' AS ChangeEffectiveDate,
RIGHT (EJob.EjFlxIDBub ,5) AS GroupID,
RIGHT ('000' + EJob.EjDivision ,4) AS SubGroup,
EBase.EbClock AS JobCategory,
EBase.EbPSID AS HealthClass,
Eemploy.EeCategory AS WorkCategory,
Eemploy.EeStatus AS CostCenter,
EPerson.EpMarital AS MaritalStatus,
EComp.EmHoursPerPay AS 'Scheduled Hours',
JobCode.JbFlsa AS 'Exempt/Non-Exempt',
EJob.EjWorkPhone AS 'Work Phone',
EJob.EjExt AS 'Work Ph. Ext.',
EPerson.EpPrimaryEmergContact AS 'JobTitle',
EPerson.EpHomePhone AS 'Emergency Phone',
EPayrollBase.PrbRateCode AS 'Hourly/Salary',
EJob.EjWhyTerm AS 'Termination Reason',
EEmploy.EeKeyEmp AS 'Key Person',
CONVERT (Varchar(12), EEmploy.EeDateOriginalHire, 101) AS 'Original Hire Date',
CONVERT (Varchar(12), EEmploy.EeDateLastHire, 101) AS 'Last Hire Date',
EComp.EmAnnual AS 'Annualized Rate of Pay',
EComp.EmKind AS 'Kind of Compensation',
'' AS 'LOAEndDate',
'' AS 'LOAStartDate',
EBase.EbFlxID AS 'FLEX ID',
EEmploy.EeDateBeg AS 'Employee Status Date'


FROM
EBase, EPerson, EJob, EEmploy, EPayrollBase, JobCode, EComp


WHERE
EpFlxIDEb = EbFlxID AND
EjFlxIDEb = EbFlxID AND
EeFlxIDEb = EbFlxID AND
PrbFlxIDEb = EbFlxID AND
EmFlxIDEb = EbFlxID AND
EjJobCode = JbJobCode AND
(EpDateBeg <= GetDate () AND
(EpDateEnd >= GetDate () OR EpDateEnd IS NULL)) AND
(EjDateBeg <= GetDate () AND
(EjDateEnd >= GetDate () OR EjDateEnd IS NULL)) AND
(EeDateBeg <= GetDate () AND
(EeDateEnd >= GetDate () OR EeDateEnd IS NULL)) AND
(PrbDateBeg <= GetDate () AND
(PrbDateEnd >= GetDate () OR PrbDateEnd IS NULL)) AND
(EmDateBeg <= GetDate () AND
(EmDateEnd >= GetDate () OR EmDateEnd IS NULL)) AND
EmKind IN ('BASE', 'BENCOMP3') AND EbFlagEmp = 'Y' OR
(EbDateEnd <> NULL AND
EeDateEnd IS NULL AND
PrbDateEnd IS NULL AND
EbFlagEmp = 'Y' AND
EbRecType = 'EBAS' AND
EpRecType = 'EPER' AND
EpDateEnd = EbDateEnd AND
EjDateEnd = EbDateEnd AND
EmDateEnd = EbDateEnd AND
EbDateEnd > GetDate())

 

[GShen]

Is your query above working or not? Do you want to add to it? Still do not know what BASE is. You have mentioned BASE record a few times. Is this a table or a record type within a table or is it a column value for a field with a record. If so, which table, which field, etc.
Is your problem you are not pulling the correct records or is it you are not settin EmAnnual to the proper value.

 

[Neozero]

The query is working. I need to add an IF THEN statement to it. Base is a record type within the ecomp table as well as Bencomp3. what is happening is when the query runs, it is pulling a bencomp3 and base record. what I need it to do is only pull the record that does not have an emannual amount of 0 so I guess what I am trying to say in my query is something along the lines of ...


IF EmKind = 'BENCOMP3' AND EmAnnual = '0'
THEN BASE
ELSE BENCOMP3

I don't know if I am still not making sense or not.

 

[GShen]

I am really trying to understand or I would not be replying and I am not trying to fustrate you. Just to make sure I understand. You query is working (EG. not getting an error) but you are NOT getting the correct records selected.
If that is correct then the Query is not working from my standpoint.

If your query is returning the correct records use the following in the select clause.

CASE EmKind
WHEN 'BENCOMP3' THEN CASE EmAnnual
WHEN 0 THEN BASE
ELSE EMCOMP3
END
ELSE EMCOMP3
END

If you query is not pulling in the correct records why don't you simply add 1 more condition of emannual > 0?

Let me know how you are making out. If you have another problem, please let me know which route above is correct.
eg. You are not getting the proper value for a field or you are not getting the proper records returned. The 1st has to do with a field calculation the latter with a WHERE condition.