asked on
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
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
Oracle Database
Last Comment
Surrano
Do you have the sid column in the second table along with the other columns db_instance, parameter , values ?
ASKER
no, sid field does not exists in the second table
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....
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,
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,
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)
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)
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.
How did you determine that sga_target for sid3 was 4GB and not null?
ASKER
the number of sid's cabe vary from 2 to 20
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;
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;
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(parametervalu
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;
SELECT RPAD('Parameter', 20) || LISTAGG(RPAD(sid, 50)) WITHIN GROUP (ORDER BY instance) FROM sids
UNION ALL
SELECT RPAD(parameter, 20) || LISTAGG(RPAD(parametervalu
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;
ASKER
sdstuber:
FROM keyword not found where expected
FROM keyword not found where expected
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
>> 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
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
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.
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.
ASKER
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(parametervalu
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;
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(parametervalu
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;
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
i modified to what i need as below and getting PVALUE a invalid identifier
SELECT RPAD('Parameter', 20)
|| EXTRACT(XMLAGG(XMLELEMENT(
FROM databases d
UNION ALL
SELECT RPAD(parameter, 20)
|| EXTRACT(XMLAGG(XMLELEMENT(
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;
SELECT RPAD('Parameter', 20)
|| EXTRACT(XMLAGG(XMLELEMENT(
FROM databases d
UNION ALL
SELECT RPAD(parameter, 20)
|| EXTRACT(XMLAGG(XMLELEMENT(
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;
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,
remove the alias from
p.pVALUE parametervalue,
so it's just
p.pVALUE,
ASKER
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
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "SYS.XMLTYPE", line 163
ASKER
there are numeric and non-numeric value in the parametervalue, is it the cause of this problem?
I think problem is the 2nd parameter of RPAD. Probably there are values longer than 20 / 50 characters.
ASKER
at this point i can get the result set, they are tab delimited.
can they be separated into columns?
can they be separated into columns?
ASKER
i want something that can be run in a sql window
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.
If you are looking for a pure sql query solution I'm afraid that's not possible. The closest thing is the pivot solution.
ASKER
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||'.pa
value_clause := param_clause||','||i||'.va
select_clause := select_clause||','||i||'.v
if from_clause = ' ' then
from_clause := ' (select * from db_parameters where db_instance='||r.instance|
else
from_clause := from_clause || ' full outer join (select * from db_parameters where db_instance='||r.instance|
' on (coalesce(null'||param_cla
end if;
where_clause := where_clause || ' or coalesce(null'||value_clau
end loop;
stmt := 'select coalesce(null'||param_clau
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
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||'.pa
value_clause := param_clause||','||i||'.va
select_clause := select_clause||','||i||'.v
if from_clause = ' ' then
from_clause := ' (select * from db_parameters where db_instance='||r.instance|
else
from_clause := from_clause || ' full outer join (select * from db_parameters where db_instance='||r.instance|
' on (coalesce(null'||param_cla
end if;
where_clause := where_clause || ' or coalesce(null'||value_clau
end loop;
stmt := 'select coalesce(null'||param_clau
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
Have you executed "create directory" beforehand? Was there an error with that?
>>> ORA-06502: PL/SQL: numeric or value error: character string buffer too small
change getstringval()
to
getclobval()
change getstringval()
to
getclobval()
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?
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?
oops indeed I forgot to mention the grant command...
grant all on directory tmp to public;
grant all on directory tmp to public;