CODEWITHSUNDEEP
sqldb2
user101289
user101289

Reputation: 10422

DB2 alias in WHERE clause

I have a couple DB2 tables, one for users and one for newsletters and I want to select using an alias in the WHERE clause.

SELECT a.*, b.tech_id as user FROM users a  
JOIN newsletter b ON b.tech_id = a.newsletter_id
WHERE timestamp(user) < current_timestamp

This is radically simplified so I can see what's going on, but I am getting an error that makes me think that the user alias isn't getting passed correctly: 

ERROR: An invalid datetime format was detected; that is, an 
invalid string representation or value was specified.

The user.tech_id is a string built from the datetime when the record was created, so it looks something like 20150210175040951186000000. I've verified that I can execute a timestamp(tech_id) successfully-- so it can't be the format of the field causing the problem.

Any ideas?

More information:

There's multiple newsletters per user. I need to get the most recent newsletter (by the tech_id) and check if that was created in the past week. So the more complex version would be something like:

SELECT a.*, b.tech_id as user FROM users a  
JOIN newsletter b ON b.tech_id = a.newsletter_id
WHERE timestamp(max(user)) < current_timestamp

Is there a way to JOIN only on the most recent record?

Upvotes: 0

Views: 2383

Answers (2)

Parfait
Parfait

Reputation: 107587

To get most recent newsletter of user, consider ordering the join query, then select top record (in DB2 you would use FETCH FIRST ONLY):

SELECT a.*, b.tech_id as user 
  FROM users a  
INNER JOIN newsletter b ON b.tech_id = a.newsletter_id
ORDER BY b.tech_id
FETCH FIRST 1 ROW ONLY;

Alternatively, you can use a subquery in WHERE clause that aggregates the max user:

SELECT a.*, b.tech_id as user 
  FROM users a  
WHERE b.tech_id IN (
  SELECT Max(n.tech_id) as maxUser
  FROM users u 
  INNER JOIN newsletter n ON n.tech_id = u.newsletter_id)

I left out the condition of timestamp(user) < current_timestamp as data stored in database will always be less than current time (i.e., now).

Upvotes: 0

Paul Maxwell
Paul Maxwell

Reputation: 35583

The order of execution is different to the order of writing. The FROM & WHERE clauses are executed before the SELECT clause hence the alias does not exist when you are trying to use it.

You would have to "nest" part of the query so that the alias is defined before the where clause. Can be easier in many cases to not use the alias.

try

WHERE timestamp(b.tech_id) < current_timestamp

The generic "order of execution" of SQL clauses is

FROM
   JOINs (as part of the from clause)
WHERE
GROUP BY
HAVING
SELECT
ORDER BY

Is there a way to JOIN only on the most recent record?

A useful technique for this is using ROW_NUMBER() assuming your DB2 supports it, and would look something like this:

SELECT
      a.*
    , b.tech_id AS techuser
FROM users a
JOIN (
      SELECT
            *
          , ROW_NUMBER() OVER (ORDER BY timestamp(tech_id) DESC) AS RN
      FROM newsletter
) b
      ON b.tech_id = a.newsletter_id
      AND b.rn = 1

this would give you just one row from newsletter, and using the DESCending order gives you the "most recent" assuming timestamp(tech_id) works as described.

Upvotes: 1

Related Questions