playing around views

hi
I just want to be sure there is no way to do it inside the DB:
I have a view which return 1000 numbers and amounts like this:
Number Amount
000 234
001 3456
... ...
999 464
I have only two very long columns. is it possible to re-arrange this into a
view to get something like this:
number amount number amount number amount
000 345 001 9861 002 865
003 4564 004 45 005 9865
thks.On Tue, 25 Jan 2005 09:05:03 -0800, Kenny M. wrote:

>I have a view which return 1000 numbers and amounts like this:
>Number Amount
>000 234
>001 3456
>... ...
>999 464
>I have only two very long columns. is it possible to re-arrange this into
a
>view to get something like this:
>number amount number amount number amount
> 000 345 001 9861 002 865
> 003 4564 004 45 005 9865
Hi Kenny,
This is a presentation task, typically done at the client. However, if you
really want to burden the server with it, try:
SELECT a.number, a.amount, b.number, b.amount, c.number, c.amount
FROM MyTable AS a
LEFT OUTER JOIN MyTable AS b
ON b.Number = a.Number + 1
LEFT OUTER JOIN MyTable AS c
ON c.Number = a.Number + 1
WHERE a.Number % 3 = 0
ORDER BY a.Number
(untested)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Why to stress your server trying to do this?. Use your client app / reportin
g
tool or programming language.
use northwind
go
select
identity(int, 0, 1) as number,
0 as amount
into
t
from
sysobjects as a cross join sysobjects as b
delete t where number > 999
update t set amount = power(2, number % 8)
select
*
from
(
select
number, amount
from
t
where
number % 3 = 0
) as a
left join
(
select
number, amount
from
t
where
number % 3 = 1
) as b
on a.number = b.number - 1
left join
(
select
number, amount
from
t
where
number % 3 = 2
) as c
on b.number = c.number - 1
drop table t
go
AMB
"Kenny M." wrote:

> hi
> I just want to be sure there is no way to do it inside the DB:
> I have a view which return 1000 numbers and amounts like this:
> Number Amount
> 000 234
> 001 3456
> ... ...
> 999 464
> I have only two very long columns. is it possible to re-arrange this into
a
> view to get something like this:
> number amount number amount number amount
> 000 345 001 9861 002 865
> 003 4564 004 45 005 9865
> thks.
>
>

Place results in Colmn rather than rows

I have a few tables that i need to run a query on and instead of having them appear in multiple rows how do i return teh results in columns instead.

eg: System Name

1 Mr A

1 Mr B

2 Mr C

2 Mr D

INTO System Name1 Name2

1 Mr A Mr B

2 Mr C Mr D

SELECT CASE WHEN THEN ELSE END

Adamus

|||

SELECT CASE Name

WHEN System_ID = '1',

THEN

Name2

ELSE

Name3

END

Not sure i get you?

|||declare @.table table
(
[System] int,
[Name] varchar(5)
)

insert into @.table
select 1, 'Mr A' union all
select 1, 'Mr B' union all
select 2, 'Mr C' union all
select 2, 'Mr D'

select a.[System], [Name 1] = min(a.[Name]), [Name 2]= max(a.[Name])
from @.table a
group by a.[System]
|||

Thanks

The names Mr A, Mr B etc will be in the hundreds so don't really fancy typing them all out. There could be up to 4 or 5 different names per system.

i have tried to adapt to this but doesn;t work:-

declare @.table table

(

[System] int,

[Name] varchar(5)

)

insert into @.table

select System_ID, (firstname + ' ' + surname) as Name union all

select System_ID, (firstname + ' ' + surname) as [Name 1] union all

select System_ID, (firstname + ' ' + surname) as [Name 2]

where system = 1

From ((((dbo.System as S..........followed by my joins....

Select a.[System], [Name 1] = min(a.[Name]), [Name 2]= max(a.[Name])

from @.table a

group by a.[System]

How do i do this when i need to search for the criterea?

|||the table variable is for demonstrating the script.

use the query and change to your actual table name.

select a.[System], [Name 1] = min(a.[Name]), [Name 2]= max(a.[Name])
from @.table a
group by a.[System]|||

ok . got it working partially,

The Min and Max just returns 2 results? Some have 3 or 4 names?

|||do this in your front end application. It can be done in T-SQL but it will not be clean

PL/SQL Machine Model (params, return, etc)

Hi,

I'm writing a PL/SQL program that isn't performing so well, and I was wondering if anyone knows of any good references where I can learn more about the machine model PL/SQL is operating under. If not, my specific questions are:

1) Are parameters passed by value or by reference? (i.e. if I pass a big VARCHAR2 does it do a memcpy of the whole thing?)
2) Same thing for return values...
3) How do you typically access big chunks of memory from different points in a PL/SQL program? In C I would just pass around and return a pointer to it, but I don't see an analogous construct here. From experimentation, the REF facility seems very inflexible -- I would like to create a REF to an arbitrary variable in my program, like you would with a pointer in C, but all the examples I've seen assign REF vars their values out of a table.
4) What's the closest I can get to a dynamically allocated array that I'd like to pass around my program? I was using pipelined tables, but they are incredibly slow. Now I'm using a VARRAY but it scares me because it has a max size.

Thanks! Sorry for all the questions, but I find it's very hard to guess at what's happening under the surface here.
PeteYou could start with the Oracle PL/SQL Reference:

http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96624/toc.htm

I can tell you that IN parameters are generally passed by value, but there is a NOCOPY option to use a reference instead, which is normally used only for passing large collections as IN parameters.

I would use an "associative array" (aka index-by table) rather than a VARRAY. These do not have a declared size like a VARRAY. If you declare the array in a package specification, it can be accessed from anywhere in your code as package_name.array_name.

PL/SQL is mainly intended as a language for working closely with the database, and in most cases any performance problems come from badly tuned SQL and database designs rather than from the PL/SQL itself. For computationally intensive processes where you spend more time crunching arrays in memory than querying the database, Pro*C may be a better bet. (Or maybe your code can be redesigned to make use of SQL set-based processing rather than array-crunching).|||Well my experience / understanding is this...

- IN parameters are passed by reference. NOCOPY does not apply.

- IN OUT and OUT parameters are passed by value / copy unless NOCOPY directive is used in which case they are passed by reference (provided NOCOPY restrictions do not apply).

- The RETURN value of a function is copied to the variable to which it is assigned.

All of which means the most efficient way to pass a large variable is in IN OUT or OUT mode using NOCOPY (provided NOCOPY restrictions do not apply!). You might also consider holding the variable as a package global variable if copying it around is causing a problem (see usual reservations about global variables).|||Thanks, Padders, I got that exactly the wrong way round! Should have read the manual before opening my gob.

(FWIW, my incorrect thought process was: for an IN parameter, you only need the value; for an IN OUT or OUT parameter you need a reference so that you know where to write the changes. Seemed so "obvious" I didn't bother to check it!)

PL/SQL Help!

[Oracle 8.1.7][PL/SQL question...]
In a stored procedure, I want to open a cursor to return, for which the query will join a few tables, like so:
PROCEDURE ppp (
crsr IN OUT crsr_type, value1_in IN INT, value2_in IN INT
op_in IN varchar2) IS
BEGIN
OPEN crsr FOR
SELECT c1, c2, c3
FROM p, q, r
WHERE ...(join-conditions)...
AND (question below);
RETURN;
END;

There is one condition in my WHERE clause that will depend on an input parameter of the procedure, namely 'op_in' (operator), and depending on what op_in is, my AND part could be:

if (op_in = 'EQ')
AND p.col = value1_in
else if (op_in = 'GT')
AND p.col > value1_in
else if (op_in = 'BTW')
AND p.col BETWEEN value1_in AND value2_in

As you see, it is the operator of the where clause changes.

I tried using DECODE, but I couldn't get it to decode on an operator.
I tried to make the crsr query a dynamic sql string, but I think it's a 9i feature (OPEN crsr FOR dyn_sql_string).

The procedure only does this and returns out the cursor.

I am a newbie to Oracle myself, and wonder if there are ways I can get around with this?You can use dynamic SQL:

PL/SQL & Recordsets

You can use PL/SQL functions in SQL statements and you can return recordsets with PL/SQL. Is there any way to combine the two in the FROM clause of a SQL statement?
function foo(a in number);
select *
from table1, foo(3)
where table1.field1 = foo.field1
Thanks.Hello,

use unnested collection types to create a nested table and a view that accesses the unnested collection types.

Then you can use the view to access your function.

If you need more help see the docu or contact me at www.alligatorsql.com

Hope that helps ?

Manfred Peter
(Alligator Company GmbH)
http://www.alligatorsql.com