redundant output [message #672454] |
Tue, 16 October 2018 02:47 |
|
suji6281
Messages: 151 Registered: September 2014
|
Senior Member |
|
|
Hi Team,
Is it possible to remove redundant data from specific columns by a sql
Actual outcome-
Column1 column 2 column 3 Column4
1 Abc Test1 Data1
1 Abc Test2 Data2
Expected outcome-
Column1 column 2 column 3 Column4
1 Abc Test1 Data1
Test2 Data2
Your response to this query will be highly appreciable.
Thanks in anticipation!
Regards,
Sekhar
|
|
|
|
Re: redundant output [message #672456 is a reply to message #672454] |
Tue, 16 October 2018 03:51 |
pablolee
Messages: 2882 Registered: May 2007 Location: Scotland
|
Senior Member |
|
|
Based on what/how you have posted, it looks like you want to move the value 'Test2' into Column1 and value Data2 into Column2. Can you explain why? Or, as Michel pointed out, this may be down to you not formatting your post appropriately. maybe give it another try and see if you can get the data to look the way that you actually want it.
|
|
|
Re: redundant output [message #672457 is a reply to message #672454] |
Tue, 16 October 2018 03:54 |
John Watson
Messages: 8963 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
Do you mean like this:pdby1>
pdby1> select job,ename from emp order by job;
JOB ENAME
--------- ----------
ANALYST SCOTT
FORD
CLERK MILLER
JAMES
SMITH
ADAMS
MANAGER BLAKE
JONES
CLARK
PRESIDENT KING
SALESMAN TURNER
MARTIN
WARD
ALLEN
14 rows selected.
pdby1>
[Updated on: Tue, 16 October 2018 03:56] Report message to a moderator
|
|
|
|
|
|
Re: redundant output [message #672476 is a reply to message #672475] |
Tue, 16 October 2018 06:30 |
John Watson
Messages: 8963 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
Version 3 was released in 1981. You need to run this,pdby1>
pdby1> select * from v$version;
BANNER
--------------------------------------------------------------------------------
BANNER_FULL
--------------------------------------------------------------------------------------------------------------
BANNER_LEGACY CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production 0
pdby1>
|
|
|
|
Re: redundant output [message #672510 is a reply to message #672455] |
Wed, 17 October 2018 02:25 |
|
suji6281
Messages: 151 Registered: September 2014
|
Senior Member |
|
|
Hi Michel,
Please find below test data for reference.requesting you please help us with query to display output in the desired format.
my current Oracle version is 12.1.0.2.0
CREATE TABLE EMPL_TBL
( EMPL_id varchar2(10) NOT NULL,
EMPL_name varchar2(50) NOT NULL,
city varchar2(50),
state varchar2(25),
zip_code varchar2(10)
)
INSERT INTO EMPL_TBL VALUES ('A1234', 'JAMES', 'AUSTIN', 'TEXAS', '502148' );
INSERT INTO EMPL_TBL VALUES ('A1234', 'JAMES', 'BOSTAN', 'TEXAS', '501231' );
INSERT INTO EMPL_TBL VALUES ('C5879', 'BLAKE', 'AUSTIN', 'TEXAS', '502148' );
INSERT INTO EMPL_TBL VALUES ('C5879', 'BLAKE', 'AUSTIN', 'TEXAS', '714782' );
table output:
EMPL_ID EMPL_NAME CITY STATE ZIP_CODE
A1234 JAMES AUSTIN TEXAS 502148
A1234 JAMES BOSTAN TEXAS 501231
C5879 BLAKE AUSTIN TEXAS 502148
C5879 BLAKE AUSTIN TEXAS 714782
expected output as below:
EMPL_ID EMPL_NAME CITY STATE ZIP_CODE
A1234 JAMES AUSTIN TEXAS 502148
BOSTAN TEXAS 501231
C5879 BLAKE AUSTIN TEXAS 502148
714782
we shouldn't display redundant data in the output.
Thank You.
Regards
Sekhar
|
|
|
|
|
Re: redundant output [message #672518 is a reply to message #672517] |
Wed, 17 October 2018 05:21 |
John Watson
Messages: 8963 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
Of course BREAK works. How do you think I did the demo yesterday? However, since you can't be bothered to show what you did it is not possible to tell you what you have done wrong.
|
|
|
Re: redundant output [message #672520 is a reply to message #672518] |
Wed, 17 October 2018 06:20 |
|
suji6281
Messages: 151 Registered: September 2014
|
Senior Member |
|
|
HI John,
I appreciate and thank you for your quick response!
Could you please check the sql below which I have constructed wherein I was expecting few of the columns to be broken with no duplicacy each time which you have correctly understood.
SELECT
S1.REPORT_SCOPE AS "Report Scope",S1.DESCR AS "Description",S1.BUSINESS_UNIT AS "Business Unit",S1.RECNAME_COMB AS "Field Combination Table",
S2.FIELDNAME AS "Field Name",A.XLATLONGNAME AS "How Specified",S2.RECNAME AS "Value Table",S3.SELECT_VALUE AS "Values"
FROM PS_NVS_SCOPE S1, PS_NVS_SCOPE_FIELD S2,PS_NVS_SCOPE_VALUE S3,PSXLATITEM A
WHERE A.FIELDNAME= 'HOW_FIELD_SPECIFY'
AND ( A.EFFDT =
(SELECT MAX(A_ED.EFFDT) FROM PSXLATITEM A_ED
WHERE A.FIELDNAME = A_ED.FIELDNAME
AND A.FIELDVALUE = A_ED.FIELDVALUE
AND A_ED.EFFDT <= SYSDATE))
AND A.EFF_STATUS='A'AND A.FIELDVALUE=S2.HOW_FIELD_SPECIFY
AND S1.SETID=S2.SETID AND S1. REPORT_SCOPE=S2.REPORT_SCOPE
AND S2.REPORT_SCOPE=S3.REPORT_SCOPE AND S2.FIELDNAME=S3.FIELDNAME
AND S2.SETID=S3.SETID AND S1.SETID='SHARE'
But If I apply Break on per your advice, Below is the query and Please let me know if my query is incorrect-
BREAK ON S1.REPORT_SCOPE
SELECT
S1.REPORT_SCOPE AS "Report Scope",S1.DESCR AS "Description",S1.BUSINESS_UNIT AS "Business Unit",S1.RECNAME_COMB AS "Field Combination Table",
S2.FIELDNAME AS "Field Name",A.XLATLONGNAME AS "How Specified",S2.RECNAME AS "Value Table",S3.SELECT_VALUE AS "Values"
FROM PS_NVS_SCOPE S1, PS_NVS_SCOPE_FIELD S2,PS_NVS_SCOPE_VALUE S3,PSXLATITEM A
WHERE A.FIELDNAME= 'HOW_FIELD_SPECIFY'
AND ( A.EFFDT =
(SELECT MAX(A_ED.EFFDT) FROM PSXLATITEM A_ED
WHERE A.FIELDNAME = A_ED.FIELDNAME
AND A.FIELDVALUE = A_ED.FIELDVALUE
AND A_ED.EFFDT <= SYSDATE))
AND A.EFF_STATUS='A'AND A.FIELDVALUE=S2.HOW_FIELD_SPECIFY
AND S1.SETID=S2.SETID AND S1. REPORT_SCOPE=S2.REPORT_SCOPE
AND S2.REPORT_SCOPE=S3.REPORT_SCOPE AND S2.FIELDNAME=S3.FIELDNAME
AND S2.SETID=S3.SETID AND S1.SETID='SHARE'
ORDER BY S1.REPORT_SCOPE;
Thank You!
Regards
Sekhar
|
|
|
Re: redundant output [message #672523 is a reply to message #672520] |
Wed, 17 October 2018 07:18 |
cookiemonster
Messages: 13962 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Sqlplus doesn't know nor care what s1 is, that's the DB's problem.
It works on the aliases the column names get in the output from the query.
Try
BREAK ON "Report Scope"
|
|
|
|
Re: redundant output [message #672530 is a reply to message #672527] |
Wed, 17 October 2018 08:04 |
cookiemonster
Messages: 13962 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
To run queries in oracle you need a client program.
So if you aren't using sqlplus what are you using?
Also
break on "s1.report_scope"
was never going to work - you don't see s1 in the output column header do you?
|
|
|
|
|
|
|
|
Re: redundant output [message #672545 is a reply to message #672542] |
Wed, 17 October 2018 08:51 |
cookiemonster
Messages: 13962 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
To upgrade the sqldeveloper you download it and install.
Should take 5 mins, unless there's some company policy stopping you.
In which case start up sqlplus and use that.
|
|
|
|
|
Re: redundant output [message #672548 is a reply to message #672547] |
Wed, 17 October 2018 10:05 |
cookiemonster
Messages: 13962 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
You can use CASE and row_number:
SELECT
CASE WHEN row_number() OVER (PARTITION BY S1.REPORT_SCOPE ORDER BY NULL) THEN S1.REPORT_SCOPE END AS "Report Scope",
S1.DESCR AS "Description",S1.BUSINESS_UNIT AS "Business Unit",S1.RECNAME_COMB AS "Field Combination Table",
S2.FIELDNAME AS "Field Name",A.XLATLONGNAME AS "How Specified",S2.RECNAME AS "Value Table",S3.SELECT_VALUE AS "Values"
FROM PS_NVS_SCOPE S1, PS_NVS_SCOPE_FIELD S2,PS_NVS_SCOPE_VALUE S3,PSXLATITEM A
WHERE A.FIELDNAME= 'HOW_FIELD_SPECIFY'
AND ( A.EFFDT =
(SELECT MAX(A_ED.EFFDT) FROM PSXLATITEM A_ED
WHERE A.FIELDNAME = A_ED.FIELDNAME
AND A.FIELDVALUE = A_ED.FIELDVALUE
AND A_ED.EFFDT <= SYSDATE))
AND A.EFF_STATUS='A'AND A.FIELDVALUE=S2.HOW_FIELD_SPECIFY
AND S1.SETID=S2.SETID AND S1. REPORT_SCOPE=S2.REPORT_SCOPE
AND S2.REPORT_SCOPE=S3.REPORT_SCOPE AND S2.FIELDNAME=S3.FIELDNAME
AND S2.SETID=S3.SETID AND S1.SETID='SHARE'
ORDER BY S1.REPORT_SCOPE;
|
|
|
|
|
|
Re: redundant output [message #672566 is a reply to message #672565] |
Thu, 18 October 2018 05:14 |
|
suji6281
Messages: 151 Registered: September 2014
|
Senior Member |
|
|
Here is the modified code-
SELECT
CASE WHEN row_number() OVER (PARTITION BY S1.REPORT_SCOPE ORDER BY NULL)=1 THEN S1.REPORT_SCOPE ELSE ' ' END as "Report Scope",
CASE WHEN row_number() OVER (PARTITION BY S1.DESCR ORDER BY NULL)=2 THEN S1.DESCR ELSE ' ' END as "Description",
CASE WHEN row_number() OVER (PARTITION BY S1.BUSINESS_UNIT ORDER BY NULL)=3 THEN S1.BUSINESS_UNIT ELSE ' ' END as "Business Unit",
S1.RECNAME_COMB AS "Field Combination Table",
S2.FIELDNAME AS "Field Name",A.XLATLONGNAME AS "How Specified",S2.RECNAME AS "Value Table",S3.SELECT_VALUE AS "Values"
FROM PS_NVS_SCOPE S1, PS_NVS_SCOPE_FIELD S2,PS_NVS_SCOPE_VALUE S3,PSXLATITEM A
WHERE A.FIELDNAME= 'HOW_FIELD_SPECIFY'
AND ( A.EFFDT =
(SELECT MAX(A_ED.EFFDT) FROM PSXLATITEM A_ED
WHERE A.FIELDNAME = A_ED.FIELDNAME
AND A.FIELDVALUE = A_ED.FIELDVALUE
AND A_ED.EFFDT <= SYSDATE))
AND A.EFF_STATUS='A'AND A.FIELDVALUE=S2.HOW_FIELD_SPECIFY
AND S1.SETID=S2.SETID AND S1. REPORT_SCOPE=S2.REPORT_SCOPE
AND S2.REPORT_SCOPE=S3.REPORT_SCOPE AND S2.FIELDNAME=S3.FIELDNAME
AND S2.SETID=S3.SETID AND S1.SETID='SHARE'
ORDER BY S1.REPORT_SCOPE;
|
|
|
Re: redundant output [message #672567 is a reply to message #672566] |
Thu, 18 October 2018 05:35 |
cookiemonster
Messages: 13962 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
We don't have your tables or data so we really can't see what the problem is.
Change the query to display the row_number functions in all cases - your problem should become obvious.
|
|
|
Re: redundant output [message #672569 is a reply to message #672567] |
Thu, 18 October 2018 05:48 |
cookiemonster
Messages: 13962 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
If you're trying to do what I think they you probably need this:
SELECT
CASE WHEN row_number() OVER (PARTITION BY S1.REPORT_SCOPE ORDER BY S1.DESCR, S1.BUSINESS_UNIT)=1 THEN S1.REPORT_SCOPE ELSE ' ' END as "Report Scope",
CASE WHEN row_number() OVER (PARTITION BY S1.REPORT_SCOPE, S1.DESCR ORDER BY S1.BUSINESS_UNIT)=1 THEN S1.DESCR ELSE ' ' END as "Description",
CASE WHEN row_number() OVER (PARTITION BY S1.REPORT_SCOPE, S1.DESCR, S1.BUSINESS_UNIT ORDER BY NULL)=1 THEN S1.BUSINESS_UNIT ELSE ' ' END as "Business Unit",
S1.RECNAME_COMB AS "Field Combination Table",
S2.FIELDNAME AS "Field Name",A.XLATLONGNAME AS "How Specified",S2.RECNAME AS "Value Table",S3.SELECT_VALUE AS "Values"
FROM PS_NVS_SCOPE S1, PS_NVS_SCOPE_FIELD S2,PS_NVS_SCOPE_VALUE S3,PSXLATITEM A
WHERE A.FIELDNAME= 'HOW_FIELD_SPECIFY'
AND ( A.EFFDT =
(SELECT MAX(A_ED.EFFDT) FROM PSXLATITEM A_ED
WHERE A.FIELDNAME = A_ED.FIELDNAME
AND A.FIELDVALUE = A_ED.FIELDVALUE
AND A_ED.EFFDT <= SYSDATE))
AND A.EFF_STATUS='A'AND A.FIELDVALUE=S2.HOW_FIELD_SPECIFY
AND S1.SETID=S2.SETID AND S1. REPORT_SCOPE=S2.REPORT_SCOPE
AND S2.REPORT_SCOPE=S3.REPORT_SCOPE AND S2.FIELDNAME=S3.FIELDNAME
AND S2.SETID=S3.SETID AND S1.SETID='SHARE'
ORDER BY S1.REPORT_SCOPE, S1.DESCR, S1.BUSINESS_UNIT;
|
|
|
|
|
|
|
Re: redundant output [message #672577 is a reply to message #672576] |
Thu, 18 October 2018 11:05 |
pablolee
Messages: 2882 Registered: May 2007 Location: Scotland
|
Senior Member |
|
|
gazzag wrote on Thu, 18 October 2018 16:55Banks are renowned for it *shudders*
Indeed. I know many that are still using a v2.1 of SQL Dev <the horror!>
|
|
|
Re: redundant output [message #672590 is a reply to message #672577] |
Fri, 19 October 2018 03:32 |
cookiemonster
Messages: 13962 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
I get why companies are cautious (or paranoid) about upgrading the DB or various OS, but I'm baffled why they would be concerned about upgrading a GUI for the DB.
|
|
|
|
|