views:

32617

answers:

6

I have a number of code value tables that contain a code and a description with a Long id.

I now want to create an entry for an Account Type that references a number of codes, so I have something like this:

insert into account_type_standard (account_type_Standard_id,
tax_status_id, recipient_id)
( select account_type_standard_seq.nextval,
ts.tax_status_id, r.recipient_id
from tax_status ts, recipient r
where ts.tax_status_code = ?
and r.recipient_code = ?)

This retrieves the appropriate values from the tax_status and recipient tables if a match is found for their respective codes. Unfortunately, recipient_code is nullable, and therefore the ? substitution value could be null. Of course, the implicit join doesn't return a row, so a row doesn't get inserted into my table.

I've tried using NVL on the ? and on the r.recipient_id.

I've tried to force an outer join on the r.recipient_code = ? by adding (+), but it's not an explicit join, so Oracle still didn't add another row.

Anyone know of a way of doing this?

I can obviously modify the statement so that I do the lookup of the recipient_id externally, and have a ? instead of r.recipient_id, and don't select from the recipient table at all, but I'd prefer to do all this in 1 SQL statement.

+3  A: 

Outter joins don't work "as expected" in that case because you have explicitly told Oracle you only want data if that criteria on that table matches. In that scenario, the outter join is rendered useless.

A work-around

INSERT INTO account_type_standard 
  (account_type_Standard_id, tax_status_id, recipient_id) 
VALUES( 
  (SELECT account_type_standard_seq.nextval FROM DUAL),
  (SELECT tax_status_id FROM tax_status WHERE tax_status_code = ?), 
  (SELECT recipient_id FROM recipient WHERE recipient_code = ?)
)

[Edit] If you expect multiple rows from a sub-select, you can add ROWNUM=1 to each where clause OR use an aggregate such as MAX or MIN. This of course may not be the best solution for all cases.

[Edit] Per comment,

  (SELECT account_type_standard_seq.nextval FROM DUAL),

can be just

  account_type_standard_seq.nextval,
Greg Ogle
Won't this have a problem is there are multiple entries in tax_status or in recipient and what happens with NULL recipient_code?
Cade Roux
Just tried it, and null recipient code results in null for the recipient_id column on account_type_standard.Don't know about multiple entries, but in my case, the tables have unique codes, so I'm ok.
Mikezx6r
As per Tony Andrews, don't need Select from DUAL for sequence value. Can just have account_type_standard_seq.nextval.
Mikezx6r
+1  A: 

It was not clear to me in the question if ts.tax_status_code is a primary or alternate key or not. Same thing with recipient_code. This would be useful to know.

You can deal with the possibility of your bind variable being null using an OR as follows. You would bind the same thing to the first two bind variables.

If you are concerned about performance, you would be better to check if the values you intend to bind are null or not and then issue different SQL statement to avoid the OR.

insert into account_type_standard 
(account_type_Standard_id, tax_status_id, recipient_id)
(
select 
   account_type_standard_seq.nextval,
   ts.tax_status_id, 
   r.recipient_id
from tax_status ts, recipient r
where (ts.tax_status_code = ? OR (ts.tax_status_code IS NULL and ? IS NULL))
and (r.recipient_code = ? OR (r.recipient_code IS NULL and ? IS NULL))
WW
Doesn't this query have potential for being very slow?
Greg Ogle
Yes, hence my statement about "If you are concerned about performance..."
WW
+1  A: 

Try:

insert into account_type_standard (account_type_Standard_id, tax_status_id, recipient_id)
select account_type_standard_seq.nextval,
       ts.tax_status_id, 
       ( select r.recipient_id
         from recipient r
         where r.recipient_code = ?
       )
from tax_status ts
where ts.tax_status_code = ?
Tony Andrews
Do you know if this is more efficient than Oglester's answer? If so, I can be swayed to accept this response.Otherwise, I think having all the selects in their respective location is clearer than having a long from and where clause. I know that's what I had, but...
Mikezx6r
I don't suppose it is significantly better (or worse). However, Oglester's solution doesn't require the select from DUAL - I have posted a new answer that shows a modified version (just *slightly* more efficient)
Tony Andrews
+1  A: 

A slightly simplified version of Oglester's solution (the sequence doesn't require a select from DUAL:

INSERT INTO account_type_standard   
  (account_type_Standard_id, tax_status_id, recipient_id) 
VALUES(   
  account_type_standard_seq.nextval,
  (SELECT tax_status_id FROM tax_status WHERE tax_status_code = ?),
  (SELECT recipient_id FROM recipient WHERE recipient_code = ?)
)
Tony Andrews
A: 

Hi,

I am trying following query:

insert into received_messages(id, content, status) values (RECEIVED_MESSAGES_SEQ.NEXT_VAL, '', '');

It gives me following error:

ORA-00984, column not allowed here, the schema is following:

create table received_messages ( id number, content blob, status varchar(1) );

create sequence received_messages_seq MINVALUE 1 MAXVALUE 999999999999999999999999999 START WITH 1 INCREMENT BY 1 CACHE 20;

Can you please help.

Thanks, Sachin http://technologistics.blogspot.com

Sachin
Any reason you didn't create a new question?I believe the issue is with the content column. I don't think you can do a regular insert into a blob... There are explicit commands for that, but I don't recall what they are off the top of my head...
Mikezx6r
A: 

insert into received_messages(id, content, status) values (RECEIVED_MESSAGES_SEQ.NEXT_VAL, empty_blob(), '');

Arjun