GENERATE NO TRANSACTION INCREMENT OR SEQUENCE BY QUERY

GENERATE NO TRANSACTION INCREMENT OR SEQUENCE BY QUERY

--GENERATE PLAN LINK200600807 FOR COLUMN 'ordersn'

--USED MAX NOT COUNT
--TESTED IN POSTGRESQL
-- FUNCTION ONLY POSTGRES 
  -- 1. '||' FOR CONCAT 
  -- 2. 'TO_CHAR' FOR GENERATE DATE FORMAT 
  -- 3. '::TEXTT' FOR CASTING

SELECT 'LINK' || to_char(CURRENT_DATE, 'YYMMDD') ||
RIGHT( '0000' || 
	  	(
			SELECT ((COALESCE(MAX(CAST(RIGHT(ordersn, 4) AS INT)),0))+1)::TEXT 
			FROM mar_tra_sales_order
			where type_so = 'Link' 
			and status in ('APPROVED','CLOSED','USED','POST') 
			and ordersn != '' 
			and ordersn is not null
		), 4)  as newordersn