current community

your communities

more stack exchange communities

Stack Exchange
tour help
stack overflow careers
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote -2 down vote favorite 
    select * from (
    select max(h.updated_datetime) as max, min(h.updated_datetime) as min from report r, report_history h, procedure_runtime_information PRI, study S
    where 
    h.report_fk=r.pk and
    r.study_fk=S.pk and
    PRI.pk=S.procedure_runtime_fk and
    extract(epoch from (max(h.updated_datetime) - min(h.updated_datetime) ) <=900 and
    h.pk IN (
    select pk from 
    (select * from report_history where report_fk=r.pk) as result
    )

  and r.status_fk =21 group by r.pk)as result1;

this is my query i have a syntax error can any one help me fix this thanks in advance

share|improve this question
1  
And the error is? –  a_horse_with_no_name Jun 4 '13 at 9:04
    
Downvoted because you left out pretty key info like the error message. Please read the asking better questions section of stackoverflow.com/tags/postgresql/info –  Craig Ringer Jun 4 '13 at 12:06

1 Answer 1

up vote 0 down vote accepted

As you didn't bother telling us what the error is I have to guess, that it's this line:

AND h.pk IN (SELECT pk FROM (SELECT * FROM report_history WHERE report_fk=r.pk) AS RESULT)

The nesting level for the where condition is "too deep" and I think it cannot see the r alias in the where clause.

But the nested select is totally useless in your case anyway, so you can rewrite that condition as:

AND h.pk IN (SELECT pk FROM report_history WHERE report_fk=r.pk)

Even if that doesn't solve your problem, it makes your query more readable.

Then you are using an aggregate in the where clause which is also not allowed, you have to move it to a having clause.

having extract(epoch from (max(h.updated_datetime) - min(h.updated_datetime))) <=900

The having clause comes after the group by

You were also missing a closing ) but that is hard to tell because of your formatting (which I find very hard to read)

You should also get used to explicit JOIN syntax. The implicit joins in the WHERE clause are error-prone and no longer recommended.

share|improve this answer
    
thanks, sorry for repliying late –  Saravana Kumar Subramaniam Jun 7 '13 at 5:16

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.