joins

[misuk11]

Im in the process of migrating from sybase to oracle and am trying to convert the query below (which works ok in its original form in sybase)

SELECT TO_NUMBER(sb.ss_box_cod) AS "id",
(sb.ss_box_time_start_num/1000)*300 AS "txs_time",
(sb.ss_box_time_start_num/1000)*300 AS "sched_time",
((sb.ss_box_time_end_num - sb.ss_box_time_start_num)/1000)*300 AS "slot_duration",
sb.ss_box_nam AS "title",
t.ctr_contrt_num,
p.ctr_key AS "series_id",
p.prg_auk AS "programme_id",
t.prg_prog_num,
svc.service_id,
svc.schedule_class_id,
t.transhist_sched_dtm AS "txs_date",
NVL(tx.id, -1) AS "billings_id",
NVL(tx.billings_title, sb.ss_box_nam) AS "billings_title",
NVL(tx.passed_flag, 'N') AS "billings_passed_flag",
NVL(tx.changed_flag, 'N') AS "changed_flag",
NVL(tx.time_changed_flag, 'N') AS "time_flag",
NVL(tx.billings_time, (sb.ss_box_time_start_num/1000)*300) AS "bill_time",
NVL(tx.exclude_flag, 'N') AS "exclude_flag",
0 AS "billings_level",
0 AS "bill_type",
1 AS "update_id",
'N' AS "update_flag",
NVL(tx.group_flag, 'N') AS "group_flag"




FROM service_classes svc,
channel c,
transhist t,
aitprg ap,
prg p,
ss_box sb,
txs_billings_dets tx

WHERE svc.service_id = 1
AND svc.schedule_class_id = 1
AND svc.channel_key = c.channel_key
AND t.transhist_sched_dtm = TO_DATE('25 JAN 2005')
AND t.transhist_channel_cli_key = svc.channel_key
AND sb.transhist_key = t.transhist_key
AND t.transhist_tx_cod = c.channel_tx_cod
AND sb.ss_box_visible_flg = 1
AND sb.ss_box_current_flg = 1
AND sb.ss_box_cod = tx.ss_box_cod(+)
AND svc.service_id = tx.service_id(+)
AND t.transhist_sched_dtm = tx.txs_date(+)
AND t.ait_key = ap.ait_key(+)
AND t.prg_prog_num = ap.prg_prog_num(+)
AND ap.ctr_key = p.ctr_key(+)
AND ap.prg_prog_num = p.prg_prog_num(+)

Im getting an error saying

'a table may be outer joined to at most one other table'

can anyone tell me what Im doing wrong here ?

tia

[dharma]

You need to start looking at tahiti.oracle.com
anyway
AND t.transhist_sched_dtm = tx.txs_date(+)
AND t.ait_key = ap.ait_key(+)
AND t.prg_prog_num = ap.prg_prog_num(+)

as the message says, the table t cannot be outerjoined with more than one table. If really needed then start using ansi joins. (your previous posts answers have examples of them)

HTH

[misuk11]

thanks will check out the site you suggested.

I took a break from it yesterday and went back to it this morning and rewrote it as below, it seems to work OK.

FROM service_classes svc
INNER JOIN channel c
ON svc.channel_key = c.channel_key
INNER JOIN transhist t
ON svc.channel_key = t.transhist_channel_cli_key
AND c.channel_tx_cod = t.transhist_tx_cod
INNER JOIN ss_box sb
ON t.transhist_key = sb.transhist_key
LEFT OUTER JOIN txs_billings_dets tx
ON sb.ss_box_cod = tx.ss_box_cod
AND svc.service_id = tx.service_id
AND t.transhist_sched_dtm = tx.txs_date
LEFT OUTER JOIN aitprg ap
ON t.ait_key = ap.ait_key
AND t.prg_prog_num = ap.prg_prog_num
LEFT OUTER JOIN prg p
ON ap.ctr_key = p.ctr_key
AND ap.prg_prog_num = p.prg_prog_num

[misuk11]

im still having problems with this query. In desparation I went back to the sybase databse and used the visual query builder thats part of the Delphi IDE and constructed the query visually. The ANSI SQL that was generated below works OK in the visual query builder, but when I run it on the oracle database I get an error message telling me that DBO_TRANSHIST.PRG_PROG_NUM is an invalid identifier. If I remove that line I then get the same error message for Dbo_transhist.ait_key. Can anyone give me some pointers here, Im really struggling with this. Heres the ANSI SQL

SELECT Dbo_service_classes.channel_key
FROM service_classes Dbo_service_classes, channel Dbo_channel, transhist Dbo_transhist
LEFT OUTER JOIN txs_billings_dets Dbo_txs_billings_dets
ON (Dbo_service_classes.service_id = Dbo_txs_billings_dets.service_id) , ss_box Dbo_ss_box
LEFT OUTER JOIN aitprg Dbo_aitprg
ON (Dbo_transhist.ait_key = Dbo_aitprg.ait_key)
AND (Dbo_transhist.prg_prog_num = Dbo_aitprg.prg_prog_num)
LEFT OUTER JOIN prg Dbo_prg
ON (Dbo_aitprg.ctr_key = Dbo_prg.ctr_key)
AND (Dbo_aitprg.prg_prog_num = Dbo_prg.prg_prog_num)
WHERE (Dbo_service_classes.channel_key = Dbo_channel.channel_auk)
AND (Dbo_service_classes.channel_key = Dbo_transhist.transhist_channel_cli_key)
AND (Dbo_channel.channel_tx_cod = Dbo_transhist.transhist_tx_cod)
AND (Dbo_ss_box.transhist_key = Dbo_transhist.transhist_key)
AND ( (Dbo_service_classes.service_id = 1)
AND (Dbo_service_classes.schedule_class_id = 1)
AND (Dbo_ss_box.ss_box_visible_flg = 1)
AND (Dbo_transhist.transhist_sched_dtm = TO_DATE('25 JAN 2005')) )

many thanks.....