Home » SQL & PL/SQL » SQL & PL/SQL » Script Help (SQL Developer)
Script Help [message #654845] Tue, 16 August 2016 02:04 Go to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 67879
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. Wink

[Updated on: Tue, 16 August 2016 03:01]

Report message to a moderator

Re: Script Help [message #654856 is a reply to message #654846] Tue, 16 August 2016 03:55 Go to previous messageGo to next message
kolpalamayaw
Messages: 9
Registered: May 2013
Location: TURKEY
Junior Member
İ am expert as well as you are are man Wink
Re: Script Help [message #654857 is a reply to message #654856] Tue, 16 August 2016 04:17 Go to previous messageGo to next message
Michel Cadot
Messages: 67879
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Please provide what is requested... if you want help, of course.

Re: Script Help [message #655238 is a reply to message #654845] Thu, 25 August 2016 09:40 Go to previous message
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
;
Previous Topic: how to restrict set of data in oracle
Next Topic: Minus Minutes
Goto Forum:
  


Current Time: Sat Jun 12 21:14:43 CDT 2021