We help IT Professionals succeed at work.

SQL Query Help PLEASE!!!!!!

eagle_ea
eagle_ea asked
on
724 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
Comment
Watch Question

Naveen KumarProduction Manager / Application Support Manager
CERTIFIED EXPERT

Commented:
Do you have the sid column in the second table along with the other columns db_instance,  parameter , values ?

Author

Commented:
no, sid field does not exists in the second table
Surranoapplication manager

Commented:
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....
Naveen KumarProduction Manager / Application Support Manager
CERTIFIED EXPERT

Commented:
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,
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
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)
Mark GeerlingsDatabase Administrator
CERTIFIED EXPERT

Commented:
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.
awking00Information Technology Specialist
CERTIFIED EXPERT

Commented:
How did you determine that sga_target for sid3 was 4GB and not null?

Author

Commented:
the number of sid's cabe vary from 2 to 20
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
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;
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
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;

Author

Commented:
sdstuber:
FROM keyword not found where expected
Surranoapplication manager
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
>> 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
Surranoapplication manager

Commented:
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.

Author

Commented:
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;
Database Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
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;
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
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,

Author

Commented:
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

Author

Commented:
there are numeric and non-numeric value in the parametervalue, is it the cause of this problem?
Surranoapplication manager

Commented:
I think problem is the 2nd parameter of RPAD. Probably there are values longer than 20 / 50 characters.

Author

Commented:
at this point i can get the result set, they are tab delimited.

can they be separated into columns?
Surranoapplication manager

Commented:
Have you tried my solution?

It should do that.

Author

Commented:
i want something that can be run in a sql window
Surranoapplication manager

Commented:
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.

Author

Commented:
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
Surranoapplication manager

Commented:
Have you executed "create directory" beforehand? Was there an error with that?
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
>>> ORA-06502: PL/SQL: numeric or value error: character string buffer too small


change getstringval()

to

getclobval()
Mark GeerlingsDatabase Administrator
CERTIFIED EXPERT

Commented:
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?
Surranoapplication manager

Commented:
oops indeed I forgot to mention the grant command...
grant all on directory tmp to public;
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.