Home » SQL & PL/SQL » SQL & PL/SQL » Script Help (SQL Developer)
Script Help [message #654845] |
Tue, 16 August 2016 02:04 |
|
kolpalamayaw
Messages: 9 Registered: May 2013 Location: TURKEY
|
Junior Member |
|
|
Hi,
I would like to ask a questions about script show as policy information.
I have a table which is called policy and ıt has 3 columns as polID, expolid and userid
İ can explanin how it works in example
ID polID expolid userid
1 58912 - MATT
2 12345 58912 SYSTEM
3 98765 12345 SYSTEM
.
.
10 XXX YYYY SYSTEM
I have another table it calls ortders which has currets polid such as 98765
I wanna retreive date with fist userid who enter the fist data
ı wrote it this scrpit but it works only 2 record.
SELECT CASE
WHEN o.expolid IS NOT NULL
THEN
(SELECT userid
FROM orders
WHERE polid = o.expolid)
ELSE
o.userid
END
"UserID "
FROM orders o
[EDITED by LF: formatted code and applied [code] tags]
[Updated on: Tue, 16 August 2016 06:36] by Moderator Report message to a moderator
|
|
|
Re: Script Help [message #654846 is a reply to message #654845] |
Tue, 16 August 2016 03:01 |
|
Michel Cadot
Messages: 68665 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
From your previous topic:
Michel Cadot wrote on Thu, 23 May 2013 15:31...
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.
Also always post your Oracle version, with 4 decimals.
...
With any SQL or PL/SQL question, please, Post a working Test case: create statements for all objects so that we will be able work to reproduce what you have.
Quote:ı wrote it this scrpit but it works only 2 record.
NEVER use SELECT in SELECT, this is for SQL expert ONLY, and you're not.
[Updated on: Tue, 16 August 2016 03:01] Report message to a moderator
|
|
|
|
|
Re: Script Help [message #655238 is a reply to message #654845] |
Thu, 25 August 2016 09:40 |
|
ALEXWE
Messages: 11 Registered: August 2016 Location: Germany
|
Junior Member |
|
|
I think he wants to get the userid, of the user who entered the data first
-The Root-
CREATE TABLE pol
(
id NUMBER,
polID NUMBER,
expolid NUMBER,
userid VARCHAR2(50)
);
INSERT INTO pol VALUES
(1,58912,NULL,'MATT'
);
INSERT INTO pol VALUES
(2,12345,58912 ,'SYSTEM'
);
INSERT INTO pol VALUES
(3,98765,12345 ,'SYSTEM2'
);
INSERT INTO pol VALUES
(4,123,NULL,'ALEX'
);
INSERT INTO pol VALUES
(5,1234,123 ,'SYSTEM3'
);
INSERT INTO pol VALUES
(6,98765,1234 ,'SYSTEM4'
);
INSERT INTO pol VALUES
(7,234,NULL,'CHRISTIAN'
);
INSERT INTO pol VALUES
(8,11111,234 ,'SYSTEM5'
);
INSERT INTO pol VALUES
(9,88888,234 ,'SYSTEM6'
);
INSERT INTO pol VALUES
(10,99999,88888 ,'SYSTEM7'
);
INSERT INTO pol VALUES
(11,99999,11111 ,'SYSTEM8'
);
--Format
COLUMN root_userid format a12
COLUMN Path format a30
COLUMN PADDED_NAME format a20
-- Make a Tree
SELECT CONNECT_BY_ROOT(userid) AS root_userid,
SYS_CONNECT_BY_PATH(userid, '/') "Path",
lpad(' ', (level - 1) * 2) || userid as padded_name,
--level l1,
--max(level) over(partition by id) max_l1,
ID
FROM pol
CONNECT BY PRIOR polid = expolid
start with id in (select id from pol where expolid is null)
--ORDER BY id ASC
;
|
|
|
Goto Forum:
Current Time: Sat Jun 29 07:24:44 CDT 2024
|