conversion sql from oracle to SQL Server

Hi Guys, I have this statement that I am converting from Oracle to SQL. Help pls:-) PP_PRICEPOINT_ID is a decimal. What is the appropriate usage..

Oracle
----
update pricepoint set pp_type = decode(substr(pp_pricepoint_id,1,1),7,0,2),
pp_qtybreakindex =substr(pp_pricepoint_id,3,1) where pp_type is null and pp_qtybreakIndex is null;

Here is its SQL
------
UPDATE pricepoint
SET pp_type =
CASE SUBSTRING(pp_pricepoint_id, 1, 1)
WHEN 7 THEN 0
ELSE 2
END,
pp_qtybreakindex = SUBSTRING(pp_pricepoint_id, 3, 1)
WHERE pp_type is null
AND pp_qtybreakIndex is null
------
I am getting the error
The data type decimal is invalid for the substring function. Allowed types are: char/varchar, nchar/nvarchar, and binary/varbinary.UPDATE pricepoint
SET pp_type = CASE SUBSTRING(CONVERT(VARCHAR(15),pp_pricepoint_id), 1, 1)
WHEN 7 THEN 0 ELSE 2
END
-- What's with this?
-- , pp_qtybreakindex = SUBSTRING(pp_pricepoint_id, 3, 1)
WHERE pp_type is null
AND pp_qtybreakIndex is null|||Try converting your pp_pricepoint variable to varchar before using the substring function

Change :

CASE SUBSTRING(pp_pricepoint_id, 1, 1)

For :

CASE SUBSTRING(Cast(pp_pricepoint_id As VarChar) , 1, 1)

Pls Note i didnt check ur substring use for the sintax.

Hope it can help|||Thanks Brett, this worked..
UPDATE pricepoint
SET pp_type = CASE SUBSTRING(CONVERT(VARCHAR(15),pp_pricepoint_id), 1, 1)
WHEN 7 THEN 0 ELSE 2
END
, pp_qtybreakindex = SUBSTRING(CONVERT(VARCHAR(15),pp_pricepoint_id), 3, 1)
WHERE pp_type is null
AND pp_qtybreakIndex is null

I am updating 2 values here..sqlsql

conversion from Access query to mssql query

Hello Guys

I am chaging the connectivity of MSaccess2K to sqlserver
the code is written in vb editor of access
i have established the connection string
but the following query is generating error of
invalid object name

strSQL = SELECT DISTINCT [Sites Union Controls].Description,
[Sites Union Controls].[Rous Reportable Site], [Sites Union Controls].[Type], Samples.SequenceNumber FROM Jobs INNER JOIN ([Sites Union Controls] INNER JOIN Samples ON [Sites Union Controls].SiteSerial = Samples.SiteSerial) ON Jobs.JobSerial = Samples.JobSerial
WHERE ((Jobs.JobSerial) = " & intJobSerial & ") ORDER BY Samples.SequenceNumber

I am getting an error invalid object name sites union controls

can't we do union of two tables as above in mssql
Please Help

Thanks In AdvanceI'm sure some one will correct me if Im wrong but I dont think this query will ever work in SQL Server, it does look like something that might run in access though.

Ive had problems like this before, Access loves adding in brackets () that just get in the way and confuse things in SQL Server. It also seams to list all the tables then join them in the FROM statement which is not s SQL thing either.

I think your also going to have a problem with the WHERE clause, namely the part " & intJobSerial & " reefers to a variable in Access. Even if you have created the variable in SQL Server the syntax is still wrong

Your query doesnt look like a union query, but a standard select query gone a bit wrong in the FROM part. Your query should look something like

DECLARE @.intJobSerial VARCHAR(50) -- this creates the variable as a 50 character text field, don't need this bit if youve done it already

SET @.intJobSerial = 'XXX' -- this sets the variable to XXX, don't need this bit if youve done it already

SELECT DISTINCT [Sites Union Controls].[Description],[Sites Union Controls].[Rous Reportable Site], [Sites Union Controls].Type, Samples.SequenceNumber

FROM [Sites Union Controls]
INNER JOIN Samples
ON [Sites Union Controls].SiteSerial = Samples.SiteSerial
INNER JOIN Jobs
ON Jobs.JobSerial = Samples.JobSerial

WHERE Jobs.JobSerial = @.intJobSerial

ORDER BY Samples.SequenceNumber

The invalid object sites union controls in actually the table used the query, Im guessing its because the FROM Part was all messed up at it was the first thing it came across after it went wrong.

Hope this helps|||Hello,

Trying by used the next name Sites_Union_Controls because I'm not sure that you can have a name with blanc characters

Good luck

Sylvie

Quote:

Originally Posted by aakash

Hello Guys

I am chaging the connectivity of MSaccess2K to sqlserver
the code is written in vb editor of access
i have established the connection string
but the following query is generating error of
invalid object name

strSQL = SELECT DISTINCT [Sites Union Controls].Description,
[Sites Union Controls].[Rous Reportable Site], [Sites Union Controls].[Type], Samples.SequenceNumber FROM Jobs INNER JOIN ([Sites Union Controls] INNER JOIN Samples ON [Sites Union Controls].SiteSerial = Samples.SiteSerial) ON Jobs.JobSerial = Samples.JobSerial
WHERE ((Jobs.JobSerial) = " & intJobSerial & ") ORDER BY Samples.SequenceNumber

I am getting an error invalid object name sites union controls

can't we do union of two tables as above in mssql
Please Help

Thanks In Advance

|||Hey, a couple of points...
SQL uses + for concatenation so you need to change your where statement to
((Jobs.JobSerial) = " + @.intJobSerial + ")

JohnK is right, if intJobSerial is a local variable then it needs to be declared and it must begin with an @..

There is nothing wrong with the FROM statement, it's a little unusual to delay the two ON clauses at the end but this gives a different result set because nulls in the 3rd table, in your query SAMPLES, can be handled differently this way. It's more effective if your mixing Left Outer and Inner Joins, however.

The biggest thing I see is that the permissions on the SQL table [Sites Union Controls] may be different than what you expect. You should probably be using a full reference to it as [database].[owner].[table] to at least eliminate the possibility that your error is a security setup problem.

Tom|||Sites Union Controls, was query in old ms access project which was connected to ms access database i am changing connectivity to ms sql 2000 ,none of the above solutions seem to be working ,
please help

Controlling the X-Axis

Hi guys...
I would like to do the following with my chart:
1. Have the label always appear vertically, and;
2. Limit the number of labels to 20 or less.
Any suggestions?
Thanks,
Forch#1: the chart control uses automatic label positioning based on the
available space. Vertical labels are only used if they don't fit
horizontally.
#2: Assuming you have the "numeric or time-scale" option turned on for the
x-axis, you could probably achieve this by setting the x-axis major interval
and/or min and max values.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Forch" <Forch@.discussions.microsoft.com> wrote in message
news:61AA4890-A0B4-4BB2-BF5C-7006FC755650@.microsoft.com...
> Hi guys...
> I would like to do the following with my chart:
> 1. Have the label always appear vertically, and;
> 2. Limit the number of labels to 20 or less.
> Any suggestions?
> Thanks,
> Forch

control transaction duration

Hi guys,
can I control the duration of a transaction ?
Using ADO I can set a command timeout, but using T-SQL or
modifying some SQLserver parameter, can I set a sort
of timeout on a transaction and get the same result (i.e. prevent
a transaction from running too long) ?
Many thanks for your kind help
Max
You can control max time you wait when you wait to be granted a lock. Check out SET LOCK_TIMEOUT.
However, you cannot set the max time you hold a transaction open or how long a query can run at the TSQL level
(ignoring the query governor), this has to be done in the client app (using ADO, ADO.NET of whatever API you
are using).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"madmax" <madmax@.discussions.microsoft.com> wrote in message
news:52836935-399C-4C37-9240-E23ABCC9F7B4@.microsoft.com...
> Hi guys,
> can I control the duration of a transaction ?
> Using ADO I can set a command timeout, but using T-SQL or
> modifying some SQLserver parameter, can I set a sort
> of timeout on a transaction and get the same result (i.e. prevent
> a transaction from running too long) ?
> Many thanks for your kind help
> Max

control transaction duration

Hi guys,
can I control the duration of a transaction ?
Using ADO I can set a command timeout, but using T-SQL or
modifying some SQLserver parameter, can I set a sort
of timeout on a transaction and get the same result (i.e. prevent
a transaction from running too long) ?
Many thanks for your kind help
MaxYou can control max time you wait when you wait to be granted a lock. Check out SET LOCK_TIMEOUT.
However, you cannot set the max time you hold a transaction open or how long a query can run at the TSQL level
(ignoring the query governor), this has to be done in the client app (using ADO, ADO.NET of whatever API you
are using).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"madmax" <madmax@.discussions.microsoft.com> wrote in message
news:52836935-399C-4C37-9240-E23ABCC9F7B4@.microsoft.com...
> Hi guys,
> can I control the duration of a transaction ?
> Using ADO I can set a command timeout, but using T-SQL or
> modifying some SQLserver parameter, can I set a sort
> of timeout on a transaction and get the same result (i.e. prevent
> a transaction from running too long) ?
> Many thanks for your kind help
> Max

control transaction duration

Hi guys,
can I control the duration of a transaction ?
Using ADO I can set a command timeout, but using T-SQL or
modifying some SQLserver parameter, can I set a sort
of timeout on a transaction and get the same result (i.e. prevent
a transaction from running too long) ?
Many thanks for your kind help
MaxYou can control max time you wait when you wait to be granted a lock. Check
out SET LOCK_TIMEOUT.
However, you cannot set the max time you hold a transaction open or how long
a query can run at the TSQL level
(ignoring the query governor), this has to be done in the client app (using
ADO, ADO.NET of whatever API you
are using).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"madmax" <madmax@.discussions.microsoft.com> wrote in message
news:52836935-399C-4C37-9240-E23ABCC9F7B4@.microsoft.com...
> Hi guys,
> can I control the duration of a transaction ?
> Using ADO I can set a command timeout, but using T-SQL or
> modifying some SQLserver parameter, can I set a sort
> of timeout on a transaction and get the same result (i.e. prevent
> a transaction from running too long) ?
> Many thanks for your kind help
> Max

continuous dates

Hi Guys,
I have a sql query that returns a count of a certain column say the
number of bugs per w on a w by w basis. there are some ws
which have no bugs and these ws are not returned. How can I write a
sql query that has all the ws whether there were bugs or not and
return 0 for the ws where there were no bugs.
for example:
at the moment:
w no. of bugs
22/1/06 10
15/1/06 5
1/1/06 2
(w 8/1/06 is missing)
what I expect :
w no. of bugs
22/1/06 10
15/1/06 5
8/1/06 3
1/1/06 2
infact I want all the ws from 1970 to present
Thanking you,
Regards,
NJYou need to provide more information to get an accurate response. It would
greatly help to see the table involved and some sample data. What is the
criteria for determining if there is a bug or not? Are you summing rows
with similar dates or is there a BUG column that has a value in it. Your
example does not make sense in that you state 8/1/06 is missing yet you show
it as having 3 bugs.
Andrew J. Kelly SQL MVP
"NJ" <npaulus@.hotmail.com> wrote in message
news:1137886146.073541.14890@.g43g2000cwa.googlegroups.com...
> Hi Guys,
> I have a sql query that returns a count of a certain column say the
> number of bugs per w on a w by w basis. there are some ws
> which have no bugs and these ws are not returned. How can I write a
> sql query that has all the ws whether there were bugs or not and
> return 0 for the ws where there were no bugs.
> for example:
> at the moment:
> w no. of bugs
> 22/1/06 10
> 15/1/06 5
> 1/1/06 2
> (w 8/1/06 is missing)
> what I expect :
> w no. of bugs
> 22/1/06 10
> 15/1/06 5
> 8/1/06 3
> 1/1/06 2
> infact I want all the ws from 1970 to present
> Thanking you,
> Regards,
> NJ
>|||Thanks for yor reply Andrew
I am summing rows with similar dates. and sorry about the 8/1/06 too it
should have 0 bugs. It doesnt show in the output of the first query
because there are no bugs during the w 8/1/06 to 14/1/06. In my
table everytime there is a bug it is entered into the database along
with the date it happened.
my query then finds the w (starting date of the w when the bug
occured i.e if the bug occoured on the 11 th jan 2006 then it becomes
8/1/06 as 8th is the starting day of the w) and counts all the rows
with similar dates. ie: I group by w. Because of this there are
ws when no bugs occured and hence dont show up in the query result.
however I want to be able to return that date with a 0 for the number
of bugs for that w.
I hope that explains a bit better Andrew.
Regards,
NJ|||Thanks for yor reply Andrew
I am summing rows with similar dates. and sorry about the 8/1/06 too it
should have 0 bugs. It doesnt show in the output of the first query
because there are no bugs during the w 8/1/06 to 14/1/06. In my
table everytime there is a bug it is entered into the database along
with the date it happened.
my query then finds the w (starting date of the w when the bug
occured i.e if the bug occoured on the 11 th jan 2006 then it becomes
8/1/06 as 8th is the starting day of the w) and counts all the rows
with similar dates. ie: I group by w. Because of this there are
ws when no bugs occured and hence dont show up in the query result.
however I want to be able to return that date with a 0 for the number
of bugs for that w.
I hope that explains a bit better Andrew.
Regards,
NJ|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.
Guessing at what you have, build a report range table:
CREATE TABLE WRanges
(w_nbr INTEGER NOT NULL PRIMARY KEY,
w_start DATETIME NOT NULL,
w_finish DATETIME NOT NULL,
CHECK (w_start, w_finish));
then do a query like this:
SELECT W.w_nbr, W.w_start, COUNT(B.bug_id)
FROM WRanges AS W
LEFT OUT JOIN
BugReport AS B
ON B.bug_date BETWEEN W.w_start AND W, w_finish
GROUP BY W.w_nbr, W.w_start;|||
--CELKO-- wrote:

>Please post DDL, so that people do not have to guess what the keys,
>constraints, Declarative Referential Integrity, data types, etc. in
>your schema are. Sample data is also a good idea, along with clear
>specifications. It is very hard to debug code when you do not let us
>see it.
>
True. Unfortunately, not letting us see this code of yours
would have been much kinder. Slop, slop, slop.

>Guessing at what you have, build a report range table:
>CREATE TABLE WRanges
>(w_nbr INTEGER NOT NULL PRIMARY KEY,
> w_start DATETIME NOT NULL,
> w_finish DATETIME NOT NULL,
> CHECK (w_start, w_finish));
>
CHECK what?

>then do a query like this:
>SELECT W.w_nbr, W.w_start, COUNT(B.bug_id)
> FROM WRanges AS W
> LEFT OUT JOIN
>
LEFT OUT JOIN?

> BugReport AS B
> ON B.bug_date BETWEEN W.w_start AND W, w_finish
>
W, w_finish? And for that matter, how is BETWEEN going
to work at all?. Do Sunday bugs get counted in two separate
ws, or do they not get counted at all? Who knows?

>GROUP BY W.w_nbr, W.w_start;
>
>
Steve Kass
Drew University|||On 21 Jan 2006 15:29:06 -0800, NJ wrote:

>Hi Guys,
>I have a sql query that returns a count of a certain column say the
>number of bugs per w on a w by w basis. there are some ws
>which have no bugs and these ws are not returned. How can I write a
>sql query that has all the ws whether there were bugs or not and
>return 0 for the ws where there were no bugs.
Hi NJ,
Use a calendar table (www.aspfaq.com/2519).
SELECT c.dt, COUNT(y.BugDate)
FROM Calendar AS c
LEFT OUTER JOIN YourTable AS y
ON y.BugDate >= c.dt
AND y.BugDate < DATEADD(day, 7, c.dt)
WHERE c.dayname = 'Sunday'
(untested - see www.aspfaq.com/5006 if you prefer a tested reply)
Hugo Kornelis, SQL Server MVP