Count is coming off differently in report and database

[barrysaab]

I have report which is developed through package but there are three data items which are not available in the report package for which i have written a sql query to fetch these items from source data base(sql server) and joined by equi join.But when i run the report and validate against database the count is coming diferent.I would appreciate your help on this issue.Thanks

[blom0344]

Different from what? Compare both sets of SQL generated and either draw your conclusions or post some more relevant details. You are not giving us anything as yet to work with   :o

[barrysaab]

Thanks Mr.Blom.This is the Custom SQL statement :
In this query stg.zno_product_offers_map table is not avilbale in cognos package, this is from data base...

select
u.user_bk as User ID,
isnull(u.first_name,'') as First Name,
isnull(u.last_name, '') as Last Name,
u.login as Email,
publication_name as Publication Name,
p.publication_bk as Publication ID,
p.publication_issn as issn,
ofr.list_price as Default offer price,
s.number_of_issues as Default offer term,
s.remaining_issues AS Remaining Issues,
p.default_currency_code as Default offer Currency,
ofr.offer_bk as Offer ID
from
BI.d_subscription s
inner join bi.d_publication p on s.publication_id = p.publication_bk
inner join bi.d_user u on s.user_id = u.user_bk
inner join stg.zno_product_offers_map stg on stg.product_id = p.publication_bk
inner join BI.d_offer ofr on ofr.offer_bk = stg.sku_id
inner join BI.d_issue i on i.product_id = s.publication_id
where i.issue_avail_date between '2012-Apr-28' and '2012-Apr-29'
and s.subscription_type_desc in ('Zinio Sale', 'Publisher Sale')
and s.newsstand_id not in ('147618658', '147618663','192101723','193222837','280167368') -- UCM Newsstand
and s.remaining_issues in (1,2,3,4)
and s.number_of_issues > 4
and u.is_test_user 1
and p.enabled = 1
and p.language_name = 'English'
and stg.offer_name = 'default'
and i.issue_status_desc = 'Active'
group by u.user_bk, isnull(u.first_name,''), isnull(u.last_name, ''), u.login, publication_name,
p.publication_bk, p.publication_issn, ofr.list_price, s.number_of_issues, s.remaining_issues, p.default_currency_code, ofr.offer_bk
order by isnull(u.first_name, ''), isnull(u.last_name,''), p.publication_name

these join condition are using for join to cagnos queries and database queries.....

inner join stg.zno_product_offers_map stg on stg.product_id = p.publication_bk
inner join BI.d_offer ofr on ofr.offer_bk = stg.sku_id

stg.offer_name = 'default'

output is diffrence from database o/p and cognos o/p...

how can i solve the issues.

plse help me.Appreciate your help.

[blom0344]

Why do you code a 'group by' in the SQL if you have no aggregates defined? Please clean up the code.. It really makes my eyes hurt   ;)

Secondly, what difference are you running into?

Thirdly, using both ANSI style joins and numerous where clauses may influence the way data is retrieved. Did you check whether the resultset from the SQL returns the data you expect (number of rows for example)

What is  o/p?