Cannot do INSERT INTO

[TracyB250]

I am new to SQL and am having difficulties inserting data into my file.

I have two files. Both the priority_address and the mailing_address files have the exact same layout:
PRIORITY
ACCOUNT
TENANT
ADDRESS1
ADDRESS2
ADDRESS3
ADDRESS4
CITY
STATE
POSTAL

The Priority_Address file may have up to three records per account with a priority of 1, 2 or 3. Each of these records have a different address. I want to select one record per account with the lowest min(priority) and write that record out to the mailing_address file.

My select works fine, but when I try to add the insert, I get an error on the POSTAL field.

ORA-00904: "PA01"."POSTAL": invalid identifier

Any help would be GREATLY appreciated!!!!



(SELECT

PA01.priority ,
PA01.account ,
PA01.tenant ,
PA01.address1,
PA01.address2,
PA01.address3,
PA01.address4,
PA01.city,
PA01.state,
PA01.postal

FROM
isa.priority_address PA01

Join (
Select Distinct
PA02.account,
PA02.tenant,
min(PA02.priority) as Min_Priority

FROM
isa.priority_address PA02

GROUP BY
PA02.account,
PA02.Tenant

) PA02 on

ISA.PA01.Account = PA02.Account
and
ISA.PA01.Tenant = PA02.Tenant
and
ISA.PA01.Priority = PA02.Min_Priority);

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
INSERT INTO ISA.MAILING_ADDRESS
(PA01.priority,
PA01.account,
PA01.tenant,
PA01.address1,
PA01.address2,
PA01.address3,
PA01.address4,
PA01.city,
PA01.state,
PA01.postal)

(SELECT

PA01.priority ,
PA01.account ,
PA01.tenant ,
PA01.address1,
PA01.address2,
PA01.address3,
PA01.address4,
PA01.city,
PA01.state,
PA01.postal

FROM
isa.priority_address PA01

Join (
Select Distinct
PA02.account,
PA02.tenant,
min(PA02.priority) as Min_Priority

FROM
isa.priority_address PA02

GROUP BY
PA02.account,
PA02.Tenant

) PA02 on

ISA.PA01.Account = PA02.Account
and
ISA.PA01.Tenant = PA02.Tenant
and
ISA.PA01.Priority = PA02.Min_Priority);

 

[gmmastros]

ORA-00904: "PA01"."POSTAL": invalid identifier

That is an ORACLE error message. You'll probably have better luck if you post in an Oracle specific forum. This forum is specific for SQL Server.

Your query would fail in SQL Server too because you have parenthesis around your select statement. I encourage you to try your query after removing those parenthesis.

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++   
INSERT INTO ISA.MAILING_ADDRESS
 (PA01.priority,
  PA01.account,
  PA01.tenant,
  PA01.address1,
  PA01.address2,
  PA01.address3,
  PA01.address4,
  PA01.city,
  PA01.state,
  PA01.postal)
  
[!][s]([/s][/!]SELECT
     
  PA01.priority ,
  PA01.account ,
  PA01.tenant ,
  PA01.address1,
  PA01.address2,
  PA01.address3,
  PA01.address4,
  PA01.city,
  PA01.state,
  PA01.postal  
    
FROM
isa.priority_address PA01
  
Join (
Select Distinct
PA02.account,
PA02.tenant,
min(PA02.priority) as Min_Priority

FROM
isa.priority_address PA02

GROUP BY
 PA02.account,
 PA02.Tenant

) PA02 on
  
 ISA.PA01.Account = PA02.Account
and
 ISA.PA01.Tenant = PA02.Tenant
and
 ISA.PA01.Priority = PA02.Min_Priority[!][s])[/s][/!];

I highlighted the parenthesis that should be removed in red.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[TracyB250]

I had already tried that. I get the same error.

 

[SQLSister]

I'm not an Oracle person either but in SQL Server you woudl need to change

INSERT INTO ISA.MAILING_ADDRESS  (PA01.priority,  PA01.account,  PA01.tenant,  PA01.address1,  PA01.address2,  PA01.address3,  PA01.address4,   PA01.city,  PA01.state,  PA01.postal)

to

INSERT INTO ISA.MAILING_ADDRESS  (priority,  account,  tenant,  address1, address2, address3,  address4,   city,  state,  postal)

becasue you can't alias the items inteh insert side of the statement, they belong to tha table you have identified to insert into and can't be any other table.

"NOTHING is more important in a database than integrity." ESquared

 

[gmmastros]

I just had another thought...

Your insert into shows a table and columns. I think you want to remove the tables. Like this:

INSERT INTO ISA.MAILING_ADDRESS
 ([!][s]PA01.[/s][/!]priority,
  [!][s]PA01.[/s][/!]account,
  [!][s]PA01.[/s][/!]tenant,
  etc....

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

Sorry sis. I didn't see that you had posted. Everyone... please continue ignoring me.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[TracyB250]

Thank you all for your input. Removing the alias did the trick!