Insert Decode help

[kss444]

I have a decode statment:
select B.account_number
, sum(decode(NLS_UPPER(db_cr_type), 'D', A.amount)) Total_Debits
, sum(decode(NLS_UPPER(db_cr_type), 'C', A.amount)) Total_Credits
, '0' OutStanding_Beginning
, count(A.amount) as New_Items
, count(decode(NLS_UPPER(A.state), 'R', A.state )) Items_Mathched
, count(decode(NLS_UPPER(A.state), 'O', A.state )) OutStanding_Items
, sum(decode(NLS_UPPER(db_cr_type), 'D', A.amount)) - sum(decode(NLS_UPPER(db_cr_type), 'C', A.amount)) Net

--, sum( decode( c2, 'col3', c3 ) ) col3
FROM ITEM A
LEFT JOIN ACCOUNT B
ON A.ACCOUNT = B.OBJECT_ID
WHERE B.ACCOUNT_NUMBER LIKE '25710%'
--and A.accepted_date between sysdate - 7 and sysdate
--and A.accepted_date between '28-Nov-2006' and '30-NOV-2006'
group by B.account_number
order by B.account_number

This returns me everything I need, now I want to convert this to an insert so I can insert it into a table called archnet.
Can anyone help me, thanks

 

[lewisp]

INSERT INTO archnet (column,column.....)
SELECT your_select_statement
/

The ORDER BY is redundant.

 

[kss444]

Didn't work, also I created seq_arch_net I am using as autonumber, sequence in oracle.
Error is ORA-00936 missing expression, thats all I get.

insert into arch_net values
select seq_arch_net_id.NEXTVAL,
select B.account_number
, sum(decode(NLS_UPPER(db_cr_type), 'D', A.amount)) Total_Debits
, sum(decode(NLS_UPPER(db_cr_type), 'C', A.amount)) Total_Credits
, '0' OutStanding_Beginning
, count(A.amount) as New_Items
, count(decode(NLS_UPPER(A.state), 'R', A.state )) Items_Mathched
, count(decode(NLS_UPPER(A.state), 'O', A.state )) OutStanding_Items
, sum(decode(NLS_UPPER(db_cr_type), 'D', A.amount)) - sum(decode(NLS_UPPER(db_cr_type), 'C', A.amount)) Net

FROM ITEM A
LEFT JOIN ACCOUNT B
ON A.ACCOUNT = B.OBJECT_ID
WHERE B.ACCOUNT_NUMBER LIKE '25710%'

group by B.account_number
order by B.account_number

 

[lewisp]

Look carefully at my code. It doesn't use VALUES.

 

[lewisp]

Also, you have 2 SELECTS.

 

[kss444]

I understand but if I remove the "Values" word then I get an error that says Missing VALUES keyword.
insert into arch_net (acct_id,total_debits,total_credits,outstanding_items_beginning,new_items_added,items_macthed
,outstanding_items_end,netamount)
seq_arch_net_id.NEXTVAL,
select B.account_number
, sum(decode(NLS_UPPER(db_cr_type), 'D', A.amount)) Total_Debits
, sum(decode(NLS_UPPER(db_cr_type), 'C', A.amount)) Total_Credits
, '0' OutStanding_Beginning
, count(A.amount) as New_Items
, count(decode(NLS_UPPER(A.state), 'R', A.state )) Items_Mathched
, count(decode(NLS_UPPER(A.state), 'O', A.state )) OutStanding_Items
, sum(decode(NLS_UPPER(db_cr_type), 'D', A.amount)) - sum(decode(NLS_UPPER(db_cr_type), 'C', A.amount)) Net

FROM ITEM A
LEFT JOIN ACCOUNT B
ON A.ACCOUNT = B.OBJECT_ID
WHERE B.ACCOUNT_NUMBER LIKE '25710%'

group by B.account_number
order by B.account_number

 

[kss444]

Here I get insert into arch_net
ORA-00936: missing expression

insert into arch_net
(acct_id,
account_number,
total_debits,
total_credits,
outstanding_items_beginning,
new_items_added,
items_macthed,
outstanding_items_end,
netamount) VALUES

select seq_arch_net_id.NEXTVAL,
B.account_number
, sum(decode(NLS_UPPER(db_cr_type), 'D', A.amount)) Total_Debits
, sum(decode(NLS_UPPER(db_cr_type), 'C', A.amount)) Total_Credits
, '0' OutStanding_Beginning
, count(A.amount) as New_Items
, count(decode(NLS_UPPER(A.state), 'R', A.state )) Items_Mathched
, count(decode(NLS_UPPER(A.state), 'O', A.state )) OutStanding_Items
, sum(decode(NLS_UPPER(db_cr_type), 'D', A.amount)) - sum(decode(NLS_UPPER(db_cr_type), 'C', A.amount)) Net

FROM ITEM A
LEFT JOIN ACCOUNT B
ON A.ACCOUNT = B.OBJECT_ID
WHERE B.ACCOUNT_NUMBER LIKE '25710%'

group by B.account_number

 

[lewisp]

insert into arch_net
(acct_id,
account_number,
total_debits,
total_credits,
outstanding_items_beginning,
new_items_added,
items_macthed,
outstanding_items_end,
netamount)   
SELECT seq_arch_net_id.NEXTVAL,
B.account_number
     , sum(decode(NLS_UPPER(db_cr_type), 'D', A.amount)) Total_Debits
     , sum(decode(NLS_UPPER(db_cr_type), 'C', A.amount)) Total_Credits
     , '0' OutStanding_Beginning
     , count(A.amount) as New_Items
     , count(decode(NLS_UPPER(A.state), 'R', A.state )) Items_Mathched
     , count(decode(NLS_UPPER(A.state), 'O', A.state )) OutStanding_Items
     , sum(decode(NLS_UPPER(db_cr_type), 'D', A.amount)) - sum(decode(NLS_UPPER(db_cr_type), 'C', A.amount)) Net    
FROM ITEM A
LEFT JOIN ACCOUNT B
ON    A.ACCOUNT = B.OBJECT_ID
WHERE  B.ACCOUNT_NUMBER LIKE '25710%'
group by B.account_number

 

[lewisp]

You use SELECT or VALUES, bot not both or neither.

 

[kss444]

Thanks for the help, I finally got it but I had to take out the seq_arch_net_id.NEXTVAL, it gave me errors if I put it before the select and it gave me errors when I had it in the select.

When in the select it gave me 2:51:33 PM ORA-02287: sequence number not allowed here.

But I believe I can get by with out it, I was just using it as my Primary Key, and having it autonumber. I can also play around with it.
Thanks again.