SQL help!

[janne11]

Apologies for this probable newbie question!

Given

table orders

id   name country  postcode
---------------------------
1    A    NL       1330
2    A    NL       7320
3    B    NL       002100
4    C    CZ       13005
5    D    GB       AB3


table charges

country  lowpc  highpc  charge
------------------------------
NL       1000   4999    10
NL       5000   9999    20
GB       AA     ZZ      25



result

orders.name  orders.country  orders.postcode  ranges.charge
-----------------------------------------------------------
A            NL              1330             10
A            NL              7320             20
B **         NL              002100           30
C *          CZ              13005            30
D            GB              AB1              25

My problem is that I want orders part of the result even if no match is found in the "charges table.
For example *, for this country,postcode combo there is nothing defined in charges table.
Or for ** where the postcode format is invalid/not in any range.
I still want these part of the result with a blank charge or a default charge (e.g. 30 in the example).

Doable in one select or do I need to get all the orders and do one select for each order in the java code to calculate the result?

Any help appreciated!
ps DB is 10g and SP is not an option...

Jan

 

[Welshbird]

Have a look at OUTER JOINS and you will probably find the way to get the answer you need.

(And post back of course if you get stuck)

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[janne11]

Thanks, I have been playing around with outer joins but without luck.
For example,

select orders.name, orders.country, 
       orders.postcode, charges.charge
	   
from   orders, charges

where  orders.country   =  charges.country(+) 
and    postcode  between lowpc and highpc(+)

This did no difference...

 

[janne11]

Oh, this worked:

select orders.name, orders.country, 
       orders.postcode, charges.charge
	   
from   orders
left outer join charges

on orders.country  = charges.country 
and    postcode between lowpc and highpc

Will figure out later why...


How about the default value? This gives null/blank where no match is found:

A NL 1330 10
B NL 7330 20
F GB AB3 25
D CZ 13004
C NL 002100


Is there anyway to set a default value instead?

 

[Toe]

if i understand ywhat your asking correctly:

there is the function 'NVL' whcih can be used in select.

syntax NVL( <column>,<value if null>)

eg.

select NVL (aColumn,'no charge')
from xxxxx

this can obviousl ybe mixed in with other columns in the select and can have an 'alias' assigned if you want to refer to it.

I think the (+) syntax for the outer join might work if you split the 'between' statement into two conditions ...

where a < b (+)
and c > b (+)

though i'm not sure withut testing

 

[janne11]

Thanks, this worked perfectly (needed + syntax due to other such joins in the not-simplified query)

select orders.name, orders.country,
orders.postcode, nvl(charges.charge,30)

from orders, charges

where orders.country = charges.country (+)
and postcode >= lowpc (+)
and postcode <= highpc (+);