October 14, 2017 at 10:06 am
I have a query that uses several left outer joins. I am attempting to alter the join to include a join on Group, but only when Division is South. If the Division is not South then I only want to join on Month and Division. I tried to use a case statement to accomplish but it is not working.
Can a case statement accomplish this?
select *
FROM Main e
left outer join rlp r on r.month = e.month and r.division = e. division and (CASE WHEN e.Division = 'South' Then e.Group = r. Group ELSE '' END)
left outer join units u on u.month = e.month and u.division = e. division and (CASE WHEN e.Division = 'South' Then e.Group = u. Group ELSE '' END)
left outer join processed x on x.month = e.month and x.division = e. division and (CASE WHEN e.Division = 'South' Then e.Group = x. Group ELSE '' END)
October 14, 2017 at 2:09 pm
"Not working"? What do you mean? Does it return incorrect results? Error? (You didn't provide an error message though). Mayne your SQL Server falls over when you use your query? (I hope not).
At a total guess:
"ELSE END"? You either need to declare an expression for the ELSE or not declare it.
If not, could you elaborate. Have a look at the link in my signature on how to post T-SQL questions.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
October 14, 2017 at 5:14 pm
I apologize if I did not providing enough information. By not working I mean I am getting an error stating "incorrect syntax near the keyword group".
To demonstrate the error I have put together some temp tables to replicate the issue.
CREATE TABLE #Main
(
market varchar(8),
division varchar(15),
month varchar(20),
revenue money
);
INSERT #Main(market)
VALUES ('dallas')
INSERT #Main(market)
VALUES ('SA')
INSERT #Main(division)
VALUES ('south')
INSERT #Main(division)
VALUES ('north')
INSERT #Main(month)
VALUES ('jan')
INSERT #Main(division)
VALUES ('feb')
INSERT #Main(revenue)
VALUES ('20000')
INSERT #Main(revenue)
VALUES ('10000')
CREATE TABLE #rlp
(
market varchar(8),
division varchar(15),
month varchar(20),
revenue money
);
INSERT #rlp(market)
VALUES ('dallas')
INSERT #rlp(market)
VALUES ('SA')
INSERT #rlp(division)
VALUES ('south')
INSERT #rlp(division)
VALUES ('north')
INSERT #rlp(month)
VALUES ('jan')
INSERT #rlp(division)
VALUES ('feb')
INSERT #rlp(revenue)
VALUES ('20000')
INSERT #rlp(revenue)
VALUES ('10000')
If OBJECT_ID('tempdb.dbo.#Main') is not null
drop table #Main
If OBJECT_ID('tempdb.dbo.#rlp') is not null
drop table #rlp
select *
from #Main e
left outer join #rlp r on r.month = e.month and r.division = e. division and (CASE WHEN e.Division = 'South' Then e.Group = r. Group ELSE '' END)
Is it possible to use a Case When in the join?
October 15, 2017 at 3:11 am
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
October 16, 2017 at 9:45 am
What you posted is pretty useless and needs to be corrected. For example, did you know that by definition, a table must have a key? We never use the old Sybase money data type; Google about its rounding errors and inaccurate math. None of your column names followed any of the ISO 11179 naming rules; for example, month is not a column name; it is a unit of measurement.
I was a C in its pure dialect PL/1 programmer decades ago, so I really get a laugh out of someone actually naming a table "Main" instead of following an RDBMS model.
CREATE TABLE Foobart_Revenues
(market_name VARCHAR(8) NOT NULL,
division_name VARCHAR(15) NOT NULL,
revenue_month CHAR(10) NOT NULL
CHECK (revenue_month LIKE '[12][0-9][0-9][0-9]-[0-1][0-9]-00'),
revenue_amt DECIMAL (12,2) NOT NULL
CHECK(revenue_amt >= 0.00),
PRIMARY KEY (market_name, division_name, revenue_month)
);
The strangest thing you've done here, however, is to screwup insertion. First of all the ANSI ISO standard syntax is "insert into"; you're using the old Sybase one word syntax. And you're insertin one column at a time! The unit of work in SQL is a set of rows. To make things even worse, the column names in the values being put in them make no sense. Why is February a division?
"SELECT *" is regarded as bad programming in SQL. It usually returns more data than you need, you're uncertain as to what that data is in case the tables get altered and it just screams out that your lazy
Left outer joins, while they are perfectly valid, are another sign bad programming. It says you failed to set up relationships among your tables and expect to have mismatches.
>> Is it possible to use a CASE Expression in the join? <<
I hope you know that in SQL cases and expression (expressions return a single scaler value), and not a control flow statement as it would be in PL/1, the language which you seem to be writing.
Why don't you follow the forum rules, and publish correct DDL for all of the tables involved in your problem? It's getting very hard to try and read your mind and correct everything you've done.
The strangest thing you've done here, however, is to screw up insertion. First of all the ANSI ISO standard syntax is "insert into"; you're using the old Sybase one word syntax. And you're inserting one column at a time! The unit of work in SQL is a set of rows. To make things even worse, the column names in the values being put in them make no sense. Why is February a division?"SELECT *" is regarded as bad programming in SQL. It usually returns more data than you need, you're uncertain as to what that data is in case the tables get altered and it just screams out that you're lazy Left outer joins, while they are perfectly valid, are another sign bad programming. It says you failed to set up relationships among your tables and expect to have mismatches.>> Is it possible to use a CASE Expression in the join? <<I hope you know that in SQL cases and expression (expressions return a single scalar value), and not a control flow statement as it would be in PL/1, the language which you seem to be writing.Why don't you follow the forum rules, and publish correct DDL for all of the tables involved in your problem? It's getting very hard to try and read your mind and correct everything you've done.
Please post DDL and follow ANSI/ISO standards when asking for help.
October 16, 2017 at 10:16 am
ollyjolly - Saturday, October 14, 2017 10:06 AMI have a query that uses several left outer joins. I am attempting to alter the join to include a join on Group, but only when Division is South. If the Division is not South then I only want to join on Month and Division. I tried to use a case statement to accomplish but it is not working.Can a case statement accomplish this?
select *
FROM Main eleft outer join rlp r on r.month = e.month and r.division = e. division and (CASE WHEN e.Division = 'South' Then e.Group = r. Group ELSE '' END)
left outer join units u on u.month = e.month and u.division = e. division and (CASE WHEN e.Division = 'South' Then e.Group = u. Group ELSE '' END)
left outer join processed x on x.month = e.month and x.division = e. division and (CASE WHEN e.Division = 'South' Then e.Group = x. Group ELSE '' END)
Simple answer, no, not the way you are trying to do it. I see you posted DDL and some sample data. Now, based on the sample data what do you expect to be returned by your query? Also, ignore Mr. Celko for now. He is just exercising his horse and not really being overly helpful at the moment.
October 16, 2017 at 11:19 am
If the column division is not nullable, the following code might be what you need. If it's nullable, you would want to add a third condition in the parenthesis.
SELECT *
FROM Main e
LEFT OUTER JOIN rlp r on r.month = e.month AND r.division = e. division AND (e.Division != 'South' OR e.Group = r.Group)
LEFT OUTER JOIN units u on u.month = e.month AND u.division = e. division AND (e.Division != 'South' OR e.Group = u.Group)
LEFT OUTER JOIN processed x on x.month = e.month AND x.division = e. division AND (e.Division != 'South' OR e.Group = x.Group);
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply