|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Case Statement?I am trying to write a simple sp that will depending on a value run different
queries. e.g begin case when a=1 then select * from b else select * from c end end something like that been working on this for a while, any easy way IF (<condition>)
BEGIN select * from ... END ELSE BEGIN select * from ... END Show quote "DBA" wrote: > I am trying to write a simple sp that will depending on a value run different > queries. > e.g > > begin > case when a=1 then > select * from b > else > select * from c > end > end > something like that > > been working on this for a while, any easy way > tried this and it did not work
Show quote "KH" wrote: > IF (<condition>) > BEGIN > select * from ... > END > ELSE > BEGIN > select * from ... > END > > > "DBA" wrote: > > > I am trying to write a simple sp that will depending on a value run different > > queries. > > e.g > > > > begin > > case when a=1 then > > select * from b > > else > > select * from c > > end > > end > > something like that > > > > been working on this for a while, any easy way > > I tried this:
if table.id ='Lat' or table.id='Lat Dist' begin select column1, column2 from table where column1 is null end else begin select column1, column2 from table where column1 is not null end end And got an error saying that The column prefix 'table' does not match with a table name or alias name used in the query Show quote "KH" wrote: > IF (<condition>) > BEGIN > select * from ... > END > ELSE > BEGIN > select * from ... > END > > > "DBA" wrote: > > > I am trying to write a simple sp that will depending on a value run different > > queries. > > e.g > > > > begin > > case when a=1 then > > select * from b > > else > > select * from c > > end > > end > > something like that > > > > been working on this for a while, any easy way > > THose are just example code snippets, intended for you to fill in the blanks
with what you actually need. You'll have to replace the pseudo code with objects, variables, etc. that actually exist in your environment. Show quote "DBA" wrote: > I tried this: > if table.id ='Lat' or table.id='Lat Dist' > begin > select column1, column2 > from table > where column1 is null > end > > else > begin > select column1, column2 > from table > where column1 is not null > end > > end > > And got an error saying that The column prefix 'table' does not match with a > table name or alias name used in the query > > > "KH" wrote: > > > IF (<condition>) > > BEGIN > > select * from ... > > END > > ELSE > > BEGIN > > select * from ... > > END > > > > > > "DBA" wrote: > > > > > I am trying to write a simple sp that will depending on a value run different > > > queries. > > > e.g > > > > > > begin > > > case when a=1 then > > > select * from b > > > else > > > select * from c > > > end > > > end > > > something like that > > > > > > been working on this for a while, any easy way > > > I think I found the solution by doing a union between queries
Show quote "KH" wrote: > THose are just example code snippets, intended for you to fill in the blanks > with what you actually need. You'll have to replace the pseudo code with > objects, variables, etc. that actually exist in your environment. > > > "DBA" wrote: > > > I tried this: > > if table.id ='Lat' or table.id='Lat Dist' > > begin > > select column1, column2 > > from table > > where column1 is null > > end > > > > else > > begin > > select column1, column2 > > from table > > where column1 is not null > > end > > > > end > > > > And got an error saying that The column prefix 'table' does not match with a > > table name or alias name used in the query > > > > > > "KH" wrote: > > > > > IF (<condition>) > > > BEGIN > > > select * from ... > > > END > > > ELSE > > > BEGIN > > > select * from ... > > > END > > > > > > > > > "DBA" wrote: > > > > > > > I am trying to write a simple sp that will depending on a value run different > > > > queries. > > > > e.g > > > > > > > > begin > > > > case when a=1 then > > > > select * from b > > > > else > > > > select * from c > > > > end > > > > end > > > > something like that > > > > > > > > been working on this for a while, any easy way > > > > On Thu, 30 Jun 2005 14:36:01 -0700, DBA wrote:
Show quote > I tried this: I think this is what you really want:> if table.id ='Lat' or table.id='Lat Dist' > begin > select column1, column2 > from table > where column1 is null > end > > else > begin > select column1, column2 > from table > where column1 is not null > end > > end SELECT column1, column2 FROM table WHERE ( (table.id ='Lat' or table.id='Lat Dist') AND column1 is null) OR (table.id <> 'Lat' AND table.id <> 'Lat Dist' AND column1 is not null) CREATE PROC mysp (@table sysname)
AS EXEC ('SELECT * FROM '+@table) -- Show quoteThanks Ravi "DBA" wrote: > I am trying to write a simple sp that will depending on a value run different > queries. > e.g > > begin > case when a=1 then > select * from b > else > select * from c > end > end > something like that > > been working on this for a while, any easy way > You can conditionally branch using if.. else.. depending on the status of a
variable, function, etc: if @a=1 select * from b else select * from c Or.. if @a=1 begin select * from b end else begin select * from c end Or you can use a case statement to conditionally return one of multiple values or sub-queries for a column: select MyTable1.A, MyTable1.B, case MyTable1.C when 1 then (select D from MyTable2 where K=1) when 2 then (select E from MyTable2 where K=1) when 3 then MyTable1.J else MyTable1.F end as X from MyTable1 Show quote "DBA" <D**@discussions.microsoft.com> wrote in message news:1730345B-7EBA-44ED-9DD9-19E369299BF0@microsoft.com... > I am trying to write a simple sp that will depending on a value run different > queries. > e.g > > begin > case when a=1 then > select * from b > else > select * from c > end > end > something like that > > been working on this for a while, any easy way > There is no CASE statement in SQL. There is a CASE **expression** and
expressions return valueds, not flow of control. Use the proprietary IF-THEN-ELSE construct in T-SQL or re-write the query with CASE exprsssions if possible. > There is no CASE statement in SQL. There is a CASE **expression** and Really? I think you'll find there is no CASE *expression* in SQL but rather> expressions return valueds, not flow of control. a case *function*. It is the *function* that evaluates the expression and returns values, not the expression. Since we're being unneccessarily pedantic and all. ;) Richard Myers wrote:
> No, at least not according to ANSI.> > There is no CASE statement in SQL. There is a CASE **expression** and > > expressions return valueds, not flow of control. > > Really? I think you'll find there is no CASE *expression* in SQL but rather > a case *function*. It is the *function* that evaluates the expression and > returns values, not the expression. Since we're being unneccessarily > pedantic and all. > > ;) Part 2 (Foundation (SQL/Foundation)) of the ANSI SQL-99 standard describes "Scalar expressions" in chapter 6. Chapter 6.20 is called "<case expression>" and describes its function "[to] specify a conditional value", its format "<case expression> ::= <case abbreviation> | <case specification>, ..." and several types of rules.Also, it is not found in the "SQL built-in functions" list of chapter 20.70. So in a simple statement like "SELECT CASE WHEN a=1 THEN b ELSE c END FROM MyTable" there is no function involved. Gert-Jan Sure but this is an SqlServer newsgroup in which case, unsure if pun is
intended, i'd go by the Books online which clearly describe it as the case function..... and besides, since when did standard adherence have any place in Microsoft anything? :=) Richard Show quote "Gert-Jan Strik" <sorry@toomuchspamalready.nl> wrote in message news:42C7BFAC.2FBE8454@toomuchspamalready.nl... > Richard Myers wrote: > > > > > There is no CASE statement in SQL. There is a CASE **expression** and > > > expressions return valueds, not flow of control. > > > > Really? I think you'll find there is no CASE *expression* in SQL but rather > > a case *function*. It is the *function* that evaluates the expression and > > returns values, not the expression. Since we're being unneccessarily > > pedantic and all. > > > > ;) > > No, at least not according to ANSI. > > Part 2 (Foundation (SQL/Foundation)) of the ANSI SQL-99 standard > describes "Scalar expressions" in chapter 6. Chapter 6.20 is called > "<case expression>" and describes its function "[to] specify a > conditional value", its format "<case expression> ::= <case > abbreviation> | <case specification>, ..." and several types of rules. > Also, it is not found in the "SQL built-in functions" list of chapter > 20.70. > > So in a simple statement like "SELECT CASE WHEN a=1 THEN b ELSE c END > FROM MyTable" there is no function involved. > > Gert-Jan >> and besides, since when did standard adherence have any place in Microsoft anything? << LOL!! Actually SQL Server is getting better over the years, so i livein hope :) SQL is a declarative language, not a procedural language. They are
expressions, not functions. Hey, I spent ten years on ANSI X3H2 Database Standard so that kind of difference is not unneccessarily pedantic at all. I have also taught SQL for 20+ years in colleges and the trade. I find that when I beat the right words into the student's heads, the concepts often follow. They suddenly have The Epiphany and they can write SQL which was impossible before they saw the light. Concepts are important. so that kind of difference is not unneccessarily
> pedantic at all. It is for audience at hand.> :) <Case statement>, <case function>, <case expression> we all understood whatwas meant. The difference bears no return in the context of the question. >> It is [pedantic] for audience at hand. << And that is why I can charge $1000 to $2000 per day teaching SQLclasses to the kids that did not get it right:) Seriously, concepts matter. The right terms matter. How much teaching experience have you had? When a student has a problem, the first thing you say is "go back to the definitions and the basics to work it out." Example: freshman calculus and someone who just does not understand the concept of a continuum versus a countably infinite set. They can mechanically work a few problems, but they have a mental model that limits what they can ever do. Do you want to be -- shudder, dread -- a non-relational, procedural programmer all your life?  |
|||||||||||||||||||||||
Other interesting topics