Query Problem

Hi All,
I am having one problem with the query. Please see below the query.
Declare @.p_code_type_cd varchar (200),--3599, 6023
@.p_lang_cd varchar (10),--39
@.p_code_val varchar (2000), --67330000, 67000000
Select @.p_code_type_cd = '3599,6023', @.p_lang_cd = '39', @.p_code_val =
'67330000, 67000000'
select code_type_cd, code_val, desc_text
from code_tbl where code_type_cd in (@.p_code_type_cd)
and code_val in (@.p_code_val)and lang_cd = @.p_lang_cd
When this query is executed it produces no result. I analyzed and found
that the query which is being executed is converted as mentioned
below:-
select code_type_cd, code_val, desc_text
from code_tbl where code_type_cd in (3599,6023)
and code_val in (67330000, 67000000) and lang_cd = 39
i think it is missing few single quotes value. I am just how
to add these quotes in the query so that it becomes like..
select code_type_cd, code_val, desc_text
from code_tbl where code_type_cd in ('3599','6023')
and code_val in ('67330000','67000000')and lang_cd = '39'Hi
You can not use an array in this way without using dynamic SQL see
http://www.sommarskog.se/arrays-in-sql.html
John
"neeju" wrote:

> Hi All,
> I am having one problem with the query. Please see below the query.
> Declare @.p_code_type_cd varchar (200),--3599, 6023
> @.p_lang_cd varchar (10),--39
> @.p_code_val varchar (2000), --67330000, 67000000
> Select @.p_code_type_cd = '3599,6023', @.p_lang_cd = '39', @.p_code_val =
> '67330000, 67000000'
> select code_type_cd, code_val, desc_text
> from code_tbl where code_type_cd in (@.p_code_type_cd)
> and code_val in (@.p_code_val)and lang_cd = @.p_lang_cd
> When this query is executed it produces no result. I analyzed and found
> that the query which is being executed is converted as mentioned
> below:-
> select code_type_cd, code_val, desc_text
> from code_tbl where code_type_cd in (3599,6023)
> and code_val in (67330000, 67000000) and lang_cd = 39
> i think it is missing few single quotes value. I am just how
> to add these quotes in the query so that it becomes like..
> select code_type_cd, code_val, desc_text
> from code_tbl where code_type_cd in ('3599','6023')
> and code_val in ('67330000','67000000')and lang_cd = '39'
>|||You can also do this with table variables, with only a few minor changes to
your code:
Declare @.p_lang_cd varchar (10)
/* Original variable assignment replaced with DECLARE ... TABLE
Select @.p_code_type_cd = '3599,6023', @.p_lang_cd = '39', @.p_code_val =
'67330000, 67000000'
*/
SELECT @.p_lang_cd = '39'
DECLARE @.p_code_type_cd TABLE ( p_code_type_cd INT )
INSERT INTO @.p_code_type_cd VALUES ( '3599' )
INSERT INTO @.p_code_type_cd VALUES ( '6023' )
DECLARE @.p_code_val TABLE ( p_code_val INT )
INSERT INTO @.p_code_val VALUES ( '67330000' )
INSERT INTO @.p_code_val VALUES ( '67000000' )
/* Original query
select code_type_cd, code_val, desc_text
from code_tbl
where code_type_cd in (@.p_code_type_cd)
and code_val in (@.p_code_val)
and lang_cd = @.p_lang_cd
*/
-- Revised query
select code_type_cd, code_val, desc_text
from code_tbl
where code_type_cd in (SELECT * FROM @.p_code_type_cd)
and code_val in (SELECT * FROM @.p_code_val)
and lang_cd = @.p_lang_cd
-- Or this might be better
select code_type_cd, code_val, desc_text
from code_tbl c
INNER JOIN @.p_code_type_cd t ON c.code_type_cd = t.p_code_type_cd
INNER JOIN @.p_code_val v ON c.code_val = v.p_code_val
WHERE lang_cd = @.p_lang_cd
Untested! Let me know how you get on.
Damien
"neeju" wrote:

> Hi All,
> I am having one problem with the query. Please see below the query.
> Declare @.p_code_type_cd varchar (200),--3599, 6023
> @.p_lang_cd varchar (10),--39
> @.p_code_val varchar (2000), --67330000, 67000000
> Select @.p_code_type_cd = '3599,6023', @.p_lang_cd = '39', @.p_code_val =
> '67330000, 67000000'
> select code_type_cd, code_val, desc_text
> from code_tbl where code_type_cd in (@.p_code_type_cd)
> and code_val in (@.p_code_val)and lang_cd = @.p_lang_cd
> When this query is executed it produces no result. I analyzed and found
> that the query which is being executed is converted as mentioned
> below:-
> select code_type_cd, code_val, desc_text
> from code_tbl where code_type_cd in (3599,6023)
> and code_val in (67330000, 67000000) and lang_cd = 39
> i think it is missing few single quotes value. I am just how
> to add these quotes in the query so that it becomes like..
> select code_type_cd, code_val, desc_text
> from code_tbl where code_type_cd in ('3599','6023')
> and code_val in ('67330000','67000000')and lang_cd = '39'
>|||Thanks Damien,
Your code executes without any problem...
We have also updated the query and used temp table for storing the
values. But we were just looking for some logic
to change the values in IN operator using some TSQL function. We also
tried Replace function and replaces ',' with quotes in start and end of
both the values But that didn't produced the result.. We used like:-
Declare @.p_code_type_cd varchar (200),--3599, 6023
@.p_lang_cd varchar (10),--39
@.p_code_val varchar (2000),
@.delimeter char(1) --67330000, 67000000
-- @.test varchar (200)
Select @.delimeter = ',', @.p_code_type_cd = '3599,6023', @.p_lang_cd =
'39', @.p_code_val = '67330000, 67000000'
select code_type_cd, code_val, desc_text
from code_tbl where code_type_cd in
(''''+replace(@.p_code_type_cd,@.delimeter
,'',''))
and code_val in (@.p_code_val)and lang_cd = @.p_lang_cd
Please let me know if any other thoughts,
Thanks,
NJ|||Hi
You can not do this without resorting to dynamic SQL. See
http://www.sommarskog.se/arrays-in-sql.html
DECLARE @.p_code_type_cd varchar (200),--3599, 6023
@.p_lang_cd varchar (10),--39
@.p_code_val varchar (2000),
@.delimeter char(1) --67330000, 67000000
DECLARE @.sqlstmt nvarchar(4000)
-- @.test varchar (200)
Select @.delimeter = ',', @.p_code_type_cd = '3599,6023', @.p_lang_cd =
'39', @.p_code_val = '67330000, 67000000'
SET @.sqlstmt = 'select code_type_cd, code_val, desc_text
from code_tbl where code_type_cd in
(' + @.p_code_type_cd + ')
and code_val in (' + @.p_code_val + ')and lang_cd = @.p_lang_cd'
SELECT @.sqlstmt
EXEC sp_executesql @.sqlstmt
John
"neeju" wrote:

> Thanks Damien,
> Your code executes without any problem...
> We have also updated the query and used temp table for storing the
> values. But we were just looking for some logic
> to change the values in IN operator using some TSQL function. We also
> tried Replace function and replaces ',' with quotes in start and end of
> both the values But that didn't produced the result.. We used like:-
> Declare @.p_code_type_cd varchar (200),--3599, 6023
> @.p_lang_cd varchar (10),--39
> @.p_code_val varchar (2000),
> @.delimeter char(1) --67330000, 67000000
> -- @.test varchar (200)
> Select @.delimeter = ',', @.p_code_type_cd = '3599,6023', @.p_lang_cd =
> '39', @.p_code_val = '67330000, 67000000'
> select code_type_cd, code_val, desc_text
> from code_tbl where code_type_cd in
> (''''+replace(@.p_code_type_cd,@.delimeter
,'',''))
> and code_val in (@.p_code_val)and lang_cd = @.p_lang_cd
> Please let me know if any other thoughts,
> Thanks,
> NJ
>|||Yes John,
It appears so.. We tried no. of things but of no use..
I argued with the developers for not using dynamic sql and promised to
give some alternative statement withoug dynamic sql but nothing works.
Anyway thanks for directing to such a good article.
Thanks,
NJ|||Actually, you are missing a proper data model. Data element names like
"code_type_cd" are absurd. A code and a type are different kinds of
attributes, so you should have names like "postal_code" or
"blood_type" instead of a list of adjectives looking for a noun. Same
problem with "code_val"
What kind of code is in "code_tbl"? This name implies that you are
dealing with furniture. It must be ONE AND ONE KIND of code to be a
valid table. There is no such thing as a "Magical, Universal Does
Everything" code table in an RDBMS. Surely you have not mixed data and
metadata in a schema to build a OTLT or MUCK? Google those words and
start your research.
You also do not seem to know that SQL is compiled, so passing a string
is not like writing code on the fly in an interpreter. Without DDL and
sensible data element names, nobody can really help you. But based on
past experience, when the schema is bad, the kludge is usually dynamic
SQL.
The reason people give you that kludge is that it gets rid of you
faster than actually soving the root problems. That could take more
time and effort than we want to give away for free in a newsgroup.
Please get some real help somewhere else.|||Hi
This is where a CLR udt in SQL 2005 would probably be useful. The article
does show some methods that avoid using dynamic SQL.
John
"neeju" wrote:

> Yes John,
> It appears so.. We tried no. of things but of no use..
> I argued with the developers for not using dynamic sql and promised to
> give some alternative statement withoug dynamic sql but nothing works.
> Anyway thanks for directing to such a good article.
> Thanks,
> NJ
>sql

Query plans

I am a bit confused after reading a SQL Server Magazine
article about query plans
If i have a query
DECLARE @.odate AS DATETIME
SET @.odate = '19980506'
SELECT * FROM Orders
WHERE OrderDate >= @.odate
GO
Will above query planbe different than
SELECT * FROM Orders
WHERE OrderDate >= '19980506'
If yes why ?
Bot abobe queries will be compliled and execution plan
will be prepared and then executed
Thanks
Sanjaythere may or may not be a different plan,
there may be slightly difference statistical estimates of
the row count involved
in the first, the optimizer will estimate the number of
rows involved for any generic value of OrderDate.
in the second, it will try to get a better estimate based
on the Orderdate value or '19980506'
>--Original Message--
>I am a bit confused after reading a SQL Server Magazine
>article about query plans
>If i have a query
> DECLARE @.odate AS DATETIME
> SET @.odate = '19980506'
> SELECT * FROM Orders
> WHERE OrderDate >= @.odate
> GO
>Will above query planbe different than
> SELECT * FROM Orders
> WHERE OrderDate >= '19980506'
>If yes why ?
>Bot abobe queries will be compliled and execution plan
>will be prepared and then executed
>Thanks
>Sanjay
>
>.
>|||Sanjay
For a variable in a open-ended range (i.e. < or >) the optimizer will
estimate that 30% of the rows in the table will meet the cristeria. That is
far to many to use an index seek with a nonclustered index, but a clustered
index could be considered.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Sanjay" <sanjayg@.hotmail.com> wrote in message
news:311501c373f8$ebe59080$a501280a@.phx.gbl...
> What is that estimate in first case, is it fixed like 10%
> 20% etc, is it selective enough to do a index seek
> Sanjay
> >--Original Message--
> >there may or may not be a different plan,
> >there may be slightly difference statistical estimates of
> >the row count involved
> >in the first, the optimizer will estimate the number of
> >rows involved for any generic value of OrderDate.
> >in the second, it will try to get a better estimate based
> >on the Orderdate value or '19980506'
> >
> >
> >>--Original Message--
> >>I am a bit confused after reading a SQL Server Magazine
> >>article about query plans
> >>
> >>If i have a query
> >> DECLARE @.odate AS DATETIME
> >> SET @.odate = '19980506'
> >> SELECT * FROM Orders
> >> WHERE OrderDate >= @.odate
> >> GO
> >>
> >>Will above query planbe different than
> >> SELECT * FROM Orders
> >> WHERE OrderDate >= '19980506'
> >>
> >>If yes why ?
> >>Bot abobe queries will be compliled and execution plan
> >>will be prepared and then executed
> >>
> >>Thanks
> >>Sanjay
> >>
> >>
> >>
> >>.
> >>
> >.
> >

Query on XML column: fn:lower-case not working?

Hi everybody, I'm looking to issue a query to an XML column like this:
SELECT *
FROM dbo.ContactRecords
WHERE XmlContent.exist(
'declare namespace
my="http://schemas.microsoft.com/office/infopath/2003/myXSD/2004-03-09T14-09
-40";
// my:myFields[my:AddressInfo[fn:contains(f
n:lower-case(my:Company[1]),"lego"
)]]')
= 1
(i skipped some other conditions, but they are working)
--> SQL 2005 tells me: There is no function
'{http://www.w3.org/2004/07/xpath-functions}:lower-case()'
however, there is one...
How can I compare string values in SQL 2005 case-insensitive?
I appreciate your help, have a good day,
MichaelHello michael.hofer@.getronics.com,
Well, you've stumbled into the chasm caused by Microsoft trying to get a
product released and the W3C XQuery WG trying to get things perfect the firs
t
time. There's a number of functions that just aren't implemented in MS's
XQuery heap but are in the spec. We might get them in service packs, or we
might get them in the next version of SQL Server. String-Upper and String-L
ower
are a couple of the common ones that we'd like to have but don't.
Interestingly enough, I see that contains() has a optional collation pattern
.
I try to find a collation that ignored case to test with but didn't have
much immediate luck. If you might want to go down that path. Or not. No prom
ises.
I'd like to offer a suggestion other than .value(path,'varchar(n)') like
'%pattern%' (e.g., do the comparsion in T-SQL as shown below) since this
is fairly ugly for the kind of stuff you're doing. But it does work.
declare @.x xml
set @.x = '<book>Ender''s Game</book><book>Xenocide</book><book>Children of
the Mind</book><book>The Hive Queen</book><book>The Hegemon</book>'
select p.x.value('.','varchar(100)')
from @.x.nodes('/book') as p(x)
where p.x.value('.','varchar(100)') like '%the%'
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/