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


Post New Web Links

Possible to use CASE in select to select different columns/values?

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

Hey everyone,

I'm trying to work some SQL to work like the following (which doesn't work/isn't allowed). Is there a way in SQL to do this?

declare @mode int
set @mode = 1

Declare @Profiles varchar(400) 
set @Profiles=''

Declare @Count int

-- This is where it's tricky.
-- If mode is 1, then we want to return the count of the records.
-- If not, we want to return the values. 
-- What we don't want to do is have to write the entire select
-- statement twice. :-)
select case @mode
	when 1 then @Count=count(1) 
	else *
from CLACX x inner join 

View Complete Post


More Related Resource Links

BDC Column with Ability to Select Multiple Values

  

Use Case:

We are creating a storage location for technical product articles. These will be SharePoint publishing pages. Each page will have metadata assigned to it to make it searchable by properties. The possible values for one of the columns (Part Number) could potentially be sourced by our ERP system. I'd like to do this to eliminate the possibility of free-text entry errors, but there are probably over 10,000 parts so a drop-down list isn't feasible.

 

Ideal Situation:

The end user enters the part number and clicks a button to verify it against a BDC entry from our ERP system. If they weren't sure on the part number they needed, they could do a lookup from that metadata entry screen. They'd need to have the option to add multiple part numbers as one technical product article could reference multiple part numbers.

 

Question:

Is BDC an option for all of this or am I looking at a custom metadata solution? I thought I heard along the way that BDC only supported one value choice, but I couldn't find a verification for that in the forums.

 

Thanks.


DVWP connections with multi-select lookup columns

  
I have a question concerning the Data View Web Part using SharePoint Designer 2007.   I have two list (A and B). List A has a lookup column (called B-items) with multiple selections to items in List B(using the Title column from B).   I create a web part page and insert a data view of using List A. I create another data view with data from List B. Next, I make a Web Part Connection between the two data views with A passing B-items column as parameter to List B data view. I then create a filter on the List B data view with a comparison of List B’s Title column equal to the parameter of List A’s B-items value when a user selects an item from List A data view. The problem is nothing appears in List B’s data view.   When I try the reverse of the above scenario it works fine.     I understand that this is properly functionality, but is there a way to achieve my first scenario? If so, how can it be accomplished?   I have looked through the web for help and found the answer here http://social.msdn.microsoft.com/Forums/en-US/sharepointcustomization/thread/749b7477-f37f-4724-94b3-b6ace770e73a seems to be what I’m looking for. However, I am unsure how to implement his code into the web part page.   I am a worse than a novice with xsl so if anyone gives an example I would greatly appreciate step by step on what code is needed a

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

How to Create an MDX Query Parameter to Select 30 Values from a Dimension?

  
Hi, I'm using SSRS 2005 to report on an SSAS cube that contains a Procedure dimension.  I don't need to use members of this dimension in my report, but rather need to select records (patients) where their chart has one or more of the codes.  I've researched this today and cannot locate the best approach.  Thus far, I've attempted to create an MDX query parameter as part of my dataset.  However, I don't know whether this is the correct approach, and how to structure the syntax so that only records with one or more procedures are included in the report?  If so, what is the proper MDX syntax for setting my Procedure code equal to the query parameter? Thanks, Sid

Select Case - Represented by Images

  
I have a question about a Select Case, and how to go about writting it. In my pages, I call to a SQL Database, that returns values from a column.  The columns present two types of data, numeric and varchar e.g 2N or .10 (they present nickles) I would like my onscreen to display images, instead of that value (once they have been found). Where 2N = imageNickle + imageNickle, as well as .10 = imageNickle + imageNickle (or display 2 of the images in place, rather) I would like to just focus on the (varchar) at this point.  Is their an easier way to write a select statement than say: Select Case howManyImages If columnValue = 2N then displayValue = "/imageNickle.jpg" & "/imageNickle.jpg" ElseIf columnValue = 3N Then displayValue = "/imageNickle.jpg" & "/imageNickle.jpg & "/imageNickle.jpg" or is this pretty simple as it is?      

Retrieve data from select columns without creating Gridview etc??

  
I have an accessdatasource with multiple columns, one column I use it to populate a dropdownlist. Can I use the data from other columns to insert into a table,  insert being done in the aspx.vb page, without creating gridview etc.   Is it possible to make a detailsview one column visible= false and still be able to evaluate its contents? Thanks,

add more than one case in select statement ?

  

hi all

i have this select statement

select

case when X  = 1 THEN ''

ELSE

CASE WHEN y = 1 THEN 'Y' END

CASE WHEN f = e THEN 'ok' END

END

FROM table

but

Incorrect syntax near the keyword 'CASE' the secound one

how can i solve this ?


Values in multiselect dropdown not getting selected when Select All option is selected

  

Values in multiselect dropdown not getting selected when Select All option is selected for a SSRS dropdown.

There are multiple dropdowns on the page. We have one dropdown whose output is being used to populate the second dropdown using a stored procedure. When we check the Select All checkbox on the dropdown then ideally upon page refresh all the values should get selected in the dropdown, but in this case the page refreshes back to the blank dropdown, forcing the user to select the values again. The next dropdown too does not get populated. This makes it impossible to run the report for all the cases by using Select All.

We ran the report on differnet database environments. My current location is Mumbai,India, the report worked fine when we ran it on a Bangalore,india database. This issue occurs when we try running it on a Chicago database server.

I am using SSRS 2005, SQL2005.

If anybody has faced such an issue earlier, please help me out here.  Thank you. 

 


Passing numeric checkbox values into a select statement

  

For efficiency's sake I've created a number of stored procedures to run against an SQL table.  Which procedure runs depends upon the checkboxlist choices a user makes before clicking a button.  The checkboxlist looks something like this:

      <asp:CheckBoxList AutoPostBack="True" runat="server">

         <asp:ListItem Enabled="True" Value="1">Item 1</asp:ListItem>

         <asp:ListItem Value="10"> Item 2</asp:ListItem>

         <asp:ListItem Value="100"> Item 3</asp:ListItem>

      </asp:CheckBoxList>As an example,

 

What I can't figure out is how to loop through the checkboxes in the Button1_Click event in the .cs code behind page to add up the values of the selected items. (The value of an unclicked box being 0.)  Converted to a string, the totaled value is then appended to a stored procedure name (MyPROC) for entry into the SQL statement.  In other words, if the user clicks just Item 1, then the Button1_Click event would produce a stored procedure name of MyPROC1, insert it into the statement, and fire off the query; if Ite

Including NULL columns as empty elements in SELECT FOR XML

  

 

Hi everyone,

 

I was wondering if it is possible for a SELECT FOR XML statement to map a row with a NULL value in a column to an empty element in XML?

 

For example, let's say I have the following table:

 

CREATE TABLE NetworkAdapter

(

ID int PRIMARY KEY

MacAddress char(17)

)

 

The table has one row with the values (10, NULL). Can I use the SELECT FOR XML statement to return the following XML:

 

<NetworkAdapter>

<ID>10</ID>

<MacAddress /> -- Or <MacAddress></MacAddress>, doesn't matter

</NetworkAdapter>

 

Is it possible to do this without using ISNULL on the MacAddress column? Or if not, how would yo

Retrieving multiple values from a select query

  

Hi,

This code works just fine for me to select * from x,y,z. However what I want to do is retrieve the values return and assign them to variables. How do I go about this? Thanks in advance.

 

 Using MyConnection As New SqlConnection("Data Source=sql2008.aspnethosting.co.uk;Initial Catalog=xxx_xxxx;Persist Security Info=True;User ID=xxx_xxx;Password=xxxxx")
            Dim MyCommand As New SqlCommand("SELECT txfrequency,mode,details FROM frequencies WHERE county = @location", MyConnection)
            MyCommand.Parameters.Add("@location", SqlDbType.NVarChar).Value = e.PostBackValue
            MyConnection.Open()
            Dim r As SqlDataReader = MyCommand.ExecuteReader()
            GridView1.DataSource = r
            GridView1.DataBind()
            MyConnection.Close()
            'SELECT COUNT(*) FROM TableName WHERE SomeWhereClause 
        End Using


 


select returning a list of values seperated by commas

  

I’m performing a select statement that returns the primary key of all the records matching  certain criteria. Instead of the returned set having  a row for each record returned, I want to just return just  one record that consists of a text field containing all the primary keys separated by commas.

 

For example:

Select personID from people

Where age < 45

 

Instead of:

Select columns based on date in row kinda like a cross tabe

  

I have a querry that will add data to a temp table for a period given the begin date and end date, for each month that there is data i need to have a column, which i did with :

Sum

 

(case when Month([Date])=1 then

Sharepoint SPDatasource select command where clause not working for some columns

  

Hi, 

       I'm using MOSS 2007. I'm using spdatasource to bind a custom list to gridview using webuser control(webpart). spdatasource select command working for all columns except the status column. my status column is a choice- radio with the following options. Pending Review, Checked and Approved. I have checked the field internal name, every thing is correct, but only for status column filtering not happening. Gridview coming as blank

i tried using 

 SelectCommand="<Query>
           <Where>
            <Eq>
             <FieldRef Name='Status'/>
             <Value Type='Choice'>Pending Review</Value>
            </Eq>
           </Where>
          </Query>";

Any idea?????

 

Regards

Wilson 


Use of CASE statement in Select using SqlDataSource

  

Hi All,

I need help regarding the use of CASE in Select of SQLDataSource. Actually I am using FORMVIEW and in the Grid i have "Consumer Numbers" as link and on click I save the Consumer Number in Session Variable. When user go into Edit and change the Consumer Number after updation the formview was coming blank cause i was using that number for select statement now. Just need to correct the SELECT and use the case properly and want to do is

"IF CONSUMER_NUMBER = <SESSION_VARIABLE_OF_CONSUMER_NO> THEN USE <SESSION VARIABLE>

ELSE

USE FIELD "CONSUMER NUMBER

How to do that please correct.


       <asp:SqlDataSource ID="SqlDSBillers" runat="server" 
ConnectionString="<%$ ConnectionStrings:SQLE2K8 %>"

SelectCommand="SELECT Bill_Master.Bill_Master_ID,
Bill_Master.Biller_ID,
CONVERT (varchar(20),
Bill_Master.Enroll_Date, 105) AS Enroll_Date,
Billers.Biller_Code,
Bill_Master.Consumer_No,
Bill_Master.NameOnConnection,

select query doesnt return values

  

hi all, i need your assistance please.

 

select Comments.Id,SuroundId,ArticleId,PosterId,PostRime,Subject,Body,Visible,Users.Username
from dbo.Comments
inner join Users on Comments.PosterId=Users.Id
where SuroundId=@suround and ArticleId=@articleId


for some reason it doesnt return any values.

before i've added the inner join it worked perfectly.

i couldn't find my mistake, though i passed over it several times.

 

thanks. 


How to select all columns except one column from a table ?

  



Hi

I can't figure out how to do this and hope you guys can give me a hint....

I want to select all columns from a table except one column:

For example,

A table has 20 columns : {1,2,3.....20}

To select all of columns in a table, i could use
select * from table;

However, I want to select only column {1....19}

I do not want to type the column names in one by one in the SQL query,

is there a way to achieve this in a short form of Select?

Thanks,


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