.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
Easy Web
Imran Ghani
Post New Web Links

Get Value from table in a Case statement with Linked Servers

Posted By:      Posted Date: October 07, 2010    Points: 0   Category :Sql Server
 

I have 2 SQL Server instances that I need to query from.  I have figured out how link the 2 servers, I just don't know how to get the value from the other instance/table and put it in the case statement. 

Here are the tables:

Instance: Default     Database:GAD

Table Sample1

ID          Integer
V301054     Float
V301060     Float



Instance: MSSQL     Database:SCA

Table DESI
ID          Integer
Unit_Code    smallint
Unit_Name    Varchar(50)
Amount      Float

Here is the Select Statement with the when clause in it.

Select top (1)V301054A as MW, Availability = case when V301054A <= '.1' then 'Deployed' else '0' end FROM [Default].[SCA].[dbo].[SAMPLES1] AS SAMP1
Group by V301054A
order by MW desc

What I need to do is get the value from this Select Statement and put it where the 'Deployed' is in the above When Clause.


View Complete Post


More Related Resource Links

SWITCH...CASE STATEMENT

  

Hello, i need help changing the following code into a switch...case statementUndecided

<script runat="server">
void Page_Load()
{
 
    if (Page.IsPostBack)
    {
    public String toSring()
    {
    int intcomputerChoice=1;
        
         if(computerChoice==1)
         {
         return(Rock);
       }
       if(computerChoice==2)
         {
         return(Paper);
       }
       if(computerChoice==3)
         {
         return(Scissors);
       }
       }
       if (Page.IsPostBack)
      {

Data Points: Updating Data in Linked Servers, Information Schema Views, and More

  

Every day a developer somewhere needs to write code to iterate through SQL ServerT system objects, query and update tables in linked servers, handle optimistic concurrency, and retrieve column and stored procedure metadata.

John Papa

MSDN Magazine November 2004


Case statement is never executing

  

Can some please advise as to why the case statement is never running

 

Imports Microsoft.VisualBasic
Imports AjaxControlToolkit


Public Class MultilevelAccordion
    Inherits System.Web.UI.UserControl
    Implements System.Web.UI.ITemplate

    Dim templateType As AccordionItemType

    Sub New(ByVal type As AccordionItemType)
        templateType = type
        MsgBox(templateType.ToString)

    End Sub

    Sub New()

    End Sub

    Public Sub InstantiateIn(ByVal container As System.Web.UI.Control) _
          Implements System.Web.UI.ITemplate.InstantiateIn

        Dim ph As New PlaceHolder()

        Select Case (templateType)
            Case AccordionItemType.Header
                Dim myLabel As Label = New Label()
                myLabel.ID = "HeaderLabel"
                MsgBox("header")


                ph.Controls.Add(myLabel)
                AddHandler ph.DataBinding, New EventHandler(AddressOf Item_DataBinding)
            Case AccordionItemType.Content
                AddHandler ph.DataBinding, New EventHandler(AddressOf Item_DataBinding)
        End Select
        container.Controls.Add(ph)
    End Sub

    Shared Sub Item_DataBinding(ByVal sender As Object, ByVal e As AccordionItemEventArgs)

        Dim ph As PlaceHolder = CType(sender, PlaceHolder)
        Dim headerValue As String = _

LINQ to SQL and Case statement

  
Working with LINQ I realized that I had to use the simple case statement in my SQL query. There is no special keyword for this. To create a case statement like structure you will have to do it in the select section of the query. Below is an example of the usage of the case statement in LINQ. var t = from n in idc.categories            select new             {                        catName =                         (n.id==1 ? "Cat1" :                        n.id==2 ? "Cat2" :                        n.id==3 ? "Cat3" : "Unknown Category"                        )             }; Here in the above code we are using multiple cases for value 1, 2 and 3

SELECT statement to return NULL by matching data from another table.

  
Hi,I am fairly new at SQL and I have been struggling for days now trying to find an answer to my problem and i have come to the point where i have run out of ideas and about to give up. I'm hoping someone can put me in the correct path. The problem I have 3 table Table 1 Department" has the following columns: REF, NAME Table 2  "Department_Collection" has the following columns: REF, DEPARTMENT_REF, MANAGER_REF, STORE_REF, ACTIVE Table 3 Store" has the following columns: REF, NAME, STORE_ID  What i am trying to do is to take all the rows in the Department table and get a matching row (DEPARTMENT.NAME, DEPARTMENT_COLLECTION.REF) from the Department_Collection table, if it does not match any then still display DEPARTMENT.NAME but mark DEPARTMENT_COLLECTION.REF as null. I have tried the following select statement but it seem to remove all null values when supplied with a 'storename' SELECT DEPARTMENT.NAME, DEPARTMENT_COLLECTION.REF FROM DEPARTMENT_COLLECTION right outer join DEPARTMENT on DEPARTMENT_COLLECTION.DEPARTMENT_REF = DEPARTMENT.REF left outer join STORE on DEPARTMENT_COLLECTION.STORE_REF = STORE.REF where STORE.NAME = 'storename' order by DEPARTMENT.NAME   Any help will be greatly appreciated. Thanks

Can we use Select clause in Case statement

  
SELECT CASE OT.ItemTypeID WHEN 6 THEN SELECT SUM(ISNULL("Item Cost",0)) FROM SUBTABLE IT WHERE IT.OrderID = OT.OrderID AND IT.ItemTypeID IN(6,11,12) WHEN 7 THEN SELECT SUM(ISNULL("Item Cost",0)) FROM SUBTABLE IT WHERE IT.OrderID = OT.OrderID AND IT.ItemTypeID IN(7,14,15) WHEN 8 THEN SELECT SUM(ISNULL("Item Cost",0)) FROM SUBTABLE IT WHERE IT.OrderID = OT.OrderID AND IT.ItemTypeID = 8 WHEN 9 THEN SELECT SUM(ISNULL("Item Cost",0)) FROM SUBTABLE IT WHERE IT.OrderID = OT.OrderID AND IT.ItemTypeID = 9 WHEN 10 THEN SELECT SUM(ISNULL("Item Cost",0)) FROM SUBTABLE IT WHERE IT.OrderID = OT.OrderID AND IT.ItemTypeID = 10 WHEN 11 THEN SELECT SUM(ISNULL("Item Cost",0)) FROM SUBTABLE IT WHERE IT.OrderID = OT.OrderID AND IT.ItemTypeID IN(6,11,12) WHEN 12 THEN SELECT SUM(ISNULL("Item Cost",0)) FROM SUBTABLE IT WHERE IT.OrderID = OT.OrderID AND IT.ItemTypeID IN(6,11,12) WHEN 14 THEN SELECT SUM(ISNULL("Item Cost",0)) FROM SUBTABLE IT WHERE IT.OrderID = OT.OrderID AND IT.ItemTypeID IN(7,14,15) WHEN 15 THEN SELECT SUM(ISNULL("Item Cost",0)) FROM SUBTABLE IT WHERE IT.OrderID = OT.OrderID AND IT.ItemTypeID IN(7,14,15) WHEN 18 THEN SELECT SUM(ISNULL("Item Cost",0)) FROM SUBTABLE IT WHERE IT.OrderID = OT.OrderID AND IT.ItemTypeID = 18 WHEN 19 THEN SELECT SUM(ISNULL(&qu

trying to use CASE within table-valued function

  
Hi, I am trying to create a table-valued function that takes a string argument and, depending upon it's value, returns one of a number of identically-structured tables (and before someone asks why multiple identically structured tables, NOT my idea!).  I am a novice with T-SQL, but I've been attempting something like this for the function: Case @Input   When 's1' then return table1   When 's1' then return table2 End ... but I keep getting errors.  Can someone help get the ball rolling and show me the basics on how to set up a function to do this?  I'm hoping since I'm just redirecting to existing tables that this function will have minimal performance hits.

Linked server and sensitive to register name of table. Problem with UPDATE.

  
Hi All. I try to work with table with "sensitive to register" name through Linked Server (MSSQL 2005/2008) and get the problem with UPDATE statement. Reason: MSSQL generates UPDATE statement with "un-quoted" table name. With SELECT/INSERT/UPDATE - no any problems. ----- Linked Database Information: 1. Firebird 2.5 2. OLEDB Provider: IBProvider v3 3. Database dialect: 3 Metadata: CREATE GENERATOR "GEN_ID_TableWithMixName1"; CREATE TABLE "TableWithMixName1" ( TEST_ID INTEGER NOT NULL, "Col" VARCHAR(100), DUMMY_COL INTEGER, CONSTRAINT "PK_TableWithMixName1" PRIMARY KEY (TEST_ID) ); CREATE TRIGGER "BI_TableWithMixName1_TEST_ID" FOR "TableWithMixName1" BEFORE INSERT AS BEGIN IF(NEW."TEST_ID" IS NULL)THEN NEW."TEST_ID" =GEN_ID("GEN_ID_TableWithMixName1",1); END; ------- MSSQL Test 1. MSSQL: select * from IBP_TEST_FB25_D3_V3...TableWithMixName1; IBProvider: Command_Execute   SELECT "Tbl1002"."TEST_ID" "Col1004",         "Tbl1002"."Col" "Col1005",         "Tbl1002"."DUMMY_COL" "Col1006"   FROM "TableWithMixName1" "Tbl1002" No Problem ------- MSSQL Test 2. MSSQL: delete from IBP_TEST_FB25_D3_V3...Tabl

Query help to write case statement in Where clause?

  
Hi All, I have table something like this:- SELECT UserName, IsManager FROM Employee IsManager has only two values either 1 (which means user is a Manager) or 0 (which means user is not a Manager) Now I want to write below query:- DECLARE @IsManager INT SET @IsManager = 2 SELECT UserName, IsManager FROM Employee WHERE IsManager = CASE WHEN @IsManager = 1 THEN 1 WHEN @IsManager = 0 THEN 0 WHEN @IsManager = 2 THEN 1 | 0 ----- THIS IS NOT WORKING, when I set @IsManager value to 2 then where clause should be like this "IsManager = 1 OR IsManager = 0" END Can anybody help me out in modifying about query so that it produces both the results when @IsManager variable value "2". Thanks Regards, Kumar

Two Output Columns from One CASE Statement

  
I have some query logic that needs to return two dynamically created columns based on two conditional tests so that - If Test1 is True then the values for columns 1 and 2 are always the same regardless of the results of Test2. - If Test1 is False then Test2 determines the value of Column2 Because the CASE statement only produces one column I am having to repeat Test1 and then nest Test2 in the ELSE portion of the second iteration of Test1.  I was hoping that someone might have a different approach that would reduce the amount of testing being performed in the SQL.  I did see the post at http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/a0e45c7d-eedd-41ca-89f9-163c4dadc1a7/ that doesn't offer much hope but I thought I'd ask. For what it's worth I'm posting the heart of my logic below.  The test is performing bitwise comparisons to determine (Test1:) If the currently logged in user has access to a document and, if not (Test2) what they need to do to gain access.  @Access will end up as a parameter in the stored procedure once I finalize this. DECLARE   @Access tinyint = 1 SELECT   Holding.HoldingID, ShortTitle, Access,   CASE     WHEN @Access &[Access] = @Access THEN (URL + [FileName])     ELSE null   END AS [HoldingURL],         CASE

Wierd case of: Msg 213, Column name or number of supplied values does not match table definition

  
Hi, I'm working on several triggers (that happen after insert or update) in order to log the changes in a different table. They all follow a similar syntax and are working fine, except for this one... I've reduced the next code to the minimum that gives an error, so we can safely assume the other parts of the trigger are working fine. INSERT INTO [Adt].[WardUnitStayLog] SELECT t.* FROM [Adt].[WardUnitStay] t INNER JOIN inserted i ON i.[Id] = t.[Id]; I've used this same syntax (but on different tables) in other triggers, and these are working perfectly fine. The above query provides the next error: Column name or number of supplied values does not match table definition. I've checked both tables for differences in the columns, but to no avail... (I've checked them manually and by outerjoining the information_schema.columns) (I've also checked the order in wich these columns are defined, they match over the two tables) These are the creation scripts for the tables: CREATE TABLE [Adt].[WardUnitStay] ( [Id] [dbo].[Id] IDENTITY(1,1) NOT NULL, [UnifiedUnitStayId] [dbo].[Id] NOT NULL, [WardCd] [dbo].[Cd] NOT NULL, [_FirstAtTm] [dbo].[Dtm] NOT NULL, [_IsReservation] BIT NOT NULL, [_LastAtTm] [dbo].[Dtm] NOT NULL, [_LastBedCd] [dbo].[Cd] NULL, [_LastPhysicianUid]

can create table statement be used in and ssis expresssion?

  
I need to modify the preparation sql task built by the import wizard. I wanted to create an expression  using the create table sql but insert the variable to append the date and another descriptor  to make it dynamic. The source is Excel The expression would look something like this "Create Table" + @user::descriptor +  @user::todaysdate+" ( name varchar(50) ..........." SSIS cannot evaluate the sql although it runs fine in ssms The column mappings will always be the same but the table name needs to be dynamic

Using Impersonation or Certificates Across Linked Servers

  
We are trying to isolate the custom reporting stored procedures from our third-party SQL Server database ("SourceData" database) by placing all of those procedures in a separate database ("MyReporting" database) on a separate server. In addition, and most critically, I am trying to prevent all ad-hoc reporting (MS Access, Excel, developers) against the third-party database. To accomplish this, I grant permission to a specific user to execute a stored proc in the reporting database and then the stored proc accesses the data in the third-party database either by impersonation using EXECUTE AS or by certificates (following the model described in this MSDN article: http://msdn.microsoft.com/en-us/library/ms188304(SQL.90).aspx). However, as soon as I try to implement this model across multiple servers using Linked Servers, I am unsuccessful (with one unsatisfactory exception). As far as I can tell, I've tried every combination of option in the Linked Server setup. The unsatisfactory option it to map the reporting user to the "SourceDataID" user in the SourceData database. That works, however, that simply opens up the ability to perform ad-hoc reporting if a user connects to the reporting database since the Linked Server exposes all of the tables that the SourceDataID has access to.Below are diagrams describing how these models are setup:In this Imperso

Oracle dataset CASE statement

  
Hi,I'm trying to create a dataset linking to Oracle, but I'm having problems getting a case statement to work in the query.It works fine in SQLPlus, Sql developer.When I try and add the statement it either puts single quotes around the statement, or throws the error 'Invalid or missing expression'.Here is the query in question:SELECT DISTINCT (CASE WHEN DEPARTMENT.DESCRIPTION LIKE '%Full Time' THEN REPLACE(DEPARTMENT.DESCRIPTION, 'Full Time') WHEN DEPARTMENT.DESCRIPTION LIKE '%Part Time' THEN REPLACE(DEPARTMENT.DESCRIPTION, 'Part Time') ELSE DEPARTMENT.DESCRIPTION END) AS DEPARTMENT FROM COURSE, COURSE_INSTANCE, COURSE_LEVEL, DEPARTMENT WHERE COURSE.OBJECT_ID = COURSE_INSTANCE.COURSE AND COURSE.COURSE_LEVEL = COURSE_LEVEL.OBJECT_ID AND COURSE.DEPARTMENT = DEPARTMENT.OBJECT_ID

MERGE command w/ linked servers & udfs

  
Hi guys - I have a MERGE statement I'm trying to fight with and losing. I'm  calling data from 2 linked servers and one of them has a user defined functions on it.. I have database A on one server (2008 R2) - the one with the UDFs and the source of the MERGE command. And database B on the other server (2008 sp1) - the target. The source of the MERGE does contain a call to a table function in server A. When both databases are on the same server, it runs fine and I'm not entirely sure how to fix it so I can get it to run between linked servers. I've tried both using OPENQUERY and just a normal statement...on the "normal" statement (Query #1) I get "Incorrect syntax near 'MERGE'. You may need to set the compatibility level of the current database" when I run it "normally" . Which the compatibility of both 2008 and 2008R2 are set to 100. When i try with OPENQUERY (#2) I get "Incorrect syntax near the keyword 'AS" . It is a dynamic query because I need to pass in my database names. @A and @B are the linked server names. Query #1 (the "normal" one just using MERGE) DECLARE @A VARCHAR(50) DECLARE @B VARCHAR(50) DECLARE @LastUpdated DATETIME DECLARE @query VARCHAR(8000) SET @A = ? SET @B = ? SET @LastUpdated = ? SET @query = ' MERGE '+@B+'.database.dbo.tblFieldVersion as target USING ( SELECT c.x, st

Case statement in where clause

  
i trying following querySelect  FirstName, LastName, ClientID, WPhone, HPhone, MPhone, Email,  CreationDate, LastModified, BrokerID,  ShareBrID1, ShareBrID2, AptShowingCID  From ClientDetail with (nolock)  Where @Assigned_id in (BrokerID, ShareBrID1, ShareBrID2)  And  if(@Type is not null and Lower(@Type) = 'inactive' )then (ClientStatus in ('active','deleted')) ELSE  (ClientStatus Is Null OR Lower(ClientStatus) = 'active') )  Order By ClientDetail.CreationDate DESC ;Select FirstName, LastName, ClientID, WPhone, HPhone, MPhone, Email, CreationDate, LastModified, BrokerID,  ShareBrID1, ShareBrID2, AptShowingCID From ClientDetail with (nolock) Where @Assigned_id in (BrokerID, ShareBrID1, ShareBrID2)  And case(@Type is not null and Lower(@Type) = 'inactive' )then (ClientStatus in ('active','deleted'))ELSE (ClientStatus Is Null OR Lower(ClientStatus) = 'active') ) Order By ClientDetail.CreationDate DESC ;but is gives an error plz help

Cannot call Table Valued Function from Dynamic stored procedure statement

  
Hello,  I have a table-valued function that splits string into a table column. I can easily call this function from a stored procedure within a regular SELECT statement: SELECT * FROM Table1 WHERE Code1 = '1' AND Code2 IN(SELECT * FROM [dbo].[fnSplitValues](@Code2String))   However when I try to use the same logic for a dynamic SELECT statement: ‘SELECT * FROM Table1 WHERE Code1 = '1' AND Code2 IN(SELECT * FROM ' + [dbo].[fnSplitValues](@Code2String) + ')’ I get an error ‘Cannot find either column "dbo" or the user-defined function or aggregate "dbo.fnSplitValues", or the name is ambiguous.’ Any idea what is wrong with my dynamic SQL?   Please help, Lana
Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend