HELP with SP_configure

I am running the following command:

exec sp_configure 'allow updates', '0'

I get:

Server: Msg 15247, Level 16, State 1, Procedure sp_configure, Line 169
User does not have permission to perform this action.

What's causeing this?

Thanks!

Kenyou need to be associated (at least) with serveradmin role to be able to perform this operation. check with your sql server dba on that.

Help With Some SQL

I have tried getting this right from within an Access Grid but without
success. My table looks like this:

12/24/2004 10:50:00 AM | CC | Level 2 | Heigle, Terry | 230
12/24/2004 3:00:00 PM | CC| Level 3 | Jost, Karen | 310
12/24/2004 7:00:00 PM | CC | Level 3 | Jost, Karen | 240
12/24/2004 10:00:00 PM | CC | Level 4 | Jost, Karen | 180
12/24/2004 11:00:00 PM | CC | Level 2 | Smith, Bob | 60

My ultimate output would be a crosstab sort of statment that would have the
four uniqe levels as columns and the dates as rows with a summation of the
amount of time at each level (level is the last value furthest from the
left)

My rudimentary SQL as come up with this...

TRANSFORM Sum(tblHistory.previousminutes) AS SumOfpreviousminutes
SELECT tblHistory.dtmTime
FROM tblHistory
GROUP BY tblHistory.dtmTime
PIVOT tblHistory.status;

The only thing wrong with this is it lists all entries for 12/24/2005
instead of one row for 12/24...

Can anyonoe suggest a corrrection

Regards

John KostenbaderJohn Kostenbader (john@.kostenbader.com) writes:
> I have tried getting this right from within an Access Grid but without
> success. My table looks like this:
> 12/24/2004 10:50:00 AM | CC | Level 2 | Heigle, Terry | 230
> 12/24/2004 3:00:00 PM | CC| Level 3 | Jost, Karen | 310
> 12/24/2004 7:00:00 PM | CC | Level 3 | Jost, Karen | 240
> 12/24/2004 10:00:00 PM | CC | Level 4 | Jost, Karen | 180
> 12/24/2004 11:00:00 PM | CC | Level 2 | Smith, Bob | 60
>
> My ultimate output would be a crosstab sort of statment that would have
> the four uniqe levels as columns and the dates as rows with a summation
> of the amount of time at each level (level is the last value furthest
> from the left)
> My rudimentary SQL as come up with this...
> TRANSFORM Sum(tblHistory.previousminutes) AS SumOfpreviousminutes
> SELECT tblHistory.dtmTime
> FROM tblHistory
> GROUP BY tblHistory.dtmTime
> PIVOT tblHistory.status;
> The only thing wrong with this is it lists all entries for 12/24/2005
> instead of one row for 12/24...

Since the you think that the result is almost right, I assume that
you are looking for answer in Access. In this case, you should post
to an Access newsgroup, as the syntax you are using is peculiar to
Access.

If you want to run your question in SQL Server, this is the right
place, but alas I have problem to understnad what is what. The
recommendation for this sort of questions is to include:

o CREATE TABLE statement for your table.
o INSERT statement with sample data.
o The desired result given the sample.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications.

You show time as Chronons and not durations, so your schema is probably
wrong . You show times in non -ISO-8601 fomats in violation of
Standard SQL. Also, read ISO-11179 so you will stop using those silly,
redundant "tbl-" prefixes. It makes you look like an OO programmer!

Try again and we can help when you give us enough to work with.|||I've posted in plenty of groups in the past without this rudeness...I'm very
sorry to the gentleman who thought I posted in the wrong group and I
apologize to the gentleman who believes me an armature and still uses "tbl"
(it happens to be a table scheme I'm comfortable with and use regularly

I've got news for you...I am an armature looking for some assistance. I
believe that is the original intent of such newsgroups.

Thank you for the intention of your posts

"John Kostenbader" <john@.kostenbader.com> wrote in message
news:89CdnS3X8axzVOLfRVn-2g@.rcn.net...
>I have tried getting this right from within an Access Grid but without
>success. My table looks like this:
> 12/24/2004 10:50:00 AM | CC | Level 2 | Heigle, Terry | 230
> 12/24/2004 3:00:00 PM | CC| Level 3 | Jost, Karen | 310
> 12/24/2004 7:00:00 PM | CC | Level 3 | Jost, Karen | 240
> 12/24/2004 10:00:00 PM | CC | Level 4 | Jost, Karen | 180
> 12/24/2004 11:00:00 PM | CC | Level 2 | Smith, Bob | 60
>
> My ultimate output would be a crosstab sort of statment that would have
> the four uniqe levels as columns and the dates as rows with a summation of
> the amount of time at each level (level is the last value furthest from
> the left)
> My rudimentary SQL as come up with this...
> TRANSFORM Sum(tblHistory.previousminutes) AS SumOfpreviousminutes
> SELECT tblHistory.dtmTime
> FROM tblHistory
> GROUP BY tblHistory.dtmTime
> PIVOT tblHistory.status;
> The only thing wrong with this is it lists all entries for 12/24/2005
> instead of one row for 12/24...
> Can anyonoe suggest a corrrection
> Regards
> John Kostenbader|||"John Kostenbader" <john@.kostenbader.com> wrote in message
news:UqadnVFCWKiDih3fRVn-ow@.rcn.net...
> I've posted in plenty of groups in the past without this rudeness...I'm
> very sorry to the gentleman who thought I posted in the wrong group and I
> apologize to the gentleman who believes me an armature and still uses
> "tbl" (it happens to be a table scheme I'm comfortable with and use
> regularly
> I've got news for you...I am an armature looking for some assistance. I
> believe that is the original intent of such newsgroups.
> Thank you for the intention of your posts

Don't worry abou the ISO-Nazis. Although they would like to prosecute
offenders, ISO standards are not legally binding and you may name your
tables as you please. If they had their way, they would be laying down
standards for the naming of children and have secret police to re-educate
those who used non-standard names.|||GROUP BY tblHistory.dtmTime

That'd be a datetime.
And you're sorta totalling by second.
You need to do something like format(tblHistory.dtmTime, "yyyymmdd")
to get all the stuff for one day totalled together.

John Kostenbader wrote:
> I have tried getting this right from within an Access Grid but
without
> success. My table looks like this:
> 12/24/2004 10:50:00 AM | CC | Level 2 | Heigle, Terry | 230
> 12/24/2004 3:00:00 PM | CC| Level 3 | Jost, Karen | 310
> 12/24/2004 7:00:00 PM | CC | Level 3 | Jost, Karen | 240
> 12/24/2004 10:00:00 PM | CC | Level 4 | Jost, Karen | 180
> 12/24/2004 11:00:00 PM | CC | Level 2 | Smith, Bob | 60
>
> My ultimate output would be a crosstab sort of statment that would
have the
> four uniqe levels as columns and the dates as rows with a summation
of the
> amount of time at each level (level is the last value furthest from
the
> left)
> My rudimentary SQL as come up with this...
> TRANSFORM Sum(tblHistory.previousminutes) AS SumOfpreviousminutes
> SELECT tblHistory.dtmTime
> FROM tblHistory
> GROUP BY tblHistory.dtmTime
> PIVOT tblHistory.status;
> The only thing wrong with this is it lists all entries for 12/24/2005

> instead of one row for 12/24...
> Can anyonoe suggest a corrrection
> Regards
> John Kostenbader|||John Kostenbader wrote:
> I've posted in plenty of groups in the past without this
rudeness...I'm very
> sorry to the gentleman who thought I posted in the wrong group and I
> apologize to the gentleman who believes me an armature and still uses
"tbl"
> (it happens to be a table scheme I'm comfortable with and use
regularly
> I've got news for you...I am an armature looking for some assistance.
I
> believe that is the original intent of such newsgroups.

He was assisting you by pointing you to some documentation, and
suggesting places where you should rethink your schema. With regard to
the tbl prefix - I think that Hungarian notation has fallen out of
favour in recent years, and the reasoning seems pretty good to me.

> My table looks like this:
> > 12/24/2004 10:50:00 AM | CC | Level 2 | Heigle, Terry | 230
> > 12/24/2004 3:00:00 PM | CC| Level 3 | Jost, Karen | 310

You would do well to learn about normalization, try googling it. With
an improved design, you will avoid many problems in the future.

TKA|||John Kostenbader (john@.kostenbader.com) writes:
> I've posted in plenty of groups in the past without this rudeness...I'm
> very sorry to the gentleman who thought I posted in the wrong group and
> I apologize to the gentleman who believes me an armature and still uses
> "tbl" (it happens to be a table scheme I'm comfortable with and use
> regularly
> I've got news for you...I am an armature looking for some assistance. I
> believe that is the original intent of such newsgroups.

And my pointer to an Access newsgroup was an attempt to assist you. It
does happen that people post to this newsgroup when they should have had
posted to an Access newsgroup. This is a newsgroup for SQL Server, where
many don't know Access, and while both SQL Server and Access uses something
they both call SQL, there are considerable differences. For instance,
the Transform function is not in SQL Server.

My suggestion that should include CREATE TABLE etc, was also an attempt
to assist you. You see, if you don't tell us what you want, you can't
get it. It may seem to rude to point out that I don't like guessing what
you want. But the story is that I spend some time per day answering posts
in this newsgroups (and in some other places). If I can spend some time N
on a well-stated problem, where I can even can test a solution, or spend
the same time to try to understand what you want to achieve, guess what
is my pick.

Sure, I could have left your post unanswered, but assuming that you want
assistance, I posted my note so that you can help us to help you.

Remember, that in these newsgroups, you never get less than what you pay
for.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Help with Security!

I have a web application for which I am required to authenticate users at the database level (no generic or application type logins permitted). I am not permitted to use Active Directory because we do not have AD installed. We chose to use standard SQL accounts. I have two groups of users:

1. Normal users
2. Super Users (can do everything a normal user can do, plus can add/delete/modify user accounts)

When a Super User is created, they are added to three fixed roles Security Administrator (Server Role) and db_accessadmin and db_securityadmin (Database Roles).

A normal user is assigned to some custom roles that we created, but is not assigned to any fixed roles (database or server) other than the default Public role.

The problem comes when a Super User attempt to add another Super user. The process fails because the Super user does not have sufficient privileges to run sp_addrolemember. The following two statements fail because of permissions:

sp_addrolemember 'db_securityadmin', N'mySuperUser'
sp_addrolemember 'db_accessadmin', N'mySuperUser'

Additional research indicates that I am required to be a member of the SysAdmin fixed role of the db_Owner role in order to have access to sp_addrolemember.

Does anyone have any suggestions for a workaround? This is pretty frustrating. I am unwilling to let my Super Users have sysadmin or db_owner rights. These grant far more access than is needed. I just want my super users to be able to add and administer normal user accounts and other Super User accounts.

Thanks,

Hugh ScottI think you are using SQL2K, if so how about using Application roles? I have not done this before but it might be worth checking out. Look up Application Roles in BOL.

My theory is that you could setup an application role with dbo authority. When you need to create a superuser you would make an additional connection to the db using an application role, create the super user and then drop the connection.|||Ding!

You are correct. I should have stated that we were using SQL 2K. I like your idea and I will give it a shot.

Thanks!

Hugh

Originally posted by Paul Young
I think you are using SQL2K, if so how about using Application roles? I have not done this before but it might be worth checking out. Look up Application Roles in BOL.

My theory is that you could setup an application role with dbo authority. When you need to create a superuser you would make an additional connection to the db using an application role, create the super user and then drop the connection.sql

Help with script and variables

Hi If I try to run the code below I get the following errors

Server: Msg 156, Level 15, State 1, Line 36
Incorrect syntax near the keyword 'view'.
Server: Msg 170, Level 15, State 1, Line 51
Line 51: Incorrect syntax near '@.month1'.

I am not sure why it does not like the keyword view ? also I am trying to use the variables in the column name of the create table but again it does not like this.

-- declare all variables!

DECLARE @.startdate datetime,
@.enddate datetime,
@.enddate1 datetime,
@.month1 char,
@.month2 char,
@.month3 char

-- declare the cursor

DECLARE call_data CURSOR FOR

SELECT dbo.removetime(DATEADD(month, -3, getdate())) as startdate,
dbo.removetime(DATEADD(month, -2, getdate())-1) as enddate,
dbo.removetime(DATEADD(month, 0, getdate())-1) as enddate1,
left(dbo.removetime(DATEADD(month, -3, getdate())),5) as month1,
left( dbo.removetime(DATEADD(month, -2, getdate())-1),4) as month2,
left(dbo.removetime(DATEADD(month, 0, getdate())-1),4) as month3

OPEN call_data

FETCH call_data INTO @.startdate,
@.enddate,
@.enddate1,
@.month1,
@.month2,
@.month3

BEGIN

--run SQL statements

drop view temp_view

create view temp_view as
select column1,column2
from some_table
where date_and_time >= @.startdate
and date_and_time <= @.enddate1

drop table temp_table

create table temp_table (
account_no int,
account_holder_surname varchar(80),
account_holder_forename varchar(80),
@.month1 money,
@.month2 money,
@.month3 money
)

END

CLOSE call_data

DEALLOCATE call_data

RETURN

For starters, the CREATE VIEW statement has to be the first statement in a batch so it can't be used in this way.

You could dynamically create your CREATE VIEW statement then use sp_executesql to execute the command.

Chris

|||

Hi thanks for the reply

I am quite new to this what do you mean by dynamically create the CREATE VIEW statement ?

|||

DECLARE @.startdate datetime,
@.enddate datetime,
@.enddate1 datetime,
@.month1 char,
@.month2 char,
@.month3 char

-- declare the cursor

DECLARE call_data CURSOR FOR

SELECT dbo.removetime(DATEADD(month, -3, getdate())) as startdate,
dbo.removetime(DATEADD(month, -2, getdate())-1) as enddate,
dbo.removetime(DATEADD(month, 0, getdate())-1) as enddate1,
left(dbo.removetime(DATEADD(month, -3, getdate())),5) as month1,
left( dbo.removetime(DATEADD(month, -2, getdate())-1),4) as month2,
left(dbo.removetime(DATEADD(month, 0, getdate())-1),4) as month3

OPEN call_data

FETCH call_data INTO @.startdate,
@.enddate,
@.enddate1,
@.month1,
@.month2,
@.month3

BEGIN

--run SQL statements

drop view temp_view

create view temp_view as
select column1,column2
from some_table
where date_and_time >= @.startdate -- you cant have variabl inside view
and date_and_time <= @.enddate1 -- you cant have variabl inside view

drop table temp_table

create table temp_table (
account_no int,
account_holder_surname varchar(80),
account_holder_forename varchar(80),
@.month1 money, -- Remove @.
@.month2 money, -- Remove @.
@.month3 money -- Remove @.

)

END

CLOSE call_data

DEALLOCATE call_data

RETURN

and also tell us what u are intended to do... there are couple of wrong sysntax in the script... you can not use variable inside a view definition...

Madhu

|||

Hi

First of I am trying to create a view of data between a specific date range. This is the last three months. So if it was to run today then the view would contain data from 1st nov 2006 to 28th feb 2007.

When it runs on the 1st Apr the view would contain data from 1st Dec to 31st march and so on. This is why I am trying to drop the view first then create it. I am then running a query on the view and inserting the results of this into the table i create in the script.

Secondly as I am working with a rolling 3 months of data I need to drop the table I insert the data into an create it again with the correct column headings ie NOV,DEC,JAN. This is why I have the variables in the create view and create table statements.

If this is not possible is their an other way of doing this ?

Hope that makes sense

|||

I wouldn't bother creating a View for temporary purposes, you can use the SELECT statement as is to directly insert data into tables.

Try the code below to drop and create your table - I see no need for a cursor in the scenario you have presented.

Chris

DECLARE @.startdate DATETIME

DECLARE @.enddate DATETIME

DECLARE @.enddate1 DATETIME

DECLARE @.month1 NVARCHAR(10)

DECLARE @.month2 NVARCHAR(10)

DECLARE @.month3 NVARCHAR(10)

DECLARE @.TableName NVARCHAR(100)

DECLARE @.SQLString NVARCHAR(4000)

SELECT @.startdate = dbo.removetime(DATEADD(month, -3, getdate())),

@.enddate = dbo.removetime(DATEADD(month, -2, getdate())-1),

@.enddate1 = dbo.removetime(DATEADD(month, 0, getdate())-1),

@.month1 = left(dbo.removetime(DATEADD(month, -3, getdate())),5),

@.month2 = left( dbo.removetime(DATEADD(month, -2, getdate())-1),4),

@.month3 = left(dbo.removetime(DATEADD(month, 0, getdate())-1),4)

--The name of the new table

SELECT @.TableName = N'Temp_Table'

--Build the string to drop the table

SET @.SQLString =

N'IF OBJECT_ID(' + QUOTENAME(@.TableName, '''') + ') IS NOT NULL DROP TABLE [' + @.TableName + '];'

--Build the string to create the table

SET @.SQLString = @.SQLString +

N'CREATE TABLE [' + @.TableName + ']

(

[account_no] int,

[account_holder_surname] varchar(80),

[account_holder_forename] varchar(80),

[' + @.month1 + '] money,

[' + @.month2 + '] money,

[' + @.month3 + '] money

)'

--Execute the statements

EXEC(@.SQLString)

--Prove that the new table exists

--EXEC sp_help 'Temp_Table'

/*

--Don't create a 'temporary' view - just use the query directly like this...

INSERT INTO.... / UPDATE etc...

select column1,column2

from some_table

where date_and_time >= @.startdate

and date_and_time <= @.enddate1

*/

|||

Hi Chris

Thanks so much. It was the syntax around the create table I could not get my head around !

Thanks

Help With Query

I have a table as below
--
name linkcode level
--
brando 1,3,8 1
damroo 1,5 2
rogers 2,7 1
shane 1,7 1
Now I want a query where I pass a level and linkcode (one of the codes
in Comma Seperated values in the record) and it returns me a recordset
with matching criteria like below
I have a variable called lcode (int) and a variable called ulevel (int)
if I pass lcode = 7 and level = 1 it should return rogers, shane in the
record set
My sql is as below
"Select name from tblnames where level =" & ulevel & " and " & lcode &
" in (linkcode)"
I am unable to run this query as it returns an error that
Syntax error converting the varchar value '1,3,8' to a column of data
type int
I even tried by updating the query with a UDF Split function, which
works fine for a specific string CSV as params but does not take the
CSV as a Fieldvalue
"Select name from tblnames where level =" & ulevel & " and " & lcode &
" in (select value from dbo.split(linkcode))"
returns
linkcode is not a recognized OPTIMIZER LOCK HINTS option
can anyone help with this.. or suggest an alternative SQL for my
problem..
I am using MS Sql Server 2000
thanks"Damroo" <damroo@.gmail.com> wrote in message
news:1124719808.592522.132090@.g49g2000cwa.googlegroups.com...
>I have a table as below
> --
> name linkcode level
> --
> brando 1,3,8 1
> damroo 1,5 2
> rogers 2,7 1
> shane 1,7 1
>
Your biggest problem is that you don't have a normalized table. 1NF says
that you each column holds a single value. You need to split your linkcode
table out. Then the queries are quite simple.
You would then have your original table that looks something like:
Table1
--
PrimaryKeycol
name
LinkCodeLookup
Level
LinkCodeLookup
--
ForeignKeyTo_Table1.PrimaryKeycol
LinkCode
New queries would then be a simple join:
SELECT [name] FROM Table1
JOIN LinkCodeLookup ON Table1.PrimarykeyCol =
LinkCodeLookup.ForeignKeyTo_Table1
WHERE Table1.level = ?
AND LinkCodeLookup.LinkCode = ?
Rick Sawtell
MCT, MCSD, MCDBA|||>> I have a table as below
Your table is a mess -- if individual link codes are valid usable attributes
in your business model, then you must represent them as individual values in
your table. Cramming multiple values into a single column is a violation of
the most fundamental principles of relational database design.
The right way ( based on your narrative ) to represent the data would be
like:
name linkcode level
--
brando 1 1
brando 3 1
brando 8 1
damroo 1 2
damroo 5 2
rogers 2 1
rogers 7 1
shane 1 1
shane 7 1
If a redesign is not possible, then you'd have to use one or more of the
workarounds posted frequently in this newsgroup. Some of them are detailed
at:
http://www.sommarskog.se/arrays-in-sql.html
Anith

Help with permission error

Hi,
I created a user and when logged in under that user name I get the foll erro
r
Server: Msg 3704, Level 16, State 1, Line 2
User does not have permission to perform this operation on table 'dbo.sale'
when I try to execute
truncate table dbo.sale
I grant select,update,delete permission on the table to the user.
What did I miss?
ThanksTruncate table is a special operation and only table owner, dbo, symin ca
n perform the operation.
It is not grantable.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:B898FE02-EE00-4734-BE0C-9F3B4447A3C4@.microsoft.com...
> Hi,
> I created a user and when logged in under that user name I get the foll er
ror
> Server: Msg 3704, Level 16, State 1, Line 2
> User does not have permission to perform this operation on table 'dbo.sale
'
> when I try to execute
> truncate table dbo.sale
> I grant select,update,delete permission on the table to the user.
> What did I miss?
> Thanks|||Hi,
Then what's the fast way to delete from a table. I have a sp that uses a
table to dump a lot of data then after the sp is completed it truncates the
table. I didn't use a temp table because I need the data in the table if the
proc fails.
Thanks
"Tibor Karaszi" wrote:

> Truncate table is a special operation and only table owner, dbo, symin
can perform the operation.
> It is not grantable.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Chris" <Chris@.discussions.microsoft.com> wrote in message
> news:B898FE02-EE00-4734-BE0C-9F3B4447A3C4@.microsoft.com...
>

Help with Join Compatibility

I was hoping someone could help me or put me on the right path to re-writing the join portion of this sql query in ANSI form for compatibility level 90. Im just not sure how to handle the three join statements and if they should go at the top in the FROM statement (dont know if that would mess up the rows produced). The query exists inside a stored proc.

SELECT
S.TYPE,
S.LOCATION_TYPE,
S.LOCATION_ID,
S.PLANNED_ARRIVAL,
S.PROJECTED_ARRIVAL,
S.ACTUAL_ARRIVAL,
S.PLANNED_DEPARTURE,
S.PROJECTED_DEPARTURE,
S.ACTUAL_DEPARTURE
FROM TAB1 S, TAB2 RL, TAB LS
WHERE
S.LOAD_ID = @.V_CURRENTLOADID AND
(RL.REGION_ID = @.REGION_ID AND
RL.ROUTE_DATE = @.ROUTE_DATE AND
RL.ROUTE_ID = @.ROUTE_ID) AND
(S.REGION_ID = RL.REGION_ID AND
S.ROUTE_DATE = RL.ROUTE_DATE AND
S.ROUTE_ID = RL.ROUTE_ID) AND
(S.LOCATION_ID =* LS.LOAD_LOCATION_ID AND

S.LOAD_ID =* LS.LOAD_ID AND
S.LOAD_STOP_ID =* LS.LOAD_STOP_ID)
ORDER BY RL.SEQ_NUM, S.ACTUAL_SEQUENCE_NUM;

Any help would be greatly appreciated

hi,

can u send me the datamodel? based on the datamodel we can form the query

Thanks,

mani

|||Do u want the table descriptions, columns and such? There are no foreign keys on any of the tables and they are independant if that help.|||

The following table,TAB1 S, TAB2 RL, TAB LS what are all the primary key name. what is required results

what is pupose of the table1,table2,table3

i'm in online

thanks,

mani

|||

here are the tables and their definitions. There are no foreign key constraints on these tables. The select statement gets dumped into a cursor. Thanks.

SQL> desc tab1
Name Null? Type
-- -- -

REGION_ID NOT NULL NVARCHAR2(9) PK
ROUTE_DATE NOT NULL DATE PK
ROUTE_ID NOT NULL NVARCHAR2(15) PK
INTERNAL_STOP_ID NOT NULL NUMBER(38) PK

STOP_IX NUMBER(38)
SEQUENCE_NUM NUMBER(38)
ACTUAL_SEQUENCE_NUM NUMBER(38)
TYPE NUMBER(38)
LOCATION_TYPE NVARCHAR2(3)
LOCATION_ID NVARCHAR2(15)

LOAD_ID NUMBER(38)
LOAD_STOP_ID NUMBER(38)

SQL> desc tab2
Name Null? Type
-- -- -

LOAD_ID NOT NULL NUMBER(38) PK

SEQ_NUM NOT NULL NUMBER(38) PK
REGION_ID NOT NULL NVARCHAR2(9) PK
ROUTE_DATE NOT NULL DATE PK
ROUTE_ID NOT NULL NVARCHAR2(9) PK
READ_ONLY NUMBER(38)
DATE_MODIFIED DATE
USER_MODIFIED NVARCHAR2(12)

SQL> desc tab3
Name Null? Type
-- -- --

LOAD_ID NOT NULL NUMBER(38) PK
LOAD_STOP_ID NOT NULL NUMBER(38) PK
SEQ_NUM NUMBER(38)
LOAD_LOCATION_ID NOT NULL NVARCHAR2(15)
PLANNED_SERVICE_TIME NUMBER(38)
ACTUAL_SERVICE_TIME NUMBER(38)
TIME_WINDOW_START DATE
TIME_WINDOW_END DATE

|||

ackermsb wrote:

I was hoping someone could help me or put me on the right path to re-writing the join portion of this sql query in ANSI form for compatibility level 90. Im just not sure how to handle the three join statements and if they should go at the top in the FROM statement (dont know if that would mess up the rows produced). The query exists inside a stored proc.

SELECT
S.TYPE,
S.LOCATION_TYPE,
S.LOCATION_ID,
S.PLANNED_ARRIVAL,
S.PROJECTED_ARRIVAL,
S.ACTUAL_ARRIVAL,
S.PLANNED_DEPARTURE,
S.PROJECTED_DEPARTURE,
S.ACTUAL_DEPARTURE
FROM TAB1 S, TAB2 RL, TAB LS
WHERE
S.LOAD_ID = @.V_CURRENTLOADID AND
(RL.REGION_ID = @.REGION_ID AND
RL.ROUTE_DATE = @.ROUTE_DATE AND
RL.ROUTE_ID = @.ROUTE_ID) AND
(S.REGION_ID = RL.REGION_ID AND
S.ROUTE_DATE = RL.ROUTE_DATE AND
S.ROUTE_ID = RL.ROUTE_ID) AND
(S.LOCATION_ID =* LS.LOAD_LOCATION_ID AND

S.LOAD_ID =* LS.LOAD_ID AND
S.LOAD_STOP_ID =* LS.LOAD_STOP_ID)
ORDER BY RL.SEQ_NUM, S.ACTUAL_SEQUENCE_NUM;

Any help would be greatly appreciated

hi, hope this helps..

SELECT A.TYPE,
A.LOCATION_TYPE,
A.LOCATION_ID,
A.PLANNED_ARRIVAL,
A.PROJECTED_ARRIVAL,
A.ACTUAL_ARRIVAL,
A.PLANNED_DEPARTURE,
A.PROJECTED_DEPARTURE,
A.ACTUAL_DEPARTURE
FROM TAB1 A
INNER JOIN TAB2 B
ON A.REGION_ID = B.REGION_ID
AND A.ROUTE_DATE = B.ROUTE_DATE
AND A.ROUTE_ID = B.ROUTE_ID
INNER JOIN TAB C
ON A.LOCATION_ID = C.LOAD_LOCATION_ID
AND A.LOAD_ID = C.LOAD_ID
AND A.LOAD_STOP_ID = C.LOAD_STOP_ID
WHERE A.LOAD_ID = @.V_CURRENTLOADID
AND B.REGION_ID = @.REGION_ID
AND B.ROUTE_DATE = @.ROUTE_DATE
AND B.ROUTE_ID = @.ROUTE_ID
ORDER BY B.SEQ_NUM, A.ACTUAL_SEQUENCE_NUM|||

SELECT
A.TYPE,
A.LOCATION_TYPE,
A.LOCATION_ID,
A.PLANNED_ARRIVAL,
A.PROJECTED_ARRIVAL,
A.ACTUAL_ARRIVAL,
A.PLANNED_DEPARTURE,
A.PROJECTED_DEPARTURE,
A.ACTUAL_DEPARTURE

From Table1 A,Table2 B,Table3 C

where B.LOAD_ID = C.LOAD_ID AND
A.REGION_ID = B.REGION_ID AND
A.LOAD_ID = @.V_CURRENTLOADID AND
B.REGION_ID = @.REGION_ID AND
B.ROUTE_DATE = @.ROUTE_DATE AND
B.ROUTE_ID = @.ROUTE_ID

|||

Rom-Rom,

I believe the way this is written is accurate however would this A =* B be a right outter join and not an inner join hence

FROM TAB1 A

INNER JOIN TAB2 B

ON A.REGION_ID = B.REGION_ID

AND A.ROUTE_DATE = B.ROUTE_DATE

AND A.ROUTE_ID = B.ROUTE_ID

RIGHT OUTER JOIN TAB C

ON A.LOCATION_ID = C.LOAD_LOCATION_ID

AND A.LOAD_ID = C.LOAD_ID

AND A.LOAD_STOP_ID = C.LOAD_STOP_ID

Let me know what you think

|||hi ackermsb,

it really depends on how the data should be presented. i made it inner joins because i thought you want existing data for related tables to be returned (that is what the equation of related fields between tables from your original query suggested).

you may do that right outer join if you want "all" records of table "tab" to exist on the result regardless if it exists on the joins of table "tab1" and "tab2", inexistent records for the latter tables will be regarded as null fields.

it's up to you, i may not know what requirements you need. hope i've been of help.

good day.