help me out with this query

[lovekang]

/*
case1)
parameter const_no = 'C_01'
parameter user_no = 'u_1'

gives.
const_no | const_name | user_no
C_01 | CONST_01 | u_1

parameter const_no = 'C_01'
parameter user_no = 'u_2'
gives no rows
but I expect ;
const_no | const_name | user_no
C_01 | CONST_01 | u_2
*/

CREATE TABLE tbl_const
(
CONST_NO VARCHAR2(10),
CONST_NAME VARCHAR2(10)
);

CREATE TABLE tbl_user
(
user_NO VARCHAR2(10),
user_NAME VARCHAR2(10),
const_no varchar2(10)
);

insert into tbl_const(const_no,const_name) values('C_01','CONST_01');
insert into tbl_const(const_no,const_name) values('C_02','CONST_02');

insert into tbl_user(user_no,user_name,const_no) values('u_1','user_1','C_01');

--my query
SELECT A.CONST_NO, A.CONST_NAME, B.user_no
FROM tbl_const A, tbl_user B
WHERE A.CONST_NO='C_01' AND 'u_1' = B.user_no(+)

SELECT A.CONST_NO, A.CONST_NAME, B.user_no
FROM tbl_const A, tbl_user B
WHERE A.CONST_NO='C_01' AND 'u_2' = B.user_no(+)

 

[Dagon]

It's a bit difficult because you haven't said what the problem is. However, your queries look a bit odd because you're not actually joining tbl_const and tbl_user on anything. If the problem is that the second query doesn't return any rows, try changing it to:

SELECT A.CONST_NO, A.CONST_NAME, B.user_no
FROM tbl_const A, tbl_user B
WHERE A.CONST_NO='C_01' AND A.CONST_NO = B.CONST_NO (+) AND 'u_2' = B.user_no(+);

 

[lovekang]

yes you're right.

This is kind of confusing.
I solved this at application level not the query.

Thanks for your help.