March 18, 2008 at 10:39 am
Can anyone tell my what is wrong with this query?
SELECT [person_last_name], [person_first_name], [person_middle_initial],
[LIVE].[dbo].ordr.fy, [LIVE].[dbo].ordr.ordr_effective_date,
[LIVE].[dbo].ordr.ordr_termination_date,
Max([LIVE].[dbo].person_pay.service_or_component_code) AS
MaxOfservice_or_component_code, [LIVE].[dbo].person_pay.state_territory_code,
[LIVE].[dbo].at_reimbursement.at_start_date,
[LIVE].[dbo].at_reimbursement.at_stop_date,
[LIVE].[dbo].asn.accounting_station_number
FROM ([LIVE].[dbo].at_reimbursement RIGHT JOIN(([LIVE].[dbo].person_pay RIGHT JOIN ([LIVE].[dbo].person LEFT JOIN [LIVE].[dbo].unit ON [LIVE].[dbo].person._unit_id = [LIVE].[dbo].unit._unit_id) ON [LIVE].[dbo].person_pay.person_id = [LIVE].[dbo].person.person_id) LEFT JOIN ([LIVE].[dbo].ordr ON [LIVE].[dbo].person.person_id = [LIVE].[dbo].ordr.person_id) ON [LIVE].[dbo].at_reimbursement.person_id=[LIVE].[dbo].person.person_id) LEFT JOIN [LIVE].[dbo].asn ON [LIVE].[dbo].at_reimbursement.asn_id=[LIVE].[dbo].asn.asn_id
GROUP BY [person_last_name], [person_first_name], [person_middle_initial], [LIVE].[dbo].ordr.fy, [LIVE].[dbo].ordr.ordr_effective_date, [LIVE].[dbo].ordr.ordr_termination_date, [LIVE].[dbo].unit.unit_name, [LIVE].[dbo].person_pay.state_territory_code,
[LIVE].[dbo].at_reimbursement.at_start_date, [LIVE].[dbo].at_reimbursement.at_stop_date
[LIVE].[dbo].asn.accouting_statoin_number
HAVING ((([LIVE].[dbo].ordr.fy)='FY07') AND ((Max([LIVE].[dbo].person_pay.service_or_component_code))='ANG'));
March 18, 2008 at 10:46 am
That's a pretty open question. You've obviously posted it because you feel there is a problem. Why don't you give us a description of what you think the problem is?
http://www.sqlservercentral.com/articles/Best+Practices/61537/
March 18, 2008 at 10:51 am
I am getting this error:
Msg 156, Level 15, State 1, Line 10
Incorrect syntax near the keyword 'ON'.
Msg 102, Level 15, State 1, Line 15
Incorrect syntax near 'Live'.
March 18, 2008 at 10:52 am
And the errors you are receiving.
DAB
March 18, 2008 at 10:53 am
Msg 156, Level 15, State 1, Line 10
Incorrect syntax near the keyword 'ON'.
Msg 102, Level 15, State 1, Line 15
Incorrect syntax near 'Live'.
March 18, 2008 at 10:55 am
Sounds only like you have a problem of parsing your query. Ensure that the syntax is correct (look especially at the comma characters).
I would start by indenting it 🙂 That will probably help you find your problem quicker then anything else...
Hanslindgren
March 18, 2008 at 10:58 am
Hans is correct. If you'll indent the joins based on the left parenthesis you will find missing right parenthesis before the GROUP BY clause.
DAB
March 18, 2008 at 10:59 am
I have tryed and still no help. Can you help?
March 18, 2008 at 11:08 am
It's amazing how much clearer it gets when you actually format your query.
SELECT
person.[person_last_name],
person.[person_first_name],
person.[person_middle_initial],
ordr.fy,
ordr.ordr_effective_date,
ordr.ordr_termination_date,
Max(person_pay.service_or_component_code) AS MaxOfservice_or_component_code,
person_pay.state_territory_code,
at_reimbursement.at_start_date,
at_reimbursement.at_stop_date,
asn.accounting_station_number
FROM
[LIVE].[dbo].at_reimbursement as at_reimbursement
RIGHT JOIN [LIVE].[dbo].person as person
ON at_reimbursement.person_id=person.person_id
RIGHT JOIN [LIVE].[dbo].person_pay as person_pay
ON person_pay.person_id = person.person_id
LEFT JOIN [LIVE].[dbo].unit as unit
ON person._unit_id = unit._unit_id
LEFT JOIN [LIVE].[dbo].ordr as ordr
ON person.person_id = ordr.person_id
LEFT JOIN [LIVE].[dbo].asn as asn
ON at_reimbursement.asn_id=asn.asn_id
GROUP BY
person.[person_last_name],
person.[person_first_name],
person.[person_middle_initial],
ordr.fy,
ordr.ordr_effective_date,
ordr.ordr_termination_date,
unit.unit_name,
person_pay.state_territory_code,
at_reimbursement.at_start_date,
at_reimbursement.at_stop_date,
asn.accouting_statoin_number
HAVING
(ordr.fy='FY07')
AND (Max(person_pay.service_or_component_code)='ANG');
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 18, 2008 at 11:55 am
In other words, Dragon3486... messy code is hard to troubleshoot. 95% of all such simple syntactical problems can easily be avoided altogether if you format your code in an easy to read and logical format. The other 5% become a lot easier to find.
Say, do you like porkchops? 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
March 18, 2008 at 11:58 am
Jeff Moden (3/18/2008)
In other words, Dragon3486... messy code is hard to troubleshoot. 95% of all such simple syntactical problems can easily be avoided altogether if you format your code in an easy to read and logical format. The other 5% become a lot easier to find.Say, do you like porkchops? 😉
Much nicer way to phrase it....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 18, 2008 at 11:59 am
I still can't get it to work.
Getting this error now:
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "person.person_id" could not be bound.
Msg 1013, Level 16, State 1, Line 1
The objects "person_pay" and "person_pay" in the FROM clause have the same exposed names. Use correlation names to distinguish them.
March 18, 2008 at 12:09 pm
hmm... I'm only exposing person_pay one time. Sounds like you have something else exposing it.
that being said - you can always just change the alias if you so wish.
SELECT
person.[person_last_name],
person.[person_first_name],
person.[person_middle_initial],
ordr.fy,
ordr.ordr_effective_date,
ordr.ordr_termination_date,
Max(person_pay1.service_or_component_code) AS MaxOfservice_or_component_code,
person_pay1.state_territory_code,
at_reimbursement.at_start_date,
at_reimbursement.at_stop_date,
asn.accounting_station_number
FROM
[LIVE].[dbo].at_reimbursement as at_reimbursement
RIGHT JOIN [LIVE].[dbo].person as person
ON at_reimbursement.person_id=person.person_id
RIGHT JOIN [LIVE].[dbo].person_pay as person_pay1
ON person_pay1.person_id = person.person_id
LEFT JOIN [LIVE].[dbo].unit as unit
ON person._unit_id = unit._unit_id
LEFT JOIN [LIVE].[dbo].ordr as ordr
ON person.person_id = ordr.person_id
LEFT JOIN [LIVE].[dbo].asn as asn
ON at_reimbursement.asn_id=asn.asn_id
GROUP BY
person.[person_last_name],
person.[person_first_name],
person.[person_middle_initial],
ordr.fy,
ordr.ordr_effective_date,
ordr.ordr_termination_date,
unit.unit_name,
person_pay1.state_territory_code,
at_reimbursement.at_start_date,
at_reimbursement.at_stop_date,
asn.accouting_statoin_number
HAVING
(ordr.fy='FY07')
AND (Max(person_pay1.service_or_component_code)='ANG');
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply