Sql Update command: clause

Showing posts with label clause. Show all posts

Wednesday, March 7, 2012

Problem with WHERE clause...

Hi All,
I keep getting the following error with my WHERE clause.
Msg 102, Level 15, State 1, Line 47
Incorrect syntax near '-'.
We've found that the code works if the segment " -
(CONVERT(Int,dbo.ReorderItemsTotals.OnPurchaseOrder))" is removed. So
it's clear that the error is within this segment.
The object "dbo.ReorderItemsTotals" is a view and uses a UNION command
in the SELECT statement. It joins two fields and outputs it as one
field - "OnPurchaseOrder". The two original fields are smallints and
the resulting UNION'd field is a varchar 10 - however I don't know why
this is.
Can anyone help fix this error? My code is as follows.
Many thanks in advance,
Rype
WHERE
(dbo.ProductAssembly.SubProduct IS NULL) AND
((((0.5 * dbo.SoldLast30TotalWith0Total.Despatched +
dbo.ReorderItemsTotals.SumOfSalesBackOrder) + (0.5 / 4 *
dbo.SoldLast120TotalWith0Total.Despatched) *
dbo.ReorderItemsTotals.ReorderLevel * 120) - (CONVERT(Int,
dbo.ReorderItemsTotals.Stock)) -
(CONVERT(Int,dbo.ReorderItemsTotals.OnPurchaseOrder)) +
dbo.ReorderItemsTotals.SumOfSalesBackOrder) > 0) OR
(dbo.ProductAssembly.SubProduct IS NULL) AND
(dbo.ProductDetails.Product1 < dbo.ReorderItemsTotalsSets.StockCode)
AND
(dbo.ProductDetails.Product1 + dbo.productdetails.product2 <
dbo.ReorderItemsTotalsSets.ReorderQuantity)This is a multi-part message in MIME format.
--=_NextPart_000_0007_01C804CC.9AEE5810
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Everything seems to check out using SSMS to balance the parentheses. I =do question if the logic is as you intend, given the groupings. Having =the OR out by itself like that is often not what is desired. Also, do =yourself a favor and start using aliases!! :-)
WHERE dbo.ProductAssembly.SubProduct IS NULL
AND ((((0.5 * dbo.SoldLast30TotalWith0Total.Despatched + =dbo.ReorderItemsTotals.SumOfSalesBackOrder) + (0.5 / 4 * dbo.SoldLast120TotalWith0Total.Despatched) * =dbo.ReorderItemsTotals.ReorderLevel * 120) - (CONVERT(Int, dbo.ReorderItemsTotals.Stock)) - =(CONVERT(Int,dbo.ReorderItemsTotals.OnPurchaseOrder)) +
dbo.ReorderItemsTotals.SumOfSalesBackOrder) > 0)
OR
(dbo.ProductAssembly.SubProduct IS NULL) AND (dbo.ProductDetails.Product1 < =dbo.ReorderItemsTotalsSets.StockCode)
AND (dbo.ProductDetails.Product1 + dbo.productdetails.product2 < =dbo.ReorderItemsTotalsSets.ReorderQuantity)
<ryanwpenfold@.gmail.com> wrote in message =news:1191330035.815038.230250@.n39g2000hsh.googlegroups.com...
> Hi All,
> > I keep getting the following error with my WHERE clause.
> > Msg 102, Level 15, State 1, Line 47
> Incorrect syntax near '-'.
> > We've found that the code works if the segment " -
> (CONVERT(Int,dbo.ReorderItemsTotals.OnPurchaseOrder))" is removed. So
> it's clear that the error is within this segment.
> > The object "dbo.ReorderItemsTotals" is a view and uses a UNION command
> in the SELECT statement. It joins two fields and outputs it as one
> field - "OnPurchaseOrder". The two original fields are smallints and
> the resulting UNION'd field is a varchar 10 - however I don't know why
> this is.
> > Can anyone help fix this error? My code is as follows.
> > Many thanks in advance,
> > Rype
> > > > WHERE
> (dbo.ProductAssembly.SubProduct IS NULL) AND
> ((((0.5 * dbo.SoldLast30TotalWith0Total.Despatched +
> dbo.ReorderItemsTotals.SumOfSalesBackOrder) + (0.5 / 4 *
> dbo.SoldLast120TotalWith0Total.Despatched) *
> dbo.ReorderItemsTotals.ReorderLevel * 120) - (CONVERT(Int,
> dbo.ReorderItemsTotals.Stock)) -
> (CONVERT(Int,dbo.ReorderItemsTotals.OnPurchaseOrder)) +
> dbo.ReorderItemsTotals.SumOfSalesBackOrder) > 0) OR
> (dbo.ProductAssembly.SubProduct IS NULL) AND
> (dbo.ProductDetails.Product1 < dbo.ReorderItemsTotalsSets.StockCode)
> AND
> (dbo.ProductDetails.Product1 + dbo.productdetails.product2 <
> dbo.ReorderItemsTotalsSets.ReorderQuantity)
>
--=_NextPart_000_0007_01C804CC.9AEE5810
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

Everything seems to check out using SSMS to balance the =parentheses. I do question if the logic is as you intend, given the groupings. =Having the OR out by itself like that is often not what is desired. Also, =do yourself a favor and start using aliases!! :-)
WHERE dbo.ProductAssembly.SubProduct IS NULL AND ((((0.5 * dbo.SoldLast30TotalWith0Total.Despatched + dbo.ReorderItemsTotals.SumOfSalesBackOrder) + (0.5 / 4 * dbo.SoldLast120TotalWith0Total.Despatched) * =dbo.ReorderItemsTotals.ReorderLevel * 120) - =(CONVERT(Int, dbo.ReorderItemsTotals.Stock)) - (CONVERT(Int,dbo.ReorderItemsTotals.OnPurchaseOrder)) + dbo.ReorderItemsTotals.SumOfSalesBackOrder) > =0) OR (dbo.ProductAssembly.SubProduct IS =NULL) AND (dbo.ProductDetails.Product1 < dbo.ReorderItemsTotalsSets.StockCode) AND =(dbo.ProductDetails.Product1 + dbo.productdetails.product2 < dbo.ReorderItemsTotalsSets.ReorderQuantity)
wrote in message news:=1191330035.815038.230250@.n39g2000hsh.googlegroups.com...> Hi All,> > I keep getting the following error with my =WHERE clause.> > Msg 102, Level 15, State 1, Line 47> =Incorrect syntax near '-'.> > We've found that the code works if the =segment " -> (CONVERT(Int,dbo.ReorderItemsTotals.OnPurchaseOrder))" is removed. So> it's clear that the error is within this segment.> > The object "dbo.ReorderItemsTotals" is a view =and uses a UNION command> in the SELECT statement. It joins two =fields and outputs it as one> field - "OnPurchaseOrder". The two =original fields are smallints and> the resulting UNION'd field is a =varchar 10 - however I don't know why> this is.> > Can anyone =help fix this error? My code is as follows.> > Many thanks in =advance,> > Rype> > > > =WHERE> (dbo.ProductAssembly.SubProduct IS NULL) AND> ((((0.5 * dbo.SoldLast30TotalWith0Total.Despatched +> dbo.ReorderItemsTotals.SumOfSalesBackOrder) + (0.5 / 4 *> dbo.SoldLast120TotalWith0Total.Despatched) *> dbo.ReorderItemsTotals.ReorderLevel * 120) - (CONVERT(Int,> dbo.ReorderItemsTotals.Stock)) -> (CONVERT(Int,dbo.ReorderItemsTotals.OnPurchaseOrder)) +> dbo.ReorderItemsTotals.SumOfSalesBackOrder) > 0) OR> (dbo.ProductAssembly.SubProduct IS NULL) AND> (dbo.ProductDetails.Product1 < =dbo.ReorderItemsTotalsSets.StockCode)> AND> (dbo.ProductDetails.Product1 + =dbo.productdetails.product2 <> =dbo.ReorderItemsTotalsSets.ReorderQuantity)>

--=_NextPart_000_0007_01C804CC.9AEE5810--

Problem with WHERE clause when column length > 255

Hello, I am using the JDCB-ODBC driver for my app, but one of the columns in my WHERE clause is 255 characters long. Because of this no rows are returned even though the statement should return 1 or 2 rows. I've tried other JDBC-ODBC drivers too and they have the same problem. Additionally, I've tried using RTrim(), Substring(), etc and it still will not work...any ideas? Is it a driver bug? Anyone know of a driver that will work?

Sample code...

ResultSet rs = dbRetrieve.getStatement().executeQuery( sql ) ;
if (rs.next()) {
// Never gets here
}

Thanx!Have you run the query (verbatim) in Microsoft Query Analyzer and seen the rows returned?

-PatP|||Originally posted by Pat Phelan
Have you run the query (verbatim) in Microsoft Query Analyzer and seen the rows returned?

-PatP

Yes, I have and it returns results. Additionally, I've removed the column from the where clause in my java file and it returns data, but of course its uselys because I need that column in the where clause...

Thanks...|||Hmm... Microsoft's JDBC driver says it will support upt to 4000 character strings. Have you used profiler to see exactly what's being sent to your SQL server?|||Since the SQL code works ok from Query Analyzer, we can assume that SQL Server to the MDAC on your client works fine. The problem must be in the JDBC driver or beyond it.

Which JDBC driver are you using? Does it have any length limitations that you are aware of (or does it keep them as surprises for the unwary)? As Peter pointed out, the Microsoft JDBC driver handles big strings (I've run 2 Kb columns out through it).

Just as another thought, are you certain that your Java app is behaving and actually submitting what you expect it to.

-PatP

Problem with WHERE clause

I'm migrating some reports to reporting services, some of the reports use a
bunch of conditional logic to produce the desired results, I'm trying to
eliminate as much of that as possible... Here's my query... At the end of th
e
post is the original sp... My problem is when I use the "@.fdate" parameter
the query is not producing any results, if I use the sp with the same params
I get results so I know something is wrong, I'm just not seeing it, wonderin
g
if someone can help...
Thx... Dan
DECLARE --Report Parameters
@.loc char(06),
@.fdate datetime,
@.dtype char(01),
@.brand char(02)
--SET @.fdate = '07/09/2005'
SET @.dtype = 'E'
SET @.brand = 'MC'
SELECT
ordet.ORDD_ORD_NO AS ordno,
ordet.ORDD_UNIT_NO AS unitno,
ordet.PROD_NO AS prodno,
ord.ord_cus_grp AS cusgrp,
ord.ord_cus_cd AS cuscd,
ordet.ORDD_FLOOR_START_DT AS floorst,
ordet.ORDD_SICKBAY_DT AS sickbay,
ordet.ORDD_DISPATCH_DT AS dispatch,
ordet.ORDD_FLOORED_DT AS floored,
ordet.ORDD_FLOORED_CMNT AS fcomment,
ordet.ORDD_SCHEDULED_LOCATION AS location,
ordet.ORDD_EST_OFFLINE_DT AS eoffline,
ISNULL(ordet.ORDD_BASE_PRICE, 0.00) AS base,
ISNULL(ordet.ORDD_CHASSIS_PRICE, 0.00) AS chassis,
ISNULL(ordet.ORDD_OPTION_PRICE, 0.00) AS options,
cus.cus_name AS cusname,
mod.model_desc AS model,
mm.mm_corp_cd AS brand,
ord.ord_cus_ord AS salescomment
FROM dbo.HR_ORDDET ordet (nolock)
INNER JOIN dbo.ord ord (nolock) on ordet.ORDD_ORD_CO = ord.ord_company and
ordet.ORDD_ORD_NO = ord.ord_no
INNER JOIN dbo.model_master mm (nolock) on ordet.ORDD_UNIT_NO = mm.sku_cd
LEFT OUTER JOIN dbo.customer cus (nolock) on cus.cus_cd = ord.ord_cus_cd
AND cus.cus_grp_cd = ord.ord_cus_grp
LEFT OUTER JOIN dbo.model mod (nolock) on substring(ordet.ORDD_UNIT_NO,5,4)
= mod.model_code
WHERE
((@.dtype IS NULL) OR (@.dtype IS NOT NULL AND @.dtype = 'E' AND
ordet.ORDD_EST_OFFLINE_DT IS NOT NULL) OR (@.dtype IS NOT NULL AND @.dtype =
'F' AND ordet.ORDD_FLOOR_START_DT IS NOT NULL)) AND
((@.loc IS NULL) OR (@.loc IS NOT NULL AND ordet.ORDD_SCHEDULED_LOCATION =
@.loc)) AND
( ordet.ORDD_INVOICED_DT IS NULL ) AND
((@.fdate IS NULL) OR (@.fdate IS NOT NULL AND @.dtype IS NOT NULL AND @.dtype
= 'E' AND ordet.ORDD_EST_OFFLINE_DT >= @.fdate) OR (@.fdate IS NOT NULL AND
@.dtype IS NOT NULL AND @.dtype = 'F' AND ordet.ORDD_FLOOR_START_DT >= @.fdate)
)
AND
((@.brand IS NULL) OR (@.brand IS NOT NULL AND @.brand IN ('HR','BC','SC') AND
mm.mm_corp_cd = @.brand) OR (@.brand IS NOT NULL AND @.brand = 'MC' AND
SUBSTRING(ordet.ORDD_UNIT_NO,5,4) > '1500' AND mm.mm_corp_cd = @.brand) OR
(@.brand IS NOT NULL AND @.brand = 'MK' AND SUBSTRING(ordet.ORDD_UNIT_NO,5,4)
<
'1500' AND mm.mm_corp_cd = 'MC'))
ORDER BY
location,
cusname,
model
/*
Begin original procedure...
*/
CREATE procedure dbo.msp_web_floored_units (@.loc char(06) = null,
@.fdate datetime = null,
@.dtype char(01) = null,
@.brand char(02) = null)
as
create table #floor
(ordno int,
unitno char(20),
prodno int,
cusgrp varchar(06),
cuscd varchar(10),
floorst datetime,
sickbay datetime,
dispatch datetime,
floored datetime,
fcomment char(25),
location char(06),
eoffline datetime,
base money,
chassis money,
options money,
cusname varchar(30),
model varchar(40),
brand char(02),
salescomment varchar(20))
if @.loc is not null
begin
insert #floor
SELECT dbo.HR_ORDDET.ORDD_ORD_NO,
dbo.HR_ORDDET.ORDD_UNIT_NO,
dbo.HR_ORDDET.PROD_NO,
dbo.ord.ord_cus_grp,
dbo.ord.ord_cus_cd,
dbo.HR_ORDDET.ORDD_FLOOR_START_DT,
dbo.HR_ORDDET.ORDD_SICKBAY_DT,
dbo.HR_ORDDET.ORDD_DISPATCH_DT,
dbo.HR_ORDDET.ORDD_FLOORED_DT ,
dbo.HR_ORDDET.ORDD_FLOORED_CMNT,
dbo.HR_ORDDET.ORDD_SCHEDULED_LOCATION,
dbo.HR_ORDDET.ORDD_EST_OFFLINE_DT,
IsNull(dbo.HR_ORDDET.ORDD_BASE_PRICE, 0.00) AS ORDD_BASE_PRICE,
IsNull(dbo.HR_ORDDET.ORDD_CHASSIS_PRICE, 0.00) AS
ORDD_CHASSIS_PRICE,
IsNull(dbo.HR_ORDDET.ORDD_OPTION_PRICE, 0.00) AS ORDD_OPTION_PRICE,
null,
null,
dbo.model_master.mm_corp_cd,
dbo.ord.ord_cus_ord
FROM dbo.HR_ORDDET (nolock)
INNER JOIN dbo.ord (nolock) on dbo.HR_ORDDET.ORDD_ORD_CO =
dbo.ord.ord_company and dbo.HR_ORDDET.ORDD_ORD_NO = dbo.ord.ord_no
INNER JOIN dbo.model_master (nolock) on dbo.HR_ORDDET.ORDD_UNIT_NO =
dbo.model_master.sku_cd
WHERE ( (@.dtype = 'E' and dbo.HR_ORDDET.ORDD_EST_OFFLINE_DT is not
null) or
(@.dtype = 'F' and dbo.HR_ORDDET.ORDD_FLOOR_START_DT is not null))
and dbo.HR_ORDDET.ORDD_INVOICED_DT is null
and dbo.HR_ORDDET.ORDD_SCHEDULED_LOCATION = @.loc
end
else
begin
insert #floor
SELECT dbo.HR_ORDDET.ORDD_ORD_NO,
dbo.HR_ORDDET.ORDD_UNIT_NO,
dbo.HR_ORDDET.PROD_NO,
dbo.ord.ord_cus_grp,
dbo.ord.ord_cus_cd,
dbo.HR_ORDDET.ORDD_FLOOR_START_DT,
dbo.HR_ORDDET.ORDD_SICKBAY_DT,
dbo.HR_ORDDET.ORDD_DISPATCH_DT,
dbo.HR_ORDDET.ORDD_FLOORED_DT ,
dbo.HR_ORDDET.ORDD_FLOORED_CMNT,
dbo.HR_ORDDET.ORDD_SCHEDULED_LOCATION,
dbo.HR_ORDDET.ORDD_EST_OFFLINE_DT,
IsNull(dbo.HR_ORDDET.ORDD_BASE_PRICE, 0.00) AS ORDD_BASE_PRICE,
IsNull(dbo.HR_ORDDET.ORDD_CHASSIS_PRICE, 0.00) AS
ORDD_CHASSIS_PRICE,
IsNull(dbo.HR_ORDDET.ORDD_OPTION_PRICE, 0.00) AS ORDD_OPTION_PRICE,
null,
null,
dbo.model_master.mm_corp_cd,
dbo.ord.ord_cus_ord
FROM dbo.HR_ORDDET (nolock)
INNER JOIN dbo.ord (nolock) ON dbo.HR_ORDDET.ORDD_ORD_CO =
dbo.ord.ord_company and dbo.HR_ORDDET.ORDD_ORD_NO = dbo.ord.ord_no
INNER JOIN dbo.model_master (nolock) ON dbo.HR_ORDDET.ORDD_UNIT_NO =
dbo.model_master.sku_cd
WHERE ( (@.dtype = 'E' and dbo.HR_ORDDET.ORDD_EST_OFFLINE_DT is not
null) or
(@.dtype = 'F' and dbo.HR_ORDDET.ORDD_FLOOR_START_DT is not null) )
and dbo.HR_ORDDET.ORDD_INVOICED_DT is null
end
if @.fdate is not null
begin
if @.dtype = 'E'
begin
delete #floor
where eoffline >= @.fdate
end
else if @.dtype = 'F'
begin
delete #floor
where floorst >= @.fdate
end
end
update #floor
set cusname = b.cus_name
from #floor a (nolock)
inner join customer b (nolock)
on a.cuscd = b.cus_cd
and a.cusgrp = b.cus_grp_cd
update #floor
set model = b.model_desc
from #floor a (nolock)
inner join model b (nolock)
on substring(a.unitno,5,4) = b.model_code
if @.brand is null
begin
select *
from #floor
order by location, cusname, model
end
else if @.brand in ('HR','BC','SC')
begin
select *
from #floor
where brand = @.brand
order by location, cusname, model
end
else if @.brand = 'MC'
begin
select *
from #floor
where brand = 'MC'
and substring(unitno,5,4) > '1500'
order by location, cusname, model
end
else if @.brand = 'MK'
begin
select *
from #floor
where brand = 'MC'
and substring(unitno,5,4) < '1500'
order by location, cusname, model
end
GOMaybe I'm missing something, but it looks like the query returns rows
where ORDD_EST_OFFLINE_DT >= @.fdate, and the procedure
returns rows where NOT (ORDD_EST_OFFLINE_DT >= @.fdate),
because it deletes the ones where ORDD_EST_OFFLINE_DT >= @.fdate.
If I have this right, then there's no reason to expect the query and
the procedure to return the same thing. There are probably no
rows where ORDD_EST_OFFLINE_DT >= @.fdate.
See comments inline.
SK
Alien2_51 wrote:

>I'm migrating some reports to reporting services, some of the reports use a
>bunch of conditional logic to produce the desired results, I'm trying to
>eliminate as much of that as possible... Here's my query... At the end of t
he
>post is the original sp... My problem is when I use the "@.fdate" parameter
>the query is not producing any results, if I use the sp with the same param
s
>I get results so I know something is wrong, I'm just not seeing it, wonderi
ng
>if someone can help...
>Thx... Dan
>
>DECLARE --Report Parameters
> @.loc char(06),
> @.fdate datetime,
> @.dtype char(01),
> @.brand char(02)
>--SET @.fdate = '07/09/2005'
>SET @.dtype = 'E'
> SELECT
>
[snip]

> WHERE
> (@.dtype = 'E' AND ordet.ORDD_EST_OFFLINE_DT IS NOT NULL) AND
> (@.dtype = 'E' AND ordet.ORDD_EST_OFFLINE_DT >= @.fdate)
>
[snip AND other stuff]
So the query returns rows where ORDD_EST_OFFLINE_DT >= @.fdate

>CREATE procedure dbo.msp_web_floored_units (@.loc char(06) = null,
> @.fdate datetime = null,
> @.dtype char(01) = null,
> @.brand char(02) = null)
>as
>
>create table #floor
>...
>if @.loc is not null
> begin
> insert #floor
> SELECT ...
> WHERE ( (@.dtype = 'E' and dbo.HR_ORDDET.ORDD_EST_OFFLINE_DT is not nul
l)
>
>if @.fdate is not null
> begin
> if @.dtype = 'E'
> begin
> delete #floor
> where eoffline >= @.fdate
> end
> end
>
>
>
The procedure deletes rows where ORDD_EST_OFFLINE_DT >= @.fdate

>
>|||Simple as that... Works perfect...
Thanks Steve!!
"Steve Kass" wrote:

> Maybe I'm missing something, but it looks like the query returns rows
> where ORDD_EST_OFFLINE_DT >= @.fdate, and the procedure
> returns rows where NOT (ORDD_EST_OFFLINE_DT >= @.fdate),
> because it deletes the ones where ORDD_EST_OFFLINE_DT >= @.fdate.
> If I have this right, then there's no reason to expect the query and
> the procedure to return the same thing. There are probably no
> rows where ORDD_EST_OFFLINE_DT >= @.fdate.
> See comments inline.
> SK
> Alien2_51 wrote:
>
> [snip]
>
> [snip AND other stuff]
> So the query returns rows where ORDD_EST_OFFLINE_DT >= @.fdate
>
> The procedure deletes rows where ORDD_EST_OFFLINE_DT >= @.fdate
>
>

Saturday, February 25, 2012

Problem with using UNION in the where clause.

Hi, I'm using the JDBC:ODBC bridge but am having a problem using it with MS
Access.
I want to run the following thru the bridge;
SELECT * FROM Horse WHERE horseName Like 'MINORS UNION'
I can run this query in Access with no probs. I get an SQL.Exception when
running thru the bridge. I am running thru a try catch block.
I am assuming the problem lies with the keyword UNION being used in the
value.
any help appreciated,
Michael.
I haven't done very much with the JDBC/ODBC bridge. I would think that as
long as the expression 'MINOR UNION' is a valid string delimited by quotes,
the reserved word theory should not be a factor. Can you post the exact
SQLException that you receive?
Carb Simien, MCSE MCDBA MCAD
Microsoft Developer Support - Web Data
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.
| From: "Finbar Saunders" <finbar@.netspace.net.au>
| Newsgroups: microsoft.public.sqlserver.jdbcdriver
| Subject: Problem with using UNION in the where clause.
| Date: Fri, 16 Apr 2004 17:46:42 +0930
| Organization: Netspace Internet
| Lines: 16
| Message-ID: <c5o4p6$2i17$1@.otis.netspace.net.au>
| NNTP-Posting-Host: dsl-203-113-238-176.sa.netspace.net.au
| X-Trace: otis.netspace.net.au 1082103398 84007 203.113.238.176 (16 Apr
2004 08:16:38 GMT)
| X-Complaints-To: usenet@.otis.netspace.net.au
| NNTP-Posting-Date: Fri, 16 Apr 2004 08:16:38 +0000 (UTC)
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
| Path:
cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!newsfeed 00.sul.t-online.de!t-onlin
e.de!newsfeed.wirehub.nl!surfnet.nl!newsfeed.kabel foon.nl!195.129.110.21.MIS
MATCH!bnewsfeed00.bru.ops.eu.uu.net!bnewsinpeer01. bru.ops.eu.uu.net!emea.uu.
net!ash.uu.net!duster.adelaide.on.net!news.melbour ne.pipenetworks.com!news.l
abyrinth.net.au!news.netspace.net.au!not-for-mail
| Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.jdbcdriver:5892
| X-Tomcat-NG: microsoft.public.sqlserver.jdbcdriver
|
| Hi, I'm using the JDBC:ODBC bridge but am having a problem using it with
MS
| Access.
|
| I want to run the following thru the bridge;
| SELECT * FROM Horse WHERE horseName Like 'MINORS UNION'
|
| I can run this query in Access with no probs. I get an SQL.Exception when
| running thru the bridge. I am running thru a try catch block.
|
| I am assuming the problem lies with the keyword UNION being used in the
| value.
|
| any help appreciated,
| Michael.
|
|
|

problem with using String Variable in IN Function

Hellow !!
My problem is that I want to use a string variable in the 'where' clause Like this.I am using VB.6

dim ID as string
ID = "('1','2')"

Select coursecode from student
where studentid in ID

Now tell me how to solve this problem. or is there any other way to solve this problem.
Plz help me out.One solution would be:

dim ID as string
ID = ".1.2."

Select coursecode from student
where ID LIKE '%.' || studentid || '.%';|||Hellow !!

First i will say thanks. Tell me some other solution, this one is not working. Let me tell you i am using SQLSever 2000.
Let me explaing my problem with some more details.

recordSet.open" select coursecode from course where student id='1'" , connectionobject

Now save the recordset data in an array or in a string

let suppose
array(0)= "Eng"
array(1)="Phy"
(OR)

dim str as string
str="('eng','phy')"

now how i will use this array or this string in another select statement like

recordset1.open "select studentid from student where coursecode = ???",connectionObject,adopenstatic

Plz dont tell to use sunquery
I will be waiting for the reply Thanks|||You can use IN:

select studentid from student where coursecode in ('eng','phy')|||Hellow :
Thanks for the reply. Actually the result of the first query is not fixed.like

recordSet.open" select coursecode from course where student id='1'" , connectionobject

'Consider the result of this query will be different ever time when ever it runs.
' Like it may give 4 records,10 records may be 15 in the recordset
'now i have two ways to store the result of the query , i can store it either in a "string variable" or in an "Array".
' if string variable is used like

dim str as string
str = "('phy','chem','math')" or str = ('eng','oop','db','oose')

(forget this how i saved the data from the query in this string like this )
the only problem with using the string in the query is the outer quots " " . like

recordset1.open "select studentid from student where coursecode = "' & str & '" ",connectionObject,adopenstatic

Now if i use an array for storing the result of the first query. Tell me the way how that array will be use in the second query like

recordset1.open "select studentid from student where coursecode = array ",connectionObject,adopenstatic

Problem with using 'OutPut Clause' in SQL SEERVER 2005

Hi everyBody,

I Tried to use "OutPut" clause in insert statment as fallows:

insert into test

OUTPUT Inserted.Name,Inserted.LastName

values ('John','kransky')

The Error:

Line 2: Incorrect syntax near 'OUTPUT'.

but I face with this error,I checked MSDN and the other sources, it seems everything is ok. But when I run it ,I face with that error message.I would be appriciate if someone help me on it.

OUTPUT values MUST be 'captured' in some fashion. Typically, the OUTPUT values are captured in a @.Temp table.

For Example, this might work for you:

Code Snippet

DECLARE @.NewRows table

( [Name] varchar(20),

LastName varchar(20)

);

INSERT INTO Test

OUTPUT Inserted.Name, Inserted.LastName

INTO @.NewRows

VALUES ( 'John', 'kransky' );

Then you can use the data in the @.NewRows table however you wish.

Problem with using ''OutPut Clause'' in SQL SEERVER 2005