Solved

SQL Query Help PLEASE!!!!!!

Posted on 2014-01-06
30
550 Views
Last Modified: 2014-02-24
Need help Experts!!
below is my requirement
i have table that contains list of database sid's
instance sid
------------------
1             sid1
2             sid2
3             sid3
4             sid4

another table that contains all the oracle parameter for any given sid form the previous table
db_instance            parameter                                  values
------------------------------------------------------------------------------------
1                              sga_target                                      4GB
1                              _unnest_subquery                         FALSE
2                              sga_target                                      3GB
2                              _unnest_subquery                         FALSE
3                              _unnest_subquery                         TRUE

above are the data i currently have. what i need to derive out of this?
for any given pattern of sid, i need to compare all the parameter values
below, is an example of expected result

only if the parameter value is different between these databases, values are displayed

parameter                            sid1                            sid2                           sid3
----------------------------------------------------------------------------------------------------------
sga_target                             4GB                            3GB                            4GB
_unnest_subquery               FALSE                         FALSE                         TRUE
0
Comment
Question by:eagle_ea
  • 10
  • 8
  • 7
  • +3
30 Comments
 
LVL 28

Expert Comment

by:Naveen Kumar
Comment Utility
Do you have the sid column in the second table along with the other columns db_instance,  parameter , values ?
0
 

Author Comment

by:eagle_ea
Comment Utility
no, sid field does not exists in the second table
0
 
LVL 8

Expert Comment

by:Surrano
Comment Utility
Do you have 4 sids as in the example or an arbitrary (possibly non-constant) set of sids? In the latter case, I'd recommend a pl/sql block that constructs the query in question....
0
 
LVL 28

Expert Comment

by:Naveen Kumar
Comment Utility
if the number of columns which you wanted in the output vary depending on the values of the data in the records of table1/table2 then you will have to use dynamic sql to prepare the query dynamically and then execute it. you can use native dynamic sql or dbms_sql package procedures/functions to achieve that.

how does your sample query looks like ? can you paste so that i can get a clear idea of what goes in the subquery and then see if i can help

select x.*,
from tbl2 x
where x.db_instance = ( select y.instance from tbl1 y where y.sid = 100 )

Thanks,
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
The number of sids must be known at the time the sql is parsed.
If you don't know, then you can either create an arbitrarily large number that you know ill be sufficient, or return the values as a concatenated string
or, as suggested above,  perform the operation in multiple steps - first querying the number of sids, then constructing a query with that many results then executing the query and dumping the output somewhere (utl_file or dbms_output being most common)
0
 
LVL 34

Expert Comment

by:Mark Geerlings
Comment Utility
Do you have a fixed number of SID values that you know in advance?  If not, this looks like a classic "matrix" or "cross-tab" report that reporting tools like Oracle Reports or Crystal Reports can handle, but that is difficult is straight SQL.
0
 
LVL 31

Expert Comment

by:awking00
Comment Utility
How did you determine that sga_target for sid3 was 4GB and not null?
0
 

Author Comment

by:eagle_ea
Comment Utility
the number of sid's cabe vary from 2 to 20
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
Since there are only 20 of them in the worst case, it's easy to simply list them

SELECT parameter,
       sid1,
       sid2,
       sid3,
       sid4
  FROM (SELECT s.sid,
               p.parameter,
               p.VALUE,
               MIN(VALUE) OVER (PARTITION BY parameter) minvalue,
               MAX(VALUE) OVER (PARTITION BY parameter) maxvalue
          FROM sids s LEFT JOIN parameters p ON s.instance = p.db_instance) PIVOT (MAX(VALUE)
                                                                            FOR sid
                                                                            IN  ('sid1' sid1,
                                                                                'sid2' sid2,
                                                                                'sid3' sid3,
                                                                                'sid4' sid4))
 WHERE minvalue != maxvalue;
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
Using the string concatenation method...  (does not require listing the sids explicitly)

SELECT RPAD('Parameter', 20) || LISTAGG(RPAD(sid, 50)) WITHIN GROUP (ORDER BY instance) FROM sids
UNION ALL
  SELECT RPAD(parameter, 20) || LISTAGG(RPAD(parametervalue, 50)) WITHIN GROUP (ORDER BY instance)
    FROM (SELECT s.instance,
                 s.sid,
                 p.parameter,
                 p.VALUE parametervalue,
                 MIN(p.VALUE) OVER (PARTITION BY parameter) minvalue,
                 MAX(p.VALUE) OVER (PARTITION BY parameter) maxvalue
            FROM sids s LEFT JOIN parameters p ON s.instance = p.db_instance)
   WHERE minvalue != maxvalue
GROUP BY parameter;
0
 

Author Comment

by:eagle_ea
Comment Utility
sdstuber:
FROM keyword not found where expected
0
 
LVL 8

Assisted Solution

by:Surrano
Surrano earned 250 total points
Comment Utility
OK here's some plsql cycle with no real restriction on number of sids, only the length of varchar2 variables needs to be adjusted if found insufficient:

First prepare an utl_file directory:

create directory tmp as '/tmp/';

Open in new window


declare
  param_clause varchar2(1000) := '';
  value_clause varchar2(1000) := '';
  select_clause varchar2(1000) := '';
  from_clause varchar2(1000) := '';
  where_clause varchar2(1000) := '';
  stmt varchar2(4000) := '';
  i varchar2(16);
  myfile utl_file.file_type;
begin
  myfile := utl_file.fopen('TMP', 'myquery.sql','W');
  from_clause := ' ';
  for r in (select instance, sid from sids order by instance)
  loop
    i := 's'||r.instance;
    param_clause := param_clause||','||i||'.parameter';
    value_clause := param_clause||','||i||'.value';
    select_clause := select_clause||','||i||'.value '||r.sid;
    if from_clause = ' ' then
        from_clause := ' (select * from params where db_instance='||r.instance||') '||i;
    else
        from_clause := from_clause || ' full outer join (select * from params where db_instance='||r.instance||') '||i||
                   ' on (coalesce(null'||param_clause||')='||i||'.parameter)';
    end if;
    where_clause := where_clause || ' or coalesce(null'||value_clause||',''(null)'')<>nvl('||i||'.value,''(null)'')';
  end loop;
  stmt := 'select coalesce(null'||param_clause||') parameter'||select_clause||' from '||from_clause||' where 1=0 ' || where_clause||';';
  dbms_output.enable;
  utl_file.put_line(myfile, stmt);
  utl_file.fflush(myfile);
  utl_file.fclose(myfile);
end;
/

@/tmp/myquery.sql

Open in new window


You can add some fancy formatting if you like, e.g. a40 for param names and a20 for param values of each column. It can also be done dynamically while creating this temp file.

HTH,
S.
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
>> FROM keyword not found where expected

I posted 2 queries, which one generates this error for you (they both worked for me)

what is your version?  please provide full numeric version like 10.1.2.3  
not an abbreviation like 10g
0
 
LVL 8

Expert Comment

by:Surrano
Comment Utility
ps. this PL/SQL block generates a query O(n^2) in length but this way it compensates for NULL (or missing) values (considering NULL being different from anything else which is not necessary what you want).

sdstuber's excellent pivot solution could be cross-bred with mine to have an arbitrary number of tables with O(n) query length but only if nulls (or missing values) are not relevant.
0
 

Author Comment

by:eagle_ea
Comment Utility
sdstuber:
oracle version is 10.2.0.4 and the version, i'm trying is the second one

SELECT RPAD('Parameter', 20) || LISTAGG(RPAD(sid, 50)) WITHIN GROUP (ORDER BY instance) FROM sids
UNION ALL
  SELECT RPAD(parameter, 20) || LISTAGG(RPAD(parametervalue, 50)) WITHIN GROUP (ORDER BY instance)
    FROM (SELECT s.instance,
                 s.sid,
                 p.parameter,
                 p.VALUE parametervalue,
                 MIN(p.VALUE) OVER (PARTITION BY parameter) minvalue,
                 MAX(p.VALUE) OVER (PARTITION BY parameter) maxvalue
            FROM sids s LEFT JOIN parameters p ON s.instance = p.db_instance)
   WHERE minvalue != maxvalue
GROUP BY parameter;
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 73

Accepted Solution

by:
sdstuber earned 250 total points
Comment Utility
listagg isn't supported until 11gR2  (11.2.0.1)

try this instead, you may want to adjust the padding sizes for whatever width is most appropriate for your reporting needs

SELECT    RPAD('Parameter', 20)
       || EXTRACT(XMLAGG(XMLELEMENT("x", RPAD(sid, 50)) ORDER BY instance), '/x/text()').getstringval()
  FROM sids
UNION ALL
  SELECT    RPAD(parameter, 20)
         || EXTRACT(XMLAGG(XMLELEMENT("x", RPAD(parametervalue, 50)) ORDER BY instance), '/x/text()').getstringval()
    FROM (SELECT s.instance,
                 s.sid,
                 p.parameter,
                 p.VALUE parametervalue,
                 MIN(p.VALUE) OVER (PARTITION BY parameter) minvalue,
                 MAX(p.VALUE) OVER (PARTITION BY parameter) maxvalue
            FROM sids s LEFT JOIN parameters p ON s.instance = p.db_instance)
   WHERE minvalue != maxvalue
GROUP BY parameter;
0
 

Author Comment

by:eagle_ea
Comment Utility
i modified to what i need as below and getting PVALUE a invalid identifier

SELECT    RPAD('Parameter', 20)
       || EXTRACT(XMLAGG(XMLELEMENT("x", RPAD(d.sid, 50)) ORDER BY instance), '/x/text()').getstringval()
  FROM databases d
UNION ALL
  SELECT    RPAD(parameter, 20)
         || EXTRACT(XMLAGG(XMLELEMENT("x", RPAD(p.pvalue, 50)) ORDER BY instance), '/x/text()').getstringval()
    FROM (SELECT s.instance,
                 s.sid,
                 p.parameter,
                 p.pVALUE parametervalue,
                 MIN(p.pVALUE) OVER (PARTITION BY parameter) minvalue,
                 MAX(p.pVALUE) OVER (PARTITION BY parameter) maxvalue
            FROM databases s LEFT JOIN db_parameters p ON s.instance = p.db_instance and s.client=161)
   WHERE minvalue != maxvalue
GROUP BY parameter;
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
If you change parametervalue to pvalue you need to change it everywhere


remove the alias from
             p.pVALUE parametervalue,

so it's just

  p.pVALUE,
0
 

Author Comment

by:eagle_ea
Comment Utility
i changed the pvalue to parameter value and having this issue now
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "SYS.XMLTYPE", line 163
0
 

Author Comment

by:eagle_ea
Comment Utility
there are numeric and non-numeric value in the parametervalue, is it the cause of this problem?
0
 
LVL 8

Expert Comment

by:Surrano
Comment Utility
I think problem is the 2nd parameter of RPAD. Probably there are values longer than 20 / 50 characters.
0
 

Author Comment

by:eagle_ea
Comment Utility
at this point i can get the result set, they are tab delimited.

can they be separated into columns?
0
 
LVL 8

Expert Comment

by:Surrano
Comment Utility
Have you tried my solution?

It should do that.
0
 

Author Comment

by:eagle_ea
Comment Utility
i want something that can be run in a sql window
0
 
LVL 8

Expert Comment

by:Surrano
Comment Utility
It can be run at sqlplus prompt if that's what you mean.
If you are looking for a pure sql query solution I'm afraid that's not possible. The closest thing is the pivot solution.
0
 

Author Comment

by:eagle_ea
Comment Utility
i tried that and i get this error
declare
  param_clause varchar2(1000) := '';
  value_clause varchar2(1000) := '';
  select_clause varchar2(1000) := '';
  from_clause varchar2(1000) := '';
  where_clause varchar2(1000) := '';
  stmt varchar2(4000) := '';
  i varchar2(16);
  myfile utl_file.file_type;
begin
  myfile := utl_file.fopen('TMP', 'myquery.sql','W');
  from_clause := ' ';
  for r in (select instance, sid from databases where sid like 'sid%' order by instance)
  loop
    i := 's'||r.instance;
    param_clause := param_clause||','||i||'.parameter';
    value_clause := param_clause||','||i||'.value';
    select_clause := select_clause||','||i||'.value '||r.sid;
    if from_clause = ' ' then
        from_clause := ' (select * from db_parameters where db_instance='||r.instance||') '||i;
    else
        from_clause := from_clause || ' full outer join (select * from db_parameters where db_instance='||r.instance||') '||i||
                   ' on (coalesce(null'||param_clause||')='||i||'.parameter)';
    end if;
    where_clause := where_clause || ' or coalesce(null'||value_clause||',''(null)'')<>nvl('||i||'.value,''(null)'')';
  end loop;
  stmt := 'select coalesce(null'||param_clause||') parameter'||select_clause||' from '||from_clause||' where 1=0 ' || where_clause||';';
  dbms_output.enable;
  utl_file.put_line(myfile, stmt);
  utl_file.fflush(myfile);
  utl_file.fclose(myfile);
end;
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 488
ORA-29283: invalid file operation
ORA-06512: at line 11
0
 
LVL 8

Expert Comment

by:Surrano
Comment Utility
Have you executed "create directory" beforehand? Was there an error with that?
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
>>> ORA-06502: PL/SQL: numeric or value error: character string buffer too small


change getstringval()

to

getclobval()
0
 
LVL 34

Expert Comment

by:Mark Geerlings
Comment Utility
These errors indicate a problem with the logical directory (TMP in this case):
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 488
ORA-29283: invalid file operation
ORA-06512: at line 11

Did you create that logical directory as a DBA?  Did you grant the owner of your procedure permission to read and write to this logical directory?
0
 
LVL 8

Expert Comment

by:Surrano
Comment Utility
oops indeed I forgot to mention the grant command...
grant all on directory tmp to public;
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now