Duplicate columns name (sql developer)
0
Everytime that i want to filter a column in order to find a certain value, i got this error:
IMAGE
My query works just fine but it is not working properly.
This is my query:
SELECT
hp.party_name
, hca.account_number
, hca.cust_account_id
-- , hcsu.LOCATION customer_site_name
, hcas.cust_acct_site_id
, hcp.phone_number
, hcp.email_address
, CASE WHEN LENGTH(hcp.phone_number) > 25 then null else hcp.phone_number END
, hl.address1
, hl.address2
, hl.address3
, hl.address4
, hl.city
, hl.province
, hl.postal_code
, hcas.status
, DECODE( hcas.attribute5, 'PUP', 'Y', 'N' )
, hca.status
FROM apps.hz_cust_accounts hca
INNER JOIN apps.hz_cust_acct_sites_all hcas ON hca.cust_account_id = hcas.cust_account_id
INNER JOIN apps.hz_party_sites hps ON hcas.party_site_id = hps.party_site_id
INNER JOIN apps.hz_locations hl ON hps.location_id = hl.location_id
INNER JOIN apps.hz_parties hp ON hps.party_id = hp.party_id
LEFT JOIN (
SELECT
owner_table_id
, max(case when contact_point_type = 'PHONE' then phone_number end) phone_number
, max(case when contact_point_type = 'EMAIL' then email_address end) email_address
FROM hz_contact_points
WHERE status = 'A'
AND primary_flag = 'Y'
AND owner_table_name = 'HZ_PARTY_SITES'
AND contact_point_type IN ('EMAIL','PHONE')
GROUP BY
owner_table_id
) hcp ON hcas.party_site_id = hcp.owner_table_id
WHERE hcas.status = 'A'
AND hps.status = 'A'
AND hca.status = 'A'
AND hca.account_number = ''
;
I've tried without success to solve this.
Can you help me?
PS: I am using SQL DEVELOPER and oracle database
sql oracle oracle11g
edited Jan 2 at 4:34
Barbaros Özhan
13.9k71634
asked Jan 2 at 4:21
programming_amazingprogramming_amazing
315
add a comment |
0
Everytime that i want to filter a column in order to find a certain value, i got this error:
IMAGE
My query works just fine but it is not working properly.
This is my query:
SELECT
hp.party_name
, hca.account_number
, hca.cust_account_id
-- , hcsu.LOCATION customer_site_name
, hcas.cust_acct_site_id
, hcp.phone_number
, hcp.email_address
, CASE WHEN LENGTH(hcp.phone_number) > 25 then null else hcp.phone_number END
, hl.address1
, hl.address2
, hl.address3
, hl.address4
, hl.city
, hl.province
, hl.postal_code
, hcas.status
, DECODE( hcas.attribute5, 'PUP', 'Y', 'N' )
, hca.status
FROM apps.hz_cust_accounts hca
INNER JOIN apps.hz_cust_acct_sites_all hcas ON hca.cust_account_id = hcas.cust_account_id
INNER JOIN apps.hz_party_sites hps ON hcas.party_site_id = hps.party_site_id
INNER JOIN apps.hz_locations hl ON hps.location_id = hl.location_id
INNER JOIN apps.hz_parties hp ON hps.party_id = hp.party_id
LEFT JOIN (
SELECT
owner_table_id
, max(case when contact_point_type = 'PHONE' then phone_number end) phone_number
, max(case when contact_point_type = 'EMAIL' then email_address end) email_address
FROM hz_contact_points
WHERE status = 'A'
AND primary_flag = 'Y'
AND owner_table_name = 'HZ_PARTY_SITES'
AND contact_point_type IN ('EMAIL','PHONE')
GROUP BY
owner_table_id
) hcp ON hcas.party_site_id = hcp.owner_table_id
WHERE hcas.status = 'A'
AND hps.status = 'A'
AND hca.status = 'A'
AND hca.account_number = ''
;
I've tried without success to solve this.
Can you help me?
PS: I am using SQL DEVELOPER and oracle database
sql oracle oracle11g
edited Jan 2 at 4:34
Barbaros Özhan
13.9k71634
asked Jan 2 at 4:21
programming_amazingprogramming_amazing
315
2
your query considers both columns' name ofhcas.statusandhca.statusasstatus, and that error raises. Just alias them with different aliases.
– Barbaros Özhan
Jan 2 at 4:34
1
Yeah, you're right. My bad :/
– programming_amazing
Jan 2 at 4:36
add a comment |
0
0
0
Everytime that i want to filter a column in order to find a certain value, i got this error:
IMAGE
My query works just fine but it is not working properly.
This is my query:
SELECT
hp.party_name
, hca.account_number
, hca.cust_account_id
-- , hcsu.LOCATION customer_site_name
, hcas.cust_acct_site_id
, hcp.phone_number
, hcp.email_address
, CASE WHEN LENGTH(hcp.phone_number) > 25 then null else hcp.phone_number END
, hl.address1
, hl.address2
, hl.address3
, hl.address4
, hl.city
, hl.province
, hl.postal_code
, hcas.status
, DECODE( hcas.attribute5, 'PUP', 'Y', 'N' )
, hca.status
FROM apps.hz_cust_accounts hca
INNER JOIN apps.hz_cust_acct_sites_all hcas ON hca.cust_account_id = hcas.cust_account_id
INNER JOIN apps.hz_party_sites hps ON hcas.party_site_id = hps.party_site_id
INNER JOIN apps.hz_locations hl ON hps.location_id = hl.location_id
INNER JOIN apps.hz_parties hp ON hps.party_id = hp.party_id
LEFT JOIN (
SELECT
owner_table_id
, max(case when contact_point_type = 'PHONE' then phone_number end) phone_number
, max(case when contact_point_type = 'EMAIL' then email_address end) email_address
FROM hz_contact_points
WHERE status = 'A'
AND primary_flag = 'Y'
AND owner_table_name = 'HZ_PARTY_SITES'
AND contact_point_type IN ('EMAIL','PHONE')
GROUP BY
owner_table_id
) hcp ON hcas.party_site_id = hcp.owner_table_id
WHERE hcas.status = 'A'
AND hps.status = 'A'
AND hca.status = 'A'
AND hca.account_number = ''
;
I've tried without success to solve this.
Can you help me?
PS: I am using SQL DEVELOPER and oracle database
sql oracle oracle11g
edited Jan 2 at 4:34
Barbaros Özhan
13.9k71634
asked Jan 2 at 4:21
programming_amazingprogramming_amazing
315
Everytime that i want to filter a column in order to find a certain value, i got this error:
IMAGE
My query works just fine but it is not working properly.
This is my query:
SELECT
hp.party_name
, hca.account_number
, hca.cust_account_id
-- , hcsu.LOCATION customer_site_name
, hcas.cust_acct_site_id
, hcp.phone_number
, hcp.email_address
, CASE WHEN LENGTH(hcp.phone_number) > 25 then null else hcp.phone_number END
, hl.address1
, hl.address2
, hl.address3
, hl.address4
, hl.city
, hl.province
, hl.postal_code
, hcas.status
, DECODE( hcas.attribute5, 'PUP', 'Y', 'N' )
, hca.status
FROM apps.hz_cust_accounts hca
INNER JOIN apps.hz_cust_acct_sites_all hcas ON hca.cust_account_id = hcas.cust_account_id
INNER JOIN apps.hz_party_sites hps ON hcas.party_site_id = hps.party_site_id
INNER JOIN apps.hz_locations hl ON hps.location_id = hl.location_id
INNER JOIN apps.hz_parties hp ON hps.party_id = hp.party_id
LEFT JOIN (
SELECT
owner_table_id
, max(case when contact_point_type = 'PHONE' then phone_number end) phone_number
, max(case when contact_point_type = 'EMAIL' then email_address end) email_address
FROM hz_contact_points
WHERE status = 'A'
AND primary_flag = 'Y'
AND owner_table_name = 'HZ_PARTY_SITES'
AND contact_point_type IN ('EMAIL','PHONE')
GROUP BY
owner_table_id
) hcp ON hcas.party_site_id = hcp.owner_table_id
WHERE hcas.status = 'A'
AND hps.status = 'A'
AND hca.status = 'A'
AND hca.account_number = ''
;
I've tried without success to solve this.
Can you help me?
PS: I am using SQL DEVELOPER and oracle database
sql oracle oracle11g
sql oracle oracle11g
edited Jan 2 at 4:34
Barbaros Özhan
13.9k71634
asked Jan 2 at 4:21
programming_amazingprogramming_amazing
315
edited Jan 2 at 4:34
Barbaros Özhan
13.9k71634
asked Jan 2 at 4:21
programming_amazingprogramming_amazing
315
edited Jan 2 at 4:34
Barbaros Özhan
13.9k71634
edited Jan 2 at 4:34
Barbaros Özhan
13.9k71634
edited Jan 2 at 4:34
Barbaros Özhan
13.9k71634
13.9k71634
asked Jan 2 at 4:21
programming_amazingprogramming_amazing
315
asked Jan 2 at 4:21
programming_amazingprogramming_amazing
315
asked Jan 2 at 4:21
programming_amazingprogramming_amazing
315
315
2
your query considers both columns' name ofhcas.statusandhca.statusasstatus, and that error raises. Just alias them with different aliases.
– Barbaros Özhan
Jan 2 at 4:34
1
Yeah, you're right. My bad :/
– programming_amazing
Jan 2 at 4:36
add a comment |
2
your query considers both columns' name ofhcas.statusandhca.statusasstatus, and that error raises. Just alias them with different aliases.
– Barbaros Özhan
Jan 2 at 4:34
1
Yeah, you're right. My bad :/
– programming_amazing
Jan 2 at 4:36
2
2
your query considers both columns' name of
hcas.status and hca.status as status, and that error raises. Just alias them with different aliases.– Barbaros Özhan
Jan 2 at 4:34
your query considers both columns' name of
hcas.status and hca.status as status, and that error raises. Just alias them with different aliases.– Barbaros Özhan
Jan 2 at 4:34
1
1
Yeah, you're right. My bad :/
– programming_amazing
Jan 2 at 4:36
Yeah, you're right. My bad :/
– programming_amazing
Jan 2 at 4:36
add a comment |
1 Answer
1
active
oldest
votes
1
You can try below using an alias for column name
SELECT
hp.party_name
, hca.account_number
, hca.cust_account_id
-- , hcsu.LOCATION customer_site_name
, hcas.cust_acct_site_id
, hcp.phone_number
, hcp.email_address
, CASE WHEN LENGTH(hcp.phone_number) > 25 then null else hcp.phone_number END
, hl.address1
, hl.address2
, hl.address3
, hl.address4
, hl.city
, hl.province
, hl.postal_code
, hcas.status as hcas_status
, DECODE( hcas.attribute5, 'PUP', 'Y', 'N' )
, hca.status as hca_status
FROM apps.hz_cust_accounts hca
INNER JOIN apps.hz_cust_acct_sites_all hcas ON hca.cust_account_id = hcas.cust_account_id
INNER JOIN apps.hz_party_sites hps ON hcas.party_site_id = hps.party_site_id
INNER JOIN apps.hz_locations hl ON hps.location_id = hl.location_id
INNER JOIN apps.hz_parties hp ON hps.party_id = hp.party_id
LEFT JOIN (
SELECT
owner_table_id
, max(case when contact_point_type = 'PHONE' then phone_number end) phone_number
, max(case when contact_point_type = 'EMAIL' then email_address end) email_address
FROM hz_contact_points
WHERE status = 'A'
AND primary_flag = 'Y'
AND owner_table_name = 'HZ_PARTY_SITES'
AND contact_point_type IN ('EMAIL','PHONE')
GROUP BY
owner_table_id
) hcp ON hcas.party_site_id = hcp.owner_table_id
WHERE hcas.status = 'A'
AND hps.status = 'A'
AND hca.status = 'A'
AND hca.account_number = ''
;
answered Jan 2 at 4:24
fa06fa06
15.5k21018
1
Oh, yeah. Thank you buddy!
– programming_amazing
Jan 2 at 4:36
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54001152%2fduplicate-columns-name-sql-developer%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
1
You can try below using an alias for column name
SELECT
hp.party_name
, hca.account_number
, hca.cust_account_id
-- , hcsu.LOCATION customer_site_name
, hcas.cust_acct_site_id
, hcp.phone_number
, hcp.email_address
, CASE WHEN LENGTH(hcp.phone_number) > 25 then null else hcp.phone_number END
, hl.address1
, hl.address2
, hl.address3
, hl.address4
, hl.city
, hl.province
, hl.postal_code
, hcas.status as hcas_status
, DECODE( hcas.attribute5, 'PUP', 'Y', 'N' )
, hca.status as hca_status
FROM apps.hz_cust_accounts hca
INNER JOIN apps.hz_cust_acct_sites_all hcas ON hca.cust_account_id = hcas.cust_account_id
INNER JOIN apps.hz_party_sites hps ON hcas.party_site_id = hps.party_site_id
INNER JOIN apps.hz_locations hl ON hps.location_id = hl.location_id
INNER JOIN apps.hz_parties hp ON hps.party_id = hp.party_id
LEFT JOIN (
SELECT
owner_table_id
, max(case when contact_point_type = 'PHONE' then phone_number end) phone_number
, max(case when contact_point_type = 'EMAIL' then email_address end) email_address
FROM hz_contact_points
WHERE status = 'A'
AND primary_flag = 'Y'
AND owner_table_name = 'HZ_PARTY_SITES'
AND contact_point_type IN ('EMAIL','PHONE')
GROUP BY
owner_table_id
) hcp ON hcas.party_site_id = hcp.owner_table_id
WHERE hcas.status = 'A'
AND hps.status = 'A'
AND hca.status = 'A'
AND hca.account_number = ''
;
answered Jan 2 at 4:24
fa06fa06
15.5k21018
1
Oh, yeah. Thank you buddy!
– programming_amazing
Jan 2 at 4:36
add a comment |
1
You can try below using an alias for column name
SELECT
hp.party_name
, hca.account_number
, hca.cust_account_id
-- , hcsu.LOCATION customer_site_name
, hcas.cust_acct_site_id
, hcp.phone_number
, hcp.email_address
, CASE WHEN LENGTH(hcp.phone_number) > 25 then null else hcp.phone_number END
, hl.address1
, hl.address2
, hl.address3
, hl.address4
, hl.city
, hl.province
, hl.postal_code
, hcas.status as hcas_status
, DECODE( hcas.attribute5, 'PUP', 'Y', 'N' )
, hca.status as hca_status
FROM apps.hz_cust_accounts hca
INNER JOIN apps.hz_cust_acct_sites_all hcas ON hca.cust_account_id = hcas.cust_account_id
INNER JOIN apps.hz_party_sites hps ON hcas.party_site_id = hps.party_site_id
INNER JOIN apps.hz_locations hl ON hps.location_id = hl.location_id
INNER JOIN apps.hz_parties hp ON hps.party_id = hp.party_id
LEFT JOIN (
SELECT
owner_table_id
, max(case when contact_point_type = 'PHONE' then phone_number end) phone_number
, max(case when contact_point_type = 'EMAIL' then email_address end) email_address
FROM hz_contact_points
WHERE status = 'A'
AND primary_flag = 'Y'
AND owner_table_name = 'HZ_PARTY_SITES'
AND contact_point_type IN ('EMAIL','PHONE')
GROUP BY
owner_table_id
) hcp ON hcas.party_site_id = hcp.owner_table_id
WHERE hcas.status = 'A'
AND hps.status = 'A'
AND hca.status = 'A'
AND hca.account_number = ''
;
answered Jan 2 at 4:24
fa06fa06
15.5k21018
1
Oh, yeah. Thank you buddy!
– programming_amazing
Jan 2 at 4:36
add a comment |
1
1
1
You can try below using an alias for column name
SELECT
hp.party_name
, hca.account_number
, hca.cust_account_id
-- , hcsu.LOCATION customer_site_name
, hcas.cust_acct_site_id
, hcp.phone_number
, hcp.email_address
, CASE WHEN LENGTH(hcp.phone_number) > 25 then null else hcp.phone_number END
, hl.address1
, hl.address2
, hl.address3
, hl.address4
, hl.city
, hl.province
, hl.postal_code
, hcas.status as hcas_status
, DECODE( hcas.attribute5, 'PUP', 'Y', 'N' )
, hca.status as hca_status
FROM apps.hz_cust_accounts hca
INNER JOIN apps.hz_cust_acct_sites_all hcas ON hca.cust_account_id = hcas.cust_account_id
INNER JOIN apps.hz_party_sites hps ON hcas.party_site_id = hps.party_site_id
INNER JOIN apps.hz_locations hl ON hps.location_id = hl.location_id
INNER JOIN apps.hz_parties hp ON hps.party_id = hp.party_id
LEFT JOIN (
SELECT
owner_table_id
, max(case when contact_point_type = 'PHONE' then phone_number end) phone_number
, max(case when contact_point_type = 'EMAIL' then email_address end) email_address
FROM hz_contact_points
WHERE status = 'A'
AND primary_flag = 'Y'
AND owner_table_name = 'HZ_PARTY_SITES'
AND contact_point_type IN ('EMAIL','PHONE')
GROUP BY
owner_table_id
) hcp ON hcas.party_site_id = hcp.owner_table_id
WHERE hcas.status = 'A'
AND hps.status = 'A'
AND hca.status = 'A'
AND hca.account_number = ''
;
answered Jan 2 at 4:24
fa06fa06
15.5k21018
You can try below using an alias for column name
SELECT
hp.party_name
, hca.account_number
, hca.cust_account_id
-- , hcsu.LOCATION customer_site_name
, hcas.cust_acct_site_id
, hcp.phone_number
, hcp.email_address
, CASE WHEN LENGTH(hcp.phone_number) > 25 then null else hcp.phone_number END
, hl.address1
, hl.address2
, hl.address3
, hl.address4
, hl.city
, hl.province
, hl.postal_code
, hcas.status as hcas_status
, DECODE( hcas.attribute5, 'PUP', 'Y', 'N' )
, hca.status as hca_status
FROM apps.hz_cust_accounts hca
INNER JOIN apps.hz_cust_acct_sites_all hcas ON hca.cust_account_id = hcas.cust_account_id
INNER JOIN apps.hz_party_sites hps ON hcas.party_site_id = hps.party_site_id
INNER JOIN apps.hz_locations hl ON hps.location_id = hl.location_id
INNER JOIN apps.hz_parties hp ON hps.party_id = hp.party_id
LEFT JOIN (
SELECT
owner_table_id
, max(case when contact_point_type = 'PHONE' then phone_number end) phone_number
, max(case when contact_point_type = 'EMAIL' then email_address end) email_address
FROM hz_contact_points
WHERE status = 'A'
AND primary_flag = 'Y'
AND owner_table_name = 'HZ_PARTY_SITES'
AND contact_point_type IN ('EMAIL','PHONE')
GROUP BY
owner_table_id
) hcp ON hcas.party_site_id = hcp.owner_table_id
WHERE hcas.status = 'A'
AND hps.status = 'A'
AND hca.status = 'A'
AND hca.account_number = ''
;
answered Jan 2 at 4:24
fa06fa06
15.5k21018
answered Jan 2 at 4:24
fa06fa06
15.5k21018
answered Jan 2 at 4:24
fa06fa06
15.5k21018
answered Jan 2 at 4:24
fa06fa06
15.5k21018
15.5k21018
1
Oh, yeah. Thank you buddy!
– programming_amazing
Jan 2 at 4:36
add a comment |
1
Oh, yeah. Thank you buddy!
– programming_amazing
Jan 2 at 4:36
1
1
Oh, yeah. Thank you buddy!
– programming_amazing
Jan 2 at 4:36
Oh, yeah. Thank you buddy!
– programming_amazing
Jan 2 at 4:36
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54001152%2fduplicate-columns-name-sql-developer%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
2
your query considers both columns' name of
hcas.statusandhca.statusasstatus, and that error raises. Just alias them with different aliases.– Barbaros Özhan
Jan 2 at 4:34
1
Yeah, you're right. My bad :/
– programming_amazing
Jan 2 at 4:36