help

[jmorris]

I am coding sql in a perl. I am trying to use dateadd. the code below is not working. It errors when the code starts the DATEADD

where B.EMPLID = A.EMPLID
and C.EMPLID = A.EMPLID
and G.EMPLID = A.EMPLID

and A.EFFDT =
(select max(A_ED.EFFDT) from PS_JOB A_ED
where A.EMPLID = A_ED.EMPLID
and A.EMPL_RCD = A_ED.EMPL_RCD
and A_ED.EFFDT <= substring(convert(char, getdate(), 121), 1, 10))
and A.EFFSEQ =
(select max(A_ES.EFFSEQ) from PS_JOB A_ES
where A.EMPLID = A_ES.EMPLID
and A.EMPL_RCD = A_ES.EMPL_RCD
and A.EFFDT = A_ES.EFFDT)


and substring(A.JOBCODE, 1, 2) <> 'CL'
and A.BUSINESS_UNIT like '$agency'
and (dateadd(DAY, -1, A.EFFDT))
and A.EFFDT between '$fromdate' and '$todate'


--

[Fou-Lu]

This is a SQL issue, not a Perl one. What RDBMS are you using?
The comparison doesn't look right here. You have a DATEADD (which may or may not exist, you need to clarify the db you are using and give us the error), but you aren't comparing to anything. In the middle of the where you'd have a simple date; perhaps you intend to compare that to something?

Also, in the future please select a more suitable title for the thread.

[jmorris]

It is oracle DB. DATEADD is a function. It obviously does not like how the dateadd is coded. I have not used this function before.

here us the error

An expression of non-boolean type specified in a context where a condition is expected, near 'and'. (SQL-42000)
[Microsoft][SQL Native Client][SQL Server]Incorrect syntax near the keyword 'and'. (SQL-42000)
[Microsoft][SQL Native Client][SQL Server]Incorrect syntax near the keyword 'and'. (SQL-42000)
[Microsoft][SQL Native Client][SQL Server]Incorrect syntax near the keyword 'and'. (SQL-42000)
[Microsoft][SQL Native Client][SQL Server]Incorrect syntax near the keyword 'and'. (SQL-42000)

[Fou-Lu]

DATEADD isn't a part of ORACLE SQL.
What will work is ADATEFIELD - INTERVAL '1' DAY(1). Although you'll still need to compare it to something.
That error itself looks to me like a SQL Server error, not a Oracle error.