need help with the following query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

need help with the following query

Author

Topic

cire
Starting Member

15 Posts

Posted - 2006-02-27 : 01:26:42

hi all i need help with the following query:

SELECT call_req.ref_num, call_req.status, ctct_1.c_first_name, ctct_1.c_last_name, call_req.summary, DATEADD(ss,call_req.open_date, '1970-01-01') AS open_date,
DATEADD(ss, call_req.close_date, '1970-01-01')
AS close_date, DATEADD(ss, call_req.open_date, '1970-01') AS Monthlogged, prob_ctg.sym, call_req.description, int_org.iorg_name, ctct_1.c_first_name AS Expr1, ctct_1.c_last_name AS Expr2,
int_org_1.iorg_name AS Expr3, ctct_2.c_first_name AS Expr4, call_req.status AS Expr5, act_log.description AS Expr6,
int_org_2.iorg_name AS Expr7, ctct_3.c_first_name AS Expr8, DATEADD(ss, call_req.open_date, '1970-01') AS Weeklogged

FROM call_req
INNER JOIN
prob_ctg ON call_req.category = prob_ctg.persid
INNER JOIN
ctct AS ctct_1 ON call_req.customer = ctct_1.id
INNER JOIN
int_org ON ctct_1.c_org_id = int_org.id
INNER JOIN
ctct AS ctct_3 ON call_req.assignee = ctct_3.id
INNER JOIN
ctct AS ctct_2 ON call_req.log_agent = ctct_2.id
int_org AS int_org_1 ON ctct.c_org_id = int_org_1.id
LEFT OUTER JOIN
[SELECT description, id, type, call_req_id, last_mod_dt FROM act_log] ON call_req.persid = [SELECT description,id,type,call_req_id,last_mod_dt].call_req_id
INNER JOIN
ctct AS ctct_3 ON call_req.log_agent = ctct_3.id
INNER JOIN
int_org AS int_org_2 ON ctct_3.c_org_id = int_org_2.id
WHERE act_log.type = 'CL')
ORDER BY call_req.ref_num

i can't run the above query, apparently sub-queries within join statements are a no no. thus do i have to use views in this case?
and i seem to have syntax errors which i can't spot atm...

Many Thanks
Eric

Thanks

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2006-02-27 : 01:38:51

Hi,
Problem lies here .

LEFT OUTER JOIN
[SELECT description, id, type, call_req_id, last_mod_dt FROM act_log] ON call_req.persid = [SELECT description,id,type,call_req_id,last_mod_dt].call_req_id INNER JOIN
ctct ctct_3 ON call_req.log_agent = ctct_3.id
INNER JOIN
int_org int_org_2 ON ctct_3.c_org_id = int_org_2.id
WHERE act_log.type = 'CL')
ORDER BY call_req.ref_num

First you should give an alias for the subquery you want to use after Left Outer Join
In the On clause you need to specify a Column Name not a subquery.

cire
Starting Member

15 Posts

Posted - 2006-02-27 : 02:17:49

oh i see, i've updated the statements with aliases but your answer seems to have trigger something else, i've got a meeting so i've to check later. Thanks

Thanks

cire
Starting Member

15 Posts

Posted - 2006-02-27 : 03:59:37

FROM
call_req
INNER JOIN prob_ctg
ON call_req.category = prob_ctg.persid
INNER JOIN ctct AS ctct_1
ON call_req.customer = ctct_1.id
INNER JOIN int_org
ON ctct_1.c_org_id = int_org.id
INNER JOIN ctct AS ctct_3
ON call_req.assignee = ctct_3.id
INNER JOIN ctct AS ctct_2
ON call_req.log_agent = ctct_2.id
INNER JOIN int_org AS int_org_1
ON ctct.c_org_id = int_org_1.id
LEFT OUTER JOIN [SELECT description, id, type, call_req_id, last_mod_dt FROM act_log] AS [closed_log]
ON call_req.persid = closed_log.call_req_id
INNER JOIN int_org AS int_org_2
ON ctct_3.c_org_id = int_org_2.id
WHERE (act_log.type = 'CL')
ORDER BY call_req.ref_num

now i got an error in
"ctct AS ctct_1 ON call_req.customer = ctct_1.id INNER JOIN"
it says: incorrect syntax near '.'
i think there is something wrong with my aliases...orsomething else

Thanks

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2006-02-27 : 04:10:51

Why you have put "[" square braces around a statement?
It should be "(" round braces

cire
Starting Member

15 Posts

Posted - 2006-02-28 : 00:44:08

hmm i thought sub-queries have to be in square brackets. i guess i'm wrong, thanks, i'll give it a try.

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources