Wednesday, March 28, 2012
If exists from within a select statement.
tables.
I need to populate some of the columns depending if there is data in a
child table that matched the parent table (a one to many relationship).
How can I put in an "if exists" into a select statement?
I need results that will be
file_a.first, file_a.second, file_b.first (when file_a.third = "y"),
file_b.first (when file_a.fourth = "y") ..
so kinda like :
select file_a.first, file_a.second,
if file_a.third = "y" then select file_b.first from file_b ..
if file_a.fourth= "y" then select file_b.first from file_b ..You can=B4t use specify dynnamic resultssets until you use dynmaic SQL
to display the columns.
http://www.sommarskog.se/dynamic_sql.html
Without dynamic SQL you could display the columns but fill them in
conditionally:
SELECT
OneTable.SomeColumn0
CASE OneTable.SomeColumn0 WHEN 'Y' THEN OtherTable.SomeColumn0 END...
But assuming that fileA is your parent table which attributes from
which row do you want to display ? If there are two rows returned for
one parent row, your query like the above one wouldn=B4t be reliable.
Perhaps it would be better to post your DDL and sample data that our
thoughts would=B4nt bw based on assumptions:
http://www.aspfaq.com/5006
HTH, jens Suessmeyer.sql
Monday, March 26, 2012
If conditional problem in T-Sql
I encounter a T-Sql problem related to if conditional processing:
The following script execute an insert statement depending on whether column 'ReportTitle' exists in table ReportPreferences. However it gets executed even when ReportTitle column is not present.
Could anyone offer some advice?
IF(Coalesce(Col_length('ReportPreferences','ReportTitle'),0) > 0)
Begin
INSERT INTO dbo.Defaults
SELECT FinancialPlannerID,ReportTitle
FROM dbo.ReportPreferences
end
GO
Were you trying to do this for entire column or for each row in the column? Col_length will always return the size as defined in the DDL. So your IF statement will always return true.
Well the code that you have written is fine it should work perfectly.
Can you provide the code that you are using for droping the column of the table ?
|||Alternatively if you want to check for existence of a column you could query the syscoumns table:
IF
EXISTS(Select*fromsyscolumnswhere [Name]='ReportPreferences'and Id=Object_Id('ReportTitle'))Begin
--Do your insertEnd
|||
Hi,
Thanks for your alternative way of querying system table for column existence.
However the problem still persists: even though the EXISTS clause is evaluated to be false, the query engine is still trying to insert statement, resulting in an error:
Server: Msg 207, Level 16, State 3, Line 6
Invalid column name 'ReportTitle'.
This is a very strange phenomena.
- Yubo
From your earlier which I am copy pasting here:
**************************************************
IF(Coalesce(Col_length('ReportPreferences','ReportTitle'),0) > 0)
Begin
INSERT INTO dbo.Defaults
SELECT FinancialPlannerID,ReportTitle
FROM dbo.ReportPreferences
end
GO
***************************************************
It shows that ReportPreferences is the name of your table while the column name isReportTitle
While if you have just copy pasted the querry from ndinakar which is :
***************************************************
IF
EXISTS (Select * from syscolumns where [Name] = 'ReportPreferences' and Id = Object_Id('ReportTitle'))
Begin
-- Do your insert
End
***************************************************
The sequence of the name of the table is wrong.
Please try this instead and I am sure your problem would be solved :) .
If EXISTS (Select * from syscolumns where [Name] = 'ReportTitle' and id = Object_Id('ReportPreferences'))
Begin
Print ('yes')
End
Else
Begin
Print ('no')
End
And if this post does answer your question please dont hesitate to mark it as Answer.
Regards,
sqlWednesday, March 21, 2012
Identity_insert
I want to know whether IDENTITY_INSERT is on or off through an SQL Statement. Depending on whether IDENTITY_INSERT is ON or OFF i can use SET IDENTITY_INSERT ON or SET IDENTITY_INSERT OFF.
Reply ASAP ...
shanky......ASAP...
try to use ERROR, if you set IDENTITY ON and it is ON, it generates error...
see http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_set-set_7zas.asp
jiri|||Setting identity_insert on only works for a single session that is trying to set identity_insert on more than 1 table is this what you are trying to check ?