How can I write that SORT depends on parameter, something like:
ORDER BY column1 case when @.param=0 then DESC else ASC end
Regards,SStandard SQL-92 does not allow you to use a function or expression in
an ORDER BY clause. The ORDER BY clause is part of a cursor and it can
only see the column names that appear in the SELECT clause list that
was used to build the result set. BP will now chime in that SQL-99
(officially called "a standard in progress" and not recognized by the
U.S. Government for actual use) does allow this.
But aside from this, there is the good programming practice of showing
the fields that are used for the sort to the user, usually on the left
side of each line since we read left to right.
The standard trick for picking a sorting order at run time is to use a
flag in CASE expression. If you want to sort on more than one column
and allow all possible combinations of sorting use one CASE per column:
SELECT
CASE @.flag_1
WHEN 'a' THEN CAST (a AS CHAR(n))
WHEN 'b' THEN CAST (b AS CHAR(n))
WHEN 'c' THEN CAST (c AS CHAR(n))
ELSE NULL END AS sort_1,
CASE @.flag_2
WHEN 'x' THEN CAST (x AS CHAR(n))
WHEN 'y' THEN CAST (y AS CHAR(n))
WHEN 'z' THEN CAST (z AS CHAR(n))
ELSE NULL END AS sort_2,
..
CASE @.flag_n
WHEN 'n1' THEN CAST (n1 AS CHAR(n))
WHEN 'n2' THEN CAST (n2 AS CHAR(n))
WHEN 'n3' THEN CAST (n3 AS CHAR(n))
ELSE NULL END AS sort_2,
FROM Foobar
WHERE ...
ORDER BY sort_1, sort_2, ...
More than one sort column and only a limited set of combinations then
use concatenation.
CASE @.flag_1
WHEN 'ab'
THEN CAST(a AS CHAR(n)) ||' ' || CAST(b AS CHAR(n))
WHEN 'ba'
THEN CAST(b AS CHAR(n)) ||' ' || CAST(a AS CHAR(n))
ELSE NULL END AS sort_1,
If you need ASC and DESC options, then use a combination of CASE and
ORDER BY
CASE @.flag_1
WHEN @.flag_1 = 'a' AND @.flag_1_ad = 'ASC'
THEN CAST (a AS CHAR(n))
WHEN @.flag_1 = 'b' AND @.flag_1_ad = 'ASC'
THEN CAST (b AS CHAR(n))
WHEN @.flag_1 = 'c' AND @.flag_1_ad = 'ASC'
THEN CAST (c AS CHAR(n))
ELSE NULL END AS sort_1_a,
CASE @.flag_1
WHEN @.flag_1 = 'a' AND @.flag_1_ad = 'DESC'
THEN CAST (a AS CHAR(n))
WHEN @.flag_1 = 'b' AND @.flag_1_ad = 'DESC'
THEN CAST (b AS CHAR(n))
WHEN @.flag_1 = 'c' AND @.flag_1_ad = 'DESC'
THEN CAST (c AS CHAR(n))
ELSE NULL END AS sort_1_d
. ORDER BY sort_1_a ASC, sort_1_d DESC
I have shown explicit CAST(<exp> AS CHAR(n)), but if the datatypes of
the THEN clause expressions were already the same, there would be no
reason to force the conversions.
You change the ELSE NULL clause to any constant of the appropriate
datatype, but it should be something useful to the reader.
A neater way of doing this is to use one column for each sorting option
so you do not have worry about CAST() operations.
SELECT ...
CASE WHEN @.flag = 'a' THEN a ELSE NULL END AS sort1,
CASE WHEN @.flag = 'b' THEN b ELSE NULL END AS sort2,
CASE WHEN @.flag = 'c' THEN c ELSE NULL END AS sort3
FROM Foobar
WHERE ...
ORDER BY sort1, sort2, sort3;|||hi,
probably this link should answer your question:
http://www.aspfaq.com/2501
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"simon" wrote:
> How can I write that SORT depends on parameter, something like:
> ORDER BY column1 case when @.param=0 then DESC else ASC end
> Regards,S
>
>sql
Showing posts with label write. Show all posts
Showing posts with label write. Show all posts
Wednesday, March 28, 2012
Monday, March 26, 2012
ORDER BY in a SP
I'm getting a little frustrated... I'm trying to write a stored proc... the
following code works fine on its own in Query Analyzer... when I put it into
my stored proc... SQL Server complains about the ORDERS BY clause.
SELECT TOP 1 UserID FROM Users
WHERE UserID > 50
ORDER BY UserID DESC
According to MSDN "The ORDER BY clause is invalid in views, inline
functions, derived tables, and subqueries, unless TOP is also specified."...
so I don't see why it isn't working.what is the Procedure code you are using. Looks like some minor typing error
It works at my end .
CREATE TABLE Users
(
User_ID int,
User_Name varchar(10)
)
GO
INSERT INTO Users
SELECT 1, 'Jon'
UNION
SELECT 10,'Mary'
UNION
SELECT 50,'Ron'
UNION
SELECT 60, 'Harry'
UNION
SELECT 80, 'Nancy'
GO
CREATE PROCEDURE usp_test
AS
SELECT TOP 1 * FROM Users
WHERE User_ID > 50
Order BY User_ID DESC
GO
Exec usp_test
GO
Cheers,
Siaj
"andrew" wrote:
> I'm getting a little frustrated... I'm trying to write a stored proc... th
e
> following code works fine on its own in Query Analyzer... when I put it in
to
> my stored proc... SQL Server complains about the ORDERS BY clause.
> SELECT TOP 1 UserID FROM Users
> WHERE UserID > 50
> ORDER BY UserID DESC
> According to MSDN "The ORDER BY clause is invalid in views, inline
> functions, derived tables, and subqueries, unless TOP is also specified.".
.
> so I don't see why it isn't working.|||I figured it out... I had the SELECT statement completely enclosed in ( ) -
which seemed to work fine without an ORDER BY... but when I added in the
ORDER BY... which was still in between the ( )... suddenly there was a
problem... YAY computers.
"siaj" wrote:
> what is the Procedure code you are using. Looks like some minor typing err
or
> It works at my end .
> CREATE TABLE Users
> (
> User_ID int,
> User_Name varchar(10)
> )
> GO
> INSERT INTO Users
> SELECT 1, 'Jon'
> UNION
> SELECT 10,'Mary'
> UNION
> SELECT 50,'Ron'
> UNION
> SELECT 60, 'Harry'
> UNION
> SELECT 80, 'Nancy'
> GO
> CREATE PROCEDURE usp_test
> AS
> SELECT TOP 1 * FROM Users
> WHERE User_ID > 50
> Order BY User_ID DESC
> GO
> Exec usp_test
> GO
> Cheers,
> Siaj
> "andrew" wrote:
>
following code works fine on its own in Query Analyzer... when I put it into
my stored proc... SQL Server complains about the ORDERS BY clause.
SELECT TOP 1 UserID FROM Users
WHERE UserID > 50
ORDER BY UserID DESC
According to MSDN "The ORDER BY clause is invalid in views, inline
functions, derived tables, and subqueries, unless TOP is also specified."...
so I don't see why it isn't working.what is the Procedure code you are using. Looks like some minor typing error
It works at my end .
CREATE TABLE Users
(
User_ID int,
User_Name varchar(10)
)
GO
INSERT INTO Users
SELECT 1, 'Jon'
UNION
SELECT 10,'Mary'
UNION
SELECT 50,'Ron'
UNION
SELECT 60, 'Harry'
UNION
SELECT 80, 'Nancy'
GO
CREATE PROCEDURE usp_test
AS
SELECT TOP 1 * FROM Users
WHERE User_ID > 50
Order BY User_ID DESC
GO
Exec usp_test
GO
Cheers,
Siaj
"andrew" wrote:
> I'm getting a little frustrated... I'm trying to write a stored proc... th
e
> following code works fine on its own in Query Analyzer... when I put it in
to
> my stored proc... SQL Server complains about the ORDERS BY clause.
> SELECT TOP 1 UserID FROM Users
> WHERE UserID > 50
> ORDER BY UserID DESC
> According to MSDN "The ORDER BY clause is invalid in views, inline
> functions, derived tables, and subqueries, unless TOP is also specified.".
.
> so I don't see why it isn't working.|||I figured it out... I had the SELECT statement completely enclosed in ( ) -
which seemed to work fine without an ORDER BY... but when I added in the
ORDER BY... which was still in between the ( )... suddenly there was a
problem... YAY computers.
"siaj" wrote:
> what is the Procedure code you are using. Looks like some minor typing err
or
> It works at my end .
> CREATE TABLE Users
> (
> User_ID int,
> User_Name varchar(10)
> )
> GO
> INSERT INTO Users
> SELECT 1, 'Jon'
> UNION
> SELECT 10,'Mary'
> UNION
> SELECT 50,'Ron'
> UNION
> SELECT 60, 'Harry'
> UNION
> SELECT 80, 'Nancy'
> GO
> CREATE PROCEDURE usp_test
> AS
> SELECT TOP 1 * FROM Users
> WHERE User_ID > 50
> Order BY User_ID DESC
> GO
> Exec usp_test
> GO
> Cheers,
> Siaj
> "andrew" wrote:
>
Monday, March 12, 2012
Oracle Stored Procedure Syntax
I'd like to write a report with a data set that calls an Oracle data source
for its result set. I have a package with a procedure that returns a ref
cursor as an out variable. What is the syntax for calling this procedure
from the Report Designer? Does the out variable with the cursor need to be
mapped to anything? Are input parameters on the procedure anything other
than regular @.parameter references?
Thanks!John,
The sql syntax is identical to the that used for MS SQL Server, except for
the @.. Switch it to a : (colon)
and the parameters will work just fine, example:
Select Blah
From tblBlah
where Blah.tblBlah = :param
I like to use the IN Keyword. If you set your parameters as multi-select,
the parameters are passed in as an string seperated by commas, so your Where
clause would look like this:
Where Blah.tblBlah IN (:Param)
Hope this helps,
rwiethorn
"John W at Sungard HE" wrote:
> I'd like to write a report with a data set that calls an Oracle data source
> for its result set. I have a package with a procedure that returns a ref
> cursor as an out variable. What is the syntax for calling this procedure
> from the Report Designer? Does the out variable with the cursor need to be
> mapped to anything? Are input parameters on the procedure anything other
> than regular @.parameter references?
> Thanks!|||Hi,
Continuing with John's question, I want to know, What is the syntax for
calling the oracle stored procedure from the Report Designer? My procedure is
returning a ref cursor as an out variable.
Regards,
Aruna
"rwiethorn" wrote:
> John,
> The sql syntax is identical to the that used for MS SQL Server, except for
> the @.. Switch it to a : (colon)
> and the parameters will work just fine, example:
> Select Blah
> From tblBlah
> where Blah.tblBlah = :param
> I like to use the IN Keyword. If you set your parameters as multi-select,
> the parameters are passed in as an string seperated by commas, so your Where
> clause would look like this:
> Where Blah.tblBlah IN (:Param)
> Hope this helps,
> rwiethorn
>
> "John W at Sungard HE" wrote:
> > I'd like to write a report with a data set that calls an Oracle data source
> > for its result set. I have a package with a procedure that returns a ref
> > cursor as an out variable. What is the syntax for calling this procedure
> > from the Report Designer? Does the out variable with the cursor need to be
> > mapped to anything? Are input parameters on the procedure anything other
> > than regular @.parameter references?
> >
> > Thanks!|||Thanks for the response - the bit with the parameters defintely helps. Do
you have an example that references a stored procedure instead of a select
statement? For example, do I have to wrap the procedure call in a PL/SQL
block like:
BEGIN
CallSomeProcedure( :InputParm1, :InputParm2, :OutputParm )
END
or is it sufficient to call the procedure without the block like:
CallSomeProcedure( :InputParm1, :InputParm2, :OutputParm )
And is the above parameter syntax going to work? What about the output
parameter that accepts the cursor back from the stored procedure? Is that
just set up as a report parameter like any other, even though it's an output
parameter? Does it matter where the output parameter is placed in the call
list, i.e. does it need to appear either first or last?
Thanks!
JW
"rwiethorn" wrote:
> John,
> The sql syntax is identical to the that used for MS SQL Server, except for
> the @.. Switch it to a : (colon)
> and the parameters will work just fine, example:
> Select Blah
> From tblBlah
> where Blah.tblBlah = :param
> I like to use the IN Keyword. If you set your parameters as multi-select,
> the parameters are passed in as an string seperated by commas, so your Where
> clause would look like this:
> Where Blah.tblBlah IN (:Param)
> Hope this helps,
> rwiethorn
>
> "John W at Sungard HE" wrote:
> > I'd like to write a report with a data set that calls an Oracle data source
> > for its result set. I have a package with a procedure that returns a ref
> > cursor as an out variable. What is the syntax for calling this procedure
> > from the Report Designer? Does the out variable with the cursor need to be
> > mapped to anything? Are input parameters on the procedure anything other
> > than regular @.parameter references?
> >
> > Thanks!|||change the command type to stored procedure.
The syntax then looks like this
packagename.procedurename
no need to put any parameters in here.
John W at Sungard HE wrote:
> Thanks for the response - the bit with the parameters defintely helps. Do
> you have an example that references a stored procedure instead of a select
> statement? For example, do I have to wrap the procedure call in a PL/SQL
> block like:
> BEGIN
> CallSomeProcedure( :InputParm1, :InputParm2, :OutputParm )
> END
> or is it sufficient to call the procedure without the block like:
> CallSomeProcedure( :InputParm1, :InputParm2, :OutputParm )
> And is the above parameter syntax going to work? What about the output
> parameter that accepts the cursor back from the stored procedure? Is that
> just set up as a report parameter like any other, even though it's an output
> parameter? Does it matter where the output parameter is placed in the call
> list, i.e. does it need to appear either first or last?
> Thanks!
> JW
> "rwiethorn" wrote:
> > John,
> > The sql syntax is identical to the that used for MS SQL Server, except for
> > the @.. Switch it to a : (colon)
> > and the parameters will work just fine, example:
> > Select Blah
> > From tblBlah
> > where Blah.tblBlah = :param
> >
> > I like to use the IN Keyword. If you set your parameters as multi-select,
> > the parameters are passed in as an string seperated by commas, so your Where
> > clause would look like this:
> > Where Blah.tblBlah IN (:Param)
> >
> > Hope this helps,
> > rwiethorn
> >
> >
> > "John W at Sungard HE" wrote:
> >
> > > I'd like to write a report with a data set that calls an Oracle data source
> > > for its result set. I have a package with a procedure that returns a ref
> > > cursor as an out variable. What is the syntax for calling this procedure
> > > from the Report Designer? Does the out variable with the cursor need to be
> > > mapped to anything? Are input parameters on the procedure anything other
> > > than regular @.parameter references?
> > >
> > > Thanks!|||John,
I'm a MS SQL Server guy myself, so I'm limited on the Oracle. I have a
Oracle DBA that makes tables for me when I define my sql statement. I've not
had the need to do any processing, I'm been only needing simple selects with
groupings.
>What about the output
> parameter that accepts the cursor back from the stored procedure?
I think you can do with out it, the system may just 'swallow' the return
value (ie a 0 or 1) indicating a success or failure.
>Does it matter where the output parameter is placed in the call
> list, i.e. does it need to appear either first or last?
Yes, parameter order does matter when your calling stored procs, you need to
match the order and the data type.
I hope this helps,
rwiethorn
"John W at Sungard HE" wrote:
> Thanks for the response - the bit with the parameters defintely helps. Do
> you have an example that references a stored procedure instead of a select
> statement? For example, do I have to wrap the procedure call in a PL/SQL
> block like:
> BEGIN
> CallSomeProcedure( :InputParm1, :InputParm2, :OutputParm )
> END
> or is it sufficient to call the procedure without the block like:
> CallSomeProcedure( :InputParm1, :InputParm2, :OutputParm )
> And is the above parameter syntax going to work? What about the output
> parameter that accepts the cursor back from the stored procedure? Is that
> just set up as a report parameter like any other, even though it's an output
> parameter? Does it matter where the output parameter is placed in the call
> list, i.e. does it need to appear either first or last?
> Thanks!
> JW
> "rwiethorn" wrote:
> > John,
> > The sql syntax is identical to the that used for MS SQL Server, except for
> > the @.. Switch it to a : (colon)
> > and the parameters will work just fine, example:
> > Select Blah
> > From tblBlah
> > where Blah.tblBlah = :param
> >
> > I like to use the IN Keyword. If you set your parameters as multi-select,
> > the parameters are passed in as an string seperated by commas, so your Where
> > clause would look like this:
> > Where Blah.tblBlah IN (:Param)
> >
> > Hope this helps,
> > rwiethorn
> >
> >
> > "John W at Sungard HE" wrote:
> >
> > > I'd like to write a report with a data set that calls an Oracle data source
> > > for its result set. I have a package with a procedure that returns a ref
> > > cursor as an out variable. What is the syntax for calling this procedure
> > > from the Report Designer? Does the out variable with the cursor need to be
> > > mapped to anything? Are input parameters on the procedure anything other
> > > than regular @.parameter references?
> > >
> > > Thanks!|||Sorry, I did not get it. How I can send an OUT parameter (CURSOR) and use it
to generate report?
Thank you
"rwiethorn" wrote:
> John,
> The sql syntax is identical to the that used for MS SQL Server, except for
> the @.. Switch it to a : (colon)
> and the parameters will work just fine, example:
> Select Blah
> From tblBlah
> where Blah.tblBlah = :param
> I like to use the IN Keyword. If you set your parameters as multi-select,
> the parameters are passed in as an string seperated by commas, so your Where
> clause would look like this:
> Where Blah.tblBlah IN (:Param)
> Hope this helps,
> rwiethorn
>
> "John W at Sungard HE" wrote:
> > I'd like to write a report with a data set that calls an Oracle data source
> > for its result set. I have a package with a procedure that returns a ref
> > cursor as an out variable. What is the syntax for calling this procedure
> > from the Report Designer? Does the out variable with the cursor need to be
> > mapped to anything? Are input parameters on the procedure anything other
> > than regular @.parameter references?
> >
> > Thanks!|||Sorry, can I have more details, please. Like, what driver did you use, did
you use parameters Tab to set parameter and so on.
Thank you
"gene.furibondo@.gmail.com" wrote:
> change the command type to stored procedure.
> The syntax then looks like this
> packagename.procedurename
> no need to put any parameters in here.
> John W at Sungard HE wrote:
> > Thanks for the response - the bit with the parameters defintely helps. Do
> > you have an example that references a stored procedure instead of a select
> > statement? For example, do I have to wrap the procedure call in a PL/SQL
> > block like:
> >
> > BEGIN
> > CallSomeProcedure( :InputParm1, :InputParm2, :OutputParm )
> > END
> >
> > or is it sufficient to call the procedure without the block like:
> >
> > CallSomeProcedure( :InputParm1, :InputParm2, :OutputParm )
> >
> > And is the above parameter syntax going to work? What about the output
> > parameter that accepts the cursor back from the stored procedure? Is that
> > just set up as a report parameter like any other, even though it's an output
> > parameter? Does it matter where the output parameter is placed in the call
> > list, i.e. does it need to appear either first or last?
> >
> > Thanks!
> >
> > JW
> >
> > "rwiethorn" wrote:
> >
> > > John,
> > > The sql syntax is identical to the that used for MS SQL Server, except for
> > > the @.. Switch it to a : (colon)
> > > and the parameters will work just fine, example:
> > > Select Blah
> > > From tblBlah
> > > where Blah.tblBlah = :param
> > >
> > > I like to use the IN Keyword. If you set your parameters as multi-select,
> > > the parameters are passed in as an string seperated by commas, so your Where
> > > clause would look like this:
> > > Where Blah.tblBlah IN (:Param)
> > >
> > > Hope this helps,
> > > rwiethorn
> > >
> > >
> > > "John W at Sungard HE" wrote:
> > >
> > > > I'd like to write a report with a data set that calls an Oracle data source
> > > > for its result set. I have a package with a procedure that returns a ref
> > > > cursor as an out variable. What is the syntax for calling this procedure
> > > > from the Report Designer? Does the out variable with the cursor need to be
> > > > mapped to anything? Are input parameters on the procedure anything other
> > > > than regular @.parameter references?
> > > >
> > > > Thanks!
>
for its result set. I have a package with a procedure that returns a ref
cursor as an out variable. What is the syntax for calling this procedure
from the Report Designer? Does the out variable with the cursor need to be
mapped to anything? Are input parameters on the procedure anything other
than regular @.parameter references?
Thanks!John,
The sql syntax is identical to the that used for MS SQL Server, except for
the @.. Switch it to a : (colon)
and the parameters will work just fine, example:
Select Blah
From tblBlah
where Blah.tblBlah = :param
I like to use the IN Keyword. If you set your parameters as multi-select,
the parameters are passed in as an string seperated by commas, so your Where
clause would look like this:
Where Blah.tblBlah IN (:Param)
Hope this helps,
rwiethorn
"John W at Sungard HE" wrote:
> I'd like to write a report with a data set that calls an Oracle data source
> for its result set. I have a package with a procedure that returns a ref
> cursor as an out variable. What is the syntax for calling this procedure
> from the Report Designer? Does the out variable with the cursor need to be
> mapped to anything? Are input parameters on the procedure anything other
> than regular @.parameter references?
> Thanks!|||Hi,
Continuing with John's question, I want to know, What is the syntax for
calling the oracle stored procedure from the Report Designer? My procedure is
returning a ref cursor as an out variable.
Regards,
Aruna
"rwiethorn" wrote:
> John,
> The sql syntax is identical to the that used for MS SQL Server, except for
> the @.. Switch it to a : (colon)
> and the parameters will work just fine, example:
> Select Blah
> From tblBlah
> where Blah.tblBlah = :param
> I like to use the IN Keyword. If you set your parameters as multi-select,
> the parameters are passed in as an string seperated by commas, so your Where
> clause would look like this:
> Where Blah.tblBlah IN (:Param)
> Hope this helps,
> rwiethorn
>
> "John W at Sungard HE" wrote:
> > I'd like to write a report with a data set that calls an Oracle data source
> > for its result set. I have a package with a procedure that returns a ref
> > cursor as an out variable. What is the syntax for calling this procedure
> > from the Report Designer? Does the out variable with the cursor need to be
> > mapped to anything? Are input parameters on the procedure anything other
> > than regular @.parameter references?
> >
> > Thanks!|||Thanks for the response - the bit with the parameters defintely helps. Do
you have an example that references a stored procedure instead of a select
statement? For example, do I have to wrap the procedure call in a PL/SQL
block like:
BEGIN
CallSomeProcedure( :InputParm1, :InputParm2, :OutputParm )
END
or is it sufficient to call the procedure without the block like:
CallSomeProcedure( :InputParm1, :InputParm2, :OutputParm )
And is the above parameter syntax going to work? What about the output
parameter that accepts the cursor back from the stored procedure? Is that
just set up as a report parameter like any other, even though it's an output
parameter? Does it matter where the output parameter is placed in the call
list, i.e. does it need to appear either first or last?
Thanks!
JW
"rwiethorn" wrote:
> John,
> The sql syntax is identical to the that used for MS SQL Server, except for
> the @.. Switch it to a : (colon)
> and the parameters will work just fine, example:
> Select Blah
> From tblBlah
> where Blah.tblBlah = :param
> I like to use the IN Keyword. If you set your parameters as multi-select,
> the parameters are passed in as an string seperated by commas, so your Where
> clause would look like this:
> Where Blah.tblBlah IN (:Param)
> Hope this helps,
> rwiethorn
>
> "John W at Sungard HE" wrote:
> > I'd like to write a report with a data set that calls an Oracle data source
> > for its result set. I have a package with a procedure that returns a ref
> > cursor as an out variable. What is the syntax for calling this procedure
> > from the Report Designer? Does the out variable with the cursor need to be
> > mapped to anything? Are input parameters on the procedure anything other
> > than regular @.parameter references?
> >
> > Thanks!|||change the command type to stored procedure.
The syntax then looks like this
packagename.procedurename
no need to put any parameters in here.
John W at Sungard HE wrote:
> Thanks for the response - the bit with the parameters defintely helps. Do
> you have an example that references a stored procedure instead of a select
> statement? For example, do I have to wrap the procedure call in a PL/SQL
> block like:
> BEGIN
> CallSomeProcedure( :InputParm1, :InputParm2, :OutputParm )
> END
> or is it sufficient to call the procedure without the block like:
> CallSomeProcedure( :InputParm1, :InputParm2, :OutputParm )
> And is the above parameter syntax going to work? What about the output
> parameter that accepts the cursor back from the stored procedure? Is that
> just set up as a report parameter like any other, even though it's an output
> parameter? Does it matter where the output parameter is placed in the call
> list, i.e. does it need to appear either first or last?
> Thanks!
> JW
> "rwiethorn" wrote:
> > John,
> > The sql syntax is identical to the that used for MS SQL Server, except for
> > the @.. Switch it to a : (colon)
> > and the parameters will work just fine, example:
> > Select Blah
> > From tblBlah
> > where Blah.tblBlah = :param
> >
> > I like to use the IN Keyword. If you set your parameters as multi-select,
> > the parameters are passed in as an string seperated by commas, so your Where
> > clause would look like this:
> > Where Blah.tblBlah IN (:Param)
> >
> > Hope this helps,
> > rwiethorn
> >
> >
> > "John W at Sungard HE" wrote:
> >
> > > I'd like to write a report with a data set that calls an Oracle data source
> > > for its result set. I have a package with a procedure that returns a ref
> > > cursor as an out variable. What is the syntax for calling this procedure
> > > from the Report Designer? Does the out variable with the cursor need to be
> > > mapped to anything? Are input parameters on the procedure anything other
> > > than regular @.parameter references?
> > >
> > > Thanks!|||John,
I'm a MS SQL Server guy myself, so I'm limited on the Oracle. I have a
Oracle DBA that makes tables for me when I define my sql statement. I've not
had the need to do any processing, I'm been only needing simple selects with
groupings.
>What about the output
> parameter that accepts the cursor back from the stored procedure?
I think you can do with out it, the system may just 'swallow' the return
value (ie a 0 or 1) indicating a success or failure.
>Does it matter where the output parameter is placed in the call
> list, i.e. does it need to appear either first or last?
Yes, parameter order does matter when your calling stored procs, you need to
match the order and the data type.
I hope this helps,
rwiethorn
"John W at Sungard HE" wrote:
> Thanks for the response - the bit with the parameters defintely helps. Do
> you have an example that references a stored procedure instead of a select
> statement? For example, do I have to wrap the procedure call in a PL/SQL
> block like:
> BEGIN
> CallSomeProcedure( :InputParm1, :InputParm2, :OutputParm )
> END
> or is it sufficient to call the procedure without the block like:
> CallSomeProcedure( :InputParm1, :InputParm2, :OutputParm )
> And is the above parameter syntax going to work? What about the output
> parameter that accepts the cursor back from the stored procedure? Is that
> just set up as a report parameter like any other, even though it's an output
> parameter? Does it matter where the output parameter is placed in the call
> list, i.e. does it need to appear either first or last?
> Thanks!
> JW
> "rwiethorn" wrote:
> > John,
> > The sql syntax is identical to the that used for MS SQL Server, except for
> > the @.. Switch it to a : (colon)
> > and the parameters will work just fine, example:
> > Select Blah
> > From tblBlah
> > where Blah.tblBlah = :param
> >
> > I like to use the IN Keyword. If you set your parameters as multi-select,
> > the parameters are passed in as an string seperated by commas, so your Where
> > clause would look like this:
> > Where Blah.tblBlah IN (:Param)
> >
> > Hope this helps,
> > rwiethorn
> >
> >
> > "John W at Sungard HE" wrote:
> >
> > > I'd like to write a report with a data set that calls an Oracle data source
> > > for its result set. I have a package with a procedure that returns a ref
> > > cursor as an out variable. What is the syntax for calling this procedure
> > > from the Report Designer? Does the out variable with the cursor need to be
> > > mapped to anything? Are input parameters on the procedure anything other
> > > than regular @.parameter references?
> > >
> > > Thanks!|||Sorry, I did not get it. How I can send an OUT parameter (CURSOR) and use it
to generate report?
Thank you
"rwiethorn" wrote:
> John,
> The sql syntax is identical to the that used for MS SQL Server, except for
> the @.. Switch it to a : (colon)
> and the parameters will work just fine, example:
> Select Blah
> From tblBlah
> where Blah.tblBlah = :param
> I like to use the IN Keyword. If you set your parameters as multi-select,
> the parameters are passed in as an string seperated by commas, so your Where
> clause would look like this:
> Where Blah.tblBlah IN (:Param)
> Hope this helps,
> rwiethorn
>
> "John W at Sungard HE" wrote:
> > I'd like to write a report with a data set that calls an Oracle data source
> > for its result set. I have a package with a procedure that returns a ref
> > cursor as an out variable. What is the syntax for calling this procedure
> > from the Report Designer? Does the out variable with the cursor need to be
> > mapped to anything? Are input parameters on the procedure anything other
> > than regular @.parameter references?
> >
> > Thanks!|||Sorry, can I have more details, please. Like, what driver did you use, did
you use parameters Tab to set parameter and so on.
Thank you
"gene.furibondo@.gmail.com" wrote:
> change the command type to stored procedure.
> The syntax then looks like this
> packagename.procedurename
> no need to put any parameters in here.
> John W at Sungard HE wrote:
> > Thanks for the response - the bit with the parameters defintely helps. Do
> > you have an example that references a stored procedure instead of a select
> > statement? For example, do I have to wrap the procedure call in a PL/SQL
> > block like:
> >
> > BEGIN
> > CallSomeProcedure( :InputParm1, :InputParm2, :OutputParm )
> > END
> >
> > or is it sufficient to call the procedure without the block like:
> >
> > CallSomeProcedure( :InputParm1, :InputParm2, :OutputParm )
> >
> > And is the above parameter syntax going to work? What about the output
> > parameter that accepts the cursor back from the stored procedure? Is that
> > just set up as a report parameter like any other, even though it's an output
> > parameter? Does it matter where the output parameter is placed in the call
> > list, i.e. does it need to appear either first or last?
> >
> > Thanks!
> >
> > JW
> >
> > "rwiethorn" wrote:
> >
> > > John,
> > > The sql syntax is identical to the that used for MS SQL Server, except for
> > > the @.. Switch it to a : (colon)
> > > and the parameters will work just fine, example:
> > > Select Blah
> > > From tblBlah
> > > where Blah.tblBlah = :param
> > >
> > > I like to use the IN Keyword. If you set your parameters as multi-select,
> > > the parameters are passed in as an string seperated by commas, so your Where
> > > clause would look like this:
> > > Where Blah.tblBlah IN (:Param)
> > >
> > > Hope this helps,
> > > rwiethorn
> > >
> > >
> > > "John W at Sungard HE" wrote:
> > >
> > > > I'd like to write a report with a data set that calls an Oracle data source
> > > > for its result set. I have a package with a procedure that returns a ref
> > > > cursor as an out variable. What is the syntax for calling this procedure
> > > > from the Report Designer? Does the out variable with the cursor need to be
> > > > mapped to anything? Are input parameters on the procedure anything other
> > > > than regular @.parameter references?
> > > >
> > > > Thanks!
>
Friday, March 9, 2012
Oracle query in SQL Server
Hi,
I want to write this query in sql server. Please help me, it is very urgent :(
Any help will be appreciated.
SELECT VALID_ITEM.PC_CODE,PC_TITLE,VALID_ITEM.DB_CONTRACT ,
valid_item.ITEM_NO,DESCPT,UNITS,CONTQ,
CONTR_PRICE ,nz(QTD,0) as qtd,ind_ovr_und,
nz(QTD,0) - NVL(QUANTITY,0) as QUANT_PREV,
Nz(QUANTITY,0) as quant_rev,
round(NVL(quantity,0)*nvl(CONTR_PRICE,0),2) as QTD_VAL,
AMT_PAID_ITEM,AMT_RET_ITEM
FROM VALID_ITEM, cqe_item,CONTRACT_PC
WHERE valid_item.db_contract = contract_pc.db_contract
and valid_item.pc_code = contract_pc.pc_code
and valid_item.db_contract = cqe_item.db_contract (+)
and cqe_item.cqe_numb (+) = :EST_NO
and valid_item.item_no = cqe_item.item_no (+)
and valid_item.pc_code = cqe_item.pc_code (+)
order by valid_item.item_noHow about
SELECT i.PC_CODE
, PC_TITLE
, i.DB_CONTRACT
, i.ITEM_NO
, DESCPT
, UNITS
, CONTQ
, CONTR_PRICE
, ISNULL(QTD,0) as qtd
, ind_ovr_und
, ISNULL(QTD,0) - ISNULL(QUANTITY,0) as QUANT_PREV
, Nz(QUANTITY,0) as quant_rev
, ROUNDISNULL(quantity,0)*ISNULL(CONTR_PRICE,0),2) as QTD_VAL
, AMT_PAID_ITEM
, AMT_RET_ITEM
FROM VALID_ITEM v
LEFT JOIN cqe_item i
ON v.db_contract = i.db_contract
AND v.item_no = i.item_no
AND v.pc_code = i.pc_code
LEFT JOIN CONTRACT_PC c
ON v.db_contract = contract_pc.db_contract
AND v.pc_code = contract_pc.pc_code
WHERE i.cqe_numb = @.EST_NO
ORDER BY i.item_no|||Hi Brett,
It worked fine but it is not extracting the same rows as Oracle code.? Data is same...what is possible reason?
SELECT i.PC_CODE
, PC_TITLE
, i.DB_CONTRACT
, i.ITEM_NO
, DESCPT
, UNITS
, CONTQ
, CONTR_PRICE
, ISNULL(QTD,0) as qtd
, ind_ovr_und
, ISNULL(QTD,0) - ISNULL(QUANTITY,0) as QUANT_PREV
, ISNULL(QUANTITY,0) as quant_rev
, ROUND (ISNULL(quantity,0)*ISNULL(CONTR_PRICE,0),2) as QTD_VAL
, AMT_PAID_ITEM
, AMT_RET_ITEM
FROM VALID_ITEM as v
LEFT JOIN cqe_item as i
ON v.db_contract = i.db_contract
AND v.item_no = i.item_no
AND v.pc_code = i.pc_code
LEFT JOIN CONTRACT_PC as c
ON v.db_contract = c.db_contract
AND v.pc_code = c.pc_code
WHERE i.cqe_numb = 58 --gave the value
and i.db_contract =26999 --add one line
ORDER BY i.item_no|||Well you added another predicate...db_Contract_num...
But I don't know how you can have an outter join to a variable...
Like this
and cqe_item.cqe_numb (+) = :EST_NO|||Brett,
I got the problem...problem is with this line of code that you probably missed to translate i.e.
cqe_item.cqe_numb (+) = :EST_NO
I think you did not give solution of right join here? Am I right?
Thanks for you time..|||I guess you missed me pointing that out...
How do you have a right join to a variable?
What does that even mean?
An outter join is between tables
Is that meant to accomodate something like...
ISNULL(cqe_item.cqe_numb,@.EST_NO) = @.EST_NO
??????????????|||Hi Brett,
in table cqe_numb can be 1,2,3...scenerio is join on values ...am I right?
But this query is working perfect with oracle... :( ...:( ...
cqe_item.cqe_numb (+) = :EST_NO
SQL> desc cqe_item
Name Null? Type
---------- --- --
DB_CONTRACT NOT NULL NUMBER(6)
=======================================
CQE_NUMB NOT NULL NUMBER(3) ====right here
=======================================
PC_CODE NOT NULL NUMBER(2)
ITEM_NO NOT NULL VARCHAR2(7)
QUANTITY NUMBER(11,3)
AMT_PAID_ITEM NUMBER(11,2)
AMT_RET_ITEM NUMBER(10,2)
QTD_ITEM NUMBER(11,3)
FY_ITEM NUMBER(4)|||I have no idea...
Anyone heard of a right join to a variable?
I want to write this query in sql server. Please help me, it is very urgent :(
Any help will be appreciated.
SELECT VALID_ITEM.PC_CODE,PC_TITLE,VALID_ITEM.DB_CONTRACT ,
valid_item.ITEM_NO,DESCPT,UNITS,CONTQ,
CONTR_PRICE ,nz(QTD,0) as qtd,ind_ovr_und,
nz(QTD,0) - NVL(QUANTITY,0) as QUANT_PREV,
Nz(QUANTITY,0) as quant_rev,
round(NVL(quantity,0)*nvl(CONTR_PRICE,0),2) as QTD_VAL,
AMT_PAID_ITEM,AMT_RET_ITEM
FROM VALID_ITEM, cqe_item,CONTRACT_PC
WHERE valid_item.db_contract = contract_pc.db_contract
and valid_item.pc_code = contract_pc.pc_code
and valid_item.db_contract = cqe_item.db_contract (+)
and cqe_item.cqe_numb (+) = :EST_NO
and valid_item.item_no = cqe_item.item_no (+)
and valid_item.pc_code = cqe_item.pc_code (+)
order by valid_item.item_noHow about
SELECT i.PC_CODE
, PC_TITLE
, i.DB_CONTRACT
, i.ITEM_NO
, DESCPT
, UNITS
, CONTQ
, CONTR_PRICE
, ISNULL(QTD,0) as qtd
, ind_ovr_und
, ISNULL(QTD,0) - ISNULL(QUANTITY,0) as QUANT_PREV
, Nz(QUANTITY,0) as quant_rev
, ROUNDISNULL(quantity,0)*ISNULL(CONTR_PRICE,0),2) as QTD_VAL
, AMT_PAID_ITEM
, AMT_RET_ITEM
FROM VALID_ITEM v
LEFT JOIN cqe_item i
ON v.db_contract = i.db_contract
AND v.item_no = i.item_no
AND v.pc_code = i.pc_code
LEFT JOIN CONTRACT_PC c
ON v.db_contract = contract_pc.db_contract
AND v.pc_code = contract_pc.pc_code
WHERE i.cqe_numb = @.EST_NO
ORDER BY i.item_no|||Hi Brett,
It worked fine but it is not extracting the same rows as Oracle code.? Data is same...what is possible reason?
SELECT i.PC_CODE
, PC_TITLE
, i.DB_CONTRACT
, i.ITEM_NO
, DESCPT
, UNITS
, CONTQ
, CONTR_PRICE
, ISNULL(QTD,0) as qtd
, ind_ovr_und
, ISNULL(QTD,0) - ISNULL(QUANTITY,0) as QUANT_PREV
, ISNULL(QUANTITY,0) as quant_rev
, ROUND (ISNULL(quantity,0)*ISNULL(CONTR_PRICE,0),2) as QTD_VAL
, AMT_PAID_ITEM
, AMT_RET_ITEM
FROM VALID_ITEM as v
LEFT JOIN cqe_item as i
ON v.db_contract = i.db_contract
AND v.item_no = i.item_no
AND v.pc_code = i.pc_code
LEFT JOIN CONTRACT_PC as c
ON v.db_contract = c.db_contract
AND v.pc_code = c.pc_code
WHERE i.cqe_numb = 58 --gave the value
and i.db_contract =26999 --add one line
ORDER BY i.item_no|||Well you added another predicate...db_Contract_num...
But I don't know how you can have an outter join to a variable...
Like this
and cqe_item.cqe_numb (+) = :EST_NO|||Brett,
I got the problem...problem is with this line of code that you probably missed to translate i.e.
cqe_item.cqe_numb (+) = :EST_NO
I think you did not give solution of right join here? Am I right?
Thanks for you time..|||I guess you missed me pointing that out...
How do you have a right join to a variable?
What does that even mean?
An outter join is between tables
Is that meant to accomodate something like...
ISNULL(cqe_item.cqe_numb,@.EST_NO) = @.EST_NO
??????????????|||Hi Brett,
in table cqe_numb can be 1,2,3...scenerio is join on values ...am I right?
But this query is working perfect with oracle... :( ...:( ...
cqe_item.cqe_numb (+) = :EST_NO
SQL> desc cqe_item
Name Null? Type
---------- --- --
DB_CONTRACT NOT NULL NUMBER(6)
=======================================
CQE_NUMB NOT NULL NUMBER(3) ====right here
=======================================
PC_CODE NOT NULL NUMBER(2)
ITEM_NO NOT NULL VARCHAR2(7)
QUANTITY NUMBER(11,3)
AMT_PAID_ITEM NUMBER(11,2)
AMT_RET_ITEM NUMBER(10,2)
QTD_ITEM NUMBER(11,3)
FY_ITEM NUMBER(4)|||I have no idea...
Anyone heard of a right join to a variable?
oracle pl/sql multiple spool help required
Now Im not an expert or anything with SQL so bear with me...
I have 3 seperate queries that each use the spool command to write to a file on the server.
e.g. i use the following construct in each of the three sql files:
set blah
set blah
spool /blah/blah.rpt
script
spool off
I wish to put these three queries into one script. Can I use the spool command three times in the one file? i.e. have spool then spool off, three times in one *.sql file?
I have read a couple of posts on pl/sql spooling and people mention utl_file, but I have no idea what this is and even if i have it...
Any help would be great. Thanks.Yes, you can. For example,
spool a1.txt
select count(*) from tab;
spool off;
spool a2.txt
select sysdate from dual;
spool off;
spool a3.txt
select 'x' dummy from dual;
spool off;
will generate 3 .txt files. However, I can't figure out why didn't you try it yourself ...|||I did try it and it didnt work the way I expected...
I have the three spools and also I have a title for each of the three files:
e.g.
ttitle 'Thanet ** Items made MISSING between 7 and 14 days ago' skip 2
I have one of these fore each script [different txt of course]. I have found that if the query returns no results it will not print the title to the file and so i have an emty file. If there are some results from on of the queries then the title does display.
I was wanting to make sure that I was using the spool correctly so thats why i asked.|||Perhaps this helps ... if you include a "dummy" query into every "spool block", you won't get an empty file even though your "real" query returns no records.
SPOOL a1.txt
TTITLE 'First top title' skip 2
BTITLE 'First bottom title'
COLUMN dummy noprint;
SELECT 'x' dummy FROM DUAL;
SELECT COUNT (*) FROM tab;
SPOOL off;
SPOOL a2.txt
TTITLE 'Second top title' skip 2
BTITLE 'Second bottom title'
COLUMN dummy noprint;
SELECT 'x' dummy FROM DUAL;
-- this query returns no rows
SELECT 'x' FROM dual WHERE sysdate = sysdate + 1;
SPOOL off;|||thanks for the help, ill give that a try.
I have 3 seperate queries that each use the spool command to write to a file on the server.
e.g. i use the following construct in each of the three sql files:
set blah
set blah
spool /blah/blah.rpt
script
spool off
I wish to put these three queries into one script. Can I use the spool command three times in the one file? i.e. have spool then spool off, three times in one *.sql file?
I have read a couple of posts on pl/sql spooling and people mention utl_file, but I have no idea what this is and even if i have it...
Any help would be great. Thanks.Yes, you can. For example,
spool a1.txt
select count(*) from tab;
spool off;
spool a2.txt
select sysdate from dual;
spool off;
spool a3.txt
select 'x' dummy from dual;
spool off;
will generate 3 .txt files. However, I can't figure out why didn't you try it yourself ...|||I did try it and it didnt work the way I expected...
I have the three spools and also I have a title for each of the three files:
e.g.
ttitle 'Thanet ** Items made MISSING between 7 and 14 days ago' skip 2
I have one of these fore each script [different txt of course]. I have found that if the query returns no results it will not print the title to the file and so i have an emty file. If there are some results from on of the queries then the title does display.
I was wanting to make sure that I was using the spool correctly so thats why i asked.|||Perhaps this helps ... if you include a "dummy" query into every "spool block", you won't get an empty file even though your "real" query returns no records.
SPOOL a1.txt
TTITLE 'First top title' skip 2
BTITLE 'First bottom title'
COLUMN dummy noprint;
SELECT 'x' dummy FROM DUAL;
SELECT COUNT (*) FROM tab;
SPOOL off;
SPOOL a2.txt
TTITLE 'Second top title' skip 2
BTITLE 'Second bottom title'
COLUMN dummy noprint;
SELECT 'x' dummy FROM DUAL;
-- this query returns no rows
SELECT 'x' FROM dual WHERE sysdate = sysdate + 1;
SPOOL off;|||thanks for the help, ill give that a try.
Oracle PL/SQL Date Function
Hi,
Can someone tell me how to write a date function for the following:
AsOfDate = If Monday, current date - 3
Else current date - 1
The date needs to be displayed in mm/dd/yyyy format.
Any help is greatly appreciated.Using DECODE:
DECODE( TO_CHAR(SYSDATE,'DY'), 'MON', SYSDATE-3, SYSDATE-1 )
Using CASE:
CASE WHEN TO_CHAR(SYSDATE,'DY')='MON' THEN SYSDATE-3 ELSE SYSDATE-1 END
In either case the result is of type DATE: use TO_CHAR to convert to required format when displaying.
Can someone tell me how to write a date function for the following:
AsOfDate = If Monday, current date - 3
Else current date - 1
The date needs to be displayed in mm/dd/yyyy format.
Any help is greatly appreciated.Using DECODE:
DECODE( TO_CHAR(SYSDATE,'DY'), 'MON', SYSDATE-3, SYSDATE-1 )
Using CASE:
CASE WHEN TO_CHAR(SYSDATE,'DY')='MON' THEN SYSDATE-3 ELSE SYSDATE-1 END
In either case the result is of type DATE: use TO_CHAR to convert to required format when displaying.
Oracle OLEDB drivers problem with Numbers
Hi All:
I am using oracle oledb drivers to write to a oledb destination.
if i give decimal values to decimal fields in the source table, i get the same in destination. But if the input is integers, in some cases, the value in the destination is different from that of source
Source Target
50 50.00
100 0.000
111 111.000
600 0.0000
520 20.00
178 178
4546.50 4546.50
I have Sql server SP2 9.0.3042 installed on my machine. Please let me know if theres something i am missing out.
Thanks,
Vipul
There is no such thing as an integer in Oracle. It's a NUMERIC(p,0) field. That is, it has no scale. SSIS doesn't support this at the moment. Instead, write your query such that you convert the "integer" field into a NUMERIC(p+1,1) field, or something like that. Then map to a decimal field in SSIS. From there, if you want integers out of the data, use a derived column to cast the values to integers.|||Let me put it this way phil. Have you come across decimal data being changed from source to target without any transforms in between ? I was not correct in putting the question but the cause of my concern is that if the source has 500 how the target is getting it as 0.
Is there some problem in SSIS for this or this is oracle oledb driver problem?
|||Have you looked at the data with a data viewer to see what is contained there? You might need to recreate the OLE DB source.|||ya i have viewed data with the data viewer before the oledb destination. Data is fine till data viewer. Theres something happening in oledb destination and thats why i suspect the drivers.
Also, the same behaviour is not happening on one of my other machine. The machine confguration of both the machine are same. I am executing the same pacakge from both the machines.
The version of software on both mahcine are:
-
SQL Server sp2 9.0.3042
Oracle 10g
Let me know your thoughts on this..
|||Is your destination SQL Server?Have you looked at the advanced properties of the OLE DB Destination to ensure that the data types for all of the columns are correct?|||
Destination is Oracle.
And i have checked all the datatypes as per ur suggestion but still the problem exists.
|||I'm going to have to bow out as I don't have an Oracle instance to test with.
Subscribe to:
Posts (Atom)