SQL Basics

 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
User Name:
Password:
Remember me



Go Back   Tutorialized ForumsDatabasesSQL Basics

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread Tutorialized Forums Sponsor:
  #1  
Old November 28th, 2012, 05:26 AM
dado123 dado123 is offline
Registered User
Tutorialized Newbie (0 - 499 posts)
 
Join Date: Nov 2012
Posts: 1 dado123 User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 10 m
Reputation Power: 0
To remove duplicate records from an sql query.

Hi,
I am running an Sql query for which Trx_Number is being hardcoded and three different trx_numbers have been given.Select query itself contains 'Distinct'.But still i am getting duplicate records.Ideally query should return only three rows but it is returning 15 rows.Plz help

Query:
SELECT DISTINCT
RCT.TRX_NUMBER,
HP.PARTY_ID,
HPS.PARTY_SITE_ID,
HZL.LOCATION_ID,
hp.party_name,
hca.account_number customer_number,
hps.party_site_name,hps.IDENTIFYING_ADDRESS_FLAG,h ZL.country,
hZL.country,
(SELECT RegistrationEO.PARTY_TAX_PROFILE_ID
|| ' , '
|| RegistrationEO.REGISTRATION_ID
|| ' , '
|| RegistrationEO.TAX_REGIME_CODE
|| ' , '
|| RegistrationEO.TAX
|| ' , '
|| RegistrationEO.TAX_JURISDICTION_CODE
|| ' , '
|| RegistrationEO.REP_PARTY_TAX_NAME
|| ' , '
|| RegistrationEO.REGISTRATION_NUMBER
FROM ZX_REGISTRATIONS RegistrationEO,
HZ_PARTIES RepTaxAuthorities,
HZ_PARTIES CollTaxAuthorities,
HZ_PARTIES IssTaxAuthorities,
HZ_PARTIES BankTaxAuthorities,
HZ_PARTIES BranchTaxAuthorities,
ZX_PARTY_TAX_PROFILE PTP,
ZX_PARTY_TAX_PROFILE RepAuthPTP,
ZX_PARTY_TAX_PROFILE CollAuthPTP,
HR_LOCATIONS loc,
ZX_REGIMES_V Regime
WHERE RegistrationEO.REP_TAX_AUTHORITY_ID =
RepAuthPTP.PARTY_TAX_PROFILE_ID(+)
AND RepAuthPTP.PARTY_ID = RepTaxAuthorities.PARTY_ID(+)
AND RegistrationEO.COLL_TAX_AUTHORITY_ID =
CollAuthPTP.PARTY_TAX_PROFILE_ID(+)
AND CollAuthPTP.PARTY_ID = CollTaxAuthorities.PARTY_ID(+)
AND RegistrationEO.BANK_ID = BankTaxAuthorities.PARTY_ID(+)
AND RegistrationEO.BANK_BRANCH_ID =
BranchTaxAuthorities.PARTY_ID(+)
AND RegistrationEO.TAX_AUTHORITY_ID =
PTP.PARTY_TAX_PROFILE_ID(+)
AND PTP.PARTY_ID = IssTaxAuthorities.PARTY_ID(+)
AND RegistrationEO.LEGAL_LOCATION_ID = loc.LOCATION_ID(+)
AND Regime.TAX_REGIME_CODE(+) = RegistrationEO.TAX_REGIME_CODE
AND RegistrationEO.PARTY_TAX_PROFILE_ID = zptp.party_id)
TAX_Details,
hcsu.SITE_USE_CODE,
rbs.name TRANSACTION_SOURCE,
rctt.name TRANSACTION_TYPE,
TO_CHAR (gd.gl_date, 'DD-MON-RRRR') GL_DATE,
TO_CHAR (rct.trx_date, 'DD-MON-RRRR') TRANSACTION_DATE,
rct.trx_number,
rct.org_id,
zl.tax_date,
zl.tax_rate_code,
(NVL (zl.tax_currency_conversion_rate, 0) * NVL (zl.line_amt, 0))
NET_AMOUNT,
(NVL (NVL (zl.tax_amt_funcl_curr, zl.tax_amt), 0)) TAX_AMOUNT,
(NVL (zl.tax_currency_conversion_rate, 0) * NVL (zl.line_amt, 0)
+ NVL (NVL (zl.tax_amt_funcl_curr, zl.tax_amt), 0))
GROSS_AMOUNT,
rctl.description,
rct.comments
FROM ra_customer_trx_all rct,
ra_customer_trx_lines_all rctl,
hz_cust_site_uses_all hcsu,
hz_cust_acct_sites_all hcs,
hz_cust_accounts_all hca,
hz_parties hp,
hz_party_sites hps,
zx_party_tax_profile zptp,
ra_batch_sources_all rbs,
ra_cust_trx_line_gl_dist_all gd,
ra_cust_trx_types_all rctt,
zx_lines_v zl,
fnd_territories_vl flv,
hz_locations hzl
WHERE rctl.line_type = 'LINE' AND RCT.TRX_NUMBER IN('7017341787','7017347243','7017342102')
AND zl.TRX_LINE_NUMBER = rctl.line_number
AND rct.customer_trx_id = rctl.customer_trx_id
-- AND rct.customer_trx_id BETWEEN 924547 AND 1040664 --AUG
-- AND rct.customer_trx_id BETWEEN 921750 AND 997067 --JUL
-- AND rct.customer_trx_id BETWEEN 857583 AND 921749 --JUN
-- AND rct.customer_trx_id BETWEEN 824690 AND 858300 --MAY
-- AND gd.gl_date BETWEEN '01-AUG-2012' AND '31-AUG-2012'
AND rct.SHIP_TO_SITE_USE_ID = hcsu.site_use_id
AND hca.cust_account_id = hcs.cust_account_id
AND hcs.cust_acct_site_id = hcsu.cust_acct_site_id
AND hca.party_id = hp.party_id
AND hp.validated_flag IS NOT NULL
AND hp.party_id = hps.party_id
--AND hps.IDENTIFYING_ADDRESS_FLAG = 'Y'
AND zptp.party_id = hp.party_id
AND rctl.org_id = zl.internal_organization_id
AND rct.org_id = rbs.org_id
AND rct.batch_source_id = rbs.batch_source_id
AND rct.org_id = gd.org_id
AND rct.customer_trx_id = gd.customer_trx_id
AND gd.account_class = 'REC'
AND gd.latest_rec_flag = 'Y'
AND rct.cust_trx_type_id = rctt.cust_trx_type_id
AND rct.org_id = rctt.org_id
AND rct.org_id = zl.internal_organization_id
AND rctl.customer_trx_id = zl.trx_id
AND ZL.TRX_NUMBER = RCT.TRX_NUMBER
AND hzl.country = flv.territory_code
AND hzl.location_id = hps.location_id
AND hZL.country IN
('AT',
'BE',
'BG',
'CY',
'CZ',
'DK',
'DK',
'EE',
'FI',
'DE',
'GR',
'HU',
'FR',
'IE',
'IT',
'LV',
'LT',
'LU',
'MT',
'NL',
'PL',
'PT',
'RO',
'SK',
'SI',
'ES',
'SE')

Reply With Quote
Reply

Viewing: Tutorialized ForumsDatabasesSQL Basics > To remove duplicate records from an sql query.


Developer Shed Advertisers and Affiliates


Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump


Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 

Powered by: vBulletin Version 3.0.5
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.

© 2003-2014 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap