title: AS400 - Why token *, ! not valid? What could be the alternate way to run the query using STRSQL - SQL Interactive Session?
body: <p>In AS400, How can I run this query using STRSQL?</p>
  <p>For the below query getting the following error message instead returning results.</p>
  <blockquote>
  <p>&quot;Token , was not valid. Valid tokens: FROM INTO.&quot;</p>
  </blockquote>
  <p>Code Snippet for Original Query:</p>
  <pre><code>WITH cte AS ( SELECT *, 
      !SUM(status != 'INACTIVE') OVER (PARTITION BY loc_code, user_id, service_area, service_sector) only_inactive,
      ROW_NUMBER() OVER (PARTITION BY loc_code, user_id, service_area, service_sector ORDER BY last_changed DESC) rn
      FROM test )
                
  SELECT * 
  FROM cte
  WHERE only_inactive AND rn = 1
  </code></pre>
  <p>After checking, the query I found the problem is the SELECT statement inside WITH clause. I don't know why this error is there? Unable to find a possible way to solve this issue.</p>
  <p>Now, I tried to remove * from the select statement inside WITH clause, somehow avoided this error. After executing my updated query again, I am getting same kind but a different error message.</p>
  <blockquote>
  <p>Token ! was not valid. Valid tokens: ( + * - ? : DAY INF LAG NAN RID
  ROW RRN CASE CAST CHAR DATE DAYS.</p>
  </blockquote>
  <p>Code Snippet for Updated Query:</p>
  <pre><code>WITH cte AS ( SELECT !SUM(status != 'INACTIVE') OVER (PARTITION BY loc_code, user_id, service_area, 
      service_sector) only_inactive,
      ROW_NUMBER() OVER (PARTITION BY loc_code, user_id, service_area, service_sector ORDER BY last_changed 
      DESC) rn
      FROM test )
                
  SELECT * 
  FROM cte
  WHERE only_inactive AND rn = 1
  </code></pre>
  <p><strong>I tried:</strong></p>
  <p>Instead of ! token I tried using &lt;&gt; and ¬= but it didn't help me in both cases. When I tried to run my code I encountered the same error message but now with &lt;&gt; and ¬= tokens.</p>
  <p><strong>Expected Result:</strong></p>
  <p>I want to return all records satisfying the logic given in the query with all columns in my table.</p>
  <p>Could someone please tell me how to solve this issue?</p>
  <hr />
  <p>I tried the following updated query just to test whether it's working without ! or NOT token or not.</p>
  <pre><code>WITH cte AS ( SELECT test.*, 
      SUM(status &lt; 'INACTIVE') OVER (PARTITION BY loc_code, user_id, service_area, service_sector) only_inactive,
      ROW_NUMBER() OVER (PARTITION BY loc_code, user_id, service_area, service_sector ORDER BY last_changed DESC) rn
      FROM test )
                
  SELECT * 
  FROM cte
  WHERE only_inactive AND rn = 1
  </code></pre>
  <p>I expected this query must have listed some results but this time I get this error message.</p>
  <blockquote>
  <p>Token &lt; was not valid. Valid tokens: ) ,.</p>
  </blockquote>
  <p>I think the problem is with tokens. Not sure what.</p>