Sql Query

Discussion in 'Databases' started by USMCCHICAGO, Mar 30, 2009.

  1. #1
    I attempting to run the following query and I m getting the error message
    -------------------
    select

    P.last_name
    ,p.first_name,
    l.id as loan_id,
    l.status_cd as loan_status,
    l.created_on as loan_created_on,
    l.funding_date_actual,
    c.id as customer_id,
    c2.id as other_customer_id,
    c2.status_cd as other_customer_status

    from customers c

    inner join loans l on l.customer_id=c.id and l.status_cd not in ('declined','withdrawn')
    and not exists (select 1 from loans l2 where l2.customer_id = c.id and l2.id < l.id and l2.status_cd not in ('withdrawn','declined'))

    inner join people p on p.frist_name = p.id
    inner join people p on c.person_id = p.id

    inner join people p2 on p.id != p2.id and
    (
    (p.home_phone=p2.home_phone and p.home_phone not like '1800%' and p.home_phone not like '1888%')
    or (p.home_phone=p2.work_phone and p.home_phone not like '1800%' and p.home_phone not like '1888%')
    or (p.home_phone=p2.mobile_phone and p.home_phone not like '1800%' and p.home_phone not like '1888%')
    or (p.work_phone=p2.home_phone and p2.home_phone not like '1800%' and p2.home_phone not like '1888%')
    or (p.work_phone=p2.mobile_phone and p.work_phone is not null and p2.mobile_phone not like '1800%' and p2.mobile_phone not like '1888%' and p2.work_phone != p2.mobile_phone)
    or (p.mobile_phone=p2.home_phone and p.mobile_phone not like '1800%' and p.mobile_phone not like '1888%')
    or (p.mobile_phone=p2.work_phone and p.mobile_phone is not null and p.mobile_phone not like '1800%' and p.mobile_phone not like '1888%' and p.work_phone != p.mobile_phone)
    or (p.mobile_phone=p2.mobile_phone and p.mobile_phone is not null and p.mobile_phone not like '1800%' and p.mobile_phone not like '1888%'and p.work_phone != p.mobile_phone)
    or /*(p.work_phone=p2.work_phone and p.work_phone not in (''))*/
    )

    inner join customers c2 on c2.person_id = p2.id


    where
    (l.funding_date_actual > current_date or l.funding_date_actual is null)
    and p.home_phone is not null

    order by 2,1,7
    --------------------------
    THE ERROR message is
    ERROR: syntax error at or near ")" at character 1621
    -----------------

    All help would be great thanks
     
    USMCCHICAGO, Mar 30, 2009 IP
  2. lp1051

    lp1051 Well-Known Member

    Messages:
    163
    Likes Received:
    7
    Best Answers:
    0
    Trophy Points:
    108
    #2
    Hi, what if you delete this line??

    or /*(p.work_phone=p2.work_phone and p.work_phone not in (''))*/

    Currently it's empty condition after OR.
     
    lp1051, Mar 30, 2009 IP
  3. druidelder

    druidelder Peon

    Messages:
    285
    Likes Received:
    17
    Best Answers:
    0
    Trophy Points:
    0
    #3
    lp is in the right track. The line that is the issue is:

    or /*(p.work_phone=p2.work_phone and p.work_phone not in (''))*/


    The error is that you start commenting after the or, but there is no next condition.

    If you want that line commented out it should be:

    /*or (p.work_phone=p2.work_phone and p.work_phone not in (''))*/
     
    druidelder, Apr 1, 2009 IP