how to rewrite this qurey

Question

Post reply

how to rewrite this qurey

Vijay-251247

Say Hey Kid

Points: 684
More actions
September 19, 2005 at 6:51 am

#89782

Hi all
I have a situation here... have a look at this query...
SELECT DISTINCT
aview_posting.posting_id,
aview_post.post_id,
id_number,
post_title,
p_t_name,
case count(aview_posting_location.posting_location_id)
when 0 then 'No Locations'
when 1 then
(select site_name from aview_posting_location a
where a.posting_location_id = max(aview_posting_location.posting_location_id))
else 'Multiple Locations'
end as posting_loc,
posting_sdate as 'noneff_sdate',
posting_edate as 'noneff_edate',
convert(int, posting_edate - posting_sdate) + 1
as ObjectLife
FROM aview_posting
join aview_Post
on aview_posting.Post_Id = aview_post.Post_id and
/* where */
aview_posting.ID_Number = '003029.2003291728220620.001'
and aview_posting.posting_sdate < '31 Dec 9999' and aview_posting.posting_edate > '01 Jan 1900'

join aview_posting_type
on aview_posting_type.Pg_type_id=aview_posting.Pg_type_id
left join aview_posting_location on aview_posting_location.posting_id = aview_posting.posting_id
and '01 Jan 1900' < aview_posting_location.valid_to_date and aview_posting_location.valid_from_date < '31 Dec 9999'
group by
aview_posting.posting_id,
aview_post.post_id,
id_number,
post_title,
p_t_name,
posting_sdate ,
posting_edate ,
convert(int, posting_edate - posting_sdate) + 1

It is the count statement (which is in BOLD) that is a problem for me..
I have to rewrite the above query so that it shouldnt count the posting location ids. It should identify different posting location ids and output the site_name. Now the above query returns 'Multiple Locations' by just counting the Posting Location IDs. Even though they are different and dont overlap i still get that message (Multiple Locations).
I need to find a way to identify if the posting location ids are the same or different. If same, 'Multiple Locations' should come up else i need the site name corresponding to that posting location id. The case statement part should be rewritten i suppose to accomodate the changes by checking the valid from, to dates of the posting.
Every posting has a valid from and valid to date. the posting locations can be different. so i need to output the posting locations based on valid from/to dates not by counting the posting location ids. Even though the posting dont overlap i still get 'Multiple Locations' (as in the case statement). that needs to be rewritten.
posting1 - 22/11/2004 to 14/01/2005
posting2 - 19/01/2005 to 21/03/2005
if the posting locations overlap, Multiple Locations message is correct.
Can anybody help???
Cheers
Vijay

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Vijay-251247 Say Hey Kid Points: 684 More actions · Answer 1

Vijay-251247

Say Hey Kid

Points: 684

September 19, 2005 at 8:05 am

#590950

Hi

i think i solved the problem.

thanks any ways

Jo Pattyn SSC-Dedicated Points: 32352 More actions · Answer 2

Vijay, mind to share the solution for future reference?

Vladan SSC-Insane Points: 22072 More actions · Answer 3

I don't understand that part about from/to dates much, but the statement with COUNT should probably be :

case count(DISTINCT aview_posting_location.posting_location_id)

However, I'm suspicious of the (select site_name ...) inside the same CASE, I would probably prefer (select max(site_name) from aview_posting_location a

where a.posting_location_id = aview_posting_location.posting_location_id). Maybe the original is correct, I don't know... anyway this version seems more understandable to me.

Vijay-251247 Say Hey Kid Points: 684 More actions · Answer 4

Thanks for your efforts guys...

I solved it by using the distinct keyword and used site_name in place of posting_location_id in the Count statement!!!

Now i can count the site_name field and display it.

Cheers