In a select statement select_list, if one of the columns in the result set is NULL, I need to substitute a constant value else I want to return the value in the column. Would I use an IF_THEN_ELSE or CASE expression and how would that look?
Thanks,
Robert
Hi,everyone.
Today I met with a interesting problem. According to Microsoft SQL SERVER 2005 BOOKS ONLINE, there is following SELECT Syntax:
(1) SELECT @local_variable (Transact-SQL)
For example:
DECLARE @i int
SELECt @i=100
(2) SELECt QUERY statement:
SELECT [ ALL | DISTINCT ]
[ TOP expression [ PERCENT ] [ WITH TIES ] ]
<select_list>
<select_list> ::=
.....
For example,
[AdventureWorks]
SELECT * FROM DatabaseLog
To my surprise, in the given project, I met this kind of SELECT statement,
DECLARE @w_type varchar(10), @i varchar(30)
SELECT @i='3'
SELECT @w_type='OLTPNORMAL' WHERE @i IN ('0','1','2','3','4','5','9',)
SELECT @w_type
The result is:
OLTPNORMAL
However, I have not met with the kind of SELECT statement before. Please give me some advice. Thank you in advance.
I need to use IF-THEN in a SQL Select statement. Using Google
I have found a couple of obscure references to this but nothing that I
can use. Tried various combinations without luck. Can
anyone point me to a good resource or supply a simple example of proper
syntax? Thanks in advance for any help provided.
Hello,
I have the following statement that I am trying to convert to a "like" statement in a SqlDataSource for a web application. I can't seem to get the syntax correct. Would someone be able to assist with this? Thanks!
SELECT * FROM [Employees] WHERE ([LName] = @LName) ORDER BY [LName], [FName]
Something like below.
SELECT * FROM [Employees] WHERE ([LName] LIKE '@LName%') ORDER BY [LName], [FName]
What is the syntax for making a query using like, the below is my code
<asp:SqlDataSource ID="Search" runat="server"
ConnectionString="<%$ ConnectionStrings:DatabaseConnectionString %>"
ProviderName="<%$ ConnectionStrings:DatabaseConnectionString.ProviderName %>"
SelectCommand="SELECT * FROM [Products]WHERE category LIKE %@category%">
<SelectParameters>
<asp:QueryStringParameter Name="category"
QueryStringField="category" Type="string" />
</SelectParameters>
</asp:SqlDataSource>
Its giving a syntax error but I dont know how to change it.
Please advise, thanks!
Hi everyone, I'm looking for a way to select the default value of a particular column but can't seem to find the syntax for this anywhere. It's a rather peculiar situation where it's needed...
Thanks
I am having trouble with this statement. I am returning multiple rows because I am doing the select statement within the loop. I need to keep the loop somehow because of the where clause of the select statement:
'AND @start not in (select sh_istart from casemas where sh_istart in (select sh_istop from casemas where sh_serial in (53565,53588,53597)))
and @start between sh_istart and sh_istop'
Is there anyway that I can maintain the ability to use the loop but not do mutiple select statements like below:
Also I'm trying really hard not to use temp tables in this example
Result from select statement below
sh_serial
-----------
53565
53597
sh_serial
-----------
53565
53597
sh_serial
-----------
sh_serial
-----------
53588
53597
Desired results:
sh_serial
-----------
53588
53597
53565
Syntax:
declare @start int
select @start = 580
declare @stop int
select @stop = 900
while @start <= @stop
begin
select sh_serial,
from casemas, schilin
WHERE (schi_shser = sh_serial)
and (schi_itemno = '004852')
and (sh_serial <> 600000)
and sh_serial in (53565,53588,53597)
and sh_serial in
(select distinct sh_serial
from casemas, schilin
WHERE (schi_shser = sh_serial)
and (schi_itemno = '004852')
and sh_serial in (53565,53588,53597)
AND @start not in (select sh_istart from casemas where sh_istart in (select sh_istop from casemas where sh_serial in (53565,53588,53597)))
and @start between sh_istart and sh_istop
group by sh_serial
having (sum(schi_qty) + 1 < 4 ))
select @start = @start + 1
end
I'd appreciate any help. Thanks! :o
Hi,
I looked up select case statements and have used them for returning single values, but can't seem to get it working when returning a select statement..
I have my SPROC configured as below, can anyone help me out as to why its not working?
btw previously I had been using something like below, but in this case it wont work as I have to make more changes than just the WHERE genderID = @genderID (hmm, hopefully that makes sense, if not ignore my example lol)
IF @genderID > 2
..
ELSE
........WHERE genderID = @genderID
thanks very much once again!
mike123
CREATE PROCEDURE [dbo].[select_123]
(
@genderID tinyint
)
AS SET NOCOUNT ON
SELECT
CASE @genderID
WHEN 1 THEN
SELECT TOP 40 *
FROM tbl
WHERE .......
WHEN 2 THEN
SELECT TOP 40 *
FROM tbl
WHERE .......
ELSE
SELECT TOP 40 *
FROM tbl
WHERE .......
END
I'm trying to order a varchar column first numerically, and secondalphanumerically using the following SQL:SELECT distinct doc_numberFROM doc_lineWHERE product_id = 'WD' AND doc_type = 'O'ORDER BY CASE WHEN IsNumeric(doc_number) = 1THEN CONVERT(FLOAT, doc_number)ELSE 999999999END,CASE WHEN IsNumeric(doc_number) = 1THEN 'ZZZZZZZZZ'ELSE doc_numberEND;When try executing this statement, I get the following error:Server: Msg 145, Level 15, State 1, Line 1ORDER BY items must appear in the select list if SELECT DISTINCT isspecified.If I take the "distinct" out, it works just fine, except for the fact that Iget many duplicates.Does anyone have any suggestions?Thanks,Frank
View 3 Replies View RelatedHi,
Simple question:
Is there any way to write this in a shorter form:
SELECT fcol like 'X' or fcol like 'Y' or fcol like 'Z'
Maybe something like (which I know it won't work but just to give you an idea what I am looking for)
SELECT fcol like ('X' or 'Y' or 'Z')
I know of this (Full query), but it doesn't work with WILD card characters
select ftype
from engecnentries
where ftype in ('%cone%','%basin%')
any ideas?
I have a table that has unit id, date, time, etc. I would like to select each unit id with the last date it has in the table. The result should have each unit listed once with the latest date in the table.
For Example:
unit id Date Time
00100 01/12/2007 8:00
00100 01/12/2007 8:45
00200 01/12/2007 8:50
00100 01/13/2007 13:30
00300 01/13/2007 13:45
00100 01/14/2007 11:00
00200 01/14/2007 11:30
the result should be:
00100 01/14/2007 11:00
00200 01/14/2007 11:30
00300 01/13/2007 13:45
Hello, I have the following query. When I run the query I get the following error message: "Incorrect syntax near keyword SELECT"--------------------------------------------------- SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY DateAdded) AS rownum, * FROM SELECT TOP (100) PERCENT Videos.VideoId, Videos.UserId, Videos.UserName, Videos.Title, Videos.Description, Videos.Tags, Videos.VideoLength, Videos.TimesHeard, Videos.ImageURL, Videos.RecType, Videos.Language, Videos.Category, Videos.DateAdded, Videos.RewardProgram, Videos.EditorChoice, TB2.hitsFROM Videos LEFT OUTER JOIN (SELECT TOP (100) PERCENT VideoId, COUNT(*) AS hits FROM (SELECT TOP (100) PERCENT UserId, VideoId, COUNT(*) AS cnt1 FROM Hits GROUP BY VideoId, UserId) AS TB1 GROUP BY VideoId) AS TB2 ON Videos.VideoId = TB2.VideoIdORDER BY TB2.hits DESC) AS T1WHERE rownum <= 5----------------------------------------- If I run the query that is in BOLD as: SELECT *
FROM (SELECT ROW_NUMBER() OVER(ORDER BY DateAdded) AS rownum, * FROM Videos) AS T1 WHERE rownum <=5the query runs just fine. Also if I run the query that is NOT bold (above), it also runs fine. What can I do to run them both together as seen above? Thank in advance,Louis
Hi All,
I need some help from u..
I wanted to know the exact flow behind SQL Server when we fire
SELECT select_list
[ FROM table_source ]
[ON Join_Condition]
[ WHERE search_condition ]
[ GROUP BY group_by_expression ]
[ HAVING search_condition ]
[ ORDER BY order_expression [ ASC | DESC ] ]
what is exact execution process mean which get first strike to server and then what followed then..
T.I.A
I'm trying to help out a beginning DBA and come up with a SQL query. Here is the information I've been given so far.
"I have a SQL database (TEST1_new) with several tables. I need to have some values updated in one of the tables HARR2APP.CUSTOMER_ORDER_LINE_TAB1). I need the value that exists in the QTY_SHIPPED field to be divided by 250. I also need to include a WHERE statement for the PLANNED_SHIP_DATE greater than 11/15/2004 and a CATALOG_NO =18053185292 or 18053185285.
Basically, what I need to do is take the value for QTY_SHIPPED for Catalog_NO 18053185285 & 18053185282 and divide the result by 250"
After reading through her statement above about 10 times and guessing a bit, here's what I was able to come up with. I'm sure the syntax is not correct but maybe it's close to being what's needed?
INSERT INTO HARR2APP.CUSTOMER_ORDER_LINE_TAB1()
(SELECT dbo.TEST1_new.HARR2APP.CUSTOMER_ORDER_LINE_TAB.QTY_SHIPPED LIMIT 1) /250),PLANNED_SHIP_DATE from dbo.TEST1_new.HARR2APP.CUSTOMER_ORDER_LINE_TAB
WHERE CATALOG_NO = ‘18053185292’
OR CATALOG_NO= ‘18053185285’
AND PLANNED_SHIP_DATE > ‘11/15/2004’;
Any help is greatly appreciated.
Chris.
This (demo) statement is fine in Access, and so far as I can see, shouldbe OK in SQL Server.But Enterprise Manager barfs at the final bracket. Can anyone helpplease?select sum(field1) as sum1, sum(field2) as sum2 from(SELECT * from test where id < 3unionSELECT * from test where id 2)In fact, I can reduce it to :-select * from(SELECT * from test)with the same effect - clearly I just need telling :-)cheers,Jim--Jima Yorkshire polymoth
View 4 Replies View RelatedI have a interger stored in x.
I want to use x in a SELECT statement like so :
SELECT * from aTable WHERE A_Column = x
This select statement is then assigned to and passed as a string like :
sql = "SELECT * from aTable WHERE A_Column = x"
How does the x get interpreted correctly ?
Have a need to scan a large table to see if a set of criteria have ever been met.
If/when the scan hits its first record meeting the criteria, the scan can be abandoned.
Is there some syntax/option that accomplishes this?
Right now, I am doing a SELECT with criteria against the table and the @@ROWCOUNT gives me a zero, or non-zero value.
But that methodology means that the SELECT has to execute against the entirety of the table.
I'd like to abandon the SELECT as soon as it detects a first record meeting the criteria.
I am getting error "Incorrect syntax near the keyword 'Select'." while running the below query.
DECLARE @DATEPROCESS DATETIME;
SET @DATEPROCESS = CAST(DATEADD(D, -((DATEPART(WEEKDAY, GETDATE()) + 1 + @@DATEFIRST) % 7), GETDATE()) AS DATE)
insert into tempjoin([PART], [SPLRNAME], [SHIPDAYS], [SPRICE]) values
(Select distinct
RC. CAT_PART AS PART,
RC. CAT_SUPPLIER AS SUPPLIER,
FFC.[Ships Within Days] AS SHIPDAYS,
ffc.[Sell Price] AS SPRICE
[code]....
I hope I'm posting this in the correct forum. If not I apologize. I have a nested select query that I imported from Oracle:
Oracle Version:
Code Snippetselect avg(days) as days from (
select dm_number, max(dm_closedate) - max(comment_closed_date) as days from dm_data
where
dm_type = 'prime' and
dm_closedate <= '31-dec-2007' and
dm_closedate >= '1-dec-2007' and
program = 'aads'
group by dm_number)
SQL Version:
select round(abs(avg(days)), 0) as days from
(select dm.dm_number, abs(datediff(DAY,max(dm.dm_closedate), max(dm.comment_closed_date))) as days
from dm_data dm, ProgramXref px
where
px.Program_Name = 'aads'
and dm.Program_Id = px.Program_Id
and dm.dm_type = 'prime'
and dm.dm_closedate <= '31-dec-2007'
and dm.dm_closedate >= '1-dec-2007'
group by dm.dm_number)
In Oracle the query runs fine. In SQL I am getting a "Line 10: Incorrect syntax near ')'." error. If I run just the nested portion of the query, there are no errors. It only happens when the first query tries to query the nested query. Can anyone help me get the syntax correct?
Thanks,
Lee
Hi,
Im fairly new to writing stored procudures so I thought you lot might be able to help with this problem.
I have a stored procudure which looks like this:
CREATE PROCEDURE usrCienet.spAdminAgencyActivate_Select
(
@strAgencyId CHAR(6)
)
AS
DECLARE @idAgency INT
SELECT @idAgency = idAgency FROM tblAgencies WHERE strAgencyId = @strAgencyId;
SELECT strName, strAddress1, strAddress2, strCounty, strCountry, strPostcode, strTelephone, strFax, bitActive, bitHeadOffice, bitFinanceBranch
FROM tblBranches
WHERE fk_idAgency = @idAgency
SELECT strFirstName, strSurname, strUsername, bitActive
FROM tblUsers
WHERE fk_idAgency = @idAgency
GO
It basically first declare's and sets @idAgency using the first small select statment, then uses that parameter to run two more selects queries which I want sending to a dataset. Now within the Query Analyzer this works fine. But in my asp.net page it trows up this error:
Exception Details: System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near 'spAdminAgencyActivate_Select'
Now the code im using in the asp.net page is as follows:
Dim objSqlConnection as New SqlConnection(ConfigurationSettings.AppSettings("strCon"))
Dim objSqlCommand_Select as New SqlCommand("spAdminAgencyActivate_Select", objSqlConnection)
objSqlCommand_Select.Parameters.Add(New SqlParameter("@strAgencyId", SqlDbType.Char, 6))
objSqlCommand_Select.Parameters("@strAgencyId").Value = "tes001"
Dim objSqlDataAdapter as New SqlDataAdapter(objSqlCommand_Select)
Dim dsActivateAgency as New DataSet()
objSqlConnection.Open()
objSqlDataAdapter.TableMappings.Add("Table", "tblBranches")
objSqlDataAdapter.TableMappings.Add("Table1", "tblUsers")
objSqlDataAdapter.Fill(dsActivateAgency)
objSqlConnection.Close()
Can anyone help? I believe the error is in the stored procedure somewhere, because if I change the stored procedure so no parameters are being passed to it then it starts working. This is what I comment out and change for it to to get it working, but obviously this is not satisfactory as a final result because the parameter is hard coded in the stored procedure. Im just showing this to see if it gives anyone a clue!!
CREATE PROCEDURE usrCienet.spAdminAgencyActivate_Select
--(
--@strAgencyId CHAR(6)
--)
AS
--DECLARE @idAgency INT
--SELECT @idAgency = idAgency FROM tblAgencies WHERE strAgencyId = @strAgencyId;
SELECT strName, strAddress1, strAddress2, strCounty, strCountry, strPostcode, strTelephone, strFax, bitActive, bitHeadOffice, bitFinanceBranch
FROM tblBranches
WHERE fk_idAgency = 1
SELECT strFirstName, strSurname, strUsername, bitActive
FROM tblUsers
WHERE fk_idAgency = 1
GO
Thanks in advance for any help!!
- Carl S
Good day!
I just can't figure out how I can display only the top record for the duplicate records in my table.
Example:
Table1
Code Date
01 10/1/05
01 10/2/05
01 10/3/05
02 9/9/05
02 9/9/05
02 9/10/05
My desired result would be:
Table1
Code Date
01 10/1/05
02 9/9/05
Thanks.
In Code Behind, What is proper select statement syntax to retrieve the @BName field from a table?Using Visual Studio 2003SQL Server DB
I created the following parameter:Dim strName As String Dim parameterBName As SqlParameter = New SqlParameter("@BName", SqlDbType.VarChar, 50) parameterBName.Value = strName myCommand.Parameters.Add(parameterBName)
I tried the following but get error:Dim strSql As String = "select @BName from Borrower where BName= DOROTHY V FOWLER "
error is:Line 1: Incorrect syntax near 'V'. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near 'V'.
Source Error:
Line 59: Line 60: Line 61: myCommand.ExecuteNonQuery() 'Execute the query
What I am trying to create a query to check, If recDT is not value or null, then will use value from SELECT top 1 recDtim FROM Serv. Otherwise, will use the value from recDT. I have tried the below query but it doesn't work. The error says, Incorrect syntax near the keyword 'SELECT'.Incorrect syntax near the keyword 'else'.1 SELECT
2 case when recDT='' then SELECT top 1 recDtim FROM Serv else recDT end
3 FROM abc
4
Anyone can help? Thanks a lot.
Why does the following call to a stored procedure get me this error:
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'CONVERT'.
Code Snippet
EXECUTE OpenInvoiceItemSP_RAM CONVERT(DATETIME,'01-01-2008'), CONVERT(DATETIME,'04/30/2008') , 1,'81350'
The stored procedure accepts two datetime parameters, followed by an INT and a varchar(10) in that order.
I can't find anything wrong in the syntax for CONVERT or any nearby items.
Help me please. Thank you.
I keep receiving the following error whenever I try and call this function to update my database.
The code was working before, all I added was an extra field to update.
Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near the keyword 'WHERE'
Public Sub MasterList_Update(sender As Object, e As DataListCommandEventArgs)
Dim strProjectName, txtProjectDescription, intProjectID, strProjectState as String
Dim intEstDuration, dtmCreationDate, strCreatedBy, strProjectLead, dtmEstCompletionDate as String
strProjectName = CType(e.Item.FindControl("txtProjectName"), TextBox).Text
txtProjectDescription = CType(e.Item.FindControl("txtProjDesc"), TextBox).Text
strProjectState = CType(e.Item.FindControl("txtStatus"), TextBox).Text
intEstDuration = CType(e.Item.FindControl("txtDuration"), TextBox).Text
dtmCreationDate = CType(e.Item.FindControl("txtCreation"),TextBox).Text
strCreatedBy = CType(e.Item.FindControl("txtCreatedBy"),TextBox).Text
strProjectLead = CType(e.Item.FindControl("txtLead"),TextBox).Text
dtmEstCompletionDate = CType(e.Item.FindControl("txtComDate"),TextBox).Text
intProjectID = CType(e.Item.FindControl("lblProjectID"), Label).Text
Dim strSQL As String
strSQL = "Update tblProject " _
& "Set strProjectName = @strProjectName, " _
& "txtProjectDescription = @txtProjectDescription, " _
& "strProjectState = @strProjectState, " _
& "intEstDuration = @intEstDuration, " _
& "dtmCreationDate = @dtmCreationDate, " _
& "strCreatedBy = @strCreatedBy, " _
& "strProjectLead = @strProjectLead, " _
& "dtmEstCompletionDate = @dtmEstCompletionDate, " _
& "WHERE intProjectID = @intProjectID"
Dim myConnection As New SqlConnection(System.Configuration.ConfigurationSettings.AppSettings("connectionstring"))
Dim cmdSQL As New SqlCommand(strSQL, myConnection)
cmdSQL.Parameters.Add(new SqlParameter("@strProjectName", SqlDbType.NVarChar, 40))
cmdSQL.Parameters("@strProjectName").Value = strProjectName
cmdSQL.Parameters.Add(new SqlParameter("@txtProjectDescription", SqlDbType.NVarChar, 30))
cmdSQL.Parameters("@txtProjectDescription").Value = txtProjectDescription
cmdSQL.Parameters.Add(new SqlParameter("@strProjectState", SqlDbType.NVarChar, 30))
cmdSQL.Parameters("@strProjectState").Value = strProjectState
cmdSQL.Parameters.Add(new SqlParameter("@intEstDuration", SqlDbType.NVarChar, 60))
cmdSQL.Parameters("@intEstDuration").Value = intEstDuration
cmdSQL.Parameters.Add(new SqlParameter("@dtmCreationDate", SqlDbType.NVarChar, 15))
cmdSQL.Parameters("@dtmCreationDate").Value = dtmCreationDate
cmdSQL.Parameters.Add(new SqlParameter("@strCreatedBy", SqlDbType.NVarChar, 10))
cmdSQL.Parameters("@strCreatedBy").Value = strCreatedBy
cmdSQL.Parameters.Add(new SqlParameter("@strProjectLead", SqlDbType.NVarChar, 15))
cmdSQL.Parameters("@strProjectLead").Value = strProjectLead
cmdSQL.Parameters.Add(new SqlParameter("@dtmEstCompletionDate", SqlDbType.NVarChar, 24))
cmdSQL.Parameters("@dtmEstCompletionDate").Value = dtmEstCompletionDate
cmdSQL.Parameters.Add(new SqlParameter("@intProjectID", SqlDbType.NChar, 5))
cmdSQL.Parameters("@intProjectID").Value = intProjectID
myConnection.Open()
cmdSQL.ExecuteNonQuery
myConnection.Close()
MasterList.EditItemIndex = -1
BindMasterList()
End Sub
Thankyou in advance.
Forgive the noob question, but i'm still learning SQL everyday and was wondering which of the following is faster? I'm just gonna post parts of the SELECT statement that i've made changes to:
INNER JOIN Facilities f ON e.Facility = f.FacilityID AND f.Name = @FacilityName
OR
WHERE f.Name = @FacilityName
My question is whether or not the query runs faster if i put the condition within the JOIN line as opposed to putting in the WHERE line? Both ways seems to return the same results but the time difference between methods is staggering? Putting the condition within the JOIN line makes the query run about 3 times faster?
Again, forgive my lack of understanding, but could someone agree or disagree and give me the cliff-notes version of why or why not?
Thanks!
Ok I am tying to convert access syntax to Sql syntax to put it in a stored procedure or view..
Here is the part that I need to convert:
SELECT [2007_hours].proj_name, [2007_hours].task_name, [2007_hours].Employee,
IIf(Mid([task_name],1,3)='PTO','PTO_Holiday',
IIf(Mid([task_name],1,7)='Holiday','PTO_Holiday',
IIf(Mid([proj_name],1,9) In ('9900-2831','9900-2788'),'II Internal',
IIf(Mid([proj_name],1,9)='9900-2787','Sales',
IIf(Mid([proj_name],1,9)='9910-2799','Sales',
IIf(Mid([proj_name],1,9)='9920-2791','Sales',
)
)
)
)
) AS timeType, Sum([2007_hours].Hours) AS SumOfHours
from................
how can you convert it to sql syntax
I need to have a nested If statment which I can't do in sql (in sql I have to have select and from Together for example ( I can't do this in sql):
select ID, FName, LName
if(SUBSTRING(FirstName, 1, 4)= 'Mike')
Begin
Replace(FirstNam,'Mike','MikeTest')
if(SUBSTRING(LastName, 1, 4)= 'Kong')
Begin
Replace(LastNam,'Kong,'KongTest')
if(SUBSTRING(Address, 1, 4)= '1245')
Begin
.........
End
End
end
Case Statement might be the solution but i could not do it.
Your input will be appreciated
Thank you
Hi,
I am a newbie to this site and hope someone can help....
I have a select statement which I would like to create an extra column and put an if statement in it.... Current syntax is:
if(TL_flag= '1', "yes") as [Trial Leave]
it is coming up with an error.... I can use Select case but I should not need to as this should work?
Any ideas?
This is the error it gives me for my code and then it calls out line 102. Line 102 is my buildDD(sql, ddlPernames) When I comment out this line the error goes away, but what I don't get is this is the same way I build all of my dropdown boxes and they all work but this one. Could it not like something in my sql select statement. thanksPrivate Sub DDLUIC_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles DDLUIC.SelectedIndexChanged
Dim taskforceID As Byte = ddlTaskForce.SelectedValueDim uic As String = DDLUIC.SelectedValue
sql = "select sidstrNAME_IND from CMS.dbo.tblSIDPERS where sidstrSSN_SM in (Select Case u.strSSN from tblAssignedPersonnel as u " _
& "where u.bitPresent = 1 and u.intUICID in (select intUICID from tblUIC where intTaskForceID = " & taskforceID & " and strUIC = '" & uic & "'))"ddlPerNames.Items.Add(New ListItem("", "0"))
buildDD(sql, ddlPerNames)
End Sub
Hello all,
I have two mult-value parameters in my report. Both of them working with selecting one or more values. But, when I test using "(Select All)" values for both parameters , only one parameter works. The "available values" for these two parameters are both from the data set.
select distinct ProductType
from Product
order by ProductType
Any suggestion? thx
I am unable to the access on table even after providing the SELECT permission on table.
Used Query by me :
Here Test is schema ; Card is table ; User is Satish
To grant select on Table
GRANT SELECT ON TEST.Card TO satish
Even after this it is not working, So provided select on schema also.
used query : GRANT SELECT ON SCHEMA::TEST TO Satish.
Hi all,
I copied and executed the following sql code in my SQL Server Management Studio Express (SSMSE):
--SELECTeg8.sql from SELECT-Using correlated subqueries of MSDN2 SELECT Examples--
USE AdventureWorks ;
GO
SELECT DISTINCT Name
FROM Production.Product p
WHERE EXISTS
(SELECT *
FROM Production.ProductModel pm
WHERE p.ProductModelID = pm.ProductModelID
AND pm.Name = 'Long-sleeve logo jersey') ;
GO
-- OR
USE AdventureWorks ;
GO
SELECT DISTINCT Name
FROM Production.Product
WHERE ProductModelID IN
(SELECT ProductModelID
FROM Production.ProductModel
WHERE Name = 'Long-sleeve logo jersey') ;
GO
=========================================
I got:
Results Messages
Name o row affected
========================================
I think I did not get a complete output from this job. Please help and advise whether I should search somewhere in the SSMSE for the complete results or I should correct some code statements in my SELECTeg8.sql for obtaining the complete results.
Thanks in advance,
Scott Chang
I have a report that uses different datasets based on the year selected by a user.
I have a year_id parameter that sets a report variable named dataset_chosen. I have varified that these are working correctly together.
I have attempted populating table cell data to display from the chosen dataset. As yet to no avail.
How could I display data from the dataset a user selects via the year_id options?