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);
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);