0

The following statement work fine:

SELECT DISTINCT 
woas.assessmentid,
woas.workorderid FROM wo_assessment
    LEFT JOIN wo_assessment woas ON woas.assessmentid = woas.assessmentid
LEFT JOIN wo_group_info wogi ON woas.assessmentid=wogi.assessmentid

However when I add the below sub query, I get a generic error about the FROM clause 'ERROR: syntax error at or near "FROM"'

first_value(wogi.groupid ) over (partition by value_partition ORDER BY woas.assessmentid) 
     FROM (SELECT woas.assessmentid,wogi.groupid, sum(case when wogi.groupid is null then 0 else 1 end) over (ORDER BY woas.assessmentid) as value_partition
FROM wo_assessment
ORDER BY  woas.assessmentid) as q

Here is the complete code:

SELECT DISTINCT 
woas.assessmentid,
woas.workorderid,
first_value(wogi.groupid ) over (partition by value_partition ORDER BY woas.assessmentid) 
     FROM (SELECT woas.assessmentid,wogi.groupid, sum(case when wogi.groupid is null then 0 else 1 end) over (ORDER BY woas.assessmentid) as value_partition
FROM wo_assessment
ORDER BY  woas.assessmentid) as q FROM wo_assessment
    LEFT JOIN wo_assessment woas ON woas.assessmentid = woas.assessmentid
LEFT JOIN wo_group_info wogi ON woas.assessmentid=wogi.assessmentid

I cant for the life of me understand why it wont validate, any help greatly appreciated.

Latest attempt, it now says by sub query returns more that one result.

SELECT DISTINCT 
woas.assessmentid,
woas.workorderid,

first_value(wogi.groupid ) over (partition by value_partition ORDER BY woas.assessmentid) ,

(SELECT woas.assessmentid,wogi.groupid, sum(case when wogi.groupid is null then 0 else 1 end) over (ORDER BY wo.assessmentid) as value_partition
FROM wo_assessment wo LEFT JOIN wo_assessment woas ON woas.assessmentid = wo.assessmentid WHERE woas.assessmentid = wo.assessmentid ORDER BY  wo.assessmentid) as q FROM wo_assessment

LEFT JOIN wo_assessment woas ON woas.assessmentid = woas.assessmentid
LEFT JOIN wo_group_info wogi ON woas.assessmentid=wogi.assessmentid
user3580480
  • 442
  • 7
  • 14
  • 45
  • You can have at most one `from` clause... – mechanical_meat Jan 31 '18 at 16:23
  • What are you trying to do? Your query doesn't make a whole lot of sense right now. – mechanical_meat Jan 31 '18 at 16:39
  • Please take a look at my earlier post. https://stackoverflow.com/questions/48543630/populate-zero-values-in-column-with-next-value-greater-than-zero I am trying to take the select query from this post and include it as a sub select as part of an over arching select statement. I have made some progress. Hopefully my new code (update above) makes a bit more sense – user3580480 Jan 31 '18 at 16:43
  • You are still referencing a table within the subquery that is joined to *outside* of the subquery. I am somewhat surprised that this even runs. – mechanical_meat Jan 31 '18 at 16:49
  • I will take a look at the earlier post, thanks for linking to it. – mechanical_meat Jan 31 '18 at 16:50

0 Answers0