Showing posts with label depending. Show all posts
Showing posts with label depending. Show all posts

Wednesday, March 28, 2012

If exists from within a select statement.

I have a simple SQL statement, I need to populate columns from several
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 insert

End

|||

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

|||Can you repost your new query and the error message pls?|||

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,

sql

Wednesday, March 21, 2012

Identity_insert

Hi friends,
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 ?