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.
| Author |
Topic |
|
ment0smintz
Starting Member
2 Posts |
 Posted - 2012-10-02 : 02:42:48
|
| [CODE]SQL> select 2 distinct qc_m|| ',' || 3 to_char(logon_dt, 'YYYY-MM-DD HH24:MI:SS')|| ',' || 4 CASE WHEN logon_dt IS NOT NULL THEN 'LOGON' ELSE 'LOGOFF' END 5 from qc_ct2 qc left join v_tls_equipment_logon tls on qc.qc_m=tls.equipment_id where 6 qc.terminal_c='T'and tls.USER_TYPE_C='1' and tls.site_c='C' order by 1 asc;[/CODE]result:26 rows selected. I have 28 records in:[CODE]select qc_m from qc_ct2 where terminal_c='T';[/CODE] what is it that I needed to keep the 28 records without been filter away by line 6 statement... but then, I need the line 6 statement to meet the critera.... |
|
|
ment0smintz
Starting Member
2 Posts |
Posted - 2012-10-02 : 03:29:55
|
| [code]select qc_m from qc_ct2 qc left join v_tls_equipment_logon tls on qc.qc_m=tls.equipment_id where case when (USER_TYPE_C='1' and (USER_TYPE_C!='1' and logon_dt IS NULL))then USER_TYPE_C='1'else (USER_TYPE_C='1' or (USER_TYPE_C!='1' and logon_dt IS NULL)endand terminal_c='T'and site_c='C' order by 1 asc;[/code] can give me some guide? |
 |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-10-02 : 07:43:07
|
| If any of the columns used in the WHERE clause are from the v_tls_equipment_logon table, you need to rewrite the query to remove those columns from the WHERE clause and instead include them in the JOIN condition.Since you have not qualified the column names with the table aliases, I am unable to tell which table the columns in the JOIN condition (such as USER_TYPE_C, terminal_c etc.) belong to. If you can either post the table schema, or qualify the column names with the table aliases (for example like tls.USER_TYPE_C, qc.terminal_c etc.), people on the forum would be able to tell you how to rewrite the query to move the filtering conditions to the JOIN clause.Take a look at these articles, they may help as well:http://www.sqlteam.com/article/writing-outer-joins-in-t-sqlhttp://www.sqlteam.com/article/additional-criteria-in-the-join-clause |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-10-02 : 10:35:05
|
| the code looks like Oracle to me. Though you may get solutions here there are very few people with Oracle experience here. So you may be better off posting Oracle questions in some oracle forums like www.orafaq.com in future------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
sql guide needed to keep original record