Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4

Thread: help

  1. #1
    New to the CF scene
    Join Date
    Oct 2012
    Posts
    2
    Thanks
    1
    Thanked 0 Times in 0 Posts

    help

    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'


    --

  • #2
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    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.

  • Users who have thanked Fou-Lu for this post:

    jmorris (10-29-2012)

  • #3
    New to the CF scene
    Join Date
    Oct 2012
    Posts
    2
    Thanks
    1
    Thanked 0 Times in 0 Posts
    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)

  • #4
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    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.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •