CODEWITHSUNDEEP
sqloracle-database
Jackie
Jackie

Reputation: 11

SQL decode or max with multiple rows and blank/null rows

I am trying to return 1 row, when the table holds both blank/null rows and multiple rows.

Table1.cust (unique) Table2.prop (3 field create a unique id with one to many cust) Table3.start (unique [cust, prop and start] some blank/null records exist for cust)

The issue is with the decode and table1, table2 and table 3 join. If I just do a join with table1 and no decode, I get extra records and don't get the null record, but when I combine the three tables, I still do not get the blank/null record.

Example Code

select table1.cust,
   table2.prop.(3 fields),
   decode(table3.startdate,' ','1901/01/01',
                            null,'1901/01/01',
                            table3.start)
from table1, table2(subquery), table3(subquery)
where table1 = table2(+)
and (table1.cust = table2.cust(+)
and table2.(3 fields) = table3.(3 fields))

Return Cust1, prop3, date3 Cust2 - Missing Cust3, prop2, date2 Cust4, prop1, date1

Example Code

select table1.cust,
   table2.prop.(3 fields),
   decode(table3.startdate,' ','1901/01/01',
                            null,'1901/01/01',
                            table3.start)
from table1, table2(subquery), table3(subquery)
where table1 = table2(+)
and (table1.cust = table2.cust(+)
and table2.(3 fields) = table3.(3 fields))

Return

Cust1, prop1, date1 (not the last record)
Cust1, prop2, date2 (not the last record)
Cust1, prop3, date3 - Good
Cust2, prop1, date1 - Good
Cust3, prop1, date1 (not the last record)
Cust3, prop2, date2 - Good
Cust4, prop1, date1 - Good

Upvotes: 1

Views: 256

Answers (1)

giorgiga
giorgiga

Reputation: 1778

I cannot be sure because I don't fully understand your writing, but the issue is most probably with:

and table2.(3 fields) = table3.(3 fields))

which excludes nulls on either side (null = null is not true in SQL and so in a where it causes rows to be excluded).

Try adding (+) (or or xxx is null) where appropriate to stop excluding the rows you want or, rather, abandon that horrible, obsolete and confusing syntax and go with from table1 left join table2 on table2.f = table1.f :)

Also, you may want to read about ternary logic.

Upvotes: 0

Related Questions