transfer Data From Acces: select

Showing posts with label select. Show all posts

Friday, March 30, 2012

how do I assign a string to a parameter Im passing to a select statement?

Hello,

I'm needing to pass a variable length number of values to a select statement so I can populate a result list with items related to all the checkboxlist items that were selected by the user. for example, the user checks products x, y and z, then hits submit, and then they see a list of all the tests they need to run for each product.

I found a UDF that parses a comma delimited string and puts the values into a table. I learned how to do this here:

http://codebetter.com/blogs/darrell.norton/archive/2003/07/01/361.aspx

I have a checkboxlist that I'm generating the string from, so the string could look like this: "1,3,4,5,7" etc.

I added the function mentioned in the URL above to my database, and if I understand right, I should be able to pass the table it creates into the select statement like so:

WHERE (OrderStatus IN ((select value from dbo.fn_Split(@.StatusList,','))) OR @.StatusList IS NULL)

but now I don't know how to assign the string value to the parameter, say to '@.solution_id'.

my current select statement which was generated by Visual Studio 2005 looks like this:

SELECT [test], [owner], [date] FROM [test_table] WHERE ([solution_ID] = @.solution_ID)

...but this only pulls results for the first item checked in the checkboxlist.

Does anyone know how this is done? I'm sure it's simple, but I'm new to ASP .NET so any help would be greatly appreciated.

First make sure you have createddbo.fn_Split .

SELECT [test], [owner], [date]FROM [test_table]WHERE ([solution_ID]IN ((select valuefrom dbo.fn_Split(@.solution_ID,',')))OR @.solution_IDISNULL)

I am not sure "OR @.solution_IDISNULL" should be added,you have to decide it according to your logic.

You are required to pass @.solution_ID to the statement(1,3,4,6 etc) then you can get corresponding test.

Hope this helps.

|||

Thanks for your response. If I'm following you, I do understand that I need to pass @.solution_ID to the select statement like you showed. I have a string of values that I created from iterating through CheckBoxList to find selected boxes. My question is, how do I assign the value of this string to @.solution_ID?

Regards,

Daniel

|||

Assume you have checkboxlist Check1, using following code to get @.solution_ID :

for (int i = 0; i < Check1.Items.Count; i++)
{
if (Check1.Items[i].Selected)
{
// List the selected items
solution_ID = solution_ID + Check1.Items[i].Text;
solution_ID = solution_ID +",";
}
}

Then connect with DB:

SqlCommand sqlcmd = new SqlCommand("SELECT [test], [owner], [date]FROM [test_table]WHERE
([solution_ID]IN ((select valuefrom dbo.fn_Split(@.solution_ID,',')))OR @.solution_IDISNULL)", sqlconn);
sqlcmd.Parameters.AddWithValue("@.solution_ID",solution_ID);
sqlconn.Open();
SqlDataReader sdr = sqlcmd.ExecuteReader();

.............

You 'd bette put bold sql script into a stored procedure.

hope this helps.

How do I append a row to SQL results?

I have a problem with a dropdowncontrol. It is databound, but I need to add "select..." to be the first item in the dropdown. Here is the SQL:

SELECT * FROM [PB_Subtopics] Where BriefID=" + DropDownList1.SelectedValue

So the problem I am having is I can't just make an item in the dropdownlist called "select..." and then use appenddatabounditems="true". I'm using ajax and it keeps appending stuff over an over without resetting. So I think I'm going to have to do this within the sql.

So maybe that was more information than you needed to know. Anyone know how to make the first row of my SQL results be "select..." or whatnot with a value of 0.

You need to do this kind of thing at the front end.

|||

You can do this with a UNION. Something like this:

"SELECT 0, 'Select...'
UNION
SELECT * FROM [PB_Subtopics] Where BriefID=" + DropDownList1.SelectedValue

The number of fields in the first SELECT has to match the second, so adjust that as needed. And I've put in line breaks here to make it clearer what is happening, but it is all one string in what I assume is VB.NET. Adjust as needed for your code.

Also, you might need to use UNION ALL instead of just UNION, depending on your data.

Put the 'Select...' in the field location where the data used to display the item in the dropdown list.

Make sense?

Don

|||

bbaxter:
I have a problem with a dropdowncontrol. It is databound, but I need to add "select..." to be the first item in the dropdown. Here is the SQL:
SELECT * FROM [PB_Subtopics] Where BriefID=" + DropDownList1.SelectedValue
So the problem I am having is I can't just make an item in the dropdownlist called "select..." and then use appenddatabounditems="true". I'm using ajax and it keeps appending stuff over an over without resetting. So I think I'm going to have to do this within the sql.
So maybe that was more information than you needed to know. Anyone know how to make the first row of my SQL results be "select..." or whatnot with a value of 0.

Try this:

1-- Get The Result Set2SELECT Col1, Col2, Col33From MyTable45UNION-- to append67SELECT'MyRowValueForCol1','MyRowValueForCol2','MyRowValueForCol3'-- columns value of the row you want to append to the result set8FROM MyRowTable-- if you are geeting this row from table OR you can ignor this line if the values are hard coded9

Good luck.

|||

You can have the resultset modified using one of the above techniques, that should work.

You can also turn on append databound items and turn off viewstate (Viewstate will prevent the multiple additions as well as cut down the size of your page and the response you get back).

Or you can just add this type of code:

Sub ddlMyDropdown_databound(sender as object,e as object) handles ddlMydropdown.databound

ctype(sender,dropdown).items.insert(0,new listitem("Select...",""))

end sub

sql

How do I add condition for a sum() value in the select statement?

Hi
I'm builing this string dynamically in C# program. I need to add one more
filtering condition of "Sum(Amount) >0" to the Select statement. I added it
but I got an error message. Can someone help on how to do this in one select
statment?
Thanks, Alpha
select exttid,sum(amount) as FeeBalance from tblLedger
where exttid in
(select distinct(exttid) from tblBilling b
where datediff(day,cast('5/17/2005' as datetime),b.formdate) >= 0
and datediff(day,cast('8/17/2005' as datetime),b.formdate) <= 0)
and void = 0
group by exttidLook at the "Having" clause of the select statement. "Having" works on
"Group By" much like the 'Where" works on "From". In your case, you would
add something like:
HAVING Sum(Amount) > 0
after your Group By section.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Alpha" <Alpha@.discussions.microsoft.com> wrote in message
news:F6DA3969-24F9-4895-B7CB-93ECC06EB852@.microsoft.com...
> Hi
> I'm builing this string dynamically in C# program. I need to add one more
> filtering condition of "Sum(Amount) >0" to the Select statement. I added
> it
> but I got an error message. Can someone help on how to do this in one
> select
> statment?
> Thanks, Alpha
> select exttid,sum(amount) as FeeBalance from tblLedger
> where exttid in
> (select distinct(exttid) from tblBilling b
> where datediff(day,cast('5/17/2005' as datetime),b.formdate) >= 0
> and datediff(day,cast('8/17/2005' as datetime),b.formdate) <= 0)
> and void = 0
> group by exttid|||Oh, I see. I have to use Having and it's after the Group. It works great.
Thank you very much and have a great day!
"Geoff N. Hiten" wrote:
> Look at the "Having" clause of the select statement. "Having" works on
> "Group By" much like the 'Where" works on "From". In your case, you would
> add something like:
> HAVING Sum(Amount) > 0
> after your Group By section.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "Alpha" <Alpha@.discussions.microsoft.com> wrote in message
> news:F6DA3969-24F9-4895-B7CB-93ECC06EB852@.microsoft.com...
> > Hi
> > I'm builing this string dynamically in C# program. I need to add one more
> > filtering condition of "Sum(Amount) >0" to the Select statement. I added
> > it
> > but I got an error message. Can someone help on how to do this in one
> > select
> > statment?
> >
> > Thanks, Alpha
> >
> > select exttid,sum(amount) as FeeBalance from tblLedger
> > where exttid in
> > (select distinct(exttid) from tblBilling b
> > where datediff(day,cast('5/17/2005' as datetime),b.formdate) >= 0
> > and datediff(day,cast('8/17/2005' as datetime),b.formdate) <= 0)
> > and void = 0
> > group by exttid
>
>

How do I add condition for a sum() value in the select statement?

Wednesday, March 28, 2012

How do i ?! Basic Select Statement

I have an sql data source..select command is something like this.

Select password from users where username = Username.Text

how can i retrieve the password value and save it into a string?! to make a comparison between password value and a textbox Password.Text

i know its a silly question but i took me long time and i still can't find the ans

We are going to need a little more context to evaluate your exact situation, and how to do what you ask, but here's 1 method:

Dim sqlConn as new SqlConnection("connection string here")

Dim sqlCmd as new SqlCommand("SELECT password from users where username = @.username", SqlConn)

sqlCmd.Paramaters.AddwithValue("@.username", username.text)

Dim DA as new SqlDataAdapter(SqlCmd)

Dim DT as new DataTable

DA.Fill(DT)

Dim myString as string = DT.rows(0).item("password").value ' This line may not be totally correct, doing it from memory and will edit it later

If myString.tolower = stringToCompare.tolower then

'Password is valid

else

'Password could be valid - but something threw you into the else statment, maybe an empty string.

end if

|||

Connection string and select command are already defined in the SqlDataSource.

how can you use the SqlDataSource to extract the value of the password?

i tried to use a hidden GridView but i didn't know how to access a specific cell

by the way i am using C#

|||

The above example is a much cleaner method of getting information like this. Putting a hidden gridview on your page is a VERY heavy method of trying to extract a single value, but it can be done.

Please see this article on how to extract a Datatable from the SqlDataSource control. Once you've done that, you can access a specific row of your datatable as mentioned above.

http://msmvps.com/blogs/egoldin/archive/2006/12/27/how-to-get-datatable-out-of-sqldatasource.aspx

I really have to recomend against this method though. If you insist on using the SqlDataSouce control to get this done, consider putting two hidden textbox on the page, and binding the value that comes back from the database to the hidden textbox, then use a compare validator and only let the user continue if page.isvalid.

How do i ?! Basic Select Statement

I have an sql data source..select command is something like this.

Select password from users where username = Username.Text

how can i retrieve the password value and save it into a string?!

to make a comparison between password value and a textbox Password.Text

i know its a silly question but i took me long time and i still can't find the answer

Hello Ahmad,

Here's some code that should help you out... I'd be happy to clarify anything if needed.

using System.Data;

using System.Data.SqlClient;

string userPassword;

string userName;

SqlConnection cnn = new SqlConnection("Data Source = (local); Initial Catalog = nameofdatabase; Integrated Security = True");

SqlCommand cmd = new SqlCommand("select Password From Users Where username = @.username", cnn);

SqlParameter UserName = new SqlParameter("@.username", userName);

cmd.Parameters.Add(UserName);

cnn.Open();

string password = (string)cmd.ExecuteScalar();

cnn.Close();

cnn.Dispose();

if (userPassword == password)

{

// success! Do something here

}

else

{

// Failure...

}

Regards.

sql

How do can I do this?

Hi,

I have this table:

colA ; colB

1) 1 2

2) 2 1

3) 2 6

4) 3 6

5) 6 3

6) 6 7

7) 7 6

and I need to select from it only the rows that do not have an opposite pair, i.e. only line (3) as it is 2 - 6 and there is no line 6 - 2, all the other lines have opposites, like line (1) 1 - 2 has line (2) 2 - 1 and line (4) has line (5), etc.

any ideas?

thanks.

SELECT b.id, b.colA, b.colB FROM mytest b WHERE b.id NOT IN (SELECT a.id

FROM myTest a INNER JOIN (SELECT id, colA, colB

FROM myTest ) d ON a.colA=d.colB AND a.colB=d.colA)

|||

thanks a lot,

but I have only colA and colB, the "x)" in my post was only for being more clear about it.

|||

Maybe something like this:

declare @.mock table (colA int, colB int)
insert into @.mock values (1, 2)
insert into @.mock values (2, 1)
insert into @.mock values (2, 6)
insert into @.mock values (3, 6)
insert into @.Mock values (6, 3)
insert into @.mock values (6, 7)
insert into @.mock values (7, 6)
select *
from @.mock x
where not exists
( select 0 from @.mock y
where x.cola = y.colb
and x.colb = y.cola
)

-- - Output: -
-- colA colB
-- -- --
-- 2 6

|||

Hi,

thanks to all but here is the solution:

SELECT *

FROM (SELECT * FROM tbl) AS t3

EXCEPT

SELECT *

FROM (SELECT t1.*

FROM tbl AS t1 INNER JOIN

tbl AS t2

on t1.colA = t2.colB AND

t1.colB = t2.colA) AS t4

better to know the new operators... :)

|||

Thank you, Alan; you caught me. I have missed the EXCEPT join at least once before, too. Please keep after me until I get it right. :-)

Dave

|||Using NOT EXISTS is the easiest way to write the query. This version using EXCEPT and joins will be much slower.

How do a pass a 'Where' string clause to a stored procedure

I need to be able to construct a SELECT statement in a stored procedure and
concatenate a CSV list of values passed as an input parm to the stored
procedure. How can I do this without declaring a variable for the Select,
then executing it. I need to use this with Reporting Services so unless the
Select fields are available in the query, RS won't work.See
http://www.sommarskog.se/dyn-search.html
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Kitty" <Kitty@.discussions.microsoft.com> wrote in message
news:B0D87B12-BDCB-488E-89C2-F5D713DFE80E@.microsoft.com...
>I need to be able to construct a SELECT statement in a stored procedure and
> concatenate a CSV list of values passed as an input parm to the stored
> procedure. How can I do this without declaring a variable for the Select,
> then executing it. I need to use this with Reporting Services so unless
> the
> Select fields are available in the query, RS won't work.|||See
http://www.sommarskog.se/arrays-in-sql.html
http://www.users.drew.edu/skass/SQL...unction.sql.txt
The outline is like this:
select stuff
from T
join ListToTableFunction(@.myList) as Items
on Items.Item = T.Item
or with the logic of ListToTableFunction directly
in the non-dynamic query. <>
Steve Kass
Drew University
Kitty wrote:

>I need to be able to construct a SELECT statement in a stored procedure and
>concatenate a CSV list of values passed as an input parm to the stored
>procedure. How can I do this without declaring a variable for the Select,
>then executing it. I need to use this with Reporting Services so unless th
e
>Select fields are available in the query, RS won't work.
>|||>> I need to be able to construct a SELECT statement in a stored
procedure and concatenate a CSV list of values passed as an input parm
to the stored procedure. <<
No, you need to learn about coupling, cohesion and the basics of
software engineering. You are supposed to know what you are doing
before you do it. The idea of doing code on the fly is a violation of
everything you should have been taught in a freshman CS course.

How do a get a listing of all available SQL Servers on a network.

I want to create a web app that will allow a user to select from a drop down or lookup all available SQL Server databases.

Thanks in advance,
Jeremyi answered my own question... hope this helps some one

To restate my problem.
I need an app that will list all of the available SQL Servers on a network. There are many ways to do this ... this is one. I would like to see better ways if anyone has time.

1. Download the SQLUtils.dll from (watch for line wrap)
http://codeproject.com/useritems/listsqlservers.asp?print=true

2. Register the SQLUtils.dll using regsvr32.exe
> regsvr32 c:/inetpub/wwwroot/lib/sqlutils.dll

3. Build your web form

// C#

using SQLUtils;

protected System.Web.UI.WebControls.TextBox TextBox1;
protected System.Web.UI.WebControls.Button Button1;
protected System.Web.UI.WebControls.ListBox ListBox1;

private void Button1_Click(object sender, System.EventArgs e)
{
SQLUtils.ListServers oSqlUtils = new SQLUtils.ListServers();
// String svr = new String();
oSqlUtils.Domain = TextBox1.Text;
oSqlUtils.GetSQLServers();
foreach( String svr in oSqlUtils.Servers )
{
ListBox1.Items.Add(svr);
} // end of foreach loop
} // end of Button1_Click()

4. You will need to add a reference in your project to the .dll file. Right click on the project and select "Add Reference". Browse to the location and select.

I also found these... (watch for line wrap)
http://www.windows2000faq.com/Articles/Index.cfm?ArticleID=14327

Friday, March 23, 2012

How could I return the next row(Or 8th row for example)

Hi,
I want to return the next row in a select ... order by ... cursor.
I don't want to loop,just SQL,Do you know any solution?
For example: in Oracle we use rownum,is there any equivalent in SQL Server?
-ThanksWhy don't you explain why you think you need a cursor? In Oracle cursors are required, but with SQL Server they are considered inefficent and are rarely used.|||Thanks blindman ,
Typical scenario may be looks like this: I return the result of a heavy select(think about many joins and ...) to the client application: I want just one row at this stage. The application processes that row and then it needs the next row in that select to process.How should I say: "The next row in that select"?
One solution: Use a table (maybe temporary table) to hold the result of that select with an identity column as the rownum of each row.
Another solution: Hold the result of select in application layer and go through a loop in application to fetch the next row.
I am just curious about how one could do this in an "on the fly" manner? You are right maybe it is not efficient in SQL Server but is there any way at all?
-Regards|||Well, I think you should hold the data in the application layer if you want to ensure a static dataset for processing. Problem is, if you go back to SQL Server for your "next" record, the underlying data may have changed. Is your application modifying the data and sending it back to SQL server to be updated?|||Is your application modifying the data and sending it back to SQL server to be updated?
No,it is not.So there is no phantom read problem.I think this is so inefficient to pull all data to client-side only because you may want the next row.Are you in agreement? SQL Server should think about this carefuly,IMHO!
-Thanks|||Well you may think that, but it doesn't, not in SQL 2K.

2k5 will have rownumber if I'm not mistaken...

But this is the best server side paging articles I've seen

http://weblogs.sqlteam.com/jeffs/archive/2004/03/22/1085.aspx

And

http://weblogs.sqlteam.com/jeffs/archive/2003/12/22/672.aspx|||No,it is not.So there is no phantom read problem.I think this is so inefficient to pull all data to client-side only because you may want the next row.Are you in agreement? SQL Server should think about this carefuly,IMHO!
-Thanks
No, I'm not in agreement. How the data is displayed is up to the presentation layer and/or middle tier, and is not the database's responsibility. If the application demands paging, then the database needs to be DESIGNED to facilitate paging. Even ROWNUMBER is meaningless for repeated calls on dynamic datasets.|||How the data is displayed is up to the presentation layer and/or middle tier, and is not the database's responsibility.
OK,I am in agreement.Maybe this is about "Presentation of Data" but we know there is not a clear line between those layers: Many things can be done in Database layer but is implemented in "Application server or middle layer" and vice versa.
Thanks to your posts but I still think it is inefficient to pull whole data between layers and I became so happy when I heard that Yukon will have rownum from Brett.Thanks!|||I'd say use the system the way it was designed. Most of your time is not going to be pulling back an entire dataset, but cycling through it row by row. Databases are designed to work on results sets (i.e., not row by row) and thus aren't very efficient when you want to do that. However, procedural languages are designed to do that very thing. By way of example, I had a cursor running on the database when I first began posting here that took approximately 15 minutes to run. Through the help of those more knowledgable here, I was able to remove the cursor and get the same results in under 30 seconds. I guess the best way to test it is do it both ways, but I'm willing to bet you'll find that pulling the entire result set back and cycling in the app will be faster than coding a cursor in the database. Just my thoughts.

How could I modify it as MDX Parameter?

Hi,
I have a MDX syntax as follow
select
[Measures].[Amount] on columns,
[XX03Buyer] on rows
from cubeef
where [XX01Product].[XX01Product].&[2212]
and want to change .&[2212] as Parameter,
that I can select Product code like 2200, 2211.
So, how should I do?
Thanks for any advice!
AngiSet up your parameter as you would normally then in the mdx change it to
="select" & "[Measures].[Amount] on columns, "
& "[XX03Buyer] on rows "
& "from cubeef "
& "where [XX01Product].[XX01Product].&["& Parameters!ProductID.Value &"]"
That should do it, as long as i got the syntax right. Problem with this is
that you can't test it unless you preview it. So if you want to get more
from you MDX suxh as more fields you need to take out all the quotes and &'s
and the = at the beginning. So my advice is save a copy of the MDX query
for a particular product so you can refresh your fields easily.
hope that helps
Greg
"Angi" <enchiw@.msn.com> wrote in message
news:%23pZmQPw9FHA.2644@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have a MDX syntax as follow
> select
> [Measures].[Amount] on columns,
> [XX03Buyer] on rows
> from cubeef
> where [XX01Product].[XX01Product].&[2212]
> and want to change .&[2212] as Parameter,
> that I can select Product code like 2200, 2211.
> So, how should I do?
> Thanks for any advice!
> Angi
>sql

How could I don this?

Here is my query, it works fine. Now, I would like to
execute this query for every item on the itemprice.item
column. I could create another Select query that would
retun all items in the item column of the itemprice table,
but I don't know how to make the first query (the one
below) to go thru every item on that list. Can someone
give some help? TIA
SELECT column1 AS Products, SUM(Total) AS Total FROM
(SELECT t1.column1, t1.column3*t2.column5 AS Total
FROM products AS t1 INNER JOIN itemprice AS t2
ON t1.productid=t2.productid
WHERE t2.item='00152') AS table
GROUP BY column1Try,
SELECT
t1.column1,
t2.item,
sum(t1.column3 * t2.column5) AS Total
FROM
products AS t1
INNER JOIN
itemprice AS t2
ON t1.productid=t2.productid
group by
t1.column1,
t2.item
go
AMB
"JLong" wrote:

> Here is my query, it works fine. Now, I would like to
> execute this query for every item on the itemprice.item
> column. I could create another Select query that would
> retun all items in the item column of the itemprice table,
> but I don't know how to make the first query (the one
> below) to go thru every item on that list. Can someone
> give some help? TIA
> SELECT column1 AS Products, SUM(Total) AS Total FROM
> (SELECT t1.column1, t1.column3*t2.column5 AS Total
> FROM products AS t1 INNER JOIN itemprice AS t2
> ON t1.productid=t2.productid
> WHERE t2.item='00152') AS table
> GROUP BY column1
>|||Thanks for your suggestion. I noticed that the only thing
I had to do was to remove the condition, so the query it
executed for every item not just the one on the condition.

>--Original Message--
>Try,
>SELECT
> t1.column1,
> t2.item,
> sum(t1.column3 * t2.column5) AS Total
>FROM
> products AS t1
> INNER JOIN
> itemprice AS t2
> ON t1.productid=t2.productid
>group by
> t1.column1,
> t2.item
>go
>
>AMB
>
>"JLong" wrote:
>
table,
>.
>sql

Wednesday, March 21, 2012

How come this JOIN no work?

SELECT c.Custid, nr.TextMessageLNEs, nr.CellPhoneCarrierId,
nr.CellMailAlias,
c.CustFullNameDisp, cp.phonenumber AS phone
FROM customers c
JOIN customers_notreplicated nr
ON c.custid=nr.custid
WHERE c.custid=16
JOIN custphone cp
ON c.custid = cp.custid
WHERE cp.phonetypeid = 11
Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'JOIN'. (the second JOIN is having
the problem)
I suspect it's the WHERE clause - but if you can't have a WHERE clause
before the second join, this seems pretty lame to me... but what do i
knowThe problem is the WHERE clause among the JOINs. Only one WHERE
clause is allowed (unless in a subquery, of course), and it has to
appear after the FROM clause and all its parts.
SELECT c.Custid, nr.TextMessageLNEs, nr.CellPhoneCarrierId,
nr.CellMailAlias,
c.CustFullNameDisp, cp.phonenumber AS phone
FROM customers c
JOIN customers_notreplicated nr
ON c.custid=nr.custid
JOIN custphone cp
ON c.custid = cp.custid
WHERE c.custid=16
AND cp.phonetypeid = 11
Roy Harvey
Beacon Falls, CT
On 17 Jan 2007 16:05:31 -0800, tootsuite@.gmail.com wrote:
>SELECT c.Custid, nr.TextMessageLNEs, nr.CellPhoneCarrierId,
>nr.CellMailAlias,
> c.CustFullNameDisp, cp.phonenumber AS phone
> FROM customers c
> JOIN customers_notreplicated nr
> ON c.custid=nr.custid
> WHERE c.custid=16
> JOIN custphone cp
> ON c.custid = cp.custid
> WHERE cp.phonetypeid = 11
>Msg 156, Level 15, State 1, Line 7
>Incorrect syntax near the keyword 'JOIN'. (the second JOIN is having
>the problem)
>I suspect it's the WHERE clause - but if you can't have a WHERE clause
>before the second join, this seems pretty lame to me... but what do i
>know|||This is a multi-part message in MIME format.
--030107000002010507010904
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
The WHERE clause is out of place (and there are too many of them). I
think you probably want something like this:
SELECT
c.Custid,
nr.TextMessageLNEs,
nr.CellPhoneCarrierId,
nr.CellMailAlias,
c.CustFullNameDisp,
cp.phonenumber AS phone
FROM customers AS c
INNER JOIN customers_notreplicated AS nr ON c.custid = nr.custid
INNER JOIN custphone AS cp ON c.custid = cp.custid
WHERE c.custid = 16
AND cp.phonetypeid = 11
Also, which database user(s) owns the [customers], [customers_notreplicated] and [custphone] tables? You ought to explicitly state the object owners - it avoids ambiguity and adds a small performance increase due to the way SQL Server resolves object names.
Check out the Transact-SQL SELECT syntax in SQL Books Online:
http://msdn2.microsoft.com/en-us/library/ms189499.aspx
--
*mike hodgson*
http://sqlnerd.blogspot.com
tootsuite@.gmail.com wrote:
>SELECT c.Custid, nr.TextMessageLNEs, nr.CellPhoneCarrierId,
>nr.CellMailAlias,
> c.CustFullNameDisp, cp.phonenumber AS phone
> FROM customers c
> JOIN customers_notreplicated nr
> ON c.custid=nr.custid
> WHERE c.custid=16
> JOIN custphone cp
> ON c.custid = cp.custid
> WHERE cp.phonetypeid = 11
>Msg 156, Level 15, State 1, Line 7
>Incorrect syntax near the keyword 'JOIN'. (the second JOIN is having
>the problem)
>I suspect it's the WHERE clause - but if you can't have a WHERE clause
>before the second join, this seems pretty lame to me... but what do i
>know
>
>
--030107000002010507010904
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>The WHERE clause is out of place (and there are too many of them).
I think you probably want something like this: 
</tt>
<pre wrap="">SELECT
c.Custid,
nr.TextMessageLNEs,
nr.CellPhoneCarrierId,
nr.CellMailAlias,
c.CustFullNameDisp,
cp.phonenumber AS phone
FROM customers AS c
INNER JOIN customers_notreplicated AS nr ON c.custid = nr.custid
INNER JOIN custphone AS cp ON c.custid = cp.custid
WHERE c.custid = 16
AND cp.phonetypeid = 11
Also, which database user(s) owns the [customers], [customers_notreplicated] and [custphone] tables? You ought to explicitly state the object owners - it avoids ambiguity and adds a small performance increase due to the way SQL Server resolves object names.
Check out the Transact-SQL SELECT syntax in SQL Books Online:
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=http://msdn2.microsoft.com/en-us/library/ms189499.aspx</a>">http://msdn2.microsoft.com/en-us/library/ms189499.aspx">http://msdn2.microsoft.com/en-us/library/ms189499.aspx</a>
</pre>
<div class="moz-signature">
-- 
 mike
hodgson 
<a href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a>

</div>
 
 
<a class="moz-txt-link-abbreviated" href="http://links.10026.com/?link=mailto:tootsuite@.gmail.com">tootsuite@.gmail.com</a> wrote:
<blockquote
cite="mid1169078731.184826.289500@.s34g2000cwa.googlegroups.com"
type="cite">
<pre wrap="">SELECT c.Custid, nr.TextMessageLNEs, nr.CellPhoneCarrierId,
nr.CellMailAlias,
c.CustFullNameDisp, cp.phonenumber AS phone
FROM customers c
JOIN customers_notreplicated nr
ON c.custid=nr.custid
WHERE c.custid=16
JOIN custphone cp
ON c.custid = cp.custid
WHERE cp.phonetypeid = 11
Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'JOIN'. (the second JOIN is having
the problem)
I suspect it's the WHERE clause - but if you can't have a WHERE clause
before the second join, this seems pretty lame to me... but what do i
know
</pre>
</blockquote>
</body>
</html>
--030107000002010507010904--

How come this JOIN no work?

how check the existing index

Hello,
How can I check an existing index on a table?
I have found this:
IF EXISTS (SELECT name FROM sysindexes
WHERE name = 'my_ind_name')
DROP INDEX my_table_name.my_ind_name
My Problem is following:
1. What happens when this index is on an other table (e.g 'my_other_table')
and not on the original table ('my_table_name') Will I get an error
message? I would like to avoid this.
2. As far as I know it is not recommended to reffer to system objects
directly.
Instead of it I should use INFORMATION_SCHEMA. But how?
Thanks for any help
Danyi, AttilaTry,
if exists(
select
*
from
sysindexes
where
[id] = object_id('owner.table_name')
and [indid] between 1 and 254
and indexproperty([id], [name], 'IsStatistics') = 0
and indexproperty([id], [name], 'IsHypothetical') = 0
and [name] = 'index_name'
)
...
There is not an information_schema view to query about indexes.
AMB
"Danyi, Attila" wrote:

> Hello,
> How can I check an existing index on a table?
> I have found this:
> IF EXISTS (SELECT name FROM sysindexes
> WHERE name = 'my_ind_name')
> DROP INDEX my_table_name.my_ind_name
> My Problem is following:
> 1. What happens when this index is on an other table (e.g 'my_other_table
')
> and not on the original table ('my_table_name') Will I get an error
> message? I would like to avoid this.
> 2. As far as I know it is not recommended to reffer to system objects
> directly.
> Instead of it I should use INFORMATION_SCHEMA. But how?
> Thanks for any help
> Danyi, Attila
>|||> 1. What happens when this index is on an other table (e.g
> 'my_other_table')
> and not on the original table ('my_table_name') Will I get an error
> message? I would like to avoid this.
You need to also filter on table:
IF EXISTS (SELECT * FROM sysindexes
WHERE name = 'my_ind_name' AND
OBJECT_NAME(id) = N'my_table_name')
DROP INDEX my_table_name.my_ind_name

> 2. As far as I know it is not recommended to reffer to system objects
> directly.
> Instead of it I should use INFORMATION_SCHEMA. But how?
Indexes are not exposed via the INFORMATION_SCHEMA views because these are
implementation specific. It's ok to use documented system table columns and
functions to query meta data, although I prefer to use the
INFORMATION_SCHEMA views when possible.
Hope this helps.
Dan Guzman
SQL Server MVP
"Danyi, Attila" <DanyiAttila@.discussions.microsoft.com> wrote in message
news:4241A175-9CA7-4B83-903D-E2787BE3927C@.microsoft.com...
> Hello,
> How can I check an existing index on a table?
> I have found this:
> IF EXISTS (SELECT name FROM sysindexes
> WHERE name = 'my_ind_name')
> DROP INDEX my_table_name.my_ind_name
> My Problem is following:
> 1. What happens when this index is on an other table (e.g
> 'my_other_table')
> and not on the original table ('my_table_name') Will I get an error
> message? I would like to avoid this.
> 2. As far as I know it is not recommended to reffer to system objects
> directly.
> Instead of it I should use INFORMATION_SCHEMA. But how?
> Thanks for any help
> Danyi, Attila
>

Monday, March 12, 2012

How can we have 'optional' parameters?

Is there anyway in SQL2005RS to have optional parameters?
Eg supposing we have a report where we want the user to be able to
select a category, but if the user makes no selection we see ALL
catagories. Is this possible.
The sql dataset is something like
select col1
from table
where category=@.category
.....is it possible to make the where clause optional? Ie if category
is NOT selected then dont do the where at all? It also seems like the
SQL2k5RS client MAKES the user select a category?
Al.Use this query instead:
select col1
from table
where category in (@.category)
and make the parameter multi-value. Your drop down will have checkboxes next
to each value, plus a selection at the top that says "All".
Alain Quesnel
alainsansspam@.logiquel.com
www.logiquel.com
"scubaal" <al@.blakes.net> wrote in message
news:bb8fd4f4-23c7-4fc8-9195-cd984397947f@.u36g2000prf.googlegroups.com...
> Is there anyway in SQL2005RS to have optional parameters?
> Eg supposing we have a report where we want the user to be able to
> select a category, but if the user makes no selection we see ALL
> catagories. Is this possible.
> The sql dataset is something like
> select col1
> from table
> where category=@.category
> .....is it possible to make the where clause optional? Ie if category
> is NOT selected then dont do the where at all? It also seems like the
> SQL2k5RS client MAKES the user select a category?
> Al.|||The other option is to have a selection called 'All'. Then in your query do
this (I use this trick a lot).
select col1 from table where (category=@.category or @.category = 'All')
This gives you an example of a query to create your category list for your
parameter.
select category as label, category as value from categorytable union
select 'All' as lable, 'All' as value
order by label
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Alain Quesnel" <alainsansspam@.logiquel.com> wrote in message
news:%23LobPxnpIHA.5916@.TK2MSFTNGP04.phx.gbl...
> Use this query instead:
> select col1
> from table
> where category in (@.category)
> and make the parameter multi-value. Your drop down will have checkboxes
> next to each value, plus a selection at the top that says "All".
>
> Alain Quesnel
> alainsansspam@.logiquel.com
> www.logiquel.com
>
> "scubaal" <al@.blakes.net> wrote in message
> news:bb8fd4f4-23c7-4fc8-9195-cd984397947f@.u36g2000prf.googlegroups.com...
>> Is there anyway in SQL2005RS to have optional parameters?
>> Eg supposing we have a report where we want the user to be able to
>> select a category, but if the user makes no selection we see ALL
>> catagories. Is this possible.
>> The sql dataset is something like
>> select col1
>> from table
>> where category=@.category
>> .....is it possible to make the where clause optional? Ie if category
>> is NOT selected then dont do the where at all? It also seems like the
>> SQL2k5RS client MAKES the user select a category?
>> Al.
>|||My preferred variation (although the others are quite good):
select col1
from table
where category LIKE @.category
You can then define a drop down list within report parameters to
display 'All' to the user, while submitting '%' as the real parameter
value.
On Apr 24, 7:39 pm, scubaal <a...@.blakes.net> wrote:
> Is there anyway in SQL2005RS to have optional parameters?
> Eg supposing we have a report where we want the user to be able to
> select a category, but if the user makes no selection we see ALL
> catagories. Is this possible.
> The sql dataset is something like
> select col1
> from table
> where category=@.category
> .....is it possible to make the where clause optional? Ie if category
> is NOT selected then dont do the where at all? It also seems like the
> SQL2k5RS client MAKES the user select a category?
> Al.|||Another option is to set the report parameter to 'Allows Nulls', and the in
you sql use:
select col1
from table
where
(category=@.category
OR
@.category is null)
-- Original Message --
From: "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com>
Newsgroups: microsoft.public.sqlserver.reportingsvcs
Sent: Friday, April 25, 2008 10:23 AM
Subject: Re: How can we have 'optional' parameters?
> The other option is to have a selection called 'All'. Then in your query
> do this (I use this trick a lot).
> select col1 from table where (category=@.category or @.category = 'All')
> This gives you an example of a query to create your category list for your
> parameter.
> select category as label, category as value from categorytable union
> select 'All' as lable, 'All' as value
> order by label
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Alain Quesnel" <alainsansspam@.logiquel.com> wrote in message
> news:%23LobPxnpIHA.5916@.TK2MSFTNGP04.phx.gbl...
>> Use this query instead:
>> select col1
>> from table
>> where category in (@.category)
>> and make the parameter multi-value. Your drop down will have checkboxes
>> next to each value, plus a selection at the top that says "All".
>>
>> Alain Quesnel
>> alainsansspam@.logiquel.com
>> www.logiquel.com
>>
>> "scubaal" <al@.blakes.net> wrote in message
>> news:bb8fd4f4-23c7-4fc8-9195-cd984397947f@.u36g2000prf.googlegroups.com...
>> Is there anyway in SQL2005RS to have optional parameters?
>> Eg supposing we have a report where we want the user to be able to
>> select a category, but if the user makes no selection we see ALL
>> catagories. Is this possible.
>> The sql dataset is something like
>> select col1
>> from table
>> where category=@.category
>> .....is it possible to make the where clause optional? Ie if category
>> is NOT selected then dont do the where at all? It also seems like the
>> SQL2k5RS client MAKES the user select a category?
>> Al.
>
"scubaal" <al@.blakes.net> wrote in message
news:bb8fd4f4-23c7-4fc8-9195-cd984397947f@.u36g2000prf.googlegroups.com...
> Is there anyway in SQL2005RS to have optional parameters?
> Eg supposing we have a report where we want the user to be able to
> select a category, but if the user makes no selection we see ALL
> catagories. Is this possible.
> The sql dataset is something like
> select col1
> from table
> where category=@.category
> .....is it possible to make the where clause optional? Ie if category
> is NOT selected then dont do the where at all? It also seems like the
> SQL2k5RS client MAKES the user select a category?
> Al.

How can we call Function into Select qry ?

Hi ,
I need to call Function in Select Qry and some of the field of that Qry is
Input to
Function
like select Col1,col2, (select dbo.fn(Col1))Col3 from X
Any help Please ?
KrisKris wrote:
> Hi ,
> I need to call Function in Select Qry and some of the field of that
> Qry is Input to
> Function
> like select Col1,col2, (select dbo.fn(Col1))Col3 from X
> Any help Please ?
> Kris
Select
Col1, Col2, dbo.fn(Col1), Col3
From
X
Is that what you mean? If not, maybe what you're talking about is the
APPLY operator in SQL 2005 which allows you to add columns to a result
set based on the results of a function:
Select Col1, Col2
CROSS APPLY dbo.MyFunc(Col1)
David Gugick - SQL Server MVP
Quest Software|||David Thanks for your reply.
What I understand is, We can not use function into Select Qry, as function
suppose to return value for each records. I have to use Cursor.
Declare c1 -->select IdNum,(COl1+Col2)Col3 from X
read Value into @.Variable
select dbo.fn(@.Variable)
When I used Function in Select Qry. It is not returning value for all record
s,
select col1,col2, (select dbo.fn(col1+col2)) Col3 from X return Values
something like
Col1, Col2 ,Col3
A B AB
X Y AB
Is there any other way i can call fuction in select Qry becoz cursor is slow
.
Thx-Kris
"David Gugick" wrote:

> Kris wrote:
> Select
> Col1, Col2, dbo.fn(Col1), Col3
> From
> X
> Is that what you mean? If not, maybe what you're talking about is the
> APPLY operator in SQL 2005 which allows you to add columns to a result
> set based on the results of a function:
> Select Col1, Col2
> CROSS APPLY dbo.MyFunc(Col1)
>
> --
> David Gugick - SQL Server MVP
> Quest Software
>|||Kris (Kris@.discussions.microsoft.com) writes:
> David Thanks for your reply.
> What I understand is, We can not use function into Select Qry, as function
> suppose to return value for each records. I have to use Cursor.
> Declare c1 -->select IdNum,(COl1+Col2)Col3 from X
> read Value into @.Variable
> select dbo.fn(@.Variable)
> When I used Function in Select Qry. It is not returning value for all
> records,
> select col1,col2, (select dbo.fn(col1+col2)) Col3 from X return Values
> something like
> Col1, Col2 ,Col3
> A B AB
> X Y AB
> Is there any other way i can call fuction in select Qry becoz cursor is
> slow.
It's not fully clear what sort of function you have, but it seems like
you have a scalar function. In such case you can do:
SELECT col1, col2, dbo.fn(col1 + col2) AS Col3 FROM X
If you don't get back all rows as expected, you may have some other
problem with your actual query.
If you have a table-valued function, it depends on which version of
SQL whether you can do this in query or not.
It is difficult to assist when you don't give accurate information of
what you are doing. Could you include the actual code you are using?
Also, please specify which version of SQL Server you are using.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Friday, March 9, 2012

How can this be done...

I'm not to good in programming sql,
what i want to do is select 3 columns from 4 possible,
what i mean is that i have 2 columns in the DB that i need to select
only one of them everytime(one of them is null, but i can't no each row
which one)
and i need the result (the one that is not null) to be in a column
select into is my answer? i don't know how to use it...
plz advice.
And if you if it will work on sql mobile would be great aswellUse CASE , check SQL Books Online. This gives you the capacity to select
different columns depending on what values exist in the column(s)
--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
___________________________________
<chook.harel@.gmail.com> wrote in message
news:1149669940.164598.26800@.i39g2000cwa.googlegroups.com...
> I'm not to good in programming sql,
> what i want to do is select 3 columns from 4 possible,
> what i mean is that i have 2 columns in the DB that i need to select
> only one of them everytime(one of them is null, but i can't no each row
> which one)
> and i need the result (the one that is not null) to be in a column
> select into is my answer? i don't know how to use it...
> plz advice.
> And if you if it will work on sql mobile would be great aswell
>|||I'm not sure , so just a guess
SELECT col1,col2 +COALESCE(col3,'') AS bla FROM Table
<chook.harel@.gmail.com> wrote in message
news:1149669940.164598.26800@.i39g2000cwa.googlegroups.com...
> I'm not to good in programming sql,
> what i want to do is select 3 columns from 4 possible,
> what i mean is that i have 2 columns in the DB that i need to select
> only one of them everytime(one of them is null, but i can't no each row
> which one)
> and i need the result (the one that is not null) to be in a column
> select into is my answer? i don't know how to use it...
> plz advice.
> And if you if it will work on sql mobile would be great aswell
>|||Use COALESCE - check in BOL.
"chook.harel@.gmail.com" wrote:

> I'm not to good in programming sql,
> what i want to do is select 3 columns from 4 possible,
> what i mean is that i have 2 columns in the DB that i need to select
> only one of them everytime(one of them is null, but i can't no each row
> which one)
> and i need the result (the one that is not null) to be in a column
> select into is my answer? i don't know how to use it...
> plz advice.
> And if you if it will work on sql mobile would be great aswell
>|||Use
isnull or coalesce
check in BOL|||Thanks very much for the quick answers,
but from what i've read the COALESCE function still gets only one
paramater..
My thing is to differ from the columns and at the end show only one...|||ooops
I've missread,
Thanks alot! I'll check it out and return with my findings :)
chook.ha...@.gmail.com wrote:
> Thanks very much for the quick answers,
> but from what i've read the COALESCE function still gets only one
> paramater..
> My thing is to differ from the columns and at the end show only one...|||Hehe So I now understand that the COALESCE function checks for NULL
but when I have a null field i've put an empty string there, so it
still gets it
is there a way for the sql to interpert the empty string "" as null?
or should I rewrite my code, so that "" will go as NULL to the db.|||then you will have to use a case.
Say you want to take the non null value between two columns col2 and col3
select case isnull(col2,'') when '' then col3 else col2 end as non_null_col
from tbl1
But if both col2 and col3 are null, then it will give u a null.
You can extend this to more than 2 columns..
Hope this helps.|||On 7 Jun 2006 02:42:00 -0700, chook.harel@.gmail.com wrote:

>Hehe So I now understand that the COALESCE function checks for NULL
>but when I have a null field i've put an empty string there, so it
>still gets it
>is there a way for the sql to interpert the empty string "" as null?
>or should I rewrite my code, so that "" will go as NULL to the db.
Hi Chook,
NULLIF (TheColumn, '')
will return NULL if TheColumn contains an empty string. Otherwise, the
contents of TheColumn is returned.
Hugo Kornelis, SQL Server MVP

Friday, March 30, 2012

how do I assign a string to a parameter Im passing to a select statement?

How do I append a row to SQL results?

How do I add condition for a sum() value in the select statement?

How do I add condition for a sum() value in the select statement?

How do I add condition for a sum() value in the select statement?

Wednesday, March 28, 2012

How do i ?! Basic Select Statement

How do i ?! Basic Select Statement

How do can I do this?

How do a pass a 'Where' string clause to a stored procedure

How do a get a listing of all available SQL Servers on a network.

Friday, March 23, 2012

How could I return the next row(Or 8th row for example)

How could I modify it as MDX Parameter?

How could I don this?

Wednesday, March 21, 2012

How come this JOIN no work?

How come this JOIN no work?

How come this JOIN no work?

how check the existing index

Monday, March 12, 2012

How can we have 'optional' parameters?

How can we call Function into Select qry ?

Friday, March 9, 2012

How can this be done...

transfer Data From Acces

Blog Archive

About Me