Select * from Order  --doesn't work
select * from [order] --works
--it's planning module in our system...

I think, it's really bad idea to use reserved words in names of tables. I'm surprised, that this db doesn't contain table named like [select]...

By solbrain, 2017-12-28 08:04:28
FOR x IN ( SELECT COUNT(*) cnt
            FROM DUAL
           WHERE EXISTS ( SELECT NULL FROM task
                           WHERE task.task_type_id = lib.task_type_check()
                             AND task.task_status_id = lib.task_status_open()
                             AND task.unit_id = in_unit_id
                             AND task.station_id = v_station_id
                        )
         )
LOOP
  IF( x.cnt = 1 ) THEN
    v_task_exist := TRUE;
  ELSE
    v_task_exist := FALSE;
  END IF;
END LOOP;
By Anonymous, 2019-08-01 15:25:36
SELECT 
	tblPosts.idPost,
	tblPosts.postTitle, 
	tblPosts.postCode, 
	tblPostsStatus.scsTitle, 
	concat(code, '<br>', pTitle) AS pTitle, (CASE WHEN postResult = 1 THEN 'yes' ELSE 'no' END) AS postResult , 
	concat(date_format(postStartedDate, '%d.%m.%Y'), '<br>', date_format(postStartedDate, '%H:%i')) AS dateStart, 
	date_format(tblPosts.updated_at, '%d.%m.%Y %H:%i') AS dtUpdated, tblPerson.mobile, tblPerson.mail, tblPerson.isoLang, tblPostsReason.surname, 
	concat(coalesce(clientFName, ''), ' ', coalesce(clientName, '')) AS client, 
	concat(coalesce(concat(tblGender.gender, ' '), ''), coalesce(eFName, ''), ' ', coalesce(eName, '')) AS pers, 
	(CASE WHEN tblPosts.idCollab is NULLTHEN '' ELSE concat(coalesce(tblCol.cFName, ''), ' ', coalesce(tblCol.cName, '')) END) AS starter, 
	concat("<img src='img/status_", tblPosts.status, ".png' height='12px' border='1' />") AS displaystat,
	concat("<a href='index.php?arg=posts&id=", tblPosts.idPost, "&idAccess=", coalesce(tblAccess.idAccess, ''), "' title='Prepare Interface'><img src='../DG/styles/flat/images/edit.gif' style='height:14px; width:32px;' border='0'></a>") AS elink, 
	concat("<a href='email.php?frame=1&idPost=", tblPosts.idPost, "&source=PERS&idSrc=", tblPerson.idPerson, "&lang=", tblPerson.isoLang, "&from=", ";;SENDER", "&to=", tblPerson.persMail, "&idTperslate=CLI_diff_joindre_can,PERS_demande_contact,PERS_demande_CV&title=", replace(replace(replace(coalesce(pTitle, ''), "'", ";;APO"), "(", ";;PLT"), ")", ";;PGT"), "' title='", concat(coalesce(persName, ''), ' ', coalesce(persFname, '')), "' class='fancybox fancybox.iframe'><img src='img/email.png' height='12px' border='1' title='Messages' ></a>") AS mailink, 
	concat("<a href='post_email.php?frame=1&op=resend&idPosts=", tblPosts.idPost, "&idPerson=", tblPerson.idPerson, "&lang=", tblPerson.isoLang, "&idAccess=", coalesce(tblAccess.idAccess, ''), "&title=", replace(replace(replace(coalesce(pTitle, ''), "'", ";;APO"), "(", ";;PLT"), ")", ";;PGT"), "' title='", concat(coalesce(perName, ''), ' ', coalesce(perFName, '')), "' class='fancybox fancybox.iframe'><img src='img/emailNew.png' height='12px' border=1 title='New Posts' ></a>") AS newlink, 
	concat("<a href='comment.php?idPosts=", tblPosts.idPost, "&idPostsElem=-1&idClient=", tblPosts.idClient, "&frame=1&title=", replace(replace(replace(pTitle, "'", ";;APO"), "(", ";;PLT"), ")", ";;PGT"), "' title='", concat(coalesce(persName, ''), ' ', coalesce(persFName, '')), "' class='fancyboxstepreload fancybox.iframe'><div id='divBadgeComm", tblPosts.idPost, "' class='divBadge'>", (select count(*) from tblComment where idPosts=tblPosts.idPost and idElem is NULLand visibleClient=1), "</div><img src='img/comment.png' height='12px' border='1' title='Comments' /></a>") AS comments, 
	concat("<a href='docs.php?frame=1&idPosts=", tblPosts.idPost, "&title=", replace(replace(replace(pTitle, "'", ";;APO"), "(", ";;PLT"), ")", ";;PGT"), "' title='", concat(coalesce(persName, ''), ' ', coalesce(persFname, '')), "' class='fancyboxstep fancybox.iframe'><div id='divBadgeDocument", tblPosts.idPost, "' class='divBadge'>", (select count(*) from tblDocs where idPosts=tblPosts.idPost and docFileName <> '' and status=1), "</div><img src='img/dir.png' height='12px' border='1' title='Docs' /></a>") AS documents, 
FROM tblPosts
-- lots of LEFT JOIN
WHERE 1;

Maybe he didn't know how to do it in PHP ?

By Not a real programmer, 2018-07-10 11:03:17
 SELECT CAST(CASE a8900.AllowCloserChanges
             WHEN 0
             THEN 0
             ELSE 1 END AS BIT) AllowCloserChanges
 FROM Vision.sub8900.tApplication8900 a8900 
 WHERE a8900.ApplicationNum = @ApplicationNum

Inheriting others' code in the fun world of corporate development.

By The snitch, 2017-12-15 21:21:56
CREATE FUNCTION [dbo].[get_number_of_workers] 
(
     @number_of_workers varchar(10)
 
)
RETURNS int
AS
BEGIN
 
 
set @number_of_workers = replace(replace(replace(replace(LTRIM(RTRIM(@number_of_workers)), char(9), ''), char(10), ''), char(13),''), char(13)+char(10), '')
 
DECLARE @temp varchar(10)
 
SELECT
    @temp = number_of_workers 
FROM
    (SELECT LTRIM(RTRIM(@number_of_workers)) as number_of_workers) as temp
WHERE
       number_of_workers like  '[0-9]'
    or number_of_workers like  '[0-9][0-9]'
    or number_of_workers like  '[0-9][0-9][0-9]'
    or number_of_workers like  '[0-9][0-9][0-9][0-9]'
    or number_of_workers like  '[0-9][0-9][0-9][0-9][0-9]'
    or number_of_workers like  '[0-9][0-9][0-9][0-9][0-9][0-9]'
    or number_of_workers like  '[0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
    or number_of_workers like  '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
    or number_of_workers like  '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
    or number_of_workers like  '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
    or number_of_workers like  '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
     
     
RETURN CONVERT(INT, @temp)
 
END
By Anonymous, 2015-07-22 14:59:09
#335 SQL +47
		CREATE OR REPLACE FUNCTION melodious.query_flags(user_name varchar(32), chan_name varchar(32), igroup_name varchar(32), iflag_name varchar(32), flagcheck bool)
		RETURNS TABLE (
			group_holders jsonb [],
			flag_id int4,
			flag_name varchar(32),
			flag jsonb
		)
		LANGUAGE plpgsql
		AS $$
		DECLARE
			uid int4 := NULL;
			cid int4 := NULL;
			gid int4 := NULL;
		BEGIN
			IF user_name <> '' THEN
				SELECT id INTO uid FROM melodious.accounts WHERE username=user_name;
				IF uid IS NULL THEN
					RAISE EXCEPTION 'no such user';
				END IF;
			END IF;
			IF chan_name <> '' THEN
				SELECT id INTO cid FROM melodious.channels WHERE name=chan_name;
				IF cid IS NULL THEN
					RAISE EXCEPTION 'no such channel';
				END IF;
			END IF;
			IF igroup_name <> '' THEN
				SELECT id INTO gid FROM melodious.groups WHERE name=igroup_name;
				IF gid IS NULL THEN
					RAISE EXCEPTION 'go such group';
				END IF;
			END IF;

			IF igroup_name = '' THEN
				IF iflag_name = '' THEN
					IF user_name = '' AND chan_name = '' THEN
						RETURN QUERY SELECT
							ARRAY_AGG(JSONB_SET(ROW_TO_JSON(gh)::JSONB, '{group_name}'::TEXT[], TO_JSONB((SELECT name FROM melodious.groups WHERE id=gh.group_id LIMIT 1)))) AS group_holders,
							gf.id flag_id,
							gf.name flag_name,
							gf.flag flag
						FROM melodious.group_holders gh
						INNER JOIN melodious.group_flags gf
						ON gh.group_id = gf.group_id
						WHERE (NOT flagcheck) OR (flagcheck AND gh.user_id IS NULL AND gh.channel_id IS NULL)
						GROUP BY gf.id, gf.name;
					ELSIF user_name <> '' AND chan_name <> '' THEN
						RETURN QUERY SELECT
							ARRAY_AGG(JSONB_SET(ROW_TO_JSON(gh)::JSONB, '{group_name}'::TEXT[], TO_JSONB((SELECT name FROM melodious.groups WHERE id=gh.group_id LIMIT 1)))) AS group_holders,
							gf.id flag_id,
							gf.name flag_name,
							gf.flag flag
						FROM melodious.group_holders gh
						INNER JOIN melodious.group_flags gf
						ON gh.group_id = gf.group_id
						WHERE (gh.user_id = uid AND gh.channel_id = cid)
							 OR (flagcheck AND gh.user_id = uid AND gh.channel_id IS NULL)
							 OR (flagcheck AND gh.user_id IS NULL AND gh.channel_id = cid)
							 OR (flagcheck AND gh.user_id IS NULL AND gh.channel_id IS NULL)
						GROUP BY gf.id, gf.name;
					ELSIF user_name <> '' AND chan_name = '' THEN
						RETURN QUERY SELECT
							ARRAY_AGG(JSONB_SET(ROW_TO_JSON(gh)::JSONB, '{group_name}'::TEXT[], TO_JSONB((SELECT name FROM melodious.groups WHERE id=gh.group_id LIMIT 1)))) AS group_holders,
							gf.id flag_id,
							gf.name flag_name,
							gf.flag flag
						FROM melodious.group_holders gh
						INNER JOIN melodious.group_flags gf
						ON gh.group_id = gf.group_id
						WHERE (NOT flagcheck AND gh.user_id = uid)
							 OR (flagcheck AND gh.user_id = uid AND gh.channel_id IS NULL)
							 OR (flagcheck AND gh.user_id IS NULL AND gh.channel_id IS NULL)
						GROUP BY gf.id, gf.name;
					ELSIF user_name = '' AND chan_name <> '' THEN
						RETURN QUERY SELECT
							ARRAY_AGG(JSONB_SET(ROW_TO_JSON(gh)::JSONB, '{group_name}'::TEXT[], TO_JSONB((SELECT name FROM melodious.groups WHERE id=gh.group_id LIMIT 1)))) AS group_holders,
							gf.id flag_id,
							gf.name flag_name,
							gf.flag flag
						FROM melodious.group_holders gh
						INNER JOIN melodious.group_flags gf
						ON gh.group_id = gf.group_id
						WHERE (NOT flagcheck AND gh.channel_id = cid)
							 OR (flagcheck AND gh.user_id IS NULL AND gh.channel_id = cid)
							 OR (flagcheck AND gh.user_id IS NULL AND gh.channel_id IS NULL)
						GROUP BY gf.id, gf.name;
					END IF;
				ELSE
					IF user_name = '' AND chan_name = '' THEN
						RETURN QUERY SELECT
							ARRAY_AGG(JSONB_SET(ROW_TO_JSON(gh)::JSONB, '{group_name}'::TEXT[], TO_JSONB((SELECT name FROM melodious.groups WHERE id=gh.group_id LIMIT 1)))) AS group_holders,
							gf.id flag_id,
							gf.name flag_name,
							gf.flag flag
						FROM melodious.group_holders gh
						INNER JOIN melodious.group_flags gf
						ON gh.group_id = gf.group_id
						WHERE (gf.name = iflag_name)
							AND (
								(NOT flagcheck)
								OR (flagcheck AND gh.user_id IS NULL AND gh.channel_id IS NULL)
							)
						GROUP BY gf.id, gf.name;
					ELSIF user_name <> '' AND chan_name <> '' THEN
						RETURN QUERY SELECT
							ARRAY_AGG(JSONB_SET(ROW_TO_JSON(gh)::JSONB, '{group_name}'::TEXT[], TO_JSONB((SELECT name FROM melodious.groups WHERE id=gh.group_id LIMIT 1)))) AS group_holders,
							gf.id flag_id,
							gf.name flag_name,
							gf.flag flag
						FROM melodious.group_holders gh
						INNER JOIN melodious.group_flags gf
						ON gh.group_id = gf.group_id
						WHERE gf.name = iflag_name
						  AND (
								(gh.user_id = uid AND gh.channel_id = cid)
							 	OR (flagcheck AND gh.user_id = uid AND gh.channel_id IS NULL)
								OR (flagcheck AND gh.user_id IS NULL AND gh.channel_id = cid)
								OR (flagcheck AND gh.user_id IS NULL AND gh.channel_id IS NULL)
							)
						GROUP BY gf.id, gf.name;
					ELSIF user_name <> '' AND chan_name = '' THEN
						RETURN QUERY SELECT
							ARRAY_AGG(JSONB_SET(ROW_TO_JSON(gh)::JSONB, '{group_name}'::TEXT[], TO_JSONB((SELECT name FROM melodious.groups WHERE id=gh.group_id LIMIT 1)))) AS group_holders,
							gf.id flag_id,
							gf.name flag_name,
							gf.flag flag
						FROM melodious.group_holders gh
						INNER JOIN melodious.group_flags gf
						ON gh.group_id = gf.group_id
						WHERE gf.name = iflag_name
							AND (
								(NOT flagcheck AND gh.user_id = uid)
								OR (flagcheck AND gh.user_id = uid AND gh.channel_id IS NULL)
								OR (flagcheck AND gh.user_id IS NULL AND gh.channel_id IS NULL)
							)
						GROUP BY gf.id, gf.name;
					ELSIF user_name = '' AND chan_name <> '' THEN
						RETURN QUERY SELECT
							ARRAY_AGG(JSONB_SET(ROW_TO_JSON(gh)::JSONB, '{group_name}'::TEXT[], TO_JSONB((SELECT name FROM melodious.groups WHERE id=gh.group_id LIMIT 1)))) AS group_holders,
							gf.id flag_id,
							gf.name flag_name,
							gf.flag flag
						FROM melodious.group_holders gh
						INNER JOIN melodious.group_flags gf
						ON gh.group_id = gf.group_id
						WHERE gf.name = iflag_name
							AND (
								(NOT flagcheck AND gh.channel_id = cid)
								OR (flagcheck AND gh.channel_id = cid AND gh.user_id IS NULL)
								OR (flagcheck AND gh.channel_id IS NULL AND gh.user_id IS NULL)
							)
						GROUP BY gf.id, gf.name;
					END IF;
				END IF;
			ELSE
				IF iflag_name = '' THEN
					IF user_name = '' AND chan_name = '' THEN
						RETURN QUERY SELECT
							ARRAY_AGG(JSONB_SET(ROW_TO_JSON(gh)::JSONB, '{group_name}'::TEXT[], TO_JSONB(igroup_name))) AS group_holders,
							gf.id flag_id,
							gf.name flag_name,
							gf.flag flag
						FROM melodious.group_holders gh
						INNER JOIN melodious.group_flags gf
						ON gh.group_id = gf.group_id
						WHERE gh.group_id = gid
							AND (
								(NOT flagcheck)
								OR (flagcheck AND gh.user_id IS NULL AND gh.channel_id IS NULL)
							)
						GROUP BY gf.id, gf.name;
					ELSIF user_name <> '' AND chan_name <> '' THEN
						RETURN QUERY SELECT
							ARRAY_AGG(JSONB_SET(ROW_TO_JSON(gh)::JSONB, '{group_name}'::TEXT[], TO_JSONB(igroup_name))) AS group_holders,
							gf.id flag_id,
							gf.name flag_name,
							gf.flag flag
						FROM melodious.group_holders gh
						INNER JOIN melodious.group_flags gf
						ON gh.group_id = gf.group_id
						WHERE gh.group_id = gid
							AND (
								(gh.channel_id = cid AND gh.user_id = uid)
								OR (flagcheck AND gh.channel_id = cid AND gh.user_id IS NULL)
								OR (flagcheck AND gh.channel_id IS NULL AND gh.user_id = uid)
								OR (flagcheck AND gh.channel_id IS NULL AND gh.user_id IS NULL)
							)
						GROUP BY gf.id, gf.name;
					ELSIF user_name <> '' AND chan_name = '' THEN
						RETURN QUERY SELECT
							ARRAY_AGG(JSONB_SET(ROW_TO_JSON(gh)::JSONB, '{group_name}'::TEXT[], TO_JSONB(igroup_name))) AS group_holders,
							gf.id flag_id,
							gf.name flag_name,
							gf.flag flag
						FROM melodious.group_holders gh
						INNER JOIN melodious.group_flags gf
						ON gh.group_id = gf.group_id
						WHERE gh.group_id = gid
							AND (
								(NOT flagcheck AND gh.user_id = uid)
								OR (flagcheck AND gh.user_id = uid AND gh.channel_id IS NULL)
								OR (flagcheck AND gh.user_id IS NULL AND gh.channel_id IS NULL)
							)
						GROUP BY gf.id, gf.name;
					ELSIF user_name = '' AND chan_name <> '' THEN
						RETURN QUERY SELECT
							ARRAY_AGG(JSONB_SET(ROW_TO_JSON(gh)::JSONB, '{group_name}'::TEXT[], TO_JSONB(igroup_name))) AS group_holders,
							gf.id flag_id,
							gf.name flag_name,
							gf.flag flag
						FROM melodious.group_holders gh
						INNER JOIN melodious.group_flags gf
						ON gh.group_id = gf.group_id
						WHERE gh.group_id = gid
							AND (
								(NOT flagcheck AND gh.channel_id = cid)
								OR (flagcheck AND gh.channel_id = cid AND gh.user_id IS NULL)
								OR (flagcheck AND gh.channel_id IS NULL AND gh.user_id IS NULL) 
							)
						GROUP BY gf.id, gf.name;
					END IF;
				ELSE
					IF user_name = '' AND chan_name = '' THEN
						RETURN QUERY SELECT
							ARRAY_AGG(JSONB_SET(ROW_TO_JSON(gh)::JSONB, '{group_name}'::TEXT[], TO_JSONB(igroup_name))) AS group_holders,
							gf.id flag_id,
							gf.name flag_name,
							gf.flag flag
						FROM melodious.group_holders gh
						INNER JOIN melodious.group_flags gf
						ON gh.group_id = gf.group_id
						INNER JOIN melodious.groups g
						ON g.id = gh.group_id
						WHERE (gf.name = iflag_name AND gh.group_id = gid)
							AND (
								(NOT flagcheck)
								OR (flagcheck AND gh.channel_id IS NULL AND gh.user_id IS NULL)
							)
						GROUP BY gf.id, gf.name;
					ELSIF user_name <> '' AND chan_name <> '' THEN
						RETURN QUERY SELECT
							ARRAY_AGG(JSONB_SET(ROW_TO_JSON(gh)::JSONB, '{group_name}'::TEXT[], TO_JSONB(igroup_name))) AS group_holders,
							gf.id flag_id,
							gf.name flag_name,
							gf.flag flag
						FROM melodious.group_holders gh
						INNER JOIN melodious.group_flags gf
						ON gh.group_id = gf.group_id
						WHERE (gf.name = iflag_name AND gh.group_id = gid)
							AND (
								(gh.user_id = uid AND gh.channel_id = cid)
								OR (flagcheck AND gh.user_id = uid AND gh.channel_id IS NULL)
								OR (flagcheck AND gh.user_id IS NULL AND gh.channel_id = cid)
								OR (flagcheck AND gh.user_id IS NULL AND gh.channel_id IS NULL)
							)
						GROUP BY gf.id, gf.name;
					ELSIF user_name <> '' AND chan_name = '' THEN
						RETURN QUERY SELECT
							ARRAY_AGG(JSONB_SET(ROW_TO_JSON(gh)::JSONB, '{group_name}'::TEXT[], TO_JSONB(igroup_name))) AS group_holders,
							gf.id flag_id,
							gf.name flag_name,
							gf.flag flag
						FROM melodious.group_holders gh
						INNER JOIN melodious.group_flags gf
						ON gh.group_id = gf.group_id
						WHERE (gh.group_id = gid AND gf.name = iflag_name)
							AND (
								(NOT flagcheck AND gh.user_id = uid)
								OR (flagcheck AND gh.user_id = uid AND gh.channel_id IS NULL)
								OR (flagcheck AND gh.user_id IS NULL AND gh.channel_id IS NULL)
							)
						GROUP BY gf.id, gf.name;
					ELSIF user_name = '' AND chan_name <> '' THEN
						RETURN QUERY SELECT
							ARRAY_AGG(JSONB_SET(ROW_TO_JSON(gh)::JSONB, '{group_name}'::TEXT[], TO_JSONB(igroup_name))) AS group_holders,
							gf.id flag_id,
							gf.name flag_name,
							gf.flag flag
						FROM melodious.group_holders gh
						INNER JOIN melodious.group_flags gf
						ON gh.group_id = gf.group_id
						WHERE (gf.name = iflag_name AND gh.group_id = gid)
							AND (
								(NOT flagcheck AND gh.channel_id = cid)
								OR (flagcheck AND gh.channel_id = cid AND gh.user_id IS NULL)
								OR (flagcheck AND gh.channel_id IS NULL AND gh.user_id IS NULL)
							)
						GROUP BY gf.id, gf.name;
					END IF;
				END IF;
			END IF;
		END;
		$$;
By Anonymous, 2019-02-05 15:36:52