Proble,m with SQL Code

Databases
1

Hello all,

I have a scenario where I need to get the next available number in a sequence.

I wrote this code which works.

WITH max_id AS (
    SELECT COALESCE(MAX(taskid), 99) as max_taskid
    FROM app_cloud_communication
),
series_range AS (
    SELECT GREATEST(max_taskid, 200) as upper_bound
    FROM max_id
) 
SELECT s.num 
FROM series_range sr 
CROSS JOIN generate_series(100, sr.upper_bound, 1) AS s(num) 
LEFT JOIN app_cloud_communication AS t ON s.num = t.taskid 
WHERE t.taskid IS NULL
ORDER BY s.num 
LIMIT 1;

However, when I try to make it into a string, it fails on the last WHERE saying it is a syntax error?

With max_id As ( Select COALESCE(Max(taskid), 99) As max_taskid FROM app_cloud_communication), series_range As ( Select GREATEST(max_taskid, 200) As upper_bound FROM max_id ) Select s.num FROM series_range srCROSS Join generate_series(100, sr.upper_bound, 1) As s(num) Left Join app_cloud_communication As t ON s.num = t.taskid WHERE t.taskid Is NULL ORDER BY s.num LIMIT 1;

Any ideas why this would fail and how to correct?
Thanks,
Tim

2

srCROSS is parsed as a single identifier — which doesn’t exist

3

Specifically, srCROSS is missing a space: sr CROSS

1 Like
4

Thank you both! Been trying to get this fixed for an hour already and never saw that!

Much appreciated!
Tim

5

Isn’t this what i wrote? :smiley:

6

Yes Sascha. Tims was clearer which is why I marked that one a solution - for the next guy who may have similar issue.

Thanks again!
Tim

1 Like