query question
-
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, open_date, '1970-01-01') AS open_date, DATEADD(ss, close_date, '1970-01-01') AS close_date, DATEADD(ss, open_date, '1970-01') AS Monthlogged, prob_ctg.sym, call_req.description, int_org.iorg_name, ctct.c_first_name, ctct.c_last_name, int_org_1.iorg_name, ctct_2.c_first_name, call_req.status, act_log.description, int_org_2.iorg_name, ctct_3.c_first_name, DATEADD(ss, 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 ON call_req.assignee = ctct.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 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
sorry for the many edit..but as i troubleshoot i solve some probs while having others
apparently sub-queries within join statements are a no no. thus do i have to use views in this case?Many Thanks, Eric
-
Could you please kindly to post DDL statements in order to have a very comprehensive view of such issue?
Thanks
-
all those brackets are confusing me
but the subselect in ON=
LEFT 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)
looks like a weird construction
-
You might have to make this a table alias....
LEFT JOIN
[SELECT description, id, type, call_req_id, last_mod_dt FROM act_log] A ON call_req.persid = A.call_req_id) INNER JOIN
ctct AS ctct_3 ON call_req.log_agent = ctct_3.id) INNER JOIN
Do you see where I gave the initial SELECT an alias of A and used that in the join? That is a DERIVED table.
Refer to the BOL, use the Index tab, go to SELECT and scroll down to the FROM entry. It talks about how to use nested SELECTs as DERIVED TABLES.
-SQLBill
-
ah yes, i seemed to have missed out an alias for the sub-query. and should the sub-query be in [ square brackets or ( round brackets?
i've posted in another forumn, and a reply mentioned that it should be in round brackets, i'm going to try it out.
Thanks
-
a separate question:
i use SELECT DATEADD(ss, table & column name, '1970-1-1') to convert a LOCAL_TIME value to yr date and time. What if i only want the output in the form of month and yr, e.g. 2006-02
what is the code for this conversion? i tried '1970-1' and it says "syntax error, converting to datetime from character string"
Thanks
-
SELECT call_req.ref_num, call_req.status, 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.c_first_name, ctct.c_last_name,
int_org_1.iorg_name ,ctct_2.c_last_name, call_req.status, act_log.description AS activity_description, int_org_2.iorg_name,ctct_3.c_last_name, 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_orgid INNER JOIN
ctct ON call_req.assignee = ctct.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 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
ok now firstly DATEADD(ss, call_req.open_date, '1970-01') AS [Monthlogged] doesnt work as i have tested it in a simpler query.
and for the above query, i get this error in sql analyzer:
Server: Msg 170, Level 15, State 1, Line 10
Line 10: Incorrect syntax near '.'.
which is this line: " CASERVICEDESK.ctct AS CASERVICEDESK.ctct_1 ON CASERVICEDESK.call_req.customer = CASERVICEDESK.ctct_1.id INNER JOIN"
so whats wrong?
-
because the alias has a dot in the name
CASERVICEDESK.ctct AS CASERVICEDESK.ctct_1 ON CASERVICEDESK.call_req.customer = CASERVICEDESK.ctct_1.id
->
CASERVICEDESK.ctct AS ctct_1 ON CASERVICEDESK.call_req.customer = ctct_1.id
-
ohhhhh u don't have to include the database name in the alias, duh! same as the sub-query...
alright thanks, have to try it out, hopefully it works..
-
what abt this question?
-
sigh why do i get this?
Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'call_req'.
1st line is: SELECT call_req.status, call_req.description, call_req.summary,
call_req is the table name, while status, description and summary are all column names,
status and summary are Varchar, Null data types, while description is test,Null data type. i got a feeling 'datalength' is required for description but i don't know the exact syntax, sql analyzer's help feature doesnt tell
or its due to ano problem? cld it be my join statements? they can be found further up this thread. help
Thanks
-
Please post the entire query by copying it from QA, where you have verified that it does not work. Such problems are often due to typos (, instead of . and similar), so we can't rely on the previously posted SQL. The problem may have been introduced later when editing the query.
As to the question about dates, if you want to output a datetime value as string with year and month only, you can find a nice table of date formats available for CONVERT function in BOL (item "CAST and CONVERT"). Then just limit the number of characters in the string to show the part you need.
For example, SELECT CONVERT(VARCHAR(7),getdate(),111) will return '2006/02'.
HTH, Vladan
-
SELECT svd.call_req.status, svd.call_req.description, svd.call_req.summary,
svd.ctct.c_last_name, svd.ctct.c_first_name, svd.int_org.iorg_name, DATEADD(ss, svd.call_req.open_date, '1970-01-01') AS open_date, DATEADD(ss,svd.call_req.close_date, '1970-01-01') AS close_date, svd.prob_ctg.sym, int_org_1.iorg_name ,ctct_2.c_last_name,
int_org_2.iorg_name,ctct_3.c_last_name,ctct_1.c_last_name
FROM svd.call_req INNER JOIN
svd.prob_ctg ON svd.call_req.category = svd.prob_ctg.persid INNER JOIN
svd.ctct AS ctct_1 ON svd.call_req.customer = ctct_1.id INNER JOIN
svd.int_org ON ctct_1.c_org_id = svd.int_org.id INNER JOIN
svd.ctct ON svd.call_req.assignee = svd.ctct.id INNER JOIN
svd.ctct AS ctct_2 ON svd.call_req.log_agent = ctct_2.id INNER JOIN svd.int_org AS int_org_1 ON svd.ctct.c_org_id = int_org_1.id LEFT OUTER JOIN (SELECT svd.act_log.description, svd.act_log.id, svd.act_log.type, svd.act_log.call_req_id,
svd.act_log.last_mod_dt FROM svd.act_log) AS [closed_log] ON
svd.call_req.persid = [closed_log].call_req INNER JOIN
svd.ctct AS ctct_3 ON svd.call_req.log_agent = ctct_3.id INNER JOIN
svd.int_org AS int_org_2 ON ctct_3.c_org_id = int_org_2.id
WHERE (svd.call_req.status <> 'CL')
ORDER BY svd.call_req.ref_num
SVD is the database name and call_req IS a Valid table... the error is:
Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'call_req'.
and i tried to use CONVERT(int, open_date, 111) AS date_opened, it returned all the original values in the database, not in the date form... i tried getdate(), it returned the value 38776 for every ticket. previously i used DATEADD(ss, column_name, '1970-1-1') and it showed all the yr,mth,day and time together. is there a similar syntax for DATEADD that shows yr and mth? i tried '1970-1' and the error was:
Server: Msg 241, Level 16, State 1, Line 1
Syntax error converting datetime from character string.
so how do i correct this...
Thanks
-
YESSSSS, i actually solved it....the 1st part at least. tore the whole query down and started from each query and slowly put the joins in and it has worked! phew..but the 2nd prob still remains, i still can't get only the month and yr out. in addition i also need the week and yr...
-
Well, what else the query could return if you ask date to be converted into INTEGER?
Convert it into VARCHAR(7) instead, as mentioned in my previous post... Please read about conversions and datatypes in BOL, if you want to understand how conversion works. It is quite well described there and I think I can hardly add anything of value if I repeat it in my own words.
Regarding the week, I'm sorry to say that, but I can't help you. "Week" has several different definitions and everybody is sure that his/her definition is the only valid. What is the "first week of the year" is the main problem, since it affects the numbering all year through. Read about DATEPART function in BOL to see, what SQL Server offers in way of months, weeks, days and other datetime units.
HTH, Vladan
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply