SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 sql guide needed to keep original record
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ment0smintz
Starting Member

Singapore
2 Posts

Posted - 10/02/2012 :  02:42:48  Show Profile  Reply with Quote


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;



result:

26 rows selected.



I have 28 records in:

select qc_m from qc_ct2 where terminal_c='T';




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

Singapore
2 Posts

Posted - 10/02/2012 :  03:29:55  Show Profile  Reply with Quote
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)
end
and terminal_c='T'
and site_c='C' order by 1 asc;
can give me some guide?
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 10/02/2012 :  07:43:07  Show Profile  Reply with Quote
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-sql
http://www.sqlteam.com/article/additional-criteria-in-the-join-clause
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 10/02/2012 :  10:35:05  Show Profile  Reply with Quote
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 MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000