Showing posts with label select. Show all posts
Showing posts with label select. Show all posts

Friday, March 30, 2012

Named queries ?

Hi,
Is there anyway i can use 2 databases in one dataset using named
queries ?
here is what i want to do
select name from database1.dbo.employees
union
select name from dabaase2.dbo.employees
i dont have reportmodel veiew etc. I am using plaing old reporting
services 2005.
ThanksAny valid SQL that you can run against SQL Server can be used. You'll want
to use the generic query designer (there is a button to switch from
graphical to generic).
So, yes, you can easily do this. Heck, I have cross database joins.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Prash" <PrashantiKoti@.gmail.com> wrote in message
news:1187030848.237751.264950@.w3g2000hsg.googlegroups.com...
> Hi,
> Is there anyway i can use 2 databases in one dataset using named
> queries ?
> here is what i want to do
> select name from database1.dbo.employees
> union
> select name from dabaase2.dbo.employees
> i dont have reportmodel veiew etc. I am using plaing old reporting
> services 2005.
> Thanks
>

Friday, March 23, 2012

Name part search. Stumped...

/*
I'm having a problem making a select statement to return the
correct results.
Here is an example of what I'm trying to do. Two tables:
@.TblNameParts which contains the master list of all the name
parts and the MasterNameID which is a pointer to the master
record.
@.TblCriteria which is based on the parts of the name being
searched for.
I need a select statement which will return a list of the
valid MasterNameIDs. I know there is a simple solution
I'm just having a hard time finding it.
The logic is:
1) All @.TblCriteria.PartValue's must be in @.TblNameParts.PartValue
grouped by the @.TblNameParts.MasterNameID.
2) @.TblCriteria.PartValue's can be in any order in @.TblNameParts.
3) A @.TblNameParts.PartValue can only be used once for a
@.TblCriteria.PartValue. This is where I'm running into
trouble. You should be able to glean what I mean by the
example's correct results below.
I really appreciate any assistance you can provide, thanks.
*/
SET NOCOUNT ON
-- Main master name parts table.
DECLARE @.TblNameParts TABLE (
MasterNameID int,
PartIndex int,
PartValue varchar(50)
)
-- MasterNameID 10: 'Stacy Smith'
INSERT INTO @.TblNameParts VALUES (10, 0, 'Stacy')
INSERT INTO @.TblNameParts VALUES (10, 1, 'Smith')
-- MasterNameID 15: 'John Doe'
INSERT INTO @.TblNameParts VALUES (15, 0, 'John')
INSERT INTO @.TblNameParts VALUES (15, 1, 'Doe')
-- MasterNameID 20: 'Stacy Ann Smith'
INSERT INTO @.TblNameParts VALUES (20, 0, 'Stacy')
INSERT INTO @.TblNameParts VALUES (20, 1, 'Ann')
INSERT INTO @.TblNameParts VALUES (20, 2, 'Smith')
-- MasterNameID 25: 'Stacy Stacy'
INSERT INTO @.TblNameParts VALUES (25, 0, 'Stacy')
INSERT INTO @.TblNameParts VALUES (25, 1, 'Stacy')
-- MasterNameID 30: 'Smith Stacy'
INSERT INTO @.TblNameParts VALUES (30, 0, 'Smith')
INSERT INTO @.TblNameParts VALUES (30, 1, 'Stacy')
-- Criteria to find a master name ID.
DECLARE @.TblCriteria TABLE (
CriteriaID int,
PartValue varchar(50)
)
-- Search 1 Criteria: 'John Doe'
INSERT INTO @.TblCriteria VALUES (0, 'John')
INSERT INTO @.TblCriteria VALUES (1, 'Doe')
-- Search 1 Correct Result:
-- MasterNameID 15
SELECT TblNameParts.MasterNameID
FROM @.TblNameParts AS TblNameParts
JOIN @.TblCriteria AS TblCriteria ON TblCriteria.PartValue =
TblNameParts.PartValue
GROUP BY TblNameParts.MasterNameID
HAVING COUNT(*) >= 2
-- Search 1 Actual Result:
-- MasterNameID 15
SELECT TblNameParts.MasterNameID
FROM @.TblNameParts AS TblNameParts
WHERE TblNameParts.PartValue IN (SELECT TblCriteria.PartValue
FROM @.TblCriteria AS TblCriteria)
GROUP BY TblNameParts.MasterNameID
HAVING COUNT(*) >= 2
-- Search 1 Actual Result:
-- MasterNameID 15
-- Reset Criteria.
DELETE
FROM @.TblCriteria
-- Search 2 Criteria: 'Stacy'
INSERT INTO @.TblCriteria VALUES (0, 'Stacy')
-- Search 2 Correct Result:
-- MasterNameID 10
-- MasterNameID 20
-- MasterNameID 25
-- MasterNameID 30
SELECT TblNameParts.MasterNameID
FROM @.TblNameParts AS TblNameParts
JOIN @.TblCriteria AS TblCriteria ON TblCriteria.PartValue =
TblNameParts.PartValue
GROUP BY TblNameParts.MasterNameID
HAVING COUNT(*) >= 1
-- Search 1 Actual Result:
-- MasterNameID 10
-- MasterNameID 20
-- MasterNameID 25
-- MasterNameID 30
SELECT TblNameParts.MasterNameID
FROM @.TblNameParts AS TblNameParts
WHERE TblNameParts.PartValue IN (SELECT TblCriteria.PartValue
FROM @.TblCriteria AS TblCriteria)
GROUP BY TblNameParts.MasterNameID
HAVING COUNT(*) >= 1
-- Search 1 Actual Result:
-- MasterNameID 10
-- MasterNameID 20
-- MasterNameID 25
-- MasterNameID 30
-- Reset Criteria.
DELETE
FROM @.TblCriteria
-- Search 3 Criteria: 'Stacy Smith'
INSERT INTO @.TblCriteria VALUES (0, 'Stacy')
INSERT INTO @.TblCriteria VALUES (1, 'Smith')
-- Search 3 Correct Result:
-- MasterNameID 10
-- MasterNameID 20
-- MasterNameID 30
SELECT TblNameParts.MasterNameID
FROM @.TblNameParts AS TblNameParts
JOIN @.TblCriteria AS TblCriteria ON TblCriteria.PartValue =
TblNameParts.PartValue
GROUP BY TblNameParts.MasterNameID
HAVING COUNT(*) >= 2
-- Search 1 Actual Result:
-- MasterNameID 10
-- MasterNameID 20
-- MasterNameID 25
-- MasterNameID 30
SELECT TblNameParts.MasterNameID
FROM @.TblNameParts AS TblNameParts
WHERE TblNameParts.PartValue IN (SELECT TblCriteria.PartValue
FROM @.TblCriteria AS TblCriteria)
GROUP BY TblNameParts.MasterNameID
HAVING COUNT(*) >= 2
-- Search 1 Actual Result:
-- MasterNameID 10
-- MasterNameID 20
-- MasterNameID 25
-- MasterNameID 30
-- Reset Criteria.
DELETE
FROM @.TblCriteria
-- Search 4 Criteria: 'Stacy Stacy'
INSERT INTO @.TblCriteria VALUES (0, 'Stacy')
INSERT INTO @.TblCriteria VALUES (1, 'Stacy')
-- Search 4 Correct Result:
-- MasterNameID 25
SELECT TblNameParts.MasterNameID
FROM @.TblNameParts AS TblNameParts
JOIN @.TblCriteria AS TblCriteria ON TblCriteria.PartValue =
TblNameParts.PartValue
GROUP BY TblNameParts.MasterNameID
HAVING COUNT(*) >= 2
-- Search 1 Actual Result:
-- MasterNameID 10
-- MasterNameID 20
-- MasterNameID 25
-- MasterNameID 30
SELECT TblNameParts.MasterNameID
FROM @.TblNameParts AS TblNameParts
WHERE TblNameParts.PartValue IN (SELECT TblCriteria.PartValue
FROM @.TblCriteria AS TblCriteria)
GROUP BY TblNameParts.MasterNameID
HAVING COUNT(*) >= 2
-- Search 1 Actual Result:
-- MasterNameID 25
SET NOCOUNT OFFGoogle for "relational division". There's a very comprehensive article on th
e
subject by Joe Celko.
ML
http://milambda.blogspot.com/

Monday, March 19, 2012

N - curious?

Looking at an if exists statement created by SQL Enterprise Manager such as:
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[DeleteAllData]') and OBJECTPROPERTY(id, N'IsProcedure') =
1), what does the N do? I've seen cases where it is and isn't necessary, but
I cannot find documentation telling what it does. "N" is a very ineffective
search!!!The N represents a Unicode string.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"CR" <c@.home.com> wrote in message
news:%23DDNZixYGHA.4424@.TK2MSFTNGP05.phx.gbl...
Looking at an if exists statement created by SQL Enterprise Manager such as:
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[DeleteAllData]') and OBJECTPROPERTY(id, N'IsProcedure') =
1), what does the N do? I've seen cases where it is and isn't necessary, but
I cannot find documentation telling what it does. "N" is a very ineffective
search!!!|||See the Books Online topic 'Server-side Programming with Unicode'
(http://msdn2.microsoft.com/en-us/library/ms191313(SQL.90).aspx).
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"CR" <c@.home.com> wrote in message
news:%23DDNZixYGHA.4424@.TK2MSFTNGP05.phx.gbl...
> Looking at an if exists statement created by SQL Enterprise Manager such
> as: if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[DeleteAllData]') and OBJECTPROPERTY(id, N'IsProcedure')
> = 1), what does the N do? I've seen cases where it is and isn't necessary,
> but I cannot find documentation telling what it does. "N" is a very
> ineffective search!!!
>

Monday, March 12, 2012

MySQL to SQL Server migration

Hi,
I would liked to know the SQL Server equivalent for the below MySQL query :
SELECT
a.col1,
a.col2,
max(a.col1)
FROM test a,
test1 b
WHERE a.col2 = b.col2
GROUP BY a.col1
Thanks,
SmithaYou can do it the same way except you can't just show a.col2. You use groupby, so it must belong to some logical output. You can do it like:

SELECT
a.col1,
max(a.col2), --or other functions...
max(a.col1)
FROM test a,
test1 b
WHERE a.col2 = b.col2
GROUP BY a.col1

OR

SELECT
a.col1,
a.col2,
max(a.col1)
FROM test a,
test1 b
WHERE a.col2 = b.col2
GROUP BY a.col1, a.col2

Hope it helps|||Adding all the select columns to the group by can't be the solution as the result will differ when compared to the MySQL query. Am I right ? Please advice.
Also I can't remove the select list item and replace it with aggregate function, as I require that too in my result set.|||imho, MSSQL is here logically correct. Which raw should sql give out if you use group by clause?

id | name
1 a
2 b
2 c
2 d
3 e
If you execute SELECT name, max(id) GROUP BY id, what should come out?
1 a
3 e
2 (?) <-- b, c or d?

So you have to be specific.|||MySQL always gives the first row '2 b'. I need the equivalent for this logic.|||Yes, and that's not definitely. MySQL gives out just the first row, and that's the first row saved in the table. The next time (say, after import-export) it may be an another row.|||SELECT
a.col1,
a.col2,
max(a.col1)
FROM test a,
test1 b
WHERE a.col2 = b.col2
GROUP BY a.col1, a.col2
Your above solution gives all the rows, is there any equivalent SQL Server query which fetches the first row from the table
|||SELECT DISTINCT ?|||SELECT distinct MAX(id), name FROM test3 GROUP BY id, name
Above query is not helping me ! It gives all the rows|||SELECT MAX(id), min(name) FROM test3 GROUP BY id

here you'll get the right grouping. If you take min(name) you'll get the first (alphabetically) grouped name row|||Can you show some sample data and the output for that query in MySQL? I'm not sure how it should behave, given that Col1 is both grouped and in an aggregate function, and Col2 is not even grouped... Very odd... If you can show the input you're using and the output you need, I can help you write a query for it. Also, please note whether you're using SQL Server 2000 or 2005. -- Adam MachanicSQL Server MVPhttp://www.datamanipulation.net-- <smith_kbase@.discussions.microsoft.com> wrote in message news:edc1b9b6-7367-4041-8e24-a17af5660785_WBRev1_@.discussions..microsoft.com...This post has been edited either by the author or a moderator in the Microsoft Forums: http://forums.microsoft.com Hi,I would liked to know the SQL Server equivalent for the below MySQL query :SELECTa.col1,a.col2,max(a.col1)FROM test a,test1 b WHERE a.col2 = b.col2GROUP BY a.col1Thanks,Smitha

MySQL to SQL Server - query migration

Hi,
I would liked to know the SQL Server equivalent for the below MySQL query :
SELECT
a.col1,
a.col2,
max(a.col1)
FROM test a,
test1 b
WHERE a.col2 = b.col2
GROUP BY a.col1
Thanks,
MiraJSELECT
a.col1,
a.col2,
max(a.col1)
FROM test a,
test1 b
WHERE a.col2 = b.col2
GROUP BY a.col1,a.col2

Friday, March 9, 2012

MySQL limit with start, offset

Hi,

I need the equivalent for below mysql query :

select *from test limit 5, 20

I know that the below is possible in SQL Server :

select top 20 * from test where column1 not in (select top 5 column1 from test).

But my problem is I want a generic solution wherein I will not be aware of the columns available within the table.

Please advice,

- MiraWithout knowing the columns? You need to understand that TOP is meaningless in a relational database (SQL Server or MySQL) without an ORDER BY clause that specifies a column. Otherwise, you have no guarantee about the order of the data that comes back, and it can change from one execution to the next.

Monday, February 20, 2012

My question is in SQL

I want to select all tables in one database using combobox .list all tables in combobox.Welcome To msdner.com!

You Can't do such a thing by using Only SQL.What kind of Application is this? What Is The Language you are working with to build it up.
Then We can Move this Question the Relevant forum.

Thanks !

My Own - Multi Select Parameter Passing Implementation

Hi,
I would like to share how I implemented in my application by passing multi
select parameters into the report.
1. In my .net file, I have created a list box which I load all the multi
values that the user wants to report on(for Ex: Equipment Id = 100,200,300,400 and so on).
2. When the user submits the page, I load the above list into a table.
3. In my stored procedure, I go after this table and build the SELECT
statement.
4. Since the report has to refreshed everytime they add another value into
the list, the report has to know that it has to refresh. For this what I have
done is on the table that i load all the values, I extract the SEQUENCE # and
pass it to the report(it is a unique # everytime when you refresh the page).
U can also pass a Random # but the uniqueness is not guaranteed and the
report may not refresh.
In the Report Designer:
1. I create a parameter called RefreshReport. This value that gets passed
into the parameter is the SEQUENCE value.
I poured my head into 3 books for some clue and couldn't find one hint.
I will be more than happy to answer any questions regarding this
implementation..hi, can you be more detailing? I've been following a guide from a book,
there's this query:
="SELECT * FROM vProductProfitability WHERE (Year = @.Year) AND (Mon
thNumberOfYear = @.Month) AND (Category IN (â'+Parameters!CategoryStrin
g.Value+â'))â'
yes, like what you said, you created a new parameter, in here, the the param
is category string, I dn't know where to put this query since i'm using a
stored procedure.. the only way we can use this query is by using text, so
about your stored procedure that you changed.. what is it?
what i can still select is 1 item, although i'm using checkboxlist, i can
select all of them, but w/ an error of course.
can you please discuss how you manage to enable multi select, since you've
posted a helping memo in here already.. advance thanks!
--
~SiMPLe~
"Suresh" wrote:
> Hi,
> I would like to share how I implemented in my application by passing multi
> select parameters into the report.
> 1. In my .net file, I have created a list box which I load all the multi
> values that the user wants to report on(for Ex: Equipment Id => 100,200,300,400 and so on).
> 2. When the user submits the page, I load the above list into a table.
> 3. In my stored procedure, I go after this table and build the SELECT
> statement.
> 4. Since the report has to refreshed everytime they add another value into
> the list, the report has to know that it has to refresh. For this what I have
> done is on the table that i load all the values, I extract the SEQUENCE # and
> pass it to the report(it is a unique # everytime when you refresh the page).
> U can also pass a Random # but the uniqueness is not guaranteed and the
> report may not refresh.
> In the Report Designer:
> 1. I create a parameter called RefreshReport. This value that gets passed
> into the parameter is the SEQUENCE value.
> I poured my head into 3 books for some clue and couldn't find one hint.
> I will be more than happy to answer any questions regarding this
> implementation..

My guess is stored procedures

Greetings All!
I just started playing with MS SQL and was wondering how i could
possibly do the following for example.
I do a 'SELECT source FROM history' and that gives me a list of
sources. Now i want to run a SELECT for each source i received from
the previous SELECT.

For further clarity, in programming terms, it would be just like
looping through an array.

Thanks Guys,
PatricePlease define what you mean by "run a SELECT for each source". It might help
to post the DDL (CREATE TABLE statements) and some sample data (INSERT
statements) for your table(s). Also, show the end-result you would want to
get from that sample data.

In general in SQL you should try to minimise procedural code such as loops.
SQLServer is optimised for set-based queries. If you want to SELECT a set of
rows for each "source" then you can probably write that query as a JOIN to
one or more tables.

For example (from the Pubs database). To retrieve all the Titles for each
Author:

SELECT A.au_fname+' '+A.au_lname AS author, T.title
FROM Authors AS A
JOIN TitleAuthor AS B
ON A.au_id = B.au_id
JOIN Titles AS T
ON B.title_id = T.title_id
ORDER BY A.au_lname, A.au_fname

--
David Portas
SQL Server MVP
--|||Hi David,
Sorry for the lack of clarity.

My plan is to detect if any users login from more than 3 sources.

I have a history table
CREATE TABLE history(client_id int, when datetime, source varchar(20))

After some inserts a SELECT * FROM history looks like

-------------
|client_id | when | source |
-------------
|1010 | <date> | 202.22.21.33 |
-------------
|1818 | <date> | 192.10.22.31 |
-------------
|1542 | <date> | 222.452.1.36 |
-------------
|1010 | <date> | 192.22.21.33 |
-------------
|1010 | <date> | 242.22.21.33 |
-------------
|1010 | <date> | 256.22.21.33 |
-------------

so from this table i would to a
SELECT DISTINCT client_id from history (if thats the syntax) this would
return
----
|client_id |
----
|1010 |
----
|1818 |
----
|1542 |
----

Now from these values i want do a
SELECT count(DISTINCT source) from history where client_id = 1010

this would return 4. Since client_id 1010 logged in from 4 different
sources.
Then i want to run the same query with client_id = 1818 and so on. I.e
loop through the client ids.

How can i do this in a stored proc. My guess is cursors. Any help?

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||No need for cursors or loops. This should give the result you want:

SELECT client_id, COUNT(DISTINCT source)
FROM History
GROUP BY client_id

--
David Portas
SQL Server MVP
--|||
Dave, you're a champ!

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!