SQL statement hangs

[slechols]

Can anyone tell me what is wrong with this sql statement. Everytime I try to execute it, it hangs up.

CREATE OR REPLACE VIEW "MDR_VIEW" ("NIIN","D_DDR",
"D_MDR","R_DDR","R_MDR","MDR","MDR_SOURCE&quot AS
SELECT
m.niin,
d.d_ddr,
d.d_mdr,
r.r_ddr,
r.r_mdr,
CASE
when d.d_mdr>r.r_mdr THEN
d.d_mdr
when r.r_mdr>d.d_mdr THEN
r.rbl_mdr
when r.r_mdr=d.d_mdr THEN
d.d_mdr
ELSE
0
END CASE,
CASE
when d.d_mdr>r.r_mdr THEN
'DA'
when r.r_mdr>d.d_mdr THEN
'RBL'
when r.r_mdr=d.d_mdr and r.r_mdr <> 0 THEN
'DA'
ELSE
'USER'
END CASE
FROM
R_MDR_VIEW r,
D_MDR_VIEW d,
MASTER_NIIN m
WHERE
r.niin(+) = m.niin
AND m.niin = d.niin(+)

 

[LKBrwnDBA]

You have duplicate END CASE without an alias, how come you are not getting: ORA-00957: duplicate column name?

You have to code as: END AS <column alias>.






----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[SantaMufasa]

slechols, When you use the &quot;CASE&quot; construct in a select, then the column label for the result of the &quot;CASE&quot; statement becomes the first word that follows the &quot;END&quot; statement. In your case (no pun intended), for both CASEs, the word &quot;CASE&quot; is the first word following &quot;END&quot;. Having two expressions with the name, &quot;CASE&quot;, WOULD HAVE caused &quot;ORA-00957: duplicate column name&quot; had you not already specified the VIEW's pseudo-column-names, (&quot;NIIN&quot;,&quot;D_DDR&quot;,&quot;D_MDR&quot;,&quot;R_DDR&quot;,&quot;R_MDR&quot;,&quot;MDR&quot;,&quot;MDR_SOURCE&quot. As it is, though, the two expressions that you temporarily name &quot;CASE&quot;, then immediately become &quot;MDR&quot; and &quot;MDR_SOURCE&quot;, thus resolving the potential conflict.

Now, on to your &quot;hanging&quot; problem. I suspect it is not resulting from a syntax or column-naming problem. So, let's look deeper. When you say, &quot;...Everytime I try to execute it, it hangs up.&quot; What, precisely, is &quot;it&quot;?...Is the hanging-&quot;it&quot; your CREATE VIEW statement, or a SELECT against your newly created view, &quot;MDR_VIEW&quot;? Your answer will determine our follow-on troubleshooting.

 

[slechols]

I have tried running this query in two different places: sql-plus and enterprise manager. I can compile the MDR_view with no problem. When I try to show the contents, it hangs. When I copy this query to sql-plus, it also hangs. Could it be a problem with the source table? This is my ddl for the table.

CREATE TABLE MASTER_NIIN&quot; (&quot;NIIN&quot; VARCHAR2(10 byte)
NOT NULL, &quot;NSN&quot; VARCHAR2(20 byte),
CONSTRAINT &quot;PK_NIIN&quot; PRIMARY KEY(&quot;NIIN&quot)
TABLESPACE &quot;EVS&quot; PCTFREE 10 PCTUSED 0 INITRANS 1 MAXTRANS 255
STORAGE ( INITIAL 64K NEXT 0K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING

Is there a way to track the sql statement as it executes to see exactly what it is doing?

 

[slechols]

Oops. Should be

CREATE TABLE MASTER_NIIN (&quot;NIIN&quot; VARCHAR2(10 byte)
NOT NULL, &quot;NSN&quot; VARCHAR2(20 byte),
CONSTRAINT &quot;PK_NIIN&quot; PRIMARY KEY(&quot;NIIN&quot)
TABLESPACE &quot;EVS&quot; PCTFREE 10 PCTUSED 0 INITRANS 1 MAXTRANS 255
STORAGE ( INITIAL 64K NEXT 0K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING

I had an extra &quot; in the query when I edited out some extraneous info.

 

[sem]

I suppose that this is a problem not with table physical/logical structure or syntax, but rather with its content (big size) in conjunction with inefficient access path. Did you check execution plan of your query? Do you have indexes on them? Did you analyze that tables?
I suppose that in fact you have a long-running-stupid-job rather than real hang.

Regards, Dima