Can i add IF statement in ORACLE SQL SELECT statement? PLEASE HELP!!! 1

[sommererdbeere]

Hi everyone,

I hope all of the experts here can help me to resolve this problem. this is somewhat complicated (at least to me), so that's why i'm asking for help and i really hope any of you can help. this is quit urgent too...

problems: oracle 8


i have 2 tables that i want to join together:
table1: inventory

ordno partno ordtype lineno

22 ii23445 rmj 1
22 ii23426 rmj 2
23 ii23345 rmj 1
c24 ii23446 os 1
e25 ii23443 os 2
25 ii23443 rmj 2

table2: rm

rm_no rm_ordno rm_partno rm_lineno

22 c23 ii23445 1
22 c24 ii23426 1
23 d70 ii23345 1
25 c23 ii23443 2

sql statement:
select
i.orderno,
i.partno,
i.ordtype,
i.lineno,
r.rm_no,
r.rm_ordno,
r.rm_partno,
r.rm_lineno
from inventory i, rm r
where

i.order_no = r.rm_ordno(+) and

i.partno = r.rm_partno(+)and
i.lineno = r.rm_lineno(+)

the "pink" text is where the problems occurred, i think.

if the above sql execute, i have rm_no rm_ordno rm_lineno
to be empty. meaning that nothing got pulled. if i replace r.rm_ordno to r.rm_no in "pink" part, it will only give me the data where i.order_no = r.rm_no.

i was thinking if i can have an "if" statment. like whenever it reads "rmj", i will have i.order_no = r.rma_no, so i can pull out the r.rm_ord_no.

i know it sounds complicated and that's why i'm seeking help from all of you.. please help as i am very stuck.. thank u thank u thank u for alll of your help.

m

 

[jad]

will:

select inventory.orderno, inventory.partno, inventory.ordtype,
       inventory.lineno, rm.rm_no, rm.rm_ordno, rm.rm_partno,
       rm.rm_lineno
from inventory, rm
where (
          (inventory.ordtype = 'rmj'
           and inventory.order_no = rm.rm_no)
       or inventory.order_no = rm.rm_ordno
  and inventory.partno = rm.rm_partno
  and inventory.lineno = rm.rm_lineno

work for you?

 

[johnherman]

Also, the Oracle DECODE statement can be used to achieve logic similar to an IF statement.

Sometimes the grass is greener on the other side because there is more manure there - original.

 

[sommererdbeere]

Hi jad and johnherman,

i have try the code, but it doesn't work. is it true that there is more to the code? 'coz it seems like it got cut off.

isn't it true decode is use to eliminate any duplicates?

please advice me . thank you thank you thank you

m

 

[BJCooperIT]

Decode is used to translate one value to another.
The syntax is: decode(your_value,if1,then1,if2,then2,... else value)

For example:

SQL> column dummy format a6
SQL> column answer format a6
SQL> select dummy, decode(dummy,'Y','Yes','N','No','?') answer from dual;

DUMMY  ANSWER
------ ------
X      ?

SQL> 
SQL> select dummy, decode(dummy,'X','123','ZZZ','456','?') answer from dual;

DUMMY  ANSWER
------ ------
X      123

^{Beware of false knowledge; it is more dangerous than ignorance. ~George Bernard Shaw}
Consultant/Custom Forms & PL/SQL - Oracle 8i & 9i - Windows 2000

 

[jogislav]

Hi all

The DECODE statement allows you to select a value depending on another value.

Example:

The statement

SELECT
DECODE(i.ordno, "22", "twenty-two", "23", "twenty-three", "none")
FROM INVENTORY I

will give you "twenty-two", if i.ordno equals "22", "twenty-three", if i.ordno equals "23" and "none" if it holds a different value. Duplicates are filtered out by using the DISTINCT function of a GROUP BY statement.

One thing:
Please be consistent with your column names. It is very confusing if you name columns once rm_no and once rma_no (or rm_ordno and rm_ord_no).

There is no IF-statement that lets you change the where-clause depending on the value of a column, and I think this is what you are trying to achieve. You will need to write several SQL-statements and combine them with a UNION statement. Please consult the Oracle documentation (SQL Reference) for details.

j

 

[jad]

select inventory.ordno, inventory.partno, inventory.ordtype,
       inventory.lineno, rm.rm_no, rm.rm_ordno, rm.rm_partno,
       rm.rm_lineno
from inventory, rm
where (
          (inventory.ordtype = 'rmj'
           and inventory.ordno = rm.rm_no)
       or inventory.ordno = rm.rm_ordno)
  and inventory.partno = rm.rm_partno
  and inventory.lineno = rm.rm_lineno;

i was sure that bracket was in there ...

you could possibly do it as a decode:

select inventory.ordno, inventory.partno, inventory.ordtype,
       inventory.lineno, rm.rm_no, rm.rm_ordno, rm.rm_partno,
       rm.rm_lineno
from inventory, rm
where inventory.ordno = decode(inventory.ordtype,
                               'rmj', rm.rm_no,
                               rm.rm_ordno)
  and inventory.partno = rm.rm_partno
  and inventory.lineno = rm.rm_lineno;

 

[sommererdbeere]

thanks everyone for the respones and sorry for the confusion of terms

i use decode in the statement:
select
i.orderno,
i.partno,
i.ordtype,
i.lineno,
r.rm_no,
r.rm_ordno,
r.rm_partno,
r.rm_lineno,
decode(i.orderno, cstr(r.rm_no), r.rm_ordno) rm_ord_no
from inventory i, rm r
where
i.partno = r.rm_partno(+)

however, i have repeated numbers.

so, do i use GROUP BY function as what jogislav sugguested?

pls. help..

m

 

[sommererdbeere]

thanks jab.. your statement works. cool..


however, i have another problem. is there anyway i can insert the data into one column w/o affecting the database? here is the outcome after i generate my sql:

sql:
select
i.orderno i_orderno,
i.partno i_partno,
i.ordtype i_ordtype,
i.lineno i_lineno,
r.rm_no r_rm_no,
r.rm_ordno r_rm_ordno,
r.rm_partno r_rm_partno,
r.rm_lineno r_rm_lineno,
decode(i.orderno, cstr(r.rm_no), r.rm_ordno) rm_ord_no
from inventory i, rm r
where
i.orderno = to_char(r.rm_no(+)) and
i.partno = r.rm_partno(+) and
i.lineno = r.rm_lineno(+)

outcome:
i_orderno r_rm_ordno i_partno r_rm_no
22 c23 ii23445 22
22 c24 ii23426 22
23 d70 ii23345 23
25 c23 ii23443 25
c24 ii23446
e25 ii23443

how can i add a coln (w/o passing into the database; meaning i do not want database will have this extra coln) next to rm_no that shows ALL the orderno?

the outome i want:[/red]

i_orderno r_rm_ordno i_partno r_rm_no r_ord&i_ord
22 c23 ii23445 22 22
22 c24 ii23426 22 22
23 d70 ii23345 23 23
25 c23 ii23443 25 25
c24 ii23446 c24
e25 ii23443 e25

please please help if any knows. thank u thank u
m

 

[jad]

in your example that column was the same as i_orderno ...

 

[sommererdbeere]

sorry.. my bad.. i want the out to look like:

i_orderno r_rm_ordno i_partno r_rm_no r_ord&i_ord
22 c23 ii23445 22 22
22 c24 ii23426 22 22
23 d70 ii23345 23 23
25 c23 ii23443 25 25
c24 ii23446 c24
e25 ii23443 e25
c23
c24
d70
c23

please help..

m

 

[sommererdbeere]

hi .. thanks for everyone who helped me.. i found out using nvl function can help me to add another column.

this is my code:

select
i.orderno i_orderno,
i.partno i_partno,
i.ordtype i_ordtype,
i.lineno i_lineno,
r.rm_no r_rm_no,
r.rm_ordno r_rm_ordno,
r.rm_partno r_rm_partno,
r.rm_lineno r_rm_lineno,
decode(i.orderno, to_char(r.rm_no), r.rm_ordno) rm_ord_no,
nvl(decode(i.orderno, to_char(r.rm_no), r.rm_ordno), i.orderno) comb_rm&i_ord_no,
from inventory i, rm r
where
i.orderno = to_char(r.rm_no(+)) and
i.partno = r.rm_partno(+) and
i.lineno = r.rm_lineno(+)

again.. thanks for those who help