ORDER BY is very slow when querying linked Server Oracle9i

I am creating 3 views from the linked server
and then I have a question at the bottom returning results.
If I exclude the ORDER BY from my question the query will take about 40
seconds,
with the ORDER BY it takes about an hour to finish.
Is this normal '
code below
--
create view xxxyyy1 as
SELECT INSATSID, STATUS
FROM HACTAR..EKOP2.INSATS
go
create view xxxyyy2 as
SELECT distinct insats, status FROM HACTAR..EKOP2.RVAINK001905
go
create view xxxyyy3 as
select a.insatsid, a.status, b.insats AS 'INK905_INSATS', b.status AS
'INK905_STATUS'
from xxxyyy1 A left outer join xxxyyy2 B
on a.insatsid = b.insats
where a.status <> b.status
go
SELECT
A.INSATSID,
A.STATUS_OLD,
A.STATUS_NEW,
A.ANDR_DATUM,
A.ANDR_TID,
B.INSATSID,
B.STATUS,
B.INK905_INSATS,
B.INK905_STATUS
FROM HACTAR..EKOP2.INSATSLOG A
right outer join xxxyyy3 B
on A.INSATSID = B.INSATSID
WHERE A.ANDR_DATUM < 20060225
ORDER BY
A.INSATSID,
A.ANDR_DATUM,
A.ANDR_TID,
A.STATUS_OLD,
A.STATUS_NEWHi
See an execution plan. My guess in order to increase performance you may
want to add an index on the column in ORDER BY clause
"ln54" <ln54@.discussions.microsoft.com> wrote in message
news:7B8F4D82-6F9B-423A-A3A4-EC957A72A876@.microsoft.com...
>I am creating 3 views from the linked server
> and then I have a question at the bottom returning results.
> If I exclude the ORDER BY from my question the query will take about 40
> seconds,
> with the ORDER BY it takes about an hour to finish.
> Is this normal '
> code below
> --
> create view xxxyyy1 as
> SELECT INSATSID, STATUS
> FROM HACTAR..EKOP2.INSATS
> go
> create view xxxyyy2 as
> SELECT distinct insats, status FROM HACTAR..EKOP2.RVAINK001905
> go
> create view xxxyyy3 as
> select a.insatsid, a.status, b.insats AS 'INK905_INSATS', b.status AS
> 'INK905_STATUS'
> from xxxyyy1 A left outer join xxxyyy2 B
> on a.insatsid = b.insats
> where a.status <> b.status
> go
> SELECT
> A.INSATSID,
> A.STATUS_OLD,
> A.STATUS_NEW,
> A.ANDR_DATUM,
> A.ANDR_TID,
> B.INSATSID,
> B.STATUS,
> B.INK905_INSATS,
> B.INK905_STATUS
> FROM HACTAR..EKOP2.INSATSLOG A
> right outer join xxxyyy3 B
> on A.INSATSID = B.INSATSID
> WHERE A.ANDR_DATUM < 20060225
> ORDER BY
> A.INSATSID,
> A.ANDR_DATUM,
> A.ANDR_TID,
> A.STATUS_OLD,
> A.STATUS_NEW
>|||Thankyou, the ORDER BY statement causes it to do a lot of Nested loops
which seems to be very time consuming
"Uri Dimant" wrote:

> Hi
> See an execution plan. My guess in order to increase performance you may
> want to add an index on the column in ORDER BY clause
>
>
>
> "ln54" <ln54@.discussions.microsoft.com> wrote in message
> news:7B8F4D82-6F9B-423A-A3A4-EC957A72A876@.microsoft.com...
>
>

ORDER BY is very slow when querying linked Server Oracle9i

I am creating 3 views from the linked server
and then I have a question at the bottom returning results.
If I exclude the ORDER BY from my question the query will take about 40
seconds,
with the ORDER BY it takes about an hour to finish.
Is this normal ?
code below
create view xxxyyy1 as
SELECT INSATSID, STATUS
FROM HACTAR..EKOP2.INSATS
go
create view xxxyyy2 as
SELECT distinct insats, status FROM HACTAR..EKOP2.RVAINK001905
go
create view xxxyyy3 as
select a.insatsid, a.status, b.insats AS 'INK905_INSATS', b.status AS
'INK905_STATUS'
from xxxyyy1 A left outer join xxxyyy2 B
on a.insatsid = b.insats
where a.status <> b.status
go
SELECT
A.INSATSID,
A.STATUS_OLD,
A.STATUS_NEW,
A.ANDR_DATUM,
A.ANDR_TID,
B.INSATSID,
B.STATUS,
B.INK905_INSATS,
B.INK905_STATUS
FROM HACTAR..EKOP2.INSATSLOG A
right outer join xxxyyy3 B
on A.INSATSID = B.INSATSID
WHERE A.ANDR_DATUM < 20060225
ORDER BY
A.INSATSID,
A.ANDR_DATUM,
A.ANDR_TID,
A.STATUS_OLD,
A.STATUS_NEW
Hi
See an execution plan. My guess in order to increase performance you may
want to add an index on the column in ORDER BY clause
"ln54" <ln54@.discussions.microsoft.com> wrote in message
news:7B8F4D82-6F9B-423A-A3A4-EC957A72A876@.microsoft.com...
>I am creating 3 views from the linked server
> and then I have a question at the bottom returning results.
> If I exclude the ORDER BY from my question the query will take about 40
> seconds,
> with the ORDER BY it takes about an hour to finish.
> Is this normal ?
> code below
> --
> create view xxxyyy1 as
> SELECT INSATSID, STATUS
> FROM HACTAR..EKOP2.INSATS
> go
> create view xxxyyy2 as
> SELECT distinct insats, status FROM HACTAR..EKOP2.RVAINK001905
> go
> create view xxxyyy3 as
> select a.insatsid, a.status, b.insats AS 'INK905_INSATS', b.status AS
> 'INK905_STATUS'
> from xxxyyy1 A left outer join xxxyyy2 B
> on a.insatsid = b.insats
> where a.status <> b.status
> go
> SELECT
> A.INSATSID,
> A.STATUS_OLD,
> A.STATUS_NEW,
> A.ANDR_DATUM,
> A.ANDR_TID,
> B.INSATSID,
> B.STATUS,
> B.INK905_INSATS,
> B.INK905_STATUS
> FROM HACTAR..EKOP2.INSATSLOG A
> right outer join xxxyyy3 B
> on A.INSATSID = B.INSATSID
> WHERE A.ANDR_DATUM < 20060225
> ORDER BY
> A.INSATSID,
> A.ANDR_DATUM,
> A.ANDR_TID,
> A.STATUS_OLD,
> A.STATUS_NEW
>
|||Thankyou, the ORDER BY statement causes it to do a lot of Nested loops
which seems to be very time consuming
"Uri Dimant" wrote:

> Hi
> See an execution plan. My guess in order to increase performance you may
> want to add an index on the column in ORDER BY clause
>
>
>
> "ln54" <ln54@.discussions.microsoft.com> wrote in message
> news:7B8F4D82-6F9B-423A-A3A4-EC957A72A876@.microsoft.com...
>
>
sql

ORDER BY is very slow when querying linked Server Oracle9i

I am creating 3 views from the linked server
and then I have a question at the bottom returning results.
If I exclude the ORDER BY from my question the query will take about 40
seconds,
with the ORDER BY it takes about an hour to finish.
Is this normal '
code below
--
create view xxxyyy1 as
SELECT INSATSID, STATUS
FROM HACTAR..EKOP2.INSATS
go
create view xxxyyy2 as
SELECT distinct insats, status FROM HACTAR..EKOP2.RVAINK001905
go
create view xxxyyy3 as
select a.insatsid, a.status, b.insats AS 'INK905_INSATS', b.status AS
'INK905_STATUS'
from xxxyyy1 A left outer join xxxyyy2 B
on a.insatsid = b.insats
where a.status <> b.status
go
SELECT
A.INSATSID,
A.STATUS_OLD,
A.STATUS_NEW,
A.ANDR_DATUM,
A.ANDR_TID,
B.INSATSID,
B.STATUS,
B.INK905_INSATS,
B.INK905_STATUS
FROM HACTAR..EKOP2.INSATSLOG A
right outer join xxxyyy3 B
on A.INSATSID = B.INSATSID
WHERE A.ANDR_DATUM < 20060225
ORDER BY
A.INSATSID,
A.ANDR_DATUM,
A.ANDR_TID,
A.STATUS_OLD,
A.STATUS_NEWHi
See an execution plan. My guess in order to increase performance you may
want to add an index on the column in ORDER BY clause
"ln54" <ln54@.discussions.microsoft.com> wrote in message
news:7B8F4D82-6F9B-423A-A3A4-EC957A72A876@.microsoft.com...
>I am creating 3 views from the linked server
> and then I have a question at the bottom returning results.
> If I exclude the ORDER BY from my question the query will take about 40
> seconds,
> with the ORDER BY it takes about an hour to finish.
> Is this normal '
> code below
> --
> create view xxxyyy1 as
> SELECT INSATSID, STATUS
> FROM HACTAR..EKOP2.INSATS
> go
> create view xxxyyy2 as
> SELECT distinct insats, status FROM HACTAR..EKOP2.RVAINK001905
> go
> create view xxxyyy3 as
> select a.insatsid, a.status, b.insats AS 'INK905_INSATS', b.status AS
> 'INK905_STATUS'
> from xxxyyy1 A left outer join xxxyyy2 B
> on a.insatsid = b.insats
> where a.status <> b.status
> go
> SELECT
> A.INSATSID,
> A.STATUS_OLD,
> A.STATUS_NEW,
> A.ANDR_DATUM,
> A.ANDR_TID,
> B.INSATSID,
> B.STATUS,
> B.INK905_INSATS,
> B.INK905_STATUS
> FROM HACTAR..EKOP2.INSATSLOG A
> right outer join xxxyyy3 B
> on A.INSATSID = B.INSATSID
> WHERE A.ANDR_DATUM < 20060225
> ORDER BY
> A.INSATSID,
> A.ANDR_DATUM,
> A.ANDR_TID,
> A.STATUS_OLD,
> A.STATUS_NEW
>|||Thankyou, the ORDER BY statement causes it to do a lot of Nested loops
which seems to be very time consuming
"Uri Dimant" wrote:
> Hi
> See an execution plan. My guess in order to increase performance you may
> want to add an index on the column in ORDER BY clause
>
>
>
> "ln54" <ln54@.discussions.microsoft.com> wrote in message
> news:7B8F4D82-6F9B-423A-A3A4-EC957A72A876@.microsoft.com...
> >I am creating 3 views from the linked server
> > and then I have a question at the bottom returning results.
> > If I exclude the ORDER BY from my question the query will take about 40
> > seconds,
> > with the ORDER BY it takes about an hour to finish.
> >
> > Is this normal '
> >
> > code below
> > --
> >
> > create view xxxyyy1 as
> > SELECT INSATSID, STATUS
> > FROM HACTAR..EKOP2.INSATS
> >
> > go
> >
> > create view xxxyyy2 as
> > SELECT distinct insats, status FROM HACTAR..EKOP2.RVAINK001905
> >
> > go
> >
> > create view xxxyyy3 as
> > select a.insatsid, a.status, b.insats AS 'INK905_INSATS', b.status AS
> > 'INK905_STATUS'
> > from xxxyyy1 A left outer join xxxyyy2 B
> > on a.insatsid = b.insats
> > where a.status <> b.status
> > go
> >
> > SELECT
> > A.INSATSID,
> > A.STATUS_OLD,
> > A.STATUS_NEW,
> > A.ANDR_DATUM,
> > A.ANDR_TID,
> > B.INSATSID,
> > B.STATUS,
> > B.INK905_INSATS,
> > B.INK905_STATUS
> > FROM HACTAR..EKOP2.INSATSLOG A
> > right outer join xxxyyy3 B
> > on A.INSATSID = B.INSATSID
> > WHERE A.ANDR_DATUM < 20060225
> > ORDER BY
> > A.INSATSID,
> > A.ANDR_DATUM,
> > A.ANDR_TID,
> > A.STATUS_OLD,
> > A.STATUS_NEW
> >
>
>

ORDER BY CASe

I am creating a stored procedure to return a sorted list
Currently like this
DECLARE @.SORT tinyint
SET @.SORT = 1 --could be anything passed in
SELECT * FROM Table
ORDER BY
CASE
WHEN 1 THEN Col1
WHEN 2 THEN Col2
...etc
Now i would like to add 'ASC' or 'DESC' like follows
CASE
WHEN 1 THEN Col1 ASC
WHEN 2 THEN Col1 DESC
...etc
But this return an error 'Incorrect syntax near the keyword 'ASC'.'
IS there a way to make this work?
Yes, but the syntax is a bit weird:
create table #a(b char(1), c char(1))
insert #a values ('a', 'z')
insert #a values ('b', 'y')
insert #a values ('c', 'x')
declare @.orderby int
set @.orderby = 1
SELECT *
FROM #a
ORDER BY
CASE WHEN @.Orderby = 1 THEN b end asc,
CASE WHEN @.Orderby = 2 THEN b end desc,
CASE WHEN @.Orderby = 3 THEN c end asc,
CASE WHEN @.Orderby = 4 THEN c end desc
"GrantMagic" <grant@.magicalia.com> wrote in message
news:O0YlBadkEHA.3356@.TK2MSFTNGP15.phx.gbl...
> I am creating a stored procedure to return a sorted list
> Currently like this
> DECLARE @.SORT tinyint
> SET @.SORT = 1 --could be anything passed in
> SELECT * FROM Table
> ORDER BY
> CASE
> WHEN 1 THEN Col1
> WHEN 2 THEN Col2
> ...etc
> Now i would like to add 'ASC' or 'DESC' like follows
> CASE
> WHEN 1 THEN Col1 ASC
> WHEN 2 THEN Col1 DESC
> ...etc
> But this return an error 'Incorrect syntax near the keyword 'ASC'.'
> IS there a way to make this work?
>
|||Brilliant, that did the job perfectly!!
Thank You!
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:OJZDGddkEHA.3648@.TK2MSFTNGP09.phx.gbl...
> Yes, but the syntax is a bit weird:
>
> create table #a(b char(1), c char(1))
> insert #a values ('a', 'z')
> insert #a values ('b', 'y')
> insert #a values ('c', 'x')
> declare @.orderby int
> set @.orderby = 1
> SELECT *
> FROM #a
> ORDER BY
> CASE WHEN @.Orderby = 1 THEN b end asc,
> CASE WHEN @.Orderby = 2 THEN b end desc,
> CASE WHEN @.Orderby = 3 THEN c end asc,
> CASE WHEN @.Orderby = 4 THEN c end desc
>
> "GrantMagic" <grant@.magicalia.com> wrote in message
> news:O0YlBadkEHA.3356@.TK2MSFTNGP15.phx.gbl...
>

ORDER BY CASe

I am creating a stored procedure to return a sorted list
Currently like this
DECLARE @.SORT tinyint
SET @.SORT = 1 --could be anything passed in
SELECT * FROM Table
ORDER BY
CASE
WHEN 1 THEN Col1
WHEN 2 THEN Col2
...etc
Now i would like to add 'ASC' or 'DESC' like follows
CASE
WHEN 1 THEN Col1 ASC
WHEN 2 THEN Col1 DESC
...etc
But this return an error 'Incorrect syntax near the keyword 'ASC'.'
IS there a way to make this work?Yes, but the syntax is a bit weird:
create table #a(b char(1), c char(1))
insert #a values ('a', 'z')
insert #a values ('b', 'y')
insert #a values ('c', 'x')
declare @.orderby int
set @.orderby = 1
SELECT *
FROM #a
ORDER BY
CASE WHEN @.Orderby = 1 THEN b end asc,
CASE WHEN @.Orderby = 2 THEN b end desc,
CASE WHEN @.Orderby = 3 THEN c end asc,
CASE WHEN @.Orderby = 4 THEN c end desc
"GrantMagic" <grant@.magicalia.com> wrote in message
news:O0YlBadkEHA.3356@.TK2MSFTNGP15.phx.gbl...
> I am creating a stored procedure to return a sorted list
> Currently like this
> DECLARE @.SORT tinyint
> SET @.SORT = 1 --could be anything passed in
> SELECT * FROM Table
> ORDER BY
> CASE
> WHEN 1 THEN Col1
> WHEN 2 THEN Col2
> ...etc
> Now i would like to add 'ASC' or 'DESC' like follows
> CASE
> WHEN 1 THEN Col1 ASC
> WHEN 2 THEN Col1 DESC
> ...etc
> But this return an error 'Incorrect syntax near the keyword 'ASC'.'
> IS there a way to make this work?
>|||Brilliant, that did the job perfectly!!
Thank You!
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:OJZDGddkEHA.3648@.TK2MSFTNGP09.phx.gbl...
> Yes, but the syntax is a bit weird:
>
> create table #a(b char(1), c char(1))
> insert #a values ('a', 'z')
> insert #a values ('b', 'y')
> insert #a values ('c', 'x')
> declare @.orderby int
> set @.orderby = 1
> SELECT *
> FROM #a
> ORDER BY
> CASE WHEN @.Orderby = 1 THEN b end asc,
> CASE WHEN @.Orderby = 2 THEN b end desc,
> CASE WHEN @.Orderby = 3 THEN c end asc,
> CASE WHEN @.Orderby = 4 THEN c end desc
>
> "GrantMagic" <grant@.magicalia.com> wrote in message
> news:O0YlBadkEHA.3356@.TK2MSFTNGP15.phx.gbl...
> > I am creating a stored procedure to return a sorted list
> >
> > Currently like this
> >
> > DECLARE @.SORT tinyint
> > SET @.SORT = 1 --could be anything passed in
> >
> > SELECT * FROM Table
> > ORDER BY
> >
> > CASE
> > WHEN 1 THEN Col1
> > WHEN 2 THEN Col2
> > ...etc
> >
> > Now i would like to add 'ASC' or 'DESC' like follows
> >
> > CASE
> > WHEN 1 THEN Col1 ASC
> > WHEN 2 THEN Col1 DESC
> > ...etc
> >
> > But this return an error 'Incorrect syntax near the keyword 'ASC'.'
> >
> > IS there a way to make this work?
> >
> >
>

Oracle parameters

I am creating a report using a query where the data source is an Oracle 8i database.

I'm selecting data that is between 2 dates:

"Select * from table where mydate is between :startdate and :stopdate".

To get all of the data I need to use :stopdate + 1 to add a day for oracle SSRS uses dateadd to get the date that oracle requires. SSRS does not like the (+1) and Oracle does not recognize the dateadd function.

How do I solve this problem?

P.S. This is my first SSRS report.

The bottom of this posting contains a simple report that shows how to do this based on a SQL Server data source.

Overall there are several options:

* you can keep your current query commandtext and perform the calculation in the report parameter as shown in the RS 2005 sample below, where the second report parameter is marked as "hidden" and its value is determined based on the first report parameter using an expression: =CDate(Parameters!StartDate.Value).AddDays(1)

* perform the datetime calculation in the commandtext. However in that case you *must* use the Oracle date functions to perform the calculation (lookup your Oracle SQL documentation)

* write a Oracle stored procedure (e.g. in PL/SQL) and call the stored procedure from Reporting Services. The stored procedure has to return the data through one OUT REF cursor (see also this related thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=356162&SiteID=1)

-- Robert

<?xml version="1.0" encoding="utf-8"?>
<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<DataSources>
<DataSource Name="Northwind">
<DataSourceReference>Northwind</DataSourceReference>
<rd:DataSourceID>95e33366-62c0-4eea-87f0-94d2534377bd</rd:DataSourceID>
</DataSource>
</DataSources>
<BottomMargin>1in</BottomMargin>
<RightMargin>1in</RightMargin>
<ReportParameters>
<ReportParameter Name="StartDate">
<DataType>DateTime</DataType>
<DefaultValue>
<Values>
<Value>=Today</Value>
</Values>
</DefaultValue>
<Prompt>StartDate</Prompt>
</ReportParameter>
<ReportParameter Name="EndDate">
<DataType>DateTime</DataType>
<DefaultValue>
<Values>
<Value>=CDate(Parameters!StartDate.Value).AddDays(1)</Value>
</Values>
</DefaultValue>
<Prompt>EndDate</Prompt>
<Hidden>true</Hidden>
</ReportParameter>
</ReportParameters>
<rd:DrawGrid>true</rd:DrawGrid>
<InteractiveWidth>8.5in</InteractiveWidth>
<rd:SnapToGrid>true</rd:SnapToGrid>
<Body>
<ReportItems>
<Textbox Name="textbox2">
<Left>0.25in</Left>
<Top>0.5in</Top>
<ZIndex>1</ZIndex>
<Width>2.375in</Width>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Height>0.25in</Height>
<Value>=Parameters!EndDate.Value</Value>
</Textbox>
<Textbox Name="textbox1">
<Left>0.25in</Left>
<Top>0.125in</Top>
<rd:DefaultName>textbox1</rd:DefaultName>
<Width>2.375in</Width>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Height>0.25in</Height>
<Value>=Parameters!StartDate.Value</Value>
</Textbox>
</ReportItems>
<Height>2in</Height>
</Body>
<rd:ReportID>e15b81e3-ab03-4e68-a6b8-0de6d6880ab5</rd:ReportID>
<LeftMargin>1in</LeftMargin>
<DataSets>
<DataSet Name="DataSet1">
<Query>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
<CommandText>select * from orders where OrderDate between @.StartDate and @.EndDate</CommandText>
<QueryParameters>
<QueryParameter Name="@.StartDate">
<Value>=Parameters!StartDate.Value</Value>
</QueryParameter>
<QueryParameter Name="@.EndDate">
<Value>=Parameters!EndDate.Value</Value>
</QueryParameter>
</QueryParameters>
<DataSourceName>Northwind</DataSourceName>
</Query>
<Fields>
<Field Name="OrderID">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>OrderID</DataField>
</Field>
<Field Name="CustomerID">
<rd:TypeName>System.String</rd:TypeName>
<DataField>CustomerID</DataField>
</Field>
<Field Name="EmployeeID">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>EmployeeID</DataField>
</Field>
<Field Name="OrderDate">
<rd:TypeName>System.DateTime</rd:TypeName>
<DataField>OrderDate</DataField>
</Field>
<Field Name="RequiredDate">
<rd:TypeName>System.DateTime</rd:TypeName>
<DataField>RequiredDate</DataField>
</Field>
<Field Name="ShippedDate">
<rd:TypeName>System.DateTime</rd:TypeName>
<DataField>ShippedDate</DataField>
</Field>
<Field Name="ShipVia">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>ShipVia</DataField>
</Field>
<Field Name="Freight">
<rd:TypeName>System.Decimal</rd:TypeName>
<DataField>Freight</DataField>
</Field>
<Field Name="ShipName">
<rd:TypeName>System.String</rd:TypeName>
<DataField>ShipName</DataField>
</Field>
<Field Name="ShipAddress">
<rd:TypeName>System.String</rd:TypeName>
<DataField>ShipAddress</DataField>
</Field>
<Field Name="ShipCity">
<rd:TypeName>System.String</rd:TypeName>
<DataField>ShipCity</DataField>
</Field>
<Field Name="ShipRegion">
<rd:TypeName>System.String</rd:TypeName>
<DataField>ShipRegion</DataField>
</Field>
<Field Name="ShipPostalCode">
<rd:TypeName>System.String</rd:TypeName>
<DataField>ShipPostalCode</DataField>
</Field>
<Field Name="ShipCountry">
<rd:TypeName>System.String</rd:TypeName>
<DataField>ShipCountry</DataField>
</Field>
</Fields>
</DataSet>
</DataSets>
<Width>6.5in</Width>
<InteractiveHeight>11in</InteractiveHeight>
<Language>en-US</Language>
<TopMargin>1in</TopMargin>
</Report>

|||

Robert,

Thanks for your response. Options 1 & 2 are feasible since we can only 'read' from the Oracle database.

Oracle DSV: Problems creating relationships with reported inconsistent datatypes.

I am adding tables to the DSV and adding the relationships but SSAS keeps complaining that the data types of the FK and PK tables do not match, even though I can see that they are in fact the same, ie NUMBER with no scale/precision set. Presumably this means Oracle uses a default?

Any ideas as to the fix? I have created a NamedQuery as a SELECT * FROM <table> and this seems to work but does seem to be a hack and is very annoying!

Searching connect, I found at that this is a bug. however, it was reported as fixed in SP1 but I am using SP2 CTP.. so I have added a new bug report for this under SP2.

the only workaround is to go back and edit the XML file. (View Code in the solution explorer context menu).

ORACLE BEFORE INSERT EQUIVALENT IN SQL SERVER

I am having trouble creating an INSTEAD OF trigger in SQL Server to
replicate a BEFORE UPDATE trigger from ORACLE.
Here is a sample of the ORACLE BEFORE UPDATE trigger:
CREATE TRIGGER myTRIGGER ON MYTABLE
begin
:new.DT := SYSDATE;
if :new.NM is NULL then
:new.NM := USER;
end if;
end myTRIGGER;

It seems as though I have to jump through hoops in SQL Server AND I
cannot come up with correct results.

Here is a snippet from SQL SERVER (this is what I figured I needed to
do after reading various articles,questions):
CREATE TRIGGER myTRIGGER on THETABLE
INSTEAD OF UPDATE
AS

SELECT * INTO #MYTABLE FROM INSERTED

UPDATE #MYTABLE SET DT = GETDATE()
UPDATE #MYTABLE SET NM = USER WHERE NM IS NULL

UPDATE THETABLE
SET
DT = (SELECT DT FROM #MYTABLE),
NM = (SELECT NM FROM #MYTABLE)
WHERE THETABLE.ID = (SELECT ID FROM #MYTABLE)

Can anyone please shed some light on this? Thanks in advance.You don't need to use temp tables and subqueries here. The example
below will do the job, assuming that ID is the primary key and is never
changed. Note that you'll also need to include other columns in the
UPDATE statement in order for those to be updated.

CREATE TRIGGER myTRIGGER on THETABLE
INSTEAD OF UPDATE
AS
UPDATE THETABLE
SET
DT = GETDATE(),
NM = COALESCE(inserted.NM, USER)
FROM inserted
WHERE THETABLE.ID = inserted.ID
GO

--
Hope this helps.

Dan Guzman
SQL Server MVP

--------
SQL FAQ links (courtesy Neil Pike):

http://www.ntfaq.com/Articles/Index...epartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--------

"DTB" <macdtb@.mac.com> wrote in message
news:4af8df45.0308170552.27c24433@.posting.google.c om...
> I am having trouble creating an INSTEAD OF trigger in SQL Server to
> replicate a BEFORE UPDATE trigger from ORACLE.
> Here is a sample of the ORACLE BEFORE UPDATE trigger:
> CREATE TRIGGER myTRIGGER ON MYTABLE
> begin
> :new.DT := SYSDATE;
> if :new.NM is NULL then
> :new.NM := USER;
> end if;
> end myTRIGGER;
> It seems as though I have to jump through hoops in SQL Server AND I
> cannot come up with correct results.
> Here is a snippet from SQL SERVER (this is what I figured I needed to
> do after reading various articles,questions):
> CREATE TRIGGER myTRIGGER on THETABLE
> INSTEAD OF UPDATE
> AS
> SELECT * INTO #MYTABLE FROM INSERTED
> UPDATE #MYTABLE SET DT = GETDATE()
> UPDATE #MYTABLE SET NM = USER WHERE NM IS NULL
> UPDATE THETABLE
> SET
> DT = (SELECT DT FROM #MYTABLE),
> NM = (SELECT NM FROM #MYTABLE)
> WHERE THETABLE.ID = (SELECT ID FROM #MYTABLE)
> Can anyone please shed some light on this? Thanks in advance.|||Thank you Dan! This works the way I want it to.

"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message news:<ZfN%a.27352$vo2.5843@.newsread1.news.atl.earthlink. net>...
> You don't need to use temp tables and subqueries here. The example
> below will do the job, assuming that ID is the primary key and is never
> changed. Note that you'll also need to include other columns in the
> UPDATE statement in order for those to be updated.
> CREATE TRIGGER myTRIGGER on THETABLE
> INSTEAD OF UPDATE
> AS
> UPDATE THETABLE
> SET
> DT = GETDATE(),
> NM = COALESCE(inserted.NM, USER)
> FROM inserted
> WHERE THETABLE.ID = inserted.ID
> GO
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> --------
> SQL FAQ links (courtesy Neil Pike):
> http://www.ntfaq.com/Articles/Index...epartmentID=800
> http://www.sqlserverfaq.com
> http://www.mssqlserver.com/faq
> --------
> "DTB" <macdtb@.mac.com> wrote in message
> news:4af8df45.0308170552.27c24433@.posting.google.c om...
> > I am having trouble creating an INSTEAD OF trigger in SQL Server to
> > replicate a BEFORE UPDATE trigger from ORACLE.
> > Here is a sample of the ORACLE BEFORE UPDATE trigger:
> > CREATE TRIGGER myTRIGGER ON MYTABLE
> > begin
> > :new.DT := SYSDATE;
> > if :new.NM is NULL then
> > :new.NM := USER;
> > end if;
> > end myTRIGGER;
> > It seems as though I have to jump through hoops in SQL Server AND I
> > cannot come up with correct results.
> > Here is a snippet from SQL SERVER (this is what I figured I needed to
> > do after reading various articles,questions):
> > CREATE TRIGGER myTRIGGER on THETABLE
> > INSTEAD OF UPDATE
> > AS
> > SELECT * INTO #MYTABLE FROM INSERTED
> > UPDATE #MYTABLE SET DT = GETDATE()
> > UPDATE #MYTABLE SET NM = USER WHERE NM IS NULL
> > UPDATE THETABLE
> > SET
> > DT = (SELECT DT FROM #MYTABLE),
> > NM = (SELECT NM FROM #MYTABLE)
> > WHERE THETABLE.ID = (SELECT ID FROM #MYTABLE)
> > Can anyone please shed some light on this? Thanks in advance.|||I'm tring to import the data from text file into Table and table have
the trigger. but it takes too long time

here is code

CREATE TRIGGER TR_TFACP200

ON TFACP200

FOR INSERT

AS

UPDATE TFACP200

SET DOCUMENT_DATE = LEFT(DDATE,2) + "/" + SUBSTRING(DDATE,3,2) + "/" +
RIGHT(DDATE,4)

WHERE ISDATE(LEFT(DDATE,2) + "/" + SUBSTRING(DDATE,3,2) + "/" +
RIGHT(DDATE,4)) = 1

AND DOCUMENT_DATE IS NULL

can any give me the solution, i thing this is happening boz. UPDATE
statement is fire on all records when we insert a single record.

I think if i use BEFORE INSERT trigger like ORACLE then it solve the
problem, but how i use the BEFORE INSERT trigger in SQL server

I'm using SQL Server 7.0, and INSTEAD OF option not available.

pls help me.

Milind

I am having trouble creating an INSTEAD OF trigger in SQL Server version
7.0 to replicate a BEFORE INSERT trigger from ORACLE.

Originally posted by Dtb

> I am having trouble creating an INSTEAD OF trigger in SQL Server to

> replicate a BEFORE UPDATE trigger from ORACLE.

> Here is a sample of the ORACLE BEFORE UPDATE trigger:

> CREATE TRIGGER myTRIGGER ON MYTABLE

> begin

> :new.DT := SYSDATE;

> if :new.NM is NULL then

> :new.NM := USER;

> end if;

> end myTRIGGER;

> It seems as though I have to jump through hoops in SQL Server AND I

> cannot come up with correct results.

> Here is a snippet from SQL SERVER (this is what I figured I needed to

> do after reading various articles,questions):

> CREATE TRIGGER myTRIGGER on THETABLE

> INSTEAD OF UPDATE

> AS

> SELECT * INTO #MYTABLE FROM INSERTED

> UPDATE #MYTABLE SET DT = GETDATE()

> UPDATE #MYTABLE SET NM = USER WHERE NM IS NULL

> UPDATE THETABLE

> SET

> DT = (SELECT DT FROM #MYTABLE),

> NM = (SELECT NM FROM #MYTABLE)

> WHERE THETABLE.ID = (SELECT ID FROM #MYTABLE)

Can anyone please shed some light on this? Thanks in advance.

--
Posted via http://dbforums.com|||Milind,

you are correct that the current problem is, that your trigger acts on
all rows of the table instead of just the inserted.

In the trigger context, all inserted rows are available in the virtual
table "inserted". (Lookup "triggers, inserted tables" in BOL for more
information).

Assuming "id" is the primary key of your table, you could use:

CREATE TRIGGER TR_TFACP200
ON TFACP200
FOR INSERT
AS

UPDATE TFACP200
SET DOCUMENT_DATE = LEFT(DDATE,2) + "/" + SUBSTRING(DDATE,3,2) + "/" +
RIGHT(DDATE,4)
WHERE ISDATE(LEFT(DDATE,2) + "/" + SUBSTRING(DDATE,3,2) + "/" +
RIGHT(DDATE,4)) = 1
AND DOCUMENT_DATE IS NULL
AND EXISTS (
SELECT 1
FROM inserted
WHERE inserted.id = TFACP200.id
)

Hope this helps,
Gert-Jan

Milind wrote:
> I'm tring to import the data from text file into Table and table have
> the trigger. but it takes too long time
> here is code
> CREATE TRIGGER TR_TFACP200
> ON TFACP200
> FOR INSERT
> AS
> UPDATE TFACP200
> SET DOCUMENT_DATE = LEFT(DDATE,2) + "/" + SUBSTRING(DDATE,3,2) + "/" +
> RIGHT(DDATE,4)
> WHERE ISDATE(LEFT(DDATE,2) + "/" + SUBSTRING(DDATE,3,2) + "/" +
> RIGHT(DDATE,4)) = 1
> AND DOCUMENT_DATE IS NULL
> can any give me the solution, i thing this is happening boz. UPDATE
> statement is fire on all records when we insert a single record.
> I think if i use BEFORE INSERT trigger like ORACLE then it solve the
> problem, but how i use the BEFORE INSERT trigger in SQL server
> I'm using SQL Server 7.0, and INSTEAD OF option not available.
> pls help me.
> Milind
> I am having trouble creating an INSTEAD OF trigger in SQL Server version
> 7.0 to replicate a BEFORE INSERT trigger from ORACLE.
> Originally posted by Dtb
> > I am having trouble creating an INSTEAD OF trigger in SQL Server to
> > replicate a BEFORE UPDATE trigger from ORACLE.
> > Here is a sample of the ORACLE BEFORE UPDATE trigger:
> > CREATE TRIGGER myTRIGGER ON MYTABLE
> > begin
> > :new.DT := SYSDATE;
> > if :new.NM is NULL then
> > :new.NM := USER;
> > end if;
> > end myTRIGGER;
> > It seems as though I have to jump through hoops in SQL Server AND I
> > cannot come up with correct results.
> > Here is a snippet from SQL SERVER (this is what I figured I needed to
> > do after reading various articles,questions):
> > CREATE TRIGGER myTRIGGER on THETABLE
> > INSTEAD OF UPDATE
> > AS
> > SELECT * INTO #MYTABLE FROM INSERTED
> > UPDATE #MYTABLE SET DT = GETDATE()
> > UPDATE #MYTABLE SET NM = USER WHERE NM IS NULL
> > UPDATE THETABLE
> > SET
> > DT = (SELECT DT FROM #MYTABLE),
> > NM = (SELECT NM FROM #MYTABLE)
> > WHERE THETABLE.ID = (SELECT ID FROM #MYTABLE)
> Can anyone please shed some light on this? Thanks in advance.
> --
> Posted via http://dbforums.com

Oracle and SQL query in one rdl

I am currently creating a query wherein all of the records returned
from an SQL Server table will have to be queried against an Oracle
database. The following algorithm describes how I intend to do the
report:
* Get the start and end dates (report parameters)
* Run the following SQL Server query:
select ID from SQL_server_table
where logtime >= startdate
and logtime <= enddate
* For each ID returned from the SQL server table
* Query the Oracle datebase
select info1, info2 from Oracle_table
where id = ID_from_SQL_server
* Report on the SQL_Server.ID, Oracle_info1, Oracle_info2
How should I do that in an SRS?
Thank you so much in advance!Read up on subreports.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<joychua97@.gmail.com> wrote in message
news:1143818223.627860.28200@.i39g2000cwa.googlegroups.com...
>I am currently creating a query wherein all of the records returned
> from an SQL Server table will have to be queried against an Oracle
> database. The following algorithm describes how I intend to do the
> report:
> * Get the start and end dates (report parameters)
> * Run the following SQL Server query:
> select ID from SQL_server_table
> where logtime >= startdate
> and logtime <= enddate
> * For each ID returned from the SQL server table
> * Query the Oracle datebase
> select info1, info2 from Oracle_table
> where id = ID_from_SQL_server
> * Report on the SQL_Server.ID, Oracle_info1, Oracle_info2
> How should I do that in an SRS?
> Thank you so much in advance!
>