CODEWITHSUNDEEP
sqlsql-serverredash
Hanz pecter
Hanz pecter

Reputation: 9

Replace null in last row of the column to text in lead function

Does someone know how to replace null in last row of the column to text in lead function for example

I'm using

select cust_id, ticketid, 
    lead(ticketid,1, no more tickets) over (order by ticket_id) as nextticketid,
    date as bookingdate
from booking_tickets
where day >= date '2024-04-01'
order by ticketid ASC

Columns cust_id, ticketid, nextticketid bookingdate from table booking_tickets

I want here to get result of last row to show end of tickets instead of null or being empty but every time I get mismatch input. I want the output to be like

Cust_id.  Ticketid.   Nextticketid. Bookingdate
1.          234.         235.        1 apr2024
2.          235.         236.        2 apr2024
3.          236.   No more tickets

But I'm getting error:

Mismatched input 'more'. Expecting: '%', '(', ')', '*', '+', ',', '-', '->', '.', '/', 'AND', 'AT', 'OR', 'ORDER', 'OVER', '[', '||', ,

Upvotes: 0

Views: 101

Answers (1)

SQLpro
SQLpro

Reputation: 5103

With the following table and dataset:

CREATE TABLE booking_tickets
(Cust_id INT,  Ticketid INT, Bookingdate DATE);

INSERT INTO booking_tickets VALUES
(1, 234, '2024-04-01'),
(2, 235, '2024-04-02'),
(3, 236, '2024-04-03');

One query could be :

SELECT Cust_id, Ticketid, 
       COALESCE(
          CAST(
             LEAD(Ticketid, 1) OVER (ORDER BY Ticketid)
             AS VARCHAR(32)), 
          'no more tickets') AS nextticketid,
       Bookingdate 
FROM   booking_tickets
WHERE  Bookingdate >= '2024-04-01'
ORDER  BY Ticketid ASC;

The result is :

Cust_id     Ticketid    nextticketid                     Bookingdate
----------- ----------- -------------------------------- -----------
1           234         235                              2024-04-01
2           235         236                              2024-04-02
3           236         no more tickets                  2024-04-03

If you want the last booking date to be NULL, you can use a CASE expression to do it.

Upvotes: 0

Related Questions