|
We know there's conditional branching facilities in T-SQL and PL-SQL etc. Is there a similar IF clause available for use in flat SQL statements?
I can't use stored procs for certain restriction reasons, and my scenario is this.
I have one table, with columns [a, b, c, d, e, f]
I will pick rows, where column c = column d.
However, if column d is blank (null), then column e will be used for the comparison, if e is blank (null), then column f will be used for the comparison.
This is easy as pie in T-SQL, PL-SQL etc. Is there a way to do this in flat SQL statements?
Thanks.
|
|
|
|
|
Hi,
you can use AND and OR operators and effectively build a WHERE clause that calculates this (pseudo-code):
c=d OR (d=null AND (c=e OR (e=null AND c=f)))
|
|
|
|
|
Thank you! That was fast.
|
|
|
|
|
You're welcome.
|
|
|
|
|
Yo can also use the CASE statement or (depending on exactly what you want) possibly the COALESCE statement in SQL Server.
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
Sorry if this sounds dumb, but I thought CASE decision making was only available on the procedure/function side, and not in flat SQL?
If not, can you point me to some sample flat SQL code with CASE usage demonstrated? That'll be very helpful.
|
|
|
|
|
You can use it anywhere, I'm not sure what you mean by Flat SQL - T-SQL is T-SQL, which runs trhe same in stored procs as out of them - the only execeptions are triggers and functions which are more restrictive.
select NonEoM = case <br />
when [Receive side type] = 'floating' and [Receive Convention] = 'EOM' then 0 <br />
when [Pay side type] = 'floating' and [Pay Convention] = 'EOM' then 0 <br />
else 1 end<br />
from table1
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
Hey everyone,
Just wondering if there's a better way to do this:
SELECT
SUM(LOOOOOOOOONG EXPRESSION) AS EX1,
SUM(ANOTHER LONG EXPRESSION) AS EX12,
SUM(LOOOOOOOOONG EXPRESSION)+SUM(ANOTHER LONG EXPRESSION) AS TOTAL
FROM SOME_TABLE
Can't it just be
SELECT
SUM(LOOOOOOOOONG EXPRESSION) AS EX1,
SUM(ANOTHER LONG EXPRESSION) AS EX12,
EX1+EX2 AS TOTAL
FROM SOME_TABLE
Anyway to make this possible??
Thanks!
All generalizations are wrong, including this one!
(\ /)
(O.o)
(><)
|
|
|
|
|
|
Giorgi Dalakishvili wrote: CTE?
Ok, I googled for Oracle CTE and I think you're talking about some Toolbox package, can you tell me what is it and why do you think it should help me??
Thanks mate!
All generalizations are wrong, including this one!
(\ /)
(O.o)
(><)
|
|
|
|
|
I believe that Common Table Expressions are limited to SQL Server 2005 and higher. I don't know of any way to use the column-alias within the select statement itself.
I are troll
|
|
|
|
|
Ok, I'm sorry, I'm using Oracle and that's why I didn't come across this CTE thing, thanks anyways mate!
All generalizations are wrong, including this one!
(\ /)
(O.o)
(><)
|
|
|
|
|
Sorry for not answering your original question properly! You can use the column-name aliases in the ORDER BY clause.
SELECT Column1, Column2, Column1 + Column2 AS Column3
FROM (SELECT COUNT(*) AS Column1,
MAX(COL#) AS Column2
FROM COL$
)--edit--
It's impossible within a single SELECT, both in TSQL aswell as PL/SQL.
Enjoy
I are troll
modified on Wednesday, April 15, 2009 11:08 AM
|
|
|
|
|
Actually, I'm already doing this and was looking for a better way.
Thanks anyways mate!
All generalizations are wrong, including this one!
(\ /)
(O.o)
(><)
|
|
|
|
|
I had one of my devs ask the exact same question in SQL Server, he got the same answer - not with SQL syntax.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Ok, Thanks mate!
All generalizations are wrong, including this one!
(\ /)
(O.o)
(><)
|
|
|
|
|
I don't think that any of the leading sql systems will allow you to express your query in the way that you want. However, whilst you can't write it like that, the query processor will actually do exactly what you want anyway; It will not re-evaluate the aggregates if they are used in further calculations in the same logical query phase, it will re-use what it already has.
|
|
|
|
|
Thanks mate!
All generalizations are wrong, including this one!
(\ /)
(O.o)
(><)
|
|
|
|
|
hello - I bang my head against the wall trying to rewrite this recursive TSQL proc so we can do this in UDF instead? (and more efficient and to be able to bind it to view column... etc)
<br />
CREATE PROCEDURE spRecursiveGetChildren <br />
(<br />
@ObjectId bigint, <br />
@ObjectClassifier varchar(255),<br />
@TempId varchar(36)<br />
)<br />
AS<br />
BEGIN<br />
DECLARE @CursorName varchar(100)<br />
DECLARE @SQL varchar(4000)<br />
DECLARE @NSQL nvarchar(4000)<br />
<br />
-- Dynamic sql + cursor name because there'll be a recursive call and we'd run into CURSOR name conflict<br />
SET @CursorName = ''spGetChildren_cursor_'' + CAST(@ObjectId as varchar(20)) + ''_'' + REPLACE(CAST(@ObjectClassifier AS varchar(70)), ''.'', ''_'')<br />
<br />
PRINT @CursorName<br />
<br />
INSERT INTO [dbo].[TMP_HierarchyMap] <br />
(<br />
ParentId,<br />
ChildId,<br />
ParentType,<br />
ChildType,<br />
...<br />
TmpUID,<br />
ReferencedId<br />
)<br />
SELECT <br />
ParentId,<br />
ChildId,<br />
ParentType,<br />
ChildType,<br />
...<br />
@TempId,<br />
Id<br />
FROM HIERARCHYMAP M1<br />
WHERE<br />
ParentId = @ObjectId<br />
AND<br />
ParentType = @ObjectClassifier<br />
AND<br />
NOT EXISTS(SELECT 1 FROM TMP_HIERARCHYMAP M2 WHERE M1.ChildId=M2.ChildId AND M1.ChildType=M2.ChildType AND ParentId=@ObjectId AND ParentType=@ObjectClassifier AND TmpUID=@TempId)<br />
<br />
SET @SQL = <br />
''DECLARE @ThisChildId bigint<br />
DECLARE @ThisChildType varchar(255)<br />
DECLARE @CountChildren int<br />
<br />
DECLARE $CURSOR_NAME$ CURSOR FOR <br />
SELECT ChildId,ChildType <br />
FROM HIERARCHYMAP <br />
WHERE <br />
ParentId = $OBJECTID$<br />
AND ParentType = ''''$OBJECTCLASSIFIER$''''<br />
<br />
OPEN $CURSOR_NAME$<br />
FETCH $CURSOR_NAME$ INTO @ThisChildId, @ThisChildType<br />
<br />
WHILE @@FETCH_STATUS = 0<br />
BEGIN <br />
<br />
SELECT @CountChildren = count(1) FROM HIERARCHYMAP WHERE ParentId = @ThisChildId AND ChildType = @ThisChildType<br />
IF (@CountChildren>0)<br />
BEGIN<br />
<br />
-- ** Recursive call<br />
EXEC spRecursiveGetChildren @ThisChildId, @ThisChildType, ''''$TEMPID$''''<br />
END<br />
FETCH $CURSOR_NAME$ INTO @ThisChildId, @ThisChildType<br />
END<br />
<br />
CLOSE $CURSOR_NAME$<br />
DEALLOCATE $CURSOR_NAME$<br />
''<br />
SET @SQL = REPLACE(@SQL, ''$CURSOR_NAME$'', @CursorName)<br />
SET @SQL = REPLACE(@SQL, ''$OBJECTID$'', CAST(@ObjectId as varchar(20)) )<br />
SET @SQL = REPLACE(@SQL, ''$OBJECTCLASSIFIER$'', CAST(@ObjectClassifier as varchar(70)) )<br />
SET @SQL = REPLACE(@SQL, ''$TEMPID$'', CAST(@TempId as varchar(36)) )<br />
<br />
SET @NSQL = CAST(@SQL AS nvarchar(4000))<br />
<br />
PRINT @NSQL<br />
<br />
-- Actually executing recursive call! This prevents me from repackage this as UDF instead.<br />
EXEC sp_executesql @NSQL<br />
END<br />
dev
|
|
|
|
|
Assuming you are using SQL2005/2008 take a look at CTE (Common Table Expression). These make recursion really easy.
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
thanks I got my recursive CTE expression working but two more questions!
is it good with UDF (user defined function)? and performance better than CURSOR?
NOTE: You can't do these from UDF:
* execute/sp_executesql to run another stored proc
* use table variable as input/output function parameter
* access temp table
Anyway I got it working (but with problem and corresponding cheats to get around them)
<br />
CREATE TABLE [dbo].[HierarchyMap] (<br />
[Id] [bigint] IDENTITY (1, 1) NOT NULL,<br />
[ParentId] [bigint] NOT NULL, <br />
[ChildId] [bigint] NOT NULL,<br />
[ParentType] [varchar] (255) NOT NULL,<br />
[ChildType] [varchar] (255) NOT NULL, <br />
CONSTRAINT [PK_HierarchyMap] PRIMARY KEY CLUSTERED <br />
(<br />
[Id] ASC<br />
)<br />
)<br />
Assuming I only have two rows in the table - select * from HierarchyMap:
<br />
Id ParentId ChildId ParentType ChildType<br />
-----------------------------------------------------------<br />
1 1 1 Group SystemUser<br />
2 1 2 Group SystemUser<br />
Now, my CTE sql is as follows and the problems are:
1. Max depth 100 for recursion exceeded
2. Duplicate rows (no idea..)
<br />
WITH Children AS<br />
(<br />
--initialization<br />
SELECT<br />
[Id],<br />
[ParentId],<br />
[ChildId],<br />
[ParentType],<br />
[ChildType]<br />
FROM HierarchyMap<br />
WHERE <br />
ParentId=1 AND ParentType='Group'<br />
UNION ALL<br />
--recursive execution<br />
SELECT <br />
[MAP].[Id],<br />
[MAP].[ParentId],<br />
[MAP].[ChildId],<br />
[MAP].[ParentType],<br />
[MAP].[ChildType],<br />
CH.Depth+1 'Depth'<br />
FROM HierarchyMap MAP INNER JOIN Children CH<br />
ON MAP.ParentId = CH.ChildId<br />
WHERE <br />
Depth<100 -- PROBLEM 1: If I don't limit depth I get error <b>"The maximum recursion 100 has been exhausted before statement completion."</b><br />
)<br />
SELECT ParentId, ParentType, ChildId, ChildType FROM Children <b>GROUP BY Id, ParentId, ParentType, ChildId, ChildType</b> -- PROBLEM 2: I don't ... understand the duplicate rows... which is why I need to do a GROUP-BY... think I did something wrong?<br />
Suggestion?
http://www.mssqltips.com/tip.asp?tip=1520
http://www.setfocus.com/TechnicalArticles/sql-server-2005-tsql-3.aspx
http://stackoverflow.com/questions/634971/sql-server-how-to-limit-cte-recursion-to-rows-just-recursivly-added[^]
dev
|
|
|
|
|
|
You can set the maxrecursion option to something other than the default of 100. If you set the value as 0 it will recurse as many times as it wants. Just make sure that the query will not lead to an infinite loop.
|
|
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
