Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 580
  • Last Modified:

SQL Query Help PLEASE!!!!!!

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
eagle_ea
Asked:
eagle_ea
  • 10
  • 8
  • 7
  • +3
2 Solutions
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
Do you have the sid column in the second table along with the other columns db_instance,  parameter , values ?
0
 
eagle_eaAuthor Commented:
no, sid field does not exists in the second table
0
 
SurranoCommented:
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
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
Naveen KumarProduction Manager / Application Support ManagerCommented:
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
 
sdstuberCommented:
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
 
Mark GeerlingsDatabase AdministratorCommented:
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
 
awking00Commented:
How did you determine that sga_target for sid3 was 4GB and not null?
0
 
eagle_eaAuthor Commented:
the number of sid's cabe vary from 2 to 20
0
 
sdstuberCommented:
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
 
sdstuberCommented:
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
 
eagle_eaAuthor Commented:
sdstuber:
FROM keyword not found where expected
0
 
SurranoCommented:
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
 
sdstuberCommented:
>> 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
 
SurranoCommented:
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
 
eagle_eaAuthor 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;
0
 
sdstuberCommented:
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
 
eagle_eaAuthor 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;
0
 
sdstuberCommented:
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
 
eagle_eaAuthor 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
0
 
eagle_eaAuthor Commented:
there are numeric and non-numeric value in the parametervalue, is it the cause of this problem?
0
 
SurranoCommented:
I think problem is the 2nd parameter of RPAD. Probably there are values longer than 20 / 50 characters.
0
 
eagle_eaAuthor Commented:
at this point i can get the result set, they are tab delimited.

can they be separated into columns?
0
 
SurranoCommented:
Have you tried my solution?

It should do that.
0
 
eagle_eaAuthor Commented:
i want something that can be run in a sql window
0
 
SurranoCommented:
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
 
eagle_eaAuthor 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
0
 
SurranoCommented:
Have you executed "create directory" beforehand? Was there an error with that?
0
 
sdstuberCommented:
>>> ORA-06502: PL/SQL: numeric or value error: character string buffer too small


change getstringval()

to

getclobval()
0
 
Mark GeerlingsDatabase AdministratorCommented:
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
 
SurranoCommented:
oops indeed I forgot to mention the grant command...
grant all on directory tmp to public;
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 10
  • 8
  • 7
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now