Query help

Hello Everyone,

I have a query that looks something like this:

DEFINE @.VAR_A VARCHAR(6)

DECLARE trsite_cursor CURSOR FOR
SELECT DISTINCT AppField
FROM TABLE_1
ORDER BY 1

OPEN trsite_cursor

FETCH NEXT FROM trsite_cursor INTO @.VAR_A

WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.SELCMD ='SELECT * FROM TABLE_2
WHERE Field1 =' + @.VAR_A +
' ORDER BY 1,2,3;'
END
...
...
...
EXEC xp_sendmail @.query = @.SELCMD,
...
...

and the rest of the query.....

The column "AppField" in TABLE_1 has been defined as varchar. Let's
assume it contains the value: ABCD. When I run it, the query fails at
the SET @.SELCMD statement, saying that the column name ABCD is
invalid. It assumes that ABCD is a column name & not a value. However,
if AppField contains a numeric value, ex: 123, I don't get any errors
& the query outputs the desired results.

So, I guess, the question is: how do I make the SET @.SELCMD treat the
value in AppField as "ABCD" or 'ABCD' and not just ABCD?

Thanks,
Suhassurround the variable in quotes in the string

SET @.SELCMD ='SELECT * FROM TABLE_2
WHERE Field1 = '' ' + @.VAR_A +
'' ' ORDER BY 1,2,3;'

also you need another fetch before the END in the while loop or you
loop infinitly|||"Suhas" <sgtembe@.hotmail.com> wrote in message
news:5c440ee2.0311201306.4e7f5323@.posting.google.c om...
> SET @.SELCMD ='SELECT * FROM TABLE_2
> WHERE Field1 =' + @.VAR_A +
> ' ORDER BY 1,2,3;'
> assume it contains the value: ABCD. When I run it, the query fails at
> the SET @.SELCMD statement, saying that the column name ABCD is
> invalid. It assumes that ABCD is a column name & not a value. However,

--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1

You need to enclose the value contained in @.VAR_A in single quotes when you
build the @.SELCMD string. In other words, you need to create a string that
contains single quotes. To do that, you use a *pair* of single quotes. So,
the SET command should look like this:

SET @.SELCMD ='SELECT * FROM TABLE_2
WHERE Field1 = ''' + @.VAR_A +
''' ORDER BY 1,2,3;'
END

If @.VAR_A = ABCD then this set command sets @.SELCMD to:
SELECT * FROM TABLE_2 WHERE Field1 = 'ABCD' ORDER BY 1,2,3;

--BEGIN PGP SIGNATURE--
Version: GnuPG v1.0.6 (MingW32)
Comment: For info see http://www.gnupg.org

iEYEARECAAYFAj+9Pw0ACgkQFt8ABY6ZYSJ0bQCdE0eEaDLP8l 0QhwQVjnMe6DME
BY8Anjp68HxMiMxCqs9zLHWEHgTkQtGw
=agcy
--END PGP SIGNATURE--

Query for finding trailing spaces in a column

It seems like our application inserting trailing spaces
into the varchar field (spaces before and after the
string). Can anyone help me with a query to find out which
rows have trailing spaces in a column ?
Thanks for any help......
SET NOCOUNT ON
CREATE TABLE #splunge
(
foo VARCHAR(10)
)
GO
INSERT #splunge SELECT 'val1'
INSERT #splunge SELECT 'val2 '
INSERT #splunge SELECT ' val3'
INSERT #splunge SELECT ' val4 '
GO
-- leading spaces:
SELECT * FROM #splunge WHERE LTRIM(foo) != foo
-- trailing spaces:
SELECT * FROM #splunge WHERE DATALENGTH(RTRIM(foo))!=DATALENGTH(foo)
GO
DROP TABLE #splunge
GO
http://www.aspfaq.com/
(Reverse address to reply.)
"John" <anonymous@.discussions.microsoft.com> wrote in message
news:2796201c4642a$94e8df30$a601280a@.phx.gbl...
> It seems like our application inserting trailing spaces
> into the varchar field (spaces before and after the
> string). Can anyone help me with a query to find out which
> rows have trailing spaces in a column ?
> Thanks for any help......
|||Thanks........

>--Original Message--
>SET NOCOUNT ON
>CREATE TABLE #splunge
>(
> foo VARCHAR(10)
>)
>GO
>INSERT #splunge SELECT 'val1'
>INSERT #splunge SELECT 'val2 '
>INSERT #splunge SELECT ' val3'
>INSERT #splunge SELECT ' val4 '
>GO
>-- leading spaces:
>SELECT * FROM #splunge WHERE LTRIM(foo) != foo
>-- trailing spaces:
>SELECT * FROM #splunge WHERE DATALENGTH(RTRIM(foo))!
=DATALENGTH(foo)
>GO
>DROP TABLE #splunge
>GO
>--
>http://www.aspfaq.com/
>(Reverse address to reply.)
>
>
>"John" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:2796201c4642a$94e8df30$a601280a@.phx.gbl...
which
>
>.
>
|||select * from TheTable where theColumn like '% '
"John" <anonymous@.discussions.microsoft.com> wrote in message
news:2796201c4642a$94e8df30$a601280a@.phx.gbl...
> It seems like our application inserting trailing spaces
> into the varchar field (spaces before and after the
> string). Can anyone help me with a query to find out which
> rows have trailing spaces in a column ?

Query for finding trailing spaces in a column

It seems like our application inserting trailing spaces
into the varchar field (spaces before and after the
string). Can anyone help me with a query to find out which
rows have trailing spaces in a column '
Thanks for any help......SET NOCOUNT ON
CREATE TABLE #splunge
(
foo VARCHAR(10)
)
GO
INSERT #splunge SELECT 'val1'
INSERT #splunge SELECT 'val2 '
INSERT #splunge SELECT ' val3'
INSERT #splunge SELECT ' val4 '
GO
-- leading spaces:
SELECT * FROM #splunge WHERE LTRIM(foo) != foo
-- trailing spaces:
SELECT * FROM #splunge WHERE DATALENGTH(RTRIM(foo))!=DATALENGTH(foo)
GO
DROP TABLE #splunge
GO
--
http://www.aspfaq.com/
(Reverse address to reply.)
"John" <anonymous@.discussions.microsoft.com> wrote in message
news:2796201c4642a$94e8df30$a601280a@.phx.gbl...
> It seems like our application inserting trailing spaces
> into the varchar field (spaces before and after the
> string). Can anyone help me with a query to find out which
> rows have trailing spaces in a column '
> Thanks for any help......|||Thanks........
>--Original Message--
>SET NOCOUNT ON
>CREATE TABLE #splunge
>(
> foo VARCHAR(10)
>)
>GO
>INSERT #splunge SELECT 'val1'
>INSERT #splunge SELECT 'val2 '
>INSERT #splunge SELECT ' val3'
>INSERT #splunge SELECT ' val4 '
>GO
>-- leading spaces:
>SELECT * FROM #splunge WHERE LTRIM(foo) != foo
>-- trailing spaces:
>SELECT * FROM #splunge WHERE DATALENGTH(RTRIM(foo))!
=DATALENGTH(foo)
>GO
>DROP TABLE #splunge
>GO
>--
>http://www.aspfaq.com/
>(Reverse address to reply.)
>
>
>"John" <anonymous@.discussions.microsoft.com> wrote in
message
>news:2796201c4642a$94e8df30$a601280a@.phx.gbl...
>> It seems like our application inserting trailing spaces
>> into the varchar field (spaces before and after the
>> string). Can anyone help me with a query to find out
which
>> rows have trailing spaces in a column '
>> Thanks for any help......
>
>.
>|||select * from TheTable where theColumn like '% '
"John" <anonymous@.discussions.microsoft.com> wrote in message
news:2796201c4642a$94e8df30$a601280a@.phx.gbl...
> It seems like our application inserting trailing spaces
> into the varchar field (spaces before and after the
> string). Can anyone help me with a query to find out which
> rows have trailing spaces in a column '

Query for finding trailing spaces in a column

It seems like our application inserting trailing spaces
into the varchar field (spaces before and after the
string). Can anyone help me with a query to find out which
rows have trailing spaces in a column '
Thanks for any help......SET NOCOUNT ON
CREATE TABLE #splunge
(
foo VARCHAR(10)
)
GO
INSERT #splunge SELECT 'val1'
INSERT #splunge SELECT 'val2 '
INSERT #splunge SELECT ' val3'
INSERT #splunge SELECT ' val4 '
GO
-- leading spaces:
SELECT * FROM #splunge WHERE LTRIM(foo) != foo
-- trailing spaces:
SELECT * FROM #splunge WHERE DATALENGTH(RTRIM(foo))!=DATALENGTH(foo)
GO
DROP TABLE #splunge
GO
http://www.aspfaq.com/
(Reverse address to reply.)
"John" <anonymous@.discussions.microsoft.com> wrote in message
news:2796201c4642a$94e8df30$a601280a@.phx
.gbl...
> It seems like our application inserting trailing spaces
> into the varchar field (spaces before and after the
> string). Can anyone help me with a query to find out which
> rows have trailing spaces in a column '
> Thanks for any help......|||Thanks........

>--Original Message--
>SET NOCOUNT ON
>CREATE TABLE #splunge
>(
> foo VARCHAR(10)
> )
>GO
>INSERT #splunge SELECT 'val1'
>INSERT #splunge SELECT 'val2 '
>INSERT #splunge SELECT ' val3'
>INSERT #splunge SELECT ' val4 '
>GO
>-- leading spaces:
>SELECT * FROM #splunge WHERE LTRIM(foo) != foo
>-- trailing spaces:
>SELECT * FROM #splunge WHERE DATALENGTH(RTRIM(foo))!
=DATALENGTH(foo)
>GO
>DROP TABLE #splunge
>GO
>--
>http://www.aspfaq.com/
>(Reverse address to reply.)
>
>
>"John" <anonymous@.discussions.microsoft.com> wrote in
message
> news:2796201c4642a$94e8df30$a601280a@.phx
.gbl...
which[vbcol=seagreen]
>
>.
>|||select * from TheTable where theColumn like '% '
"John" <anonymous@.discussions.microsoft.com> wrote in message
news:2796201c4642a$94e8df30$a601280a@.phx
.gbl...
> It seems like our application inserting trailing spaces
> into the varchar field (spaces before and after the
> string). Can anyone help me with a query to find out which
> rows have trailing spaces in a column '

QUERY Exceeding variable max length

Hi,

A query is exceeding the length of varchar and nvarchar variable.
Because I'm picking the data from each record from table and giving it
to the query.

suggest me some way to do it.

sample query:

SELECT P1.*, (P1.Q1 + P1.Q2 + P1.Q3 + P1.Q4) AS YearTotal
FROM (SELECT Year,
SUM(CASE P.Quarter WHEN 1 THEN P.Amount ELSE 0 END) AS
Q1,
SUM(CASE P.Quarter WHEN 2 THEN P.Amount ELSE 0 END) AS
Q2,
SUM(CASE P.Quarter WHEN 3 THEN P.Amount ELSE 0 END) AS
Q3,
SUM(CASE P.Quarter WHEN 4 THEN P.Amount ELSE 0 END) AS Q4
FROM Pivot1 AS P
GROUP BY P.Year) AS P1
GO

--> even the P.QUARTER ... FIELD NAME IS BEING GENERATED
DYNAMICALLY.

MY QUERY IS EXCEEDING VARCHAR AND NVARCHAR LIMIT.

THANX IN ADV.One option is to split up the VARCHAR variable into multiple variables &
concatenate them in your EXEC like:
EXEC( @.v1 + @.v2 + ...)

Also, you can avoid all these hazzles, if you bring back the resultset to
the client application & do the pivoting on the front end.

--
- Anith
( Please reply to newsgroups only )

Query by length of varchar column

Hi mates,

I have a Table:Test with column text:varchar(255). I want get rows where text length to be longer than 100. Is it possible?

Thx in advance,

Look at LEN and DATALENGTH functions in BOL.

select *

from TableName

where datalength(colName) > 100

|||Thx very much!

Query Assistance

Hi,
Assuming the following table structure and sample data:
CREATE TABLE TestTab
(Col1 varchar(30) NOT NULL,
Col2 varchar(30) NOT NULL,
Col3 varchar(50) NOT NULL)
Col1 Col2 Col3
XYZ 54 Test
XYZ 54 Simple
ABC 28 Bogus
How can I return the only one entry for a combination of Col1 and Col2?
I.e., Resultset
Col1 Col2 Col3
XYZ 54 Test
ABC 28 Bogus
It doesn't matter wich record is return i.e., Col3 Test or Simple is fine.
Thanks
JerrySELECT col1, col2, MIN(col3)
FROM Test
GROUP BY col1, col2;|||>> How can I return the only one entry for a combination of Col1 and Col2?
You have to specify which row you want to return
It may not matter to the users, but it matters to the DBMS to consistently
derive the resultset. One option would be to simply use an extrema function
on col3 like:
SELECT col1, col2, MAX( col3 )
FROM tbl
GROUP BY col1, col2 ;
Anith|||Duh! Spaced that one. Thanks guys!
Jerry
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1126563751.937497.46640@.g43g2000cwa.googlegroups.com...
> SELECT col1, col2, MIN(col3)
> FROM Test
> GROUP BY col1, col2;
>

query assistance

I have a field in a table that the datatype is varchar
the data stored in the field is a date in the format of 7/19/2007
I need to run a query that will give me all teh records that is of a
certain date or newer.
teh query I am running is
Select * from table1 where {do rec] >= '07/19/2007'
what I get is pretty much everything. items dated 7/31/2006 etc...
how can I run this query correctly?
The reason the field is a varchar is becasue I don't want the 'time' in the
field along with the date.
thanks in advance.The obvious question is why you stored the values as varchar instead of datetime. I suggest you fix
the design before it causes more trouble, bad performance and grief.
Having said that, you can convert the value to datetime (in the WHERE clause) before you do the
conversion. Since you use a date format which isn't language neutral (assuming the values are stored
in the format m/d/yyyy), you need to use CONVERT (with a proper formatting code) instead of CAST,
and because the conversion of the column value to datetime, you need to be prepared for the query to
be slow. Something like:
WHERE CONVERT(datetime, dtcol, 101) > '20070719'
I suggest you give below some time and thoughts:
http://www.karaszi.com/SQLServer/info_datetime.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Johnfli" <john@.ivhs.us> wrote in message news:uqVwl3S1HHA.3760@.TK2MSFTNGP03.phx.gbl...
>I have a field in a table that the datatype is varchar
> the data stored in the field is a date in the format of 7/19/2007
> I need to run a query that will give me all teh records that is of a certain date or newer.
> teh query I am running is
> Select * from table1 where {do rec] >= '07/19/2007'
> what I get is pretty much everything. items dated 7/31/2006 etc...
> how can I run this query correctly?
> The reason the field is a varchar is becasue I don't want the 'time' in the field along with the
> date.
>
> thanks in advance.
>
>|||I tried going to your link, but the page cannot be found
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:A0346BCE-A87B-40A3-9C98-247B6A6B273D@.microsoft.com...
> The obvious question is why you stored the values as varchar instead of
> datetime. I suggest you fix the design before it causes more trouble, bad
> performance and grief.
> Having said that, you can convert the value to datetime (in the WHERE
> clause) before you do the conversion. Since you use a date format which
> isn't language neutral (assuming the values are stored in the format
> m/d/yyyy), you need to use CONVERT (with a proper formatting code) instead
> of CAST, and because the conversion of the column value to datetime, you
> need to be prepared for the query to be slow. Something like:
> WHERE CONVERT(datetime, dtcol, 101) > '20070719'
> I suggest you give below some time and thoughts:
> http://www.karaszi.com/SQLServer/info_datetime.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Johnfli" <john@.ivhs.us> wrote in message
> news:uqVwl3S1HHA.3760@.TK2MSFTNGP03.phx.gbl...
>>I have a field in a table that the datatype is varchar
>> the data stored in the field is a date in the format of 7/19/2007
>> I need to run a query that will give me all teh records that is of a
>> certain date or newer.
>> teh query I am running is
>> Select * from table1 where {do rec] >= '07/19/2007'
>> what I get is pretty much everything. items dated 7/31/2006 etc...
>> how can I run this query correctly?
>> The reason the field is a varchar is becasue I don't want the 'time' in
>> the field along with the date.
>>
>> thanks in advance.
>>
>|||On Aug 2, 12:53 pm, "Johnfli" <j...@.ivhs.us> wrote:
> I have a field in a table that the datatype is varchar
> the data stored in the field is a date in the format of 7/19/2007
> I need to run a query that will give me all teh records that is of a
> certain date or newer.
> teh query I am running is
> Select * from table1 where {do rec] >= '07/19/2007'
> what I get is pretty much everything. items dated 7/31/2006 etc...
> how can I run this query correctly?
> The reason the field is a varchar is becasue I don't want the 'time' in the
> field along with the date.
> thanks in advance.
I do agree that you really should change your database design to use a
proper data type. This will make your life easier and not require
some of the conversions. Storing the time in the database shouldn't
cause you any grief and you shouldn't have any issues with the query
you specified
Of course your workaround will be to utilize the convert function in
your query.|||Perhaps there was a temporary IP hiccup somewhere. I just re-tried my link and it work fine for me:
http://www.karaszi.com/SQLServer/info_datetime.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Johnfli" <john@.ivhs.us> wrote in message news:etTzacV1HHA.4344@.TK2MSFTNGP03.phx.gbl...
>I tried going to your link, but the page cannot be found
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:A0346BCE-A87B-40A3-9C98-247B6A6B273D@.microsoft.com...
>> The obvious question is why you stored the values as varchar instead of
>> datetime. I suggest you fix the design before it causes more trouble, bad
>> performance and grief.
>> Having said that, you can convert the value to datetime (in the WHERE
>> clause) before you do the conversion. Since you use a date format which
>> isn't language neutral (assuming the values are stored in the format
>> m/d/yyyy), you need to use CONVERT (with a proper formatting code) instead
>> of CAST, and because the conversion of the column value to datetime, you
>> need to be prepared for the query to be slow. Something like:
>> WHERE CONVERT(datetime, dtcol, 101) > '20070719'
>> I suggest you give below some time and thoughts:
>> http://www.karaszi.com/SQLServer/info_datetime.asp
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Johnfli" <john@.ivhs.us> wrote in message
>> news:uqVwl3S1HHA.3760@.TK2MSFTNGP03.phx.gbl...
>>I have a field in a table that the datatype is varchar
>> the data stored in the field is a date in the format of 7/19/2007
>> I need to run a query that will give me all teh records that is of a
>> certain date or newer.
>> teh query I am running is
>> Select * from table1 where {do rec] >= '07/19/2007'
>> what I get is pretty much everything. items dated 7/31/2006 etc...
>> how can I run this query correctly?
>> The reason the field is a varchar is becasue I don't want the 'time' in
>> the field along with the date.
>>
>> thanks in advance.
>>
>>
>|||I am trying to change the field type to DateTime, but I can not figure out
how. :(
"acorcoran" <acorcoran@.gmail.com> wrote in message
news:1186105967.168834.62160@.19g2000hsx.googlegroups.com...
> On Aug 2, 12:53 pm, "Johnfli" <j...@.ivhs.us> wrote:
>> I have a field in a table that the datatype is varchar
>> the data stored in the field is a date in the format of 7/19/2007
>> I need to run a query that will give me all teh records that is of a
>> certain date or newer.
>> teh query I am running is
>> Select * from table1 where {do rec] >= '07/19/2007'
>> what I get is pretty much everything. items dated 7/31/2006 etc...
>> how can I run this query correctly?
>> The reason the field is a varchar is becasue I don't want the 'time' in
>> the
>> field along with the date.
>> thanks in advance.
> I do agree that you really should change your database design to use a
> proper data type. This will make your life easier and not require
> some of the conversions. Storing the time in the database shouldn't
> cause you any grief and you shouldn't have any issues with the query
> you specified
> Of course your workaround will be to utilize the convert function in
> your query.
>|||Here's how such a ALTER TABLE statement could look like:
ALTER TABLE tbl
ALTER COLUMN col datetime
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Johnfli" <john@.ivhs.us> wrote in message news:%23meH$Jf1HHA.3788@.TK2MSFTNGP02.phx.gbl...
>I am trying to change the field type to DateTime, but I can not figure out
> how. :(
>
>
> "acorcoran" <acorcoran@.gmail.com> wrote in message
> news:1186105967.168834.62160@.19g2000hsx.googlegroups.com...
>> On Aug 2, 12:53 pm, "Johnfli" <j...@.ivhs.us> wrote:
>> I have a field in a table that the datatype is varchar
>> the data stored in the field is a date in the format of 7/19/2007
>> I need to run a query that will give me all teh records that is of a
>> certain date or newer.
>> teh query I am running is
>> Select * from table1 where {do rec] >= '07/19/2007'
>> what I get is pretty much everything. items dated 7/31/2006 etc...
>> how can I run this query correctly?
>> The reason the field is a varchar is becasue I don't want the 'time' in
>> the
>> field along with the date.
>> thanks in advance.
>> I do agree that you really should change your database design to use a
>> proper data type. This will make your life easier and not require
>> some of the conversions. Storing the time in the database shouldn't
>> cause you any grief and you shouldn't have any issues with the query
>> you specified
>> Of course your workaround will be to utilize the convert function in
>> your query.
>