Why will this case statement not work?
It checks the length of a char field TRAN_TIME and pads it accordingly.
SELECT a.TRAN_TIME
FROM QUINN_ST..get_bcp_h_cvcmis a(nolock), QUINN_CT..Rec_Pol_WH b(nolock)
WHERE a.POLICY_CODE = b.Policy_desc
AND a.TRAN_DT >= '20051221'
AND (CASE len(a.TRAN_TIME) When 5 THEN '0' + a.TRAN_TIME When 4 Then '00' +
a.TRAN_TIME ELSE a.TRAN_TIME END) > '172206'
AND a.TRAN_TIME > '172206'
and a.POLICY_CODE in ('GNI/QMV/0035330','GEI/QMP/2294532',
'GEI/QMP/2294454','GEI/QMP/2294528','GEI/QMP/2294530','GEI/QMV/0263095','GEI
/QMP/2294534',
'GEI/QMP/2294527','GEI/QMV/0263087')or what is that padding function
lpad or something'
"marcmc" wrote:
> Why will this case statement not work?
> It checks the length of a char field TRAN_TIME and pads it accordingly.
> SELECT a.TRAN_TIME
> FROM QUINN_ST..get_bcp_h_cvcmis a(nolock), QUINN_CT..Rec_Pol_WH b(nolock)
> WHERE a.POLICY_CODE = b.Policy_desc
> AND a.TRAN_DT >= '20051221'
> AND (CASE len(a.TRAN_TIME) When 5 THEN '0' + a.TRAN_TIME When 4 Then '00'
+
> a.TRAN_TIME ELSE a.TRAN_TIME END) > '172206'
> AND a.TRAN_TIME > '172206'
> and a.POLICY_CODE in ('GNI/QMV/0035330','GEI/QMP/2294532',
> 'GEI/QMP/2294454','GEI/QMP/2294528','GEI/QMP/2294530','GEI/QMV/0263095','G
EI/QMP/2294534',
> 'GEI/QMP/2294527','GEI/QMV/0263087')|||SELECT a.TRAN_TIME
FROM
QUINN_ST..get_bcp_h_cvcmis a(nolock),
QUINN_CT..Rec_Pol_WH b(nolock)
WHERE a.POLICY_CODE =3D b.Policy_desc
AND a.TRAN_DT >=3D '20051221'
AND (
CASE len(a.TRAN_TIME)
When 5 THEN '0' + a.TRAN_TIME
When 4 Then '00' + a.TRAN_TIME
ELSE a.TRAN_TIME END
) > '172206'
AND a.TRAN_TIME > '172206'
and a.POLICY_CODE in ('GNI/QMV/0035330','GEI/QMP/2294532',
'GEI/QMP/2294454','GEI/QMP/2294528','GEI/QMP/2294530','GEI/QMV/0263095','GE=
=ADI/QMP/2294534',
'GEI/QMP/2294527','GEI/QMV/0263087')
Syntactically it looks OK, but there could be some conversion issue
depending on your datatypes of the table (which you didn=B4t sned in a
ddl script with the issue)
BTW: What do you mean by lpad ?
HTH, jens Suessmeyer.|||lpad was an oracle padding func i was thinking of, how can i pad one or two
zeroes to the left of lets say the char '93826 '
'
"Jens" wrote:
> SELECT a.TRAN_TIME
> FROM
> QUINN_ST..get_bcp_h_cvcmis a(nolock),
> QUINN_CT..Rec_Pol_WH b(nolock)
> WHERE a.POLICY_CODE = b.Policy_desc
> AND a.TRAN_DT >= '20051221'
> AND (
> CASE len(a.TRAN_TIME)
> When 5 THEN '0' + a.TRAN_TIME
> When 4 Then '00' + a.TRAN_TIME
> ELSE a.TRAN_TIME END
> ) > '172206'
> AND a.TRAN_TIME > '172206'
> and a.POLICY_CODE in ('GNI/QMV/0035330','GEI/QMP/2294532',
> 'GEI/QMP/2294454','GEI/QMP/2294528','GEI/QMP/2294530','GEI/QMV/0263095','G
E_I/QMP/2294534',
> 'GEI/QMP/2294527','GEI/QMV/0263087')
> Syntactically it looks OK, but there could be some conversion issue
> depending on your datatypes of the table (which you didn′t sned in a
> ddl script with the issue)
> BTW: What do you mean by lpad ?
> HTH, jens Suessmeyer.
>|||e.g., pad resulting length of 6
right(replicate('0',6) + convert(varchar, '93826'), 6)
marcmc wrote:
> lpad was an oracle padding func i was thinking of, how can i pad one or tw
o
> zeroes to the left of lets say the char '93826 '
> '
> "Jens" wrote:
>|||Thats easy:
DECLARE @.SOMEVALUE VARCHAR(20)
SET @.SOMEVALUE = '998'
SET @.SOMEVALUE = RIGHT('0000000' + @.SOMEVALUE,7)
PRINT @.SOMEVALUE
HTH, Jens Suessmeyer.|||ah - missed the char, have to trim it first
right(replicate('0',6) + convert(varchar, rtrim('93826 ')), 6)
Trey Walpole wrote:
> e.g., pad resulting length of 6
> right(replicate('0',6) + convert(varchar, '93826'), 6)
> marcmc wrote:
>|||combining Trey's and Jen's suggestions...
If your input is character data, you should probably trim spaces from both
sides unless you know and can rely on the incoming data.
RIGHT('000000' + LTRIM(RTRIM(@.SOMEVALUE)), 6)
if you need it to be dynamic, use the replicate version
RIGHT(REPLICATE('0', @.PadSize) + LTRIM(RTRIM(@.SomeValue)), @.PadSize)
if it's numeric data,
RIGHT(REPLICATE('0', @.PadSize) + CONVERT(VARCHAR, @.SomeNumeric), @.PadSize)
if you prefer CAST instead of CONVERT, that works too.
Hope that helps,
Joe
"Jens" wrote:
> Thats easy:
> DECLARE @.SOMEVALUE VARCHAR(20)
> SET @.SOMEVALUE = '998'
> SET @.SOMEVALUE = RIGHT('0000000' + @.SOMEVALUE,7)
> PRINT @.SOMEVALUE
>
> HTH, Jens Suessmeyer.
>
Showing posts with label char. Show all posts
Showing posts with label char. Show all posts
Thursday, March 29, 2012
Tuesday, March 27, 2012
CASE statement help
I'm receiving a parameter@.
@.deletions AS CHAR(1)
I want to use this in a select case statement:
--
SELECT
columnA,
columnB,
columnC
FROM
tableA
WHERE
columnC = 'a',
AND
columnD = 'b'
CASE @.deletions
WHEN 'N' THEN AND (Catalogue_Product.Update_Code <> 'D')
END
--
It doesn't seem to like this case statement in the WHERE section...it's
happy with it in the SELECT section.
Any ideas?
Thanks
GriffLet's start off with the basics of programming. There is no CASE
statement in SQL; there is a CASE **expression**; expression return
values. You are still trying to write a procedural language and SQL is
a declarative language.
Guessing at what you meant, try something like this:
SELECT columnA, columnB, columnC
FROM TableA , Catalogue_Products AS P
WHERE columnC = 'a',
AND columnD = 'b'
AND CASE
WHEN @.deletions = 'N'
AND P.update_code <> 'D'
THEN 'N' ELSE 'Y' END = 'Y' ;|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1115142172.036337.315110@.z14g2000cwz.googlegroups.com...
>SQL is a declarative language.
Apologies for use of wrong terminology...
Okay, not sure I explained what I was attempting to do particularly well, so
attempt #2
I want my WHERE statement to behave in one of two ways:
EITHER
WHERE colC='a' AND colD='b'
OR
WHERE colC='a' AND colD='b' AND colE<>'d'
So, the WHERE statement will either have the "AND colE<>'d'" bit or it
won't.
I thought of using a SELECT EXPRESION based on an input parameter to control
this, but it doesn't appear to like it.
For example
WHERE
colC='a'
AND
colD='b'
CASE @.myInputParameter
WHEN 'n' THEN AND colE<>'d'
END
Griff|||DECLARE @.myInputParameter CHAR(1)
SELECT columnA, columnB, columnC
FROM tableA
WHERE columnC = 'a'
AND columnD = 'b'
AND ((@.myInputParameter = 'N' AND columnE <> 'D')
OR (@.myInputParameter <> 'N'))
You'll probably get better performance out of it (index issues may surface
from using OR, <> and variables in your WHERE clause) if you split it up
into two possible queries based on an IF statement:
DECLARE @.myInputParameter CHAR(1)
IF @.myInputParameter = 'Y'
BEGIN
SELECT columnA, columnB, columnC
FROM tableA
WHERE columnC = 'a'
AND columnD = 'b'
END
ELSE
BEGIN
SELECT columnA, columnB, columnC
FROM tableA
WHERE columnC = 'a'
AND columnD = 'b'
AND columnE <> 'D'
END
"Griff" <Howling@.The.Moon> wrote in message
news:OMdEHmAUFHA.3840@.tk2msftngp13.phx.gbl...
> "--CELKO--" <jcelko212@.earthlink.net> wrote in message
> news:1115142172.036337.315110@.z14g2000cwz.googlegroups.com...
> Apologies for use of wrong terminology...
> Okay, not sure I explained what I was attempting to do particularly well,
> so attempt #2
> I want my WHERE statement to behave in one of two ways:
> EITHER
> WHERE colC='a' AND colD='b'
> OR
> WHERE colC='a' AND colD='b' AND colE<>'d'
> So, the WHERE statement will either have the "AND colE<>'d'" bit or it
> won't.
> I thought of using a SELECT EXPRESION based on an input parameter to
> control this, but it doesn't appear to like it.
> For example
> WHERE
> colC='a'
> AND
> colD='b'
> CASE @.myInputParameter
> WHEN 'n' THEN AND colE<>'d'
> END
> Griff
>
>
>|||Direct translation into SQL:
SELECT columnA, columnB, columnC
FROM TableA
WHERE CASE WHEN colC =' a' AND colD =' b' AND @.my_switch = 'Y'
THEN 'Y'
WHEN colC =' a' AND colD =' b' AND colE<>'d'
AND @.my_switch = 'N'
THEN 'Y' ELSE 'N' END = ' Y";
You can factor out the common sub-expression predicates, but this might
be easier to read and maintain.sql
@.deletions AS CHAR(1)
I want to use this in a select case statement:
--
SELECT
columnA,
columnB,
columnC
FROM
tableA
WHERE
columnC = 'a',
AND
columnD = 'b'
CASE @.deletions
WHEN 'N' THEN AND (Catalogue_Product.Update_Code <> 'D')
END
--
It doesn't seem to like this case statement in the WHERE section...it's
happy with it in the SELECT section.
Any ideas?
Thanks
GriffLet's start off with the basics of programming. There is no CASE
statement in SQL; there is a CASE **expression**; expression return
values. You are still trying to write a procedural language and SQL is
a declarative language.
Guessing at what you meant, try something like this:
SELECT columnA, columnB, columnC
FROM TableA , Catalogue_Products AS P
WHERE columnC = 'a',
AND columnD = 'b'
AND CASE
WHEN @.deletions = 'N'
AND P.update_code <> 'D'
THEN 'N' ELSE 'Y' END = 'Y' ;|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1115142172.036337.315110@.z14g2000cwz.googlegroups.com...
>SQL is a declarative language.
Apologies for use of wrong terminology...
Okay, not sure I explained what I was attempting to do particularly well, so
attempt #2
I want my WHERE statement to behave in one of two ways:
EITHER
WHERE colC='a' AND colD='b'
OR
WHERE colC='a' AND colD='b' AND colE<>'d'
So, the WHERE statement will either have the "AND colE<>'d'" bit or it
won't.
I thought of using a SELECT EXPRESION based on an input parameter to control
this, but it doesn't appear to like it.
For example
WHERE
colC='a'
AND
colD='b'
CASE @.myInputParameter
WHEN 'n' THEN AND colE<>'d'
END
Griff|||DECLARE @.myInputParameter CHAR(1)
SELECT columnA, columnB, columnC
FROM tableA
WHERE columnC = 'a'
AND columnD = 'b'
AND ((@.myInputParameter = 'N' AND columnE <> 'D')
OR (@.myInputParameter <> 'N'))
You'll probably get better performance out of it (index issues may surface
from using OR, <> and variables in your WHERE clause) if you split it up
into two possible queries based on an IF statement:
DECLARE @.myInputParameter CHAR(1)
IF @.myInputParameter = 'Y'
BEGIN
SELECT columnA, columnB, columnC
FROM tableA
WHERE columnC = 'a'
AND columnD = 'b'
END
ELSE
BEGIN
SELECT columnA, columnB, columnC
FROM tableA
WHERE columnC = 'a'
AND columnD = 'b'
AND columnE <> 'D'
END
"Griff" <Howling@.The.Moon> wrote in message
news:OMdEHmAUFHA.3840@.tk2msftngp13.phx.gbl...
> "--CELKO--" <jcelko212@.earthlink.net> wrote in message
> news:1115142172.036337.315110@.z14g2000cwz.googlegroups.com...
> Apologies for use of wrong terminology...
> Okay, not sure I explained what I was attempting to do particularly well,
> so attempt #2
> I want my WHERE statement to behave in one of two ways:
> EITHER
> WHERE colC='a' AND colD='b'
> OR
> WHERE colC='a' AND colD='b' AND colE<>'d'
> So, the WHERE statement will either have the "AND colE<>'d'" bit or it
> won't.
> I thought of using a SELECT EXPRESION based on an input parameter to
> control this, but it doesn't appear to like it.
> For example
> WHERE
> colC='a'
> AND
> colD='b'
> CASE @.myInputParameter
> WHEN 'n' THEN AND colE<>'d'
> END
> Griff
>
>
>|||Direct translation into SQL:
SELECT columnA, columnB, columnC
FROM TableA
WHERE CASE WHEN colC =' a' AND colD =' b' AND @.my_switch = 'Y'
THEN 'Y'
WHEN colC =' a' AND colD =' b' AND colE<>'d'
AND @.my_switch = 'N'
THEN 'Y' ELSE 'N' END = ' Y";
You can factor out the common sub-expression predicates, but this might
be easier to read and maintain.sql
Thursday, March 22, 2012
Case order by
Hello
I was wondering if you guys could help me, I have a table with Firstname and
Lastname columns.
And I want to pass a parameter @.SortCol char(10) and @.AscDesc, so I can sort
Firstname ASC/DESC or Lastname ASC/DESC....
how's that done, i need to put 4 different Cases?
TIA
/LasseWhat about that
Use northwind
DECLARE @.col varchar(200)
DECLARE @.Direc varchar(200)
SET @.col = 'OrderID'
SET @.Direc = 'ASC'
EXEC('Select * from Orders Order by ' + @.col + ' ' + @.Direc)
http://www.sommarskog.se/dynamic_sql.html
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Lasse Edsvik" <lasse@.nospam.com> schrieb im Newsbeitrag
news:OKlIIzxSFHA.1384@.TK2MSFTNGP09.phx.gbl...
> Hello
> I was wondering if you guys could help me, I have a table with Firstname
> and
> Lastname columns.
> And I want to pass a parameter @.SortCol char(10) and @.AscDesc, so I can
> sort
> Firstname ASC/DESC or Lastname ASC/DESC....
> how's that done, i need to put 4 different Cases?
> TIA
> /Lasse
>|||SELECT <column list>
FROM <table>
ORDER BY CASE @.AscDesc WHEN 'ASC' THEN
CASE @.SortCol
WHEN 'Firstname' THEN Firstname
WHEN 'Lastname' THEN Lastname
END END ASC,
CASE @.AscDesc WHEN 'DESC' THEN
CASE @.SortCol
WHEN 'Firstname' THEN Firstname
WHEN 'Lastname' THEN Lastname
END END DESC,
Jacco Schalkwijk
SQL Server MVP
"Lasse Edsvik" <lasse@.nospam.com> wrote in message
news:OKlIIzxSFHA.1384@.TK2MSFTNGP09.phx.gbl...
> Hello
> I was wondering if you guys could help me, I have a table with Firstname
> and
> Lastname columns.
> And I want to pass a parameter @.SortCol char(10) and @.AscDesc, so I can
> sort
> Firstname ASC/DESC or Lastname ASC/DESC....
> how's that done, i need to put 4 different Cases?
> TIA
> /Lasse
>|||Lasse
ORDER BY CASE WHEN @.sort = 'col1' AND @.dir = 1 THEN col1 END ASC,
CASE WHEN @.sort = 'col1' AND @.dir = 0 THEN col1 END DESC,
CASE WHEN @.sort = 'col2' AND @.dir = 1 THEN col2 END ASC,
CASE WHEN @.sort = 'col2' AND @.dir = 0 THEN col2 END DESC
"Lasse Edsvik" <lasse@.nospam.com> wrote in message
news:OKlIIzxSFHA.1384@.TK2MSFTNGP09.phx.gbl...
> Hello
> I was wondering if you guys could help me, I have a table with Firstname
and
> Lastname columns.
> And I want to pass a parameter @.SortCol char(10) and @.AscDesc, so I can
sort
> Firstname ASC/DESC or Lastname ASC/DESC....
> how's that done, i need to put 4 different Cases?
> TIA
> /Lasse
>|||How do I use a variable in an ORDER BY clause?
http://www.aspfaq.com/show.asp?id=2501
AMB
"Lasse Edsvik" wrote:
> Hello
> I was wondering if you guys could help me, I have a table with Firstname a
nd
> Lastname columns.
> And I want to pass a parameter @.SortCol char(10) and @.AscDesc, so I can so
rt
> Firstname ASC/DESC or Lastname ASC/DESC....
> how's that done, i need to put 4 different Cases?
> TIA
> /Lasse
>
>|||The below uses one input variable to control which column to sort by, and
whether to cort asc or desc...
Use NorthWind
Declare @.Order TinyInt
Set @.Order = 1 -- 2,3,4
-- --
Select * From Customers
Order By Case @.Order
When 1 Then CompanyName
When 3 Then ContactName
End,
Case @.Order
When 2 Then CompanyName
When 4 Then ContactName
End Desc
"Lasse Edsvik" wrote:
> Hello
> I was wondering if you guys could help me, I have a table with Firstname a
nd
> Lastname columns.
> And I want to pass a parameter @.SortCol char(10) and @.AscDesc, so I can so
rt
> Firstname ASC/DESC or Lastname ASC/DESC....
> how's that done, i need to put 4 different Cases?
> TIA
> /Lasse
>
>sql
I was wondering if you guys could help me, I have a table with Firstname and
Lastname columns.
And I want to pass a parameter @.SortCol char(10) and @.AscDesc, so I can sort
Firstname ASC/DESC or Lastname ASC/DESC....
how's that done, i need to put 4 different Cases?
TIA
/LasseWhat about that
Use northwind
DECLARE @.col varchar(200)
DECLARE @.Direc varchar(200)
SET @.col = 'OrderID'
SET @.Direc = 'ASC'
EXEC('Select * from Orders Order by ' + @.col + ' ' + @.Direc)
http://www.sommarskog.se/dynamic_sql.html
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Lasse Edsvik" <lasse@.nospam.com> schrieb im Newsbeitrag
news:OKlIIzxSFHA.1384@.TK2MSFTNGP09.phx.gbl...
> Hello
> I was wondering if you guys could help me, I have a table with Firstname
> and
> Lastname columns.
> And I want to pass a parameter @.SortCol char(10) and @.AscDesc, so I can
> sort
> Firstname ASC/DESC or Lastname ASC/DESC....
> how's that done, i need to put 4 different Cases?
> TIA
> /Lasse
>|||SELECT <column list>
FROM <table>
ORDER BY CASE @.AscDesc WHEN 'ASC' THEN
CASE @.SortCol
WHEN 'Firstname' THEN Firstname
WHEN 'Lastname' THEN Lastname
END END ASC,
CASE @.AscDesc WHEN 'DESC' THEN
CASE @.SortCol
WHEN 'Firstname' THEN Firstname
WHEN 'Lastname' THEN Lastname
END END DESC,
Jacco Schalkwijk
SQL Server MVP
"Lasse Edsvik" <lasse@.nospam.com> wrote in message
news:OKlIIzxSFHA.1384@.TK2MSFTNGP09.phx.gbl...
> Hello
> I was wondering if you guys could help me, I have a table with Firstname
> and
> Lastname columns.
> And I want to pass a parameter @.SortCol char(10) and @.AscDesc, so I can
> sort
> Firstname ASC/DESC or Lastname ASC/DESC....
> how's that done, i need to put 4 different Cases?
> TIA
> /Lasse
>|||Lasse
ORDER BY CASE WHEN @.sort = 'col1' AND @.dir = 1 THEN col1 END ASC,
CASE WHEN @.sort = 'col1' AND @.dir = 0 THEN col1 END DESC,
CASE WHEN @.sort = 'col2' AND @.dir = 1 THEN col2 END ASC,
CASE WHEN @.sort = 'col2' AND @.dir = 0 THEN col2 END DESC
"Lasse Edsvik" <lasse@.nospam.com> wrote in message
news:OKlIIzxSFHA.1384@.TK2MSFTNGP09.phx.gbl...
> Hello
> I was wondering if you guys could help me, I have a table with Firstname
and
> Lastname columns.
> And I want to pass a parameter @.SortCol char(10) and @.AscDesc, so I can
sort
> Firstname ASC/DESC or Lastname ASC/DESC....
> how's that done, i need to put 4 different Cases?
> TIA
> /Lasse
>|||How do I use a variable in an ORDER BY clause?
http://www.aspfaq.com/show.asp?id=2501
AMB
"Lasse Edsvik" wrote:
> Hello
> I was wondering if you guys could help me, I have a table with Firstname a
nd
> Lastname columns.
> And I want to pass a parameter @.SortCol char(10) and @.AscDesc, so I can so
rt
> Firstname ASC/DESC or Lastname ASC/DESC....
> how's that done, i need to put 4 different Cases?
> TIA
> /Lasse
>
>|||The below uses one input variable to control which column to sort by, and
whether to cort asc or desc...
Use NorthWind
Declare @.Order TinyInt
Set @.Order = 1 -- 2,3,4
-- --
Select * From Customers
Order By Case @.Order
When 1 Then CompanyName
When 3 Then ContactName
End,
Case @.Order
When 2 Then CompanyName
When 4 Then ContactName
End Desc
"Lasse Edsvik" wrote:
> Hello
> I was wondering if you guys could help me, I have a table with Firstname a
nd
> Lastname columns.
> And I want to pass a parameter @.SortCol char(10) and @.AscDesc, so I can so
rt
> Firstname ASC/DESC or Lastname ASC/DESC....
> how's that done, i need to put 4 different Cases?
> TIA
> /Lasse
>
>sql
Wednesday, March 7, 2012
Carriage return within column alias
Is there a way to insert a carriage return or line feed in the middle of a column alias within a select statement? I tried using the CHAR function for the ascii value of the carriage return, but SQL Server wouldn't allow it inside the alias name for the column.
Any ideas?What are you trying to achieve with the end result? Is the result to be used in html or something? If so, you can use html tags in the header.|||The database query will be used in an ASP script run from a web site. The problem was that there were so many columns that I couldn't fit them on one page landscape for printing. If I can put some of the column headings (which are declared as aliases in my SQL query) on two lines as opposed to one long heading line, it will save page space.|||The simple solution, then is to put the HTML tag in the alias.. ie
SELECT col1 as 'COLUMN <BR> ONE'
bla bla bla
then when the column header is rendered by the asp, if it is set up correctly, it will put the break in. I believe, however, that there are ways to do this in HTML w/o the need of putting it in the column name.
Hope this helps.
Any ideas?What are you trying to achieve with the end result? Is the result to be used in html or something? If so, you can use html tags in the header.|||The database query will be used in an ASP script run from a web site. The problem was that there were so many columns that I couldn't fit them on one page landscape for printing. If I can put some of the column headings (which are declared as aliases in my SQL query) on two lines as opposed to one long heading line, it will save page space.|||The simple solution, then is to put the HTML tag in the alias.. ie
SELECT col1 as 'COLUMN <BR> ONE'
bla bla bla
then when the column header is rendered by the asp, if it is set up correctly, it will put the break in. I believe, however, that there are ways to do this in HTML w/o the need of putting it in the column name.
Hope this helps.
Subscribe to:
Posts (Atom)