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

Naveen Kumar

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

eagle_ea

ASKER

no, sid field does not exists in the second table

Surrano

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 Kumar

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 Stuber

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 Geerlings

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.

awking00

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

eagle_ea

ASKER

the number of sid's cabe vary from 2 to 20

Sean Stuber

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 Stuber

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;

eagle_ea

ASKER

sdstuber:
FROM keyword not found where expected

SOLUTION

Surrano

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Sean Stuber

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

Surrano

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.

eagle_ea

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(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;

ASKER CERTIFIED SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

eagle_ea

ASKER

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 Stuber

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,

eagle_ea

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

eagle_ea

ASKER

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

Surrano

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

eagle_ea

ASKER

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

can they be separated into columns?

Surrano

Have you tried my solution?

It should do that.

eagle_ea

ASKER

i want something that can be run in a sql window

Surrano

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.

eagle_ea

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||'.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

Surrano

Have you executed "create directory" beforehand? Was there an error with that?

Sean Stuber

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

change getstringval()

to

getclobval()

Mark Geerlings

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?

Surrano

oops indeed I forgot to mention the grant command...
grant all on directory tmp to public;