ok, smarties...

here's a v7 style DBA script that flattens the columns list of
USER_CONS_COLUMNS into comma separated list (part of a set of scripts to
check if FK constraints are indexed)

sample output:

TABLE_NAME C CONSTRAINT_NAME COLUMNS
------------ - --------------- --------------------
INV P SYS_C0016017 ID
INV_ITM P INV_ITM_PK INV_ITM, ITM_SEQ
INV_ITM R SYS_C0016020 INV_ITM

how would you improve it for
[_] 8i?
[_] 9i?
[_] 10g?
[_] 11x?

-- mcs

SELECT table_name
,constraint_type
,constraint_name
, col1
|| DECODE(col2, NULL, NULL, ', ')
|| col2
|| DECODE(col3, NULL, NULL, ', ')
|| col3
|| DECODE(col4, NULL, NULL, ', ')
|| col4
|| DECODE(col5, NULL, NULL, ', ')
|| col5 AS columns
FROM (SELECT table_name
,constraint_type
,constraint_name
,MIN(DECODE(position, 1, column_name)) col1
,MIN(DECODE(position, 2, column_name)) col2
,MIN(DECODE(position, 3, column_name)) col3
,MIN(DECODE(position, 4, column_name)) col4
,MIN(DECODE(position, 5, column_name)) col5
FROM (SELECT ucc.table_name
,uc.constraint_type
,ucc.constraint_name
,ucc.position
,ucc.column_name
FROM user_cons_columns ucc
,user_constraints uc
WHERE uc.constraint_type IN ('P', 'R')
AND uc.constraint_name = ucc.constraint_name
AND uc.table_name = ucc.table_name
AND uc.owner = ucc.owner)
GROUP BY table_name
,constraint_type
,constraint_name)
ORDER BY table_name
,constraint_type
,constraint_name

Mark C. Stock wrote:

> ok, smarties...
>
> here's a v7 style DBA script that flattens the columns list of
> USER_CONS_COLUMNS into comma separated list (part of a set of scripts to
> check if FK constraints are indexed)
>
> sample output:
>
> TABLE_NAME C CONSTRAINT_NAME COLUMNS
> ------------ - --------------- --------------------
> INV P SYS_C0016017 ID
> INV_ITM P INV_ITM_PK INV_ITM, ITM_SEQ
> INV_ITM R SYS_C0016020 INV_ITM
>
> how would you improve it for
> [_] 8i?
> [_] 9i?
> [_] 10g?
> [_] 11x?
>
> -- mcs
>
> SELECT table_name
> ,constraint_type
> ,constraint_name
> , col1
> || DECODE(col2, NULL, NULL, ', ')
> || col2
> || DECODE(col3, NULL, NULL, ', ')
> || col3
> || DECODE(col4, NULL, NULL, ', ')
> || col4
> || DECODE(col5, NULL, NULL, ', ')
> || col5 AS columns
> FROM (SELECT table_name
> ,constraint_type
> ,constraint_name
> ,MIN(DECODE(position, 1, column_name)) col1
> ,MIN(DECODE(position, 2, column_name)) col2
> ,MIN(DECODE(position, 3, column_name)) col3
> ,MIN(DECODE(position, 4, column_name)) col4
> ,MIN(DECODE(position, 5, column_name)) col5
> FROM (SELECT ucc.table_name
> ,uc.constraint_type
> ,ucc.constraint_name
> ,ucc.position
> ,ucc.column_name
> FROM user_cons_columns ucc
> ,user_constraints uc
> WHERE uc.constraint_type IN ('P', 'R')
> AND uc.constraint_name = ucc.constraint_name
> AND uc.table_name = ucc.table_name
> AND uc.owner = ucc.owner)
> GROUP BY table_name
> ,constraint_type
> ,constraint_name)
> ORDER BY table_name
> ,constraint_type
> ,constraint_name

I'm going to present this to my students tonight at class.

But in version 11x the syntax is:

SELECT whatiwant
FROM whereeveritis

--
Daniel Morgan
http://www.outreach.washington.edu/e...ad/oad_crs.asp
http://www.outreach.washington.edu/e...oa/aoa_crs.asp
damorgan@x.washington.edu
(replace 'x' with a 'u' to reply)

you forgot the hint:

| I'm going to present this to my students tonight at class.
|
| But in version 11x the syntax is:
|
| SELECT --+ faster_than_a_greased_pig
whatiwant
| FROM whereeveritis
|
| --
| Daniel Morgan
| http://www.outreach.washington.edu/e...ad/oad_crs.asp
| http://www.outreach.washington.edu/e...oa/aoa_crs.asp
| damorgan@x.washington.edu
| (replace 'x' with a 'u' to reply)
|

I thought in v11x the query was

<resultset>
return what i want here without any sql just xml ta
</resultset>

--
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer
******************************************
"Mark C. Stock" <mcstockX@Xenquery .com> wrote in message
news:aa2dnVmvtr6GJb_dRVn-vw@comcast.com...
> you forgot the hint:
>
> | I'm going to present this to my students tonight at class.
> |
> | But in version 11x the syntax is:
> |
> | SELECT --+ faster_than_a_greased_pig
> whatiwant
> | FROM whereeveritis
> |
> | --
> | Daniel Morgan
> | http://www.outreach.washington.edu/e...ad/oad_crs.asp
> | http://www.outreach.washington.edu/e...oa/aoa_crs.asp
> | damorgan@x.washington.edu
> | (replace 'x' with a 'u' to reply)
> |
>
>

Niall Litchfield wrote:
>
> I thought in v11x the query was
>
> <resultset>
> return what i want here without any sql just xml ta
> </resultset>
>

Apparently the proposed syntax, with RPM mode (Read Programmer's Mind -
TM} will be something like

<resultset>
AnswerHere
</resultset>

or, using the high performance modifier, it's

<resultset>
AnswerHereNOW
</resultset>

Mark C. Stock wrote:

> you forgot the hint:
>
> | I'm going to present this to my students tonight at class.
> |
> | But in version 11x the syntax is:
> |
> | SELECT --+ faster_than_a_greased_pig
> whatiwant
> | FROM whereeveritis
> |

ROFL

--
Daniel Morgan
http://www.outreach.washington.edu/e...ad/oad_crs.asp
http://www.outreach.washington.edu/e...oa/aoa_crs.asp
damorgan@x.washington.edu
(replace 'x' with a 'u' to reply)