join or subselect: queries

Showing posts with label queries. Show all posts

Monday, March 26, 2012

joning result of dynamic queries

i m making the code of search button in aspx page using C#.

i have multiple dynamic queries that take data from multiple tables

but i dnt know how to join the result of these queries.

example:

1) first query using table1

2)second query using table2

3)third query using table3

how to join the result of these queries at asp.net page?

thanx in advance

If the result sets of all the queries are similar (ie. same number of columns of the same data types)

Then just issue one command with all three queries union'd together

Code Snippet

SELECT x,y,z From Table1

UNION ALL

SELECT x, y z From Table2

UNION All

SELECT x, y, z From Table 3

|||

thanx for reply but it is not useful at all

because i m asking how to combine the result of dynamic queries that relate multiple tables and u r sending simple sql query.i willl try to explain my problem in moredetail as follow.

TABLE NAME COLUMNNAMES

1) tbQualification -- ResumeId,Graduation,PostGraduation

2) tbStrength-- Resumeid,Strength

3)tbExperience Resumeid,YearExperience,MonthExperience

there is a button of SEARCH .clicking on this button search is to be made in all columns fields.this is to be done throgh dynamic query or stored procedure.

this cant be done using simple sql query because we are writting code in aspx page.

plizz help me out

all replies are welcome

Thanx.

|||

You can store the result of each query in the temp table then join those temp tables for your final query...

Code Snippet

Create Table #tbQualificationResult (

ResumeId int,

Graduation varchar(20),

PostGraduation varchar(20)

)

Create Table #tbStrength (

Resumeid int,

Strength int

)

Create Table #tbExperience (

Resumeid int,

YearExperience int,

MonthExperience int

)

Insert Into #tbQualificationResult

Exec sp_executesql 'Your Dynamic Search Query'

Insert Into #tbStrength

Exec sp_executesql 'Your Dynamic Search Query'

Insert Into #tbExperience

Exec sp_executesql 'Your Dynamic Search Query'

Select

COALESCE(A.ResumeId, B.ResumeId, C.ResumeId)

,A.Graduation

,A.PostGraduation

,C.Strength

,C.YearExperience

,C.MonthExperience

From

#tbQualificationResult A

Full OUTER JOIN #tbStrength B on A.ResumeId = B.ResumeId

FULL OUTER JOIN #tbExperience C On A.ResumeId = C.ResumeId

|||

One approach to your issue, from my response and Mani's, is to code the combinations on the back-end in SQL Server.

However, this can also be done at the BI or UI tier.

See these articles for creating datasets with multiple tables and establishing datarelations between those tables.

http://msdn2.microsoft.com/en-us/library/ss7fbaez(VS.80).aspx

http://msdn2.microsoft.com/en-us/library/ay82azad(VS.80).aspx

http://msdn2.microsoft.com/en-us/library/d6s958d6(VS.80).aspx

|||

thanx a lot for reply.

plizz tel how i write query where u say"your dynamic query" because i dnt know how to write dynamic query in sql.

i know only in aspx page and it is different to write in sql so pliz tel.

need urgently

thanx

sql

Joins Vs Where clause - Performance Query

Hi There !!

To finetune performance for some of our queries,

I have come across suggestions to use

- JOINS instead of WHERE clause wherever possible
- and avoid using Aliases

Although Avoiding aliases looks reasonable I am yet to be convinced about JOINS replacing the WHERE CLAUSE . What is the experts take on this one ??

Also,

I checked the estimated plan in SQL server by running the following 2 queries into my Query Designer

tables : dba ( empid, empname )
project ( project_empid references dba.empid, project_name )

USING A WHERE CLAUSE and Alias
--------
select a.emp_name from dbo.dba a, dbo.project b
where
a.empid =b.project_emp
and b.project_name is not null

USING A JOIN
------
select emp_name from dbo.dba
as
a inner JOIN dbo.project
ON empid = dbo.project.project_emp
AND dbo.project.project_name is not NULL

******

I find from the Estimated plan that both the queries give the same amount of cost ( I/O, CPU, et all ) :shocked:

Any comments/ suggestions.

Thanks,

Have a great time
-Ranjit.

------------
It pays to be honest to your DBAmy experience is that 99% of the time, the optimizer is smart enough to generate the same plan regardless of whether you use the ansi join syntax or not. I prefer the ansi syntax just for purity's sake however.

If you haven't already, you should measure first (using profiler) to find where the bottlenecks are. Only after you have measured can you begin to address perf issues.

Finally, I am fairly certain that changing from one join syntax to another is not going to fix any perf issues you may have.|||Finally, I am fairly certain that changing from one join syntax to another is not going to fix any perf issues you may have.Agreed - ANSI syntax is merely convention (although of course you can do more than an inner join with ANSI).

and avoid using AliasesNope again - this is just a convention too. Some people think aliases make code easier to read, blindman does not. :)

I don't know where you stand in performance tuning experience but everyone of any level can find something of use here:
http://www.sql-server-performance.com/articles_performance.asp

HTH|||avoiding aliases is not "reasonable"

:)|||Some people think aliases make code easier to read, blindman does not. :)
My reputation preceeds me.
But even I don't claim the aliases hurt performance.

Wednesday, March 21, 2012

Joining two SQL queries with different conditions

Ok I am stumped on this. i tried an inner join where posted_jrnl_line as a
and posted_jrnl_line as b, also tried subqueries where (select jrnlId [then
conditions]) to no avail...
I have two queries that execute perfectly. the two queries are retrieving
data from one table but the conditions vary between the queries.
I would like to roll the two queries into one queries so I can create a
report for users.
Query #1
select
jrnl_id as 'System Jrnl',
jrnl_user_alpha_fld_3 as 'Proj Jrnl',
SUM(trans_amt) as 'Proj Jrnl Total'
from
DBSjepc.dbo.posted_jrnl_line
where
dbs_gl_account = '3475'
and jrnl_origin_code = 'PJ'
and posting_pd = '11'
and posting_yr = '2006'
and jrnl_user_alpha_fld_3 like 'TBC%'
Group by
jrnl_id, jrnl_user_alpha_fld_3)
So the output of Query 1 is
System Jrnl | Proj Jrnl | Proj Jrnl Total
Query #2
select jrnl_id as 'Ldr Jrnl',
jrnl_user_alpha_fld_3 as 'Ldr None',
SUM(trans_amt) as 'Ldr Jrnl Total'
from DBSjepc.dbo.posted_jrnl_line
where dbs_gl_account = '3475'
and not jrnl_origin_code in ('PJ','PR')
and posting_pd = '11'
and posting_yr = '2006'
and jrnl_id like 'TBC%'
Group by jrnl_id,jrnl_user_alpha_fld_3
So the output of Query 2 is
Ldr Jrnl | Ldr None | Ldr Jrnl Total
I would like the output to be
System Jrnl | Proj Jrnl | Proj Jrnl Total | Ldr Jrnl | Ldr None | Ldr Jrnl
Total
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200606/1I noticed that the WHERE criteria is not the same. Which criteria you you
want?
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"jsheldon via webservertalk.com" <u2880@.uwe> wrote in message
news:621d8e5ca05d2@.uwe...
> Ok I am stumped on this. i tried an inner join where posted_jrnl_line as
> a
> and posted_jrnl_line as b, also tried subqueries where (select jrnlId
> [then
> conditions]) to no avail...
> I have two queries that execute perfectly. the two queries are retrieving
> data from one table but the conditions vary between the queries.
> I would like to roll the two queries into one queries so I can create a
> report for users.
> Query #1
> select
> jrnl_id as 'System Jrnl',
> jrnl_user_alpha_fld_3 as 'Proj Jrnl',
> SUM(trans_amt) as 'Proj Jrnl Total'
> from
> DBSjepc.dbo.posted_jrnl_line
> where
> dbs_gl_account = '3475'
> and jrnl_origin_code = 'PJ'
> and posting_pd = '11'
> and posting_yr = '2006'
> and jrnl_user_alpha_fld_3 like 'TBC%'
> Group by
> jrnl_id, jrnl_user_alpha_fld_3)
> So the output of Query 1 is
> System Jrnl | Proj Jrnl | Proj Jrnl Total
>
> Query #2
> select jrnl_id as 'Ldr Jrnl',
> jrnl_user_alpha_fld_3 as 'Ldr None',
> SUM(trans_amt) as 'Ldr Jrnl Total'
> from DBSjepc.dbo.posted_jrnl_line
> where dbs_gl_account = '3475'
> and not jrnl_origin_code in ('PJ','PR')
> and posting_pd = '11'
> and posting_yr = '2006'
> and jrnl_id like 'TBC%'
> Group by jrnl_id,jrnl_user_alpha_fld_3
> So the output of Query 2 is
> Ldr Jrnl | Ldr None | Ldr Jrnl Total
> I would like the output to be
> System Jrnl | Proj Jrnl | Proj Jrnl Total | Ldr Jrnl | Ldr None | Ldr Jrnl
> Total
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Forum...amming/200606/1|||Hi arnie,
that is correct the where clauses are different by the jrnl_origin field.
Query #1 must contain PJ for its output and Query #2 must NOT contain PJ and
PR for its output
Arnie Rowland wrote:
>I noticed that the WHERE criteria is not the same. Which criteria you you
>want?
>
>[quoted text clipped - 50 lines]
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200606/1|||You have in effect two distinctly different queries. Your desired output pro
vides nothing in common between the two queries.
Is all the data on a single line somehow related?
Are you trying for:
System Jrnl | Proj Jrnl | Proj Jrnl Total | Ldr Jrnl | Ldr None | Ldr Jrnl
-- -- -- -- -- --
data data data NULL NULL NULL
NULL NULL NULL data data data
OR
System Jrnl | Proj Jrnl | Proj Jrnl Total | Ldr Jrnl | Ldr None | Ldr Jrnl
-- -- -- -- -- --
data data data data data data
If you hope for the later, you best bet is to create the report in a client
application -like a reporting tool such as SQL Reporting Services -or even A
ccess.
From the limited information, I don't see how you will get to the second exa
mple. If you would send in the table DDL and some represtentative sample dat
a in the form of INSERT statements -you may be able to get more help.
Regards,
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"jsheldon via webservertalk.com" <u2880@.uwe> wrote in message news:621dd08c3fcbe@.uwe...darkred">
> Hi arnie,
>
> that is correct the where clauses are different by the jrnl_origin field.
> Query #1 must contain PJ for its output and Query #2 must NOT contain PJ a
nd
> PR for its output
>
> Arnie Rowland wrote:
>
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Forum...amming/200606/1|||Hi Arnie,
Actually a mix but Option #1 is what is what I am trying to strive for. the
output does have a relation but not a database one. Users enter a project
journal must use the same journal ID when making their ledger journal.
This is desired.
System Jrnl | Proj Jrnl | Proj Jrnl Total | Ldr Jrnl | Ldr None | Ldr Jrnl
>-- -- -- -- -- --
data data data data NULL data
However if placed on two lines that is desirable as well: see below
System Jrnl | Proj Jrnl | Proj Jrnl Total | Ldr Jrnl | Ldr None | Ldr Jrnl
>-- -- -- -- -- --
data data data NULL NULL NULL
NULL NULL NULL data data data
Arnie Rowland wrote:
>You have in effect two distinctly different queries. Your desired output pr
ovides nothing in common between the two queries.
>Is all the data on a single line somehow related?
>Are you trying for:
>System Jrnl | Proj Jrnl | Proj Jrnl Total | Ldr Jrnl | Ldr None | Ldr Jrnl
>-- -- -- -- -- --
>data data data NULL NULL NULL
>NULL NULL NULL data data data
>OR
>System Jrnl | Proj Jrnl | Proj Jrnl Total | Ldr Jrnl | Ldr None | Ldr Jrnl
>-- -- -- -- -- --
>data data data data data data
>If you hope for the later, you best bet is to create the report in a client
application -like a reporting tool such as SQL Reporting Services -or even
Access.
>From the limited information, I don't see how you will get to the second ex
ample. If you would send in the table DDL and some represtentative sample da
ta in the form of INSERT statements -you may be able to get more help.
>Regards,
>
>[quoted text clipped - 10 lines]
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200606/1

joining two queries ?

How can I return the results of two different queries ?
So that the recordset returned has all the fields from query A, plus the
results from query B ?
Both return the same columns.
ALookup UNION in Books Online.
David Portas
SQL Server MVP
--

Joining two large queries as derived tables

All,
I want to join two large queries together keying on a common column. I
have simplifed the queries / derived tables; but I am trying to do
something like what I have below. How do you join two derived tables?
SELECT * FROM
(SELECT Name AS 'Name1', ... other columns
FROM [TABLE1]) AS a
JOIN
SELECT * FROM
(SELECT Name AS 'Name2', ... other columns
FROM [TABLE2]) AS b
ON a.Name = b.NameTry,
SELECT *
FROM
(SELECT Name AS 'Name1', ... other columns FROM [TABLE1]) AS a
inner JOIN
(SELECT Name AS 'Name2', ... other columns FROM [TABLE2]) AS b
ON a.Name = b.Name
go
AMB
"dpless@.gmail.com" wrote:

> All,
> I want to join two large queries together keying on a common column. I
> have simplifed the queries / derived tables; but I am trying to do
> something like what I have below. How do you join two derived tables?
>
> SELECT * FROM
> (SELECT Name AS 'Name1', ... other columns
> FROM [TABLE1]) AS a
> JOIN
> SELECT * FROM
> (SELECT Name AS 'Name2', ... other columns
> FROM [TABLE2]) AS b
> ON a.Name = b.Name
>|||Is this what you are trying to do?
SELECT * FROM
(SELECT * FROM table1) a
INNER JOIN (select * from table2) b ON a.name=b.name
-- or --
SELECT * FROM
(SELECT * FROM dailychallenge) a,
(SELECT * FROM DailyChallengeResponse) b
WHERE a.challengedate=b.challengedate
Let us know how you make out.
Mark Graveline
Take The Challenge
http://www.sqlchallenge.com
--

Joining tables in different databases

I'm working on an ASP project where the clients want to be able to
effectively perform SELECT queries joining tables from two different
databases (located on the same SQL-Server).

Does this involve creating virtual tables that link to another database, or
am I completely on the wrong track?

Any hints as to where I might find more information (buzz-words, etc.) would
be most appreciated.

ThanksTry 'four part names' :).
server.database.owner.table

"Captain Nemo" <nemo@.nospam.com> wrote in message
news:6oqBf.8267$wl.3901@.text.news.blueyonder.co.uk ...
> Hi
> I'm working on an ASP project where the clients want to be able to
> effectively perform SELECT queries joining tables from two different
> databases (located on the same SQL-Server).
> Does this involve creating virtual tables that link to another database,
> or
> am I completely on the wrong track?
> Any hints as to where I might find more information (buzz-words, etc.)
> would
> be most appreciated.
> Thanks|||Use 3-part naming:

select
*
from
dbo.MyTable l
join
OtherDB.dbo.OtherTable o on o.PK = l.PK

--
Tom

----------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com

"Captain Nemo" <nemo@.nospam.com> wrote in message
news:6oqBf.8267$wl.3901@.text.news.blueyonder.co.uk ...
Hi

I'm working on an ASP project where the clients want to be able to
effectively perform SELECT queries joining tables from two different
databases (located on the same SQL-Server).

Does this involve creating virtual tables that link to another database, or
am I completely on the wrong track?

Any hints as to where I might find more information (buzz-words, etc.) would
be most appreciated.

Thanks|||The OP said the two DB's were on the same server. Therefore, 3-part naming
is sufficient.

--
Tom

----------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com

"MC" <marko_culo#@.#yahoo#.#com#> wrote in message
news:dr5cc8$bue$1@.magcargo.vodatel.hr...
Try 'four part names' :).
server.database.owner.table

It sure is! I've just tried it out. Where I went wrong was thinking that
2-part naming would do it (omitting the 'dbo').

Thanks, Tom|||Agreed, but why not provide a more complete info since theres a little
difference? He may need to pull data from two servers tomorrow...

"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:LBqBf.4368$ft2.109590@.news20.bellglobal.com.. .
> The OP said the two DB's were on the same server. Therefore, 3-part
> naming
> is sufficient.
> --
> Tom
> ----------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> "MC" <marko_culo#@.#yahoo#.#com#> wrote in message
> news:dr5cc8$bue$1@.magcargo.vodatel.hr...
> Try 'four part names' :).
> server.database.owner.table
>
> MC
>
> "Captain Nemo" <nemo@.nospam.com> wrote in message
> news:6oqBf.8267$wl.3901@.text.news.blueyonder.co.uk ...
>> Hi
>>
>> I'm working on an ASP project where the clients want to be able to
>> effectively perform SELECT queries joining tables from two different
>> databases (located on the same SQL-Server).
>>
>> Does this involve creating virtual tables that link to another database,
>> or
>> am I completely on the wrong track?
>>
>> Any hints as to where I might find more information (buzz-words, etc.)
>> would
>> be most appreciated.
>>
>> Thanks
>>
>>|||There's often a performance difference.

--
Tom

----------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com

"MC" <marko_culo#@.#yahoo#.#com#> wrote in message
news:dr5eu2$s5q$1@.magcargo.vodatel.hr...
Agreed, but why not provide a more complete info since theres a little
difference? He may need to pull data from two servers tomorrow...

"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:dRsBf.4433$ft2.115520@.news20.bellglobal.com.. .
> There's often a performance difference.
> --
> Tom
> ----------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> "MC" <marko_culo#@.#yahoo#.#com#> wrote in message
> news:dr5eu2$s5q$1@.magcargo.vodatel.hr...
> Agreed, but why not provide a more complete info since theres a little
> difference? He may need to pull data from two servers tomorrow...
>
> MC
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:LBqBf.4368$ft2.109590@.news20.bellglobal.com.. .
>> The OP said the two DB's were on the same server. Therefore, 3-part
>> naming
>> is sufficient.
>>
>> --
>> Tom
>>
>> ----------------
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Columnist, SQL Server Professional
>> Toronto, ON Canada
>> www.pinpub.com
>>
>> "MC" <marko_culo#@.#yahoo#.#com#> wrote in message
>> news:dr5cc8$bue$1@.magcargo.vodatel.hr...
>> Try 'four part names' :).
>> server.database.owner.table
>>
>>
>> MC
>>
>>
>> "Captain Nemo" <nemo@.nospam.com> wrote in message
>> news:6oqBf.8267$wl.3901@.text.news.blueyonder.co.uk ...
>>> Hi
>>>
>>> I'm working on an ASP project where the clients want to be able to
>>> effectively perform SELECT queries joining tables from two different
>>> databases (located on the same SQL-Server).
>>>
>>> Does this involve creating virtual tables that link to another database,
>>> or
>>> am I completely on the wrong track?
>>>
>>> Any hints as to where I might find more information (buzz-words, etc.)
>>> would
>>> be most appreciated.
>>>
>>> Thanks
>>>
>>>
>>
>>
>>
>|||MC (marko_culo#@.#yahoo#.#com#) writes:
> Do you mean that actually specifing servername slows down the query? Could
> you explain why?

If the server name use is @.@.servername, SQL Server will shortcut, and
there is no overhead.

But if the linked server is defined a true loopback, so that there is a
new connection made, there is obviously an overhead, as data is first
passed to SQL Server, to the OLE DB provider on one connection, and then
the OLE DB provider passes the data back to another connection.

--
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|||Thank you for that, I assumed engine would 'optimize' that and never
checked. Just when I think I actually know something ;)....

"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9756F1F84A0F4Yazorman@.127.0.0.1...
> MC (marko_culo#@.#yahoo#.#com#) writes:
>> Do you mean that actually specifing servername slows down the query?
>> Could
>> you explain why?
> If the server name use is @.@.servername, SQL Server will shortcut, and
> there is no overhead.
> But if the linked server is defined a true loopback, so that there is a
> new connection made, there is obviously an overhead, as data is first
> passed to SQL Server, to the OLE DB provider on one connection, and then
> the OLE DB provider passes the data back to another connection.
> --
> 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|||MC (marko_culo#@.#yahoo#.#com#) writes:
> Thank you for that, I assumed engine would 'optimize' that and never
> checked. Just when I think I actually know something ;)....

As I mentioned, it does optimize when the server is @.@.servername, which
it can recognize. But it does not analyse connection strings.

--
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

Monday, March 19, 2012

Joining table UDFs in queries

Hi,
I've got a table UDF which takes two parameters and returns a table, as
follows:
CREATE FUNCTION dbo.ftblPeriodYear (@.pCompanyID varchar(15), @.pDate
datetime)
RETURNS @.tblPeriodYear TABLE
(
Period tinyint,
Year smallint
)
AS
BEGIN
<snipped to save space>
RETURN
END
That works fine. However, is it possible to use this UDF as part of a query
where the input parameters come from another table?
E.g. the two input parameters I want to pass to the function are contained
within the Sales table, and I could output them as follows:
SELECT
CompanyID,
SaleDate,
<other fields>
FROM
Sales
Ideally, I'm looking for some way of combining the query on the table with
the UDF e.g.
SELECT
CompanyID,
SaleDate,
ftblPeriodYear(CompanyID, SaleDate)
FROM
Sales
Is this even possible?
Any assistance gratefully received.
MarkI'm afraid not in SQL Server 2000. This is new functionality added in SQL
Server 2005 via the APPLY table operator, e.g.,
SELECT ...
FROM Sales AS S
CROSS APPLY ftblPeriodYear(S.CompanyID, S.SaleDate) AS F;
You can find more details here:
http://www.windowsitpro.com/Article...47145.html?Ad=1
http://msdn.microsoft.com/library/d...TSQLEnhance.asp
BG, SQL Server MVP
www.SolidQualityLearning.com
Join us for the SQL Server 2005 launch at the SQL W

in Israel!
[url]http://www.microsoft.com/israel/sql/sqlw

/default.mspx[/url]
"Mark Rae" <mark@.mark-N-O-S-P-A-M-rae.co.uk> wrote in message
news:eueZ2kxyFHA.460@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I've got a table UDF which takes two parameters and returns a table, as
> follows:
> CREATE FUNCTION dbo.ftblPeriodYear (@.pCompanyID varchar(15), @.pDate
> datetime)
> RETURNS @.tblPeriodYear TABLE
> (
> Period tinyint,
> Year smallint
> )
> AS
> BEGIN
> <snipped to save space>
> RETURN
> END
> That works fine. However, is it possible to use this UDF as part of a
> query where the input parameters come from another table?
> E.g. the two input parameters I want to pass to the function are contained
> within the Sales table, and I could output them as follows:
> SELECT
> CompanyID,
> SaleDate,
> <other fields>
> FROM
> Sales
> Ideally, I'm looking for some way of combining the query on the table with
> the UDF e.g.
> SELECT
> CompanyID,
> SaleDate,
> ftblPeriodYear(CompanyID, SaleDate)
> FROM
> Sales
>
> Is this even possible?
> Any assistance gratefully received.
> Mark
>|||That :
SELECT
CompanyID,
SaleDate,
ftblPeriodYear(CompanyID, SaleDate)
FROM=20
Sales=20
doesn=B4t work. :-(|||"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1128675411.933739.210250@.g14g2000cwa.googlegroups.com...

>That :
>SELECT
> CompanyID,
> SaleDate,
> ftblPeriodYear(CompanyID, SaleDate)
>FROM
> Sales
>
>doesnt work. :-(
Er, yeah I know - that was the reason for my post...|||"Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in message
news:unelksxyFHA.3720@.TK2MSFTNGP14.phx.gbl...

> I'm afraid not in SQL Server 2000. This is new functionality added in SQL
> Server 2005 via the APPLY table operator, e.g.,
Thanks - I was vaguely aware that there was something like this in SQL
Server 2005, but wondered if it had an equivalent in 2000...

joining results of 2 queries

Does anyone know how I can 'join' the results of
one SQL query to the bottom of another?

Eg. I have two queries:

1. SELECT Name, Surname FROM People WHERE Surname = Smith

NAME SURNAME

Adam Smith
John Smith
Michael Smith
Steve Smith

2. SELECT Name, Surname FROM People WHERE Surname = Jones

NAME SURNAME

Bob Jones
Larry Jones
Tom Jones

What I want to produce is:

NAME SURNAME

Adam Smith
John Smith
Michael Smith
Steve Smith
Bob Jones
Larry Jones
Tom Jones

However, if I use UNION like this:

SELECT Name, Surname FROM People WHERE Surname = Smith
UNION
SELECT Name, Surname FROM People WHERE Surname = Jones

it mixes up all the results:

NAME SURNAME

Adam Smith
Bob Jones
John Smith
Larry Jones
Michael Smith
Steve Smith
Tom Jones

(I guess it's sorting by the first field, NAME).

Is there a way to stop it sorting the results, so that it
just tacks the second query results to the bottom of the
first query results?

(I realise I could use "ORDER BY Surname" to get the same result
in this simple example, but for the more complicated queries
I want to use it won't work).

Thanks for any help,

Matt.Hi Matt,

Union sorts the data and tries to remove duplicates. Use UNION ALL to
override this behavior. If the data is stored in a table, use an
identity column to preserve the order. - Louis|||odonnell_matthew@.hotmail.com (Matt O'Donnell) wrote in message news:<cc73ca09.0311150527.4f160205@.posting.google.com>...
> Does anyone know how I can 'join' the results of
> one SQL query to the bottom of another?
> Eg. I have two queries:
>
> 1. SELECT Name, Surname FROM People WHERE Surname = Smith
>
> NAME SURNAME
> Adam Smith
> John Smith
> Michael Smith
> Steve Smith
>
> 2. SELECT Name, Surname FROM People WHERE Surname = Jones
>
> NAME SURNAME
> Bob Jones
> Larry Jones
> Tom Jones
>
> What I want to produce is:
>
> NAME SURNAME
> Adam Smith
> John Smith
> Michael Smith
> Steve Smith
> Bob Jones
> Larry Jones
> Tom Jones
>
> However, if I use UNION like this:
> SELECT Name, Surname FROM People WHERE Surname = Smith
> UNION
> SELECT Name, Surname FROM People WHERE Surname = Jones
>
> it mixes up all the results:
> NAME SURNAME
> Adam Smith
> Bob Jones
> John Smith
> Larry Jones
> Michael Smith
> Steve Smith
> Tom Jones
>
> (I guess it's sorting by the first field, NAME).
> Is there a way to stop it sorting the results, so that it
> just tacks the second query results to the bottom of the
> first query results?
>
> (I realise I could use "ORDER BY Surname" to get the same result
> in this simple example, but for the more complicated queries
> I want to use it won't work).
> Thanks for any help,
> Matt.

WHy not use

SELECT Name, Surname FROM People WHERE Surname = 'Jones' or 'SMITH'
ORDER BY Surname, Name

Simple and avoids the need for a UNION

HOpe this helps

Duncan|||I agree that in this case you should just use an OR, but assuming your real
case is more complicated, how about

SELECT 1 AS ResultSetNum, Name, Surname FROM People WHERE Surname = Smith
UNION ALL
SELECT 2 AS ResultSetNum, Name, Surname FROM People WHERE Surname = Jones
ORDER BY ResultSetNum

"duncan" <dwang@.woodace.co.uk> wrote in message
news:8fe85207.0311151415.3ece8226@.posting.google.c om...
> odonnell_matthew@.hotmail.com (Matt O'Donnell) wrote in message
news:<cc73ca09.0311150527.4f160205@.posting.google.com>...
> > Does anyone know how I can 'join' the results of
> > one SQL query to the bottom of another?
> > Eg. I have two queries:
> > 1. SELECT Name, Surname FROM People WHERE Surname = Smith
> > NAME SURNAME
> > Adam Smith
> > John Smith
> > Michael Smith
> > Steve Smith
> > 2. SELECT Name, Surname FROM People WHERE Surname = Jones
> > NAME SURNAME
> > Bob Jones
> > Larry Jones
> > Tom Jones
> > What I want to produce is:
> > NAME SURNAME
> > Adam Smith
> > John Smith
> > Michael Smith
> > Steve Smith
> > Bob Jones
> > Larry Jones
> > Tom Jones
> > However, if I use UNION like this:
> > SELECT Name, Surname FROM People WHERE Surname = Smith
> > UNION
> > SELECT Name, Surname FROM People WHERE Surname = Jones
> > it mixes up all the results:
> > NAME SURNAME
> > Adam Smith
> > Bob Jones
> > John Smith
> > Larry Jones
> > Michael Smith
> > Steve Smith
> > Tom Jones
> > (I guess it's sorting by the first field, NAME).
> > Is there a way to stop it sorting the results, so that it
> > just tacks the second query results to the bottom of the
> > first query results?
> > (I realise I could use "ORDER BY Surname" to get the same result
> > in this simple example, but for the more complicated queries
> > I want to use it won't work).
> > Thanks for any help,
> > Matt.
>
> WHy not use
> SELECT Name, Surname FROM People WHERE Surname = 'Jones' or 'SMITH'
> ORDER BY Surname, Name
>
> Simple and avoids the need for a UNION
> HOpe this helps
>
> Duncan

Monday, March 12, 2012

Joining Count Queries?

Hi,
I want to join 2 count queries toegther and can't work out how to go about
it.
The first query obtains the total products by country...
select count(*) as TotalProducts,
c.Country_Name
from product_source ps, country c where
ps.product_id in (select product_id from record_links where link_reference =
901)
and
ps.country_code = c.country_code
group by c.Country_Name
which gives...
14 Ukraine
6 Poland
1 Russia
1 Belarus
1 Lithuania
The second query obtains the countries with new products...
select count(*) as NewProducts,
c.Country_Name
from product_source ps, country c where
ps.product_id in (select product_id from record_links where link_reference =
901 and date_linked > '01/11/2005')
and
ps.country_code = c.country_code
group by c.Country_Name
which gives
1 Poland
So what I want is a single line for each country in a combined query...
14 Ukraine 0
6 Poland 1
1 Russia 0
1 Belarus 0
1 Lithuania 0
Is there a way of doing some kind of outer join to get the results i need?
thanks,
jpXref: TK2MSFTNGP01.phx.gbl microsoft.public.sqlserver.server:435994
I worked this out using a stored procedure with a sub select...
/* Country Breakdown */
CREATE PROCEDURE [dbo].[GetCountryBreakdown] AS
select count(*) as TotalProducts,
c.Country_code,
c.Country_name,
(
select count(*) as NewProducts
from product_source p where
p.product_id in (select product_id from record_links where link_reference =
901 and date_linked > '01/11/2005')
and
p.country_code = c.country_code
)
from product_source ps, country c where
ps.product_id in (select product_id from record_links where link_reference =
901)
and
ps.country_code = c.country_code
group by c.Country_Code,c.Country_name
GO
"jp" <jpfrancoisuk@.yahoo.co.uk> wrote in message
news:Zi_eg.4241$xH1.3173@.newsfe3-gui.ntli.net...
> Hi,
> I want to join 2 count queries toegther and can't work out how to go about
> it.
> The first query obtains the total products by country...
> select count(*) as TotalProducts,
> c.Country_Name
> from product_source ps, country c where
> ps.product_id in (select product_id from record_links where link_reference
> = 901)
> and
> ps.country_code = c.country_code
> group by c.Country_Name
> which gives...
> 14 Ukraine
> 6 Poland
> 1 Russia
> 1 Belarus
> 1 Lithuania
> The second query obtains the countries with new products...
> select count(*) as NewProducts,
> c.Country_Name
> from product_source ps, country c where
> ps.product_id in (select product_id from record_links where link_reference
> = 901 and date_linked > '01/11/2005')
> and
> ps.country_code = c.country_code
> group by c.Country_Name
> which gives
> 1 Poland
> So what I want is a single line for each country in a combined query...
> 14 Ukraine 0
> 6 Poland 1
> 1 Russia 0
> 1 Belarus 0
> 1 Lithuania 0
> Is there a way of doing some kind of outer join to get the results i need?
> thanks,
> jp
>

Joining Count Queries?

Hi,
I want to join 2 count queries toegther and can't work out how to go about
it.
The first query obtains the total products by country...
select count(*) as TotalProducts,
c.Country_Name
from product_source ps, country c where
ps.product_id in (select product_id from record_links where link_reference = 901)
and
ps.country_code = c.country_code
group by c.Country_Name
which gives...
14 Ukraine
6 Poland
1 Russia
1 Belarus
1 Lithuania
The second query obtains the countries with new products...
select count(*) as NewProducts,
c.Country_Name
from product_source ps, country c where
ps.product_id in (select product_id from record_links where link_reference = 901 and date_linked > '01/11/2005')
and
ps.country_code = c.country_code
group by c.Country_Name
which gives
1 Poland
So what I want is a single line for each country in a combined query...
14 Ukraine 0
6 Poland 1
1 Russia 0
1 Belarus 0
1 Lithuania 0
Is there a way of doing some kind of outer join to get the results i need?
thanks,
jpI worked this out using a stored procedure with a sub select...
/* Country Breakdown */
CREATE PROCEDURE [dbo].[GetCountryBreakdown] AS
select count(*) as TotalProducts,
c.Country_code,
c.Country_name,
(
select count(*) as NewProducts
from product_source p where
p.product_id in (select product_id from record_links where link_reference =901 and date_linked > '01/11/2005')
and
p.country_code = c.country_code
)
from product_source ps, country c where
ps.product_id in (select product_id from record_links where link_reference =901)
and
ps.country_code = c.country_code
group by c.Country_Code,c.Country_name
GO
"jp" <jpfrancoisuk@.yahoo.co.uk> wrote in message
news:Zi_eg.4241$xH1.3173@.newsfe3-gui.ntli.net...
> Hi,
> I want to join 2 count queries toegther and can't work out how to go about
> it.
> The first query obtains the total products by country...
> select count(*) as TotalProducts,
> c.Country_Name
> from product_source ps, country c where
> ps.product_id in (select product_id from record_links where link_reference
> = 901)
> and
> ps.country_code = c.country_code
> group by c.Country_Name
> which gives...
> 14 Ukraine
> 6 Poland
> 1 Russia
> 1 Belarus
> 1 Lithuania
> The second query obtains the countries with new products...
> select count(*) as NewProducts,
> c.Country_Name
> from product_source ps, country c where
> ps.product_id in (select product_id from record_links where link_reference
> = 901 and date_linked > '01/11/2005')
> and
> ps.country_code = c.country_code
> group by c.Country_Name
> which gives
> 1 Poland
> So what I want is a single line for each country in a combined query...
> 14 Ukraine 0
> 6 Poland 1
> 1 Russia 0
> 1 Belarus 0
> 1 Lithuania 0
> Is there a way of doing some kind of outer join to get the results i need?
> thanks,
> jp
>

Joining 2 queries in one table

I am trying to join 2 queries within one table, but as the results return 2 columns and 12 rows for each query, I am having problems working out how to join them together. (This is actually part of a far larger join of many queries, but this is the bit I am stuck on!)

Any help would be appreciated.

Thanks

SELECT CONVERT(nvarchar,MONTH(Appointment.actual_end_date))+'/'+CONVERT(nvarchar,YEAR(Appointment.actual_end_date)) AS 'Month',COUNT(*) AS 'TotalAppsPerMonth' FROM Appointment
WHERE Appointment.actual_end_date BETWEEN DATEADD(mm, DATEDIFF(mm,0,GETDATE()-341), 0)
AND DATEADD(ms,-3,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1, 0))
GROUP BY MONTH(Appointment.actual_end_date),YEAR(Appointment.actual_end_date)

You want to UNION them from what I can see.

SELECT CONVERT(nvarchar,MONTH(Job.job_cdate))+'/'+CONVERT(nvarchar,YEAR(Job.job_cdate)) AS 'Month',COUNT(*) AS 'TotalJobsIn Year' FROM Job
WHERE Job.job_cdate BETWEEN DATEADD(mm, DATEDIFF(mm,0,GETDATE()-341), 0)
AND DATEADD(ms,-3,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1, 0))
GROUP BY MONTH(Job.job_cdate),YEAR(Job.job_cdate)
UNION ALL
SELECT CONVERT(nvarchar,MONTH(Appointment.actual_end_date))+'/'+CONVERT(nvarchar,YEAR(Appointment.actual_end_date)) AS 'Month',COUNT(*) AS 'TotalAppsPerMonth' FROM Appointment
WHERE Appointment.actual_end_date BETWEEN DATEADD(mm, DATEDIFF(mm,0,GETDATE()-341), 0)
AND DATEADD(ms,-3,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1, 0))
GROUP BY MONTH(Appointment.actual_end_date),YEAR(Appointment.actual_end_date)

Adamus

|||

Here You Go...

Code Snippet

Select

Isnull(JobData.[Month],AppointmentData.[Month]) as [Month]

,JobData.[TotalJobsIn Month]

,AppointmentData.[TotalAppsPer Month]

From

(

SELECT

CONVERT(nvarchar,MONTH(Job.job_cdate))+'/'+CONVERT(nvarchar,YEAR(Job.job_cdate)) AS 'Month'

,COUNT(*) AS 'TotalJobsIn Month'

FROM

Job

WHERE

Job.job_cdate BETWEEN DATEADD(mm, DATEDIFF(mm,0,GETDATE()-341), 0)

AND DATEADD(ms,-3,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1, 0))

GROUP BY

MONTH(Job.job_cdate),YEAR(Job.job_cdate)

) as JobData

FULL OUTER Join

(

SELECT

CONVERT(nvarchar,MONTH(Appointment.actual_end_date))+'/'+CONVERT(nvarchar,YEAR(Appointment.actual_end_date)) AS 'Month'

,COUNT(*) AS 'TotalAppsPer Month'

FROM

Appointment

WHERE

Appointment.actual_end_date BETWEEN DATEADD(mm, DATEDIFF(mm,0,GETDATE()-341), 0)

AND DATEADD(ms,-3,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1, 0))

GROUP BY

MONTH(Appointment.actual_end_date),YEAR(Appointment.actual_end_date)

) as AppointmentData On JobData.[Month] = AppointmentData.[Month]

|||Thanks Manivannan. That is exactly what I needed - I knew I couldn't use the union clause as I wanted the results next to each other.
Thanks so much
K

joining 2 queries

How would i join these 2 queries:
QUERY 1:
SELECT DATEPART(yyyy, PERIODS.PERIOD_DATE) AS dteYear, DATEPART(mm,
PERIODS.PERIOD_DATE) AS dteMonth, SUM(FCST.FCST_QTY) AS FCST
FROM FCST INNER JOIN
PERIODS ON FCST.FCST_PERIODID = PERIODS.PERIOD_ID
GROUP BY DATEPART(yyyy, PERIODS.PERIOD_DATE), DATEPART(mm,
PERIODS.PERIOD_DATE)
returns:
year | month | FCST
QUERY 2:
SELECT DATEPART(yyyy, MOMA200F.DAMOV) AS dteYear, DATEPART(mm,
MOMA200F.DAMOV) AS Expr1, SUM(MOMA200F.QTMOV * TAGACGV1.CFTAG)
AS Exits
FROM MOMA200F INNER JOIN
TAGACGV1 ON SUBSTRING(MOMA200F.CDPAR, 8, 1) =
TAGACGV1.CDTAG
GROUP BY DATEPART(yyyy, MOMA200F.DAMOV), DATEPART(mm, MOMA200F.DAMOV),
MOMA200F.ATM01, MOMA200F.CDMAG, MOMA200F.CAUAZ2
HAVING (DATEPART(yyyy, MOMA200F.DAMOV) = DATEPART(yyyy, DATEADD(yyyy, -
1, GETDATE())) AND DATEPART(mm, MOMA200F.DAMOV) = 12 OR
DATEPART(yyyy, MOMA200F.DAMOV) = 2005 AND DATEPART(mm,
MOMA200F.DAMOV) BETWEEN 1 AND 11) AND (MOMA200F.ATM01 = '') AND
(MOMA200F.CDMAG = 'pf') AND (MOMA200F.CAUAZ2 = '-')
ORDER BY DATEPART(yyyy, MOMA200F.DAMOV), DATEPART(mm, MOMA200F.DAMOV)
returns:
year | month | exits
What i should see is:
year | month | exits | FCST
Many thanks in advance
PS: If you can point me to some good resources for "complicated" queries, i
would be thankfull
EricSELECT Q1.Year, Q1.Month, Q2.Exits, Q1.FCST
FROM
(SELECT DATEPART(yyyy, PERIODS.PERIOD_DATE) AS dteYear, DATEPART(mm,
PERIODS.PERIOD_DATE) AS dteMonth, SUM(FCST.FCST_QTY) AS FCST
FROM FCST INNER JOIN
PERIODS ON FCST.FCST_PERIODID = PERIODS.PERIOD_ID
GROUP BY DATEPART(yyyy, PERIODS.PERIOD_DATE), DATEPART(mm,
PERIODS.PERIOD_DATE)) Q1(year, Month, FCST)
INNER JOIN
(SELECT DATEPART(yyyy, MOMA200F.DAMOV) AS dteYear, DATEPART(mm,
MOMA200F.DAMOV) AS Expr1, SUM(MOMA200F.QTMOV * TAGACGV1.CFTAG)
AS Exits
FROM MOMA200F INNER JOIN
TAGACGV1 ON SUBSTRING(MOMA200F.CDPAR, 8, 1) =
TAGACGV1.CDTAG
GROUP BY DATEPART(yyyy, MOMA200F.DAMOV), DATEPART(mm, MOMA200F.DAMOV),
MOMA200F.ATM01, MOMA200F.CDMAG, MOMA200F.CAUAZ2
HAVING (DATEPART(yyyy, MOMA200F.DAMOV) = DATEPART(yyyy, DATEADD(yyyy, -
1, GETDATE())) AND DATEPART(mm, MOMA200F.DAMOV) = 12 OR
DATEPART(yyyy, MOMA200F.DAMOV) = 2005 AND DATEPART(mm,
MOMA200F.DAMOV) BETWEEN 1 AND 11) AND (MOMA200F.ATM01 = '') AND
(MOMA200F.CDMAG = 'pf') AND (MOMA200F.CAUAZ2 = '-')
ORDER BY DATEPART(yyyy, MOMA200F.DAMOV), DATEPART(mm, MOMA200F.DAMOV))
Q2(Year, Month, Exits)
ON Q1.Year = Q2.Year AND Q1.Month = Q2.Month
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"ericvdb" <eric.vdb@.gmail.com> wrote in message
news:O7A9qmNFFHA.2176@.TK2MSFTNGP15.phx.gbl...
> How would i join these 2 queries:
> QUERY 1:
> SELECT DATEPART(yyyy, PERIODS.PERIOD_DATE) AS dteYear, DATEPART(mm,
> PERIODS.PERIOD_DATE) AS dteMonth, SUM(FCST.FCST_QTY) AS FCST
> FROM FCST INNER JOIN
> PERIODS ON FCST.FCST_PERIODID = PERIODS.PERIOD_ID
> GROUP BY DATEPART(yyyy, PERIODS.PERIOD_DATE), DATEPART(mm,
> PERIODS.PERIOD_DATE)
> returns:
> year | month | FCST
> QUERY 2:
> SELECT DATEPART(yyyy, MOMA200F.DAMOV) AS dteYear, DATEPART(mm,
> MOMA200F.DAMOV) AS Expr1, SUM(MOMA200F.QTMOV * TAGACGV1.CFTAG)
> AS Exits
> FROM MOMA200F INNER JOIN
> TAGACGV1 ON SUBSTRING(MOMA200F.CDPAR, 8, 1) =
> TAGACGV1.CDTAG
> GROUP BY DATEPART(yyyy, MOMA200F.DAMOV), DATEPART(mm, MOMA200F.DAMOV),
> MOMA200F.ATM01, MOMA200F.CDMAG, MOMA200F.CAUAZ2
> HAVING (DATEPART(yyyy, MOMA200F.DAMOV) = DATEPART(yyyy,
> DATEADD(yyyy, -
> 1, GETDATE())) AND DATEPART(mm, MOMA200F.DAMOV) = 12 OR
> DATEPART(yyyy, MOMA200F.DAMOV) = 2005 AND
> DATEPART(mm,
> MOMA200F.DAMOV) BETWEEN 1 AND 11) AND (MOMA200F.ATM01 = '') AND
> (MOMA200F.CDMAG = 'pf') AND (MOMA200F.CAUAZ2 = '-')
> ORDER BY DATEPART(yyyy, MOMA200F.DAMOV), DATEPART(mm, MOMA200F.DAMOV)
> returns:
> year | month | exits
>
> What i should see is:
> year | month | exits | FCST
>
> Many thanks in advance
> PS: If you can point me to some good resources for "complicated" queries,
> i
> would be thankfull
> Eric
>|||Hi,
Thanks for the fast reply.
I tried your sollution but it throws an error:
Server: Msg 1033, Level 15, State 1, Line 23
The ORDER BY clause is invalid in views, inline functions, derived tables,
and subqueries, unless TOP is also specified.
Thanks
"Roji. P. Thomas" <thomasroji@.gmail.com> wrote in message
news:eWKELqNFFHA.1936@.TK2MSFTNGP14.phx.gbl...
> SELECT Q1.Year, Q1.Month, Q2.Exits, Q1.FCST
> FROM
> (SELECT DATEPART(yyyy, PERIODS.PERIOD_DATE) AS dteYear, DATEPART(mm,
> PERIODS.PERIOD_DATE) AS dteMonth, SUM(FCST.FCST_QTY) AS FCST
> FROM FCST INNER JOIN
> PERIODS ON FCST.FCST_PERIODID = PERIODS.PERIOD_ID
> GROUP BY DATEPART(yyyy, PERIODS.PERIOD_DATE), DATEPART(mm,
> PERIODS.PERIOD_DATE)) Q1(year, Month, FCST)
> INNER JOIN
> (SELECT DATEPART(yyyy, MOMA200F.DAMOV) AS dteYear, DATEPART(mm,
> MOMA200F.DAMOV) AS Expr1, SUM(MOMA200F.QTMOV * TAGACGV1.CFTAG)
> AS Exits
> FROM MOMA200F INNER JOIN
> TAGACGV1 ON SUBSTRING(MOMA200F.CDPAR, 8, 1) =
> TAGACGV1.CDTAG
> GROUP BY DATEPART(yyyy, MOMA200F.DAMOV), DATEPART(mm, MOMA200F.DAMOV),
> MOMA200F.ATM01, MOMA200F.CDMAG, MOMA200F.CAUAZ2
> HAVING (DATEPART(yyyy, MOMA200F.DAMOV) = DATEPART(yyyy,
DATEADD(yyyy, -
> 1, GETDATE())) AND DATEPART(mm, MOMA200F.DAMOV) = 12 OR
> DATEPART(yyyy, MOMA200F.DAMOV) = 2005 AND
DATEPART(mm,
> MOMA200F.DAMOV) BETWEEN 1 AND 11) AND (MOMA200F.ATM01 = '') AND
> (MOMA200F.CDMAG = 'pf') AND (MOMA200F.CAUAZ2 = '-')
> ORDER BY DATEPART(yyyy, MOMA200F.DAMOV), DATEPART(mm, MOMA200F.DAMOV))
> Q2(Year, Month, Exits)
> ON Q1.Year = Q2.Year AND Q1.Month = Q2.Month
>
>
> --
> Roji. P. Thomas
> Net Asset Management
> https://www.netassetmanagement.com
>
> "ericvdb" <eric.vdb@.gmail.com> wrote in message
> news:O7A9qmNFFHA.2176@.TK2MSFTNGP15.phx.gbl...
queries,
>|||Right. Remove the order by clause from the derived table and try again
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"ericvdb" <eric.vdb@.gmail.com> wrote in message
news:uN2BnIOFFHA.3728@.TK2MSFTNGP14.phx.gbl...
> Hi,
> Thanks for the fast reply.
> I tried your sollution but it throws an error:
> Server: Msg 1033, Level 15, State 1, Line 23
> The ORDER BY clause is invalid in views, inline functions, derived tables,
> and subqueries, unless TOP is also specified.
> Thanks
> "Roji. P. Thomas" <thomasroji@.gmail.com> wrote in message
> news:eWKELqNFFHA.1936@.TK2MSFTNGP14.phx.gbl...
> DATEADD(yyyy, -
> DATEPART(mm,
> queries,
>

Friday, February 24, 2012

JOIN queries ?

Hi,

Can you give the equivalent query for the following Oracle query :

SELECT col2, col3 FROM submission,
lkup so
WHERE so.txt (+) = TRIM(col2)
AND so.pid (+) = 'SO'
AND so.cde (+) = 'RW'

In the above query a constant value is used in JOIN, so I am not aware of how to use the JOIN clause for the above constant value ( 'SO' , 'RW' )

Upto my knowledge about JOIN query equivalents :

Oracle query :-

select *from test1 a, test2 b where a.sno(+) = b.col

Equivalent SQL Server query :-

select *from test1 a JOIN test2 b on a.sno = b.col

Hope the above is correct. Here my doubt is, if in the above query, b.col is replaced by a constant value say 'abcd' , then what will be the table name used in right of the JOIN clause. Or any other equivalent for this is available ?

Eg :-

Oracle query :-

select *from test1 a, test2 b where a.sno(+) = 'abcd'

Thanks,
SamHi Sam,

the syntax used by Oracle with "(+)" in the WHERE-clause is equivalent to an OUTER JOIN in SQL Server and DB2.

The example you mentioned should be a LEFT OUTER JOIN, but I'm always a bit confused when using OUTER JOINS in Oracle, so I may be wrong :-)

You can lookup Oracle syntax at http://tahiti.oracle.com
Carsten|||Hi, OK, it is RIGHT JOIN, but my requirement is different, Please go through my posting once again. Thanks for your information.|||SELECT col2, col3 FROM submission,
lkup so
WHERE so.txt (+) = TRIM(col2)
AND so.pid (+) = 'SO'
AND so.cde (+) = 'RW'

SELECT col2, col3 FROM submission
Right Outer Join lkup so
On so.txt = LTRIM(RTRIM(col2)) AND so.pid = 'SO' AND so.cde = 'RW'

JOIN queries

join queries

ok...here's the thing, i've been trying to join two queries in one but i'm not getting any results, maybe i'm doing it wrong i don't know
here are the queries:
select tbEntidadesMoradas.codEntidade, Entidades.Entidade, Moradas.Morada, Moradas.CodPostal, Moradas.Localidade FROM Entidades INNER JOIN tbEntidadesMoradas ON Entidades.CodEntidade = tbEntidadesMoradas.codEntidade INNER JOIN Moradas ON tbEntidadesMoradas.codMorada = Moradas.codMorada
the other one
select tbEntidadesAltContact.codEntidade, tbEntidadesAltContact.codAltContc, ContactosAlternativos.Nome, ContactosAlternativos.Morada, ContactosAlternativos.Contacto FROM tbEntidadesAltContact INNER JOIN ContactosAlternativos ON tbEntidadesAltContact.codAltContc = ContactosAlternativos.codConAlt
what i want is to join them or re-arrange them for the outup of the two of them to be only one..
thanks in advance
Can you not use a union? Now do the queries relate to each other.

Monday, February 20, 2012

Join on subquery with distinct?

Can someone give me some reasonable explanation why these two queries are performing dramatically different:

First (slow):

select
tableA.column1,
tableA.column2
from
tableA join (select distinct col1,col2,col3 from myView) vw
on vw.col1=tableA.col1 and vw.col2=tableA.col2 and vw.col3=tableA.col3

Second (fast):

create table #tempTable (col1 varchar(200),col2 int,col3 varchar(200))

insert into #tempTable
select distinct col1,col2,col3 from myView

select

tableA.column1,

tableA.column2

from

tableA join #tempTable vw

on vw.col1=tableA.col1 and vw.col2=tableA.col2 and vw.col3=tableA.col3

SQL Server 2005 Express
Win2K3 Server

Regards,
Marko Simic

Marko:

I can't really mock this without knowing how the view is structured. It is not possible to mock a query plan that involves a view without knowing the components of the view. Can you give details of the makeup of the view?

Dave

|||

Can you post the plan for your queries, such as:

set showplan_text on
--set statistics profile on
go

--your query--

go
--set statistics profile off
set showplan_text off

The showplan one gives you estimated, the profile one gives you actual plan. That will be very helpful in this.

|||

When I executed queries (for generating estimated and actual plans), suddenly problematic one executed much faster.
Most probably, problem was about server's hardware performance, like insufficient RAM, at the moment of query execution.
My assumption is based on the fact that problematic query is using subquery, which by many views, is using more memory then query with temp table.

Anyway, I am sending you a link to XLS file with all statistics (of query with subquery) you asked, in case that something else could cause strange behavior and can be seen from this.

http://139.142.50.130/test/statistics.xls

Thank you for your efforts

|||

Without both to compare it is really impossible to say too confidently, but there are a couple of really large looking table scans (Clustered Index Scan = ordered table scan) followed by a hash match join. This kind of operation can be greatly affected by hardware performance/contention, as the hash match executes faster with more ram.

Comparing the plans might shed some light on it, but if it runs adequately now... :)

|||The optimiser will expand out queries like the first one. Just one of those annoying things about it. You could improve the speed by putting changing it to "select top (999999) distinct col1, col2, col3 from myView", because that will force the server to materialise the table first. Any number will do, so long as it's larger than the number of rows you expect to get out (otherwise you'll lose rows).

Which effectively is the same as populating a temporary table. :)

Rob|||

First of all, thank you all for your replies.
Rob, will you please tell me, what did you mean by "materialise table".
Did you mean that database would move view from memory to hard drive?
And, if that is true, would db engine make that table within tempdb space (as temp tables are) or in original database space?

|||When I say 'materialise', I just mean that the engine will create the table in memory and then join the other tables to it. It won't move it from memory to the hard drive - it will stay in RAM.

Rob|||

ok.Thanks. Then it works as I expected.

|||Ah, great. Glad to help. Can you mark it as an answer please? I know MS are keen for this to happen on all threads.|||With pleasure :) Done|||:) Cheers

Monday, March 26, 2012

joning result of dynamic queries

Joins Vs Where clause - Performance Query

Wednesday, March 21, 2012

Joining two SQL queries with different conditions

joining two queries ?

Joining two large queries as derived tables

Joining tables in different databases

Monday, March 19, 2012

Joining table UDFs in queries

joining results of 2 queries

Monday, March 12, 2012

Joining Count Queries?

Joining Count Queries?

Joining 2 queries in one table

joining 2 queries

Friday, February 24, 2012

JOIN queries ?

JOIN queries

join queries

Monday, February 20, 2012

Join on subquery with distinct?

join or subselect

Blog Archive

About Me