If I select this item and enter some value into the field, should this field
automatically be formatted as a hyperlink or do I need to manually format
the field.
Thanks,You need the fully formed URL.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Tim Kelley" <tkelley@.company.com> wrote in message
news:eA16ZPo3GHA.3344@.TK2MSFTNGP05.phx.gbl...
> If I select this item and enter some value into the field, should this
> field automatically be formatted as a hyperlink or do I need to manually
> format the field.
> Thanks,
>
Showing posts with label select. Show all posts
Showing posts with label select. Show all posts
Friday, March 30, 2012
Wednesday, March 28, 2012
Jump to report expression
Hi,
Was hoping to select a jump to report based upon the value of field. Is
this not possible ?
iif(Fields!ACATEGORY.Value="Receipts","Shop 2006 Inventory Analysis Detail
Receipts Report","Shop 2006 Inventory Analysis Detail Consumption Report" )
keep getting .....
The specified server url does not link to the report server for this report
manager or is not in the correct format.Just needed a "=" before the iif
"steve" wrote:
> Hi,
> Was hoping to select a jump to report based upon the value of field. Is
> this not possible ?
> iif(Fields!ACATEGORY.Value="Receipts","Shop 2006 Inventory Analysis Detail
> Receipts Report","Shop 2006 Inventory Analysis Detail Consumption Report" )
>
> keep getting .....
> The specified server url does not link to the report server for this report
> manager or is not in the correct format.
Was hoping to select a jump to report based upon the value of field. Is
this not possible ?
iif(Fields!ACATEGORY.Value="Receipts","Shop 2006 Inventory Analysis Detail
Receipts Report","Shop 2006 Inventory Analysis Detail Consumption Report" )
keep getting .....
The specified server url does not link to the report server for this report
manager or is not in the correct format.Just needed a "=" before the iif
"steve" wrote:
> Hi,
> Was hoping to select a jump to report based upon the value of field. Is
> this not possible ?
> iif(Fields!ACATEGORY.Value="Receipts","Shop 2006 Inventory Analysis Detail
> Receipts Report","Shop 2006 Inventory Analysis Detail Consumption Report" )
>
> keep getting .....
> The specified server url does not link to the report server for this report
> manager or is not in the correct format.
Friday, March 23, 2012
JOINS and WHERE clause
hello,
I was curious and wanted to know if there was a difference between the
following statements.
SELECT FirstName, LastName
FROM Person
INNER JOIN Address on Person.PersonID = Address.PersonID AND
Address.AddressType = 'Home'
and:
SELECT FirstName, LastName
FROM Person
INNER JOIN Address on Person.PersonID = Address.PersonID
WHERE
Address.AddressType = 'Home'
thank you for your help
BenDid you compare the execution plans? Did you trace the execution of each
query in SQL Profiler? (Use dbcc freeproccache between each take.)
ML
http://milambda.blogspot.com/|||You will get the same results, as for the performance:
Is Person a table or a view? If a view, how complex is it?|||The answer is yes, and no. Yes, these two queries are mathematically
equivalent. However, they are asking two different questions. Not as bad
as what color is the sky and what color is a Marge Simpson's hair, but
different questions.
The FROM clause builds a working set of data based on the JOINS. The WHERE
clause critieria is applied to each row from that set individually and rows
removed accordingly.
In your two queries:
* The first is building a set of rows by joining person and address,
looking for rows where the personIds match and address.addressType is home.
* The second is building a set of rows by joining person and address,
looking for rows where the personIds match. Then it is going back over this
set and tossing out any where Address.AddressType is not home.
Why this is significant is not so much for queries like this, in completely
INNER JOIN queries the results will always be the same. (someone correct me
if there is a case where it can be different) However, when OUTER JOINs are
involved, things get much more interesting. Say this was and OUTER JOIN:
SELECT FirstName, LastName
FROM Person
LEFT OUTER JOIN Address on Person.PersonID = Address.PersonID
WHERE Address.AddressType = 'Home'
So when the set is assembled, all rows in the Person table will be included
in the output, and only address rows where there is a match.
Well, when the WHERE clause is applied to the results of the FROM clause,
person rows that did not have addresses would fall out of the set, since the
addressType of the null row is null, not home.
So the point is that you need to query the table like you mean it, if you
are shaping the set of data with your criteria, it goes in the FROM clause,
if it is eliminating rows from the set, put it in the WHERE clause.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
<dreamer@.gmail.com> wrote in message
news:1138136404.172038.122140@.g47g2000cwa.googlegroups.com...
> hello,
> I was curious and wanted to know if there was a difference between the
> following statements.
> SELECT FirstName, LastName
> FROM Person
> INNER JOIN Address on Person.PersonID = Address.PersonID AND
> Address.AddressType = 'Home'
> and:
> SELECT FirstName, LastName
> FROM Person
> INNER JOIN Address on Person.PersonID = Address.PersonID
> WHERE
> Address.AddressType = 'Home'
> thank you for your help
> Ben
>
I was curious and wanted to know if there was a difference between the
following statements.
SELECT FirstName, LastName
FROM Person
INNER JOIN Address on Person.PersonID = Address.PersonID AND
Address.AddressType = 'Home'
and:
SELECT FirstName, LastName
FROM Person
INNER JOIN Address on Person.PersonID = Address.PersonID
WHERE
Address.AddressType = 'Home'
thank you for your help
BenDid you compare the execution plans? Did you trace the execution of each
query in SQL Profiler? (Use dbcc freeproccache between each take.)
ML
http://milambda.blogspot.com/|||You will get the same results, as for the performance:
Is Person a table or a view? If a view, how complex is it?|||The answer is yes, and no. Yes, these two queries are mathematically
equivalent. However, they are asking two different questions. Not as bad
as what color is the sky and what color is a Marge Simpson's hair, but
different questions.
The FROM clause builds a working set of data based on the JOINS. The WHERE
clause critieria is applied to each row from that set individually and rows
removed accordingly.
In your two queries:
* The first is building a set of rows by joining person and address,
looking for rows where the personIds match and address.addressType is home.
* The second is building a set of rows by joining person and address,
looking for rows where the personIds match. Then it is going back over this
set and tossing out any where Address.AddressType is not home.
Why this is significant is not so much for queries like this, in completely
INNER JOIN queries the results will always be the same. (someone correct me
if there is a case where it can be different) However, when OUTER JOINs are
involved, things get much more interesting. Say this was and OUTER JOIN:
SELECT FirstName, LastName
FROM Person
LEFT OUTER JOIN Address on Person.PersonID = Address.PersonID
WHERE Address.AddressType = 'Home'
So when the set is assembled, all rows in the Person table will be included
in the output, and only address rows where there is a match.
Well, when the WHERE clause is applied to the results of the FROM clause,
person rows that did not have addresses would fall out of the set, since the
addressType of the null row is null, not home.
So the point is that you need to query the table like you mean it, if you
are shaping the set of data with your criteria, it goes in the FROM clause,
if it is eliminating rows from the set, put it in the WHERE clause.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
<dreamer@.gmail.com> wrote in message
news:1138136404.172038.122140@.g47g2000cwa.googlegroups.com...
> hello,
> I was curious and wanted to know if there was a difference between the
> following statements.
> SELECT FirstName, LastName
> FROM Person
> INNER JOIN Address on Person.PersonID = Address.PersonID AND
> Address.AddressType = 'Home'
> and:
> SELECT FirstName, LastName
> FROM Person
> INNER JOIN Address on Person.PersonID = Address.PersonID
> WHERE
> Address.AddressType = 'Home'
> thank you for your help
> Ben
>
Joins
I want to convert the following statement:
SELECT E.Region, O.Instance_name, C.Disease_cat, C.Constraint_ref, M.Sex,
C.Sex_code, M.Age_start, M.Age_end, M.Age_range, SUM(M.Person_count) AS
Person_count, C.Conversion_factor, (SUM(M.Person_count))*C.Conversion_factor
FROM
Geo_definitions_normalised O LEFT OUTER JOIN
ICD_specifications_for_disease_ICD_9_ICD_10_calc_spec_SPLIT C,
Geo_mortality_count_SPLIT M,
Experimental_parameters E
WHERE C.ICD_code = M.ICD
AND M.Cause BETWEEN C.ICD_start AND C.ICD_end_adjusted
AND M.Output_Area = O.Output_Area
AND O.Geo_category_class = E.Region
AND C.Sex_code_conversion = M.Sex
AND E.Region = @.region
AND E.Unique_identifier = @.Unique_identifier
GROUP BY /*O.Geo_category_class, */E.Region, O.Instance_name,
C.Disease_cat, M.Sex, M.Age_start, M.Age_end, M.Age_range, C.Sex_code,
C.Constraint_ref, C.Conversion_factor, M.Person_count
ORDER BY /* O.Geo_category_class, */E.Region, O.Instance_name,
C.Disease_cat, M.Sex, M.Age_start, M.Age_end, M.Age_range, C.Sex_code,
C.Constraint_ref, C.Conversion_factor, M.Person_count
into something like this:
SELECT /*O.Geo_category_class,*/ E.Region, O.Instance_name, C.Disease_cat,
C.Constraint_ref, M.Sex, C.Sex_code,
M.Age_start, M.Age_end, M.Age_range, SUM(M.Person_count) AS
Person_count, C.Conversion_factor, (SUM(M.Person_count))*C.Conversion_factor
FROM
Geo_definitions_normalised O LEFT OUTER JOIN
ICD_specifications_for_disease_ICD_9_ICD_10_calc_spec_SPLIT C
JOIN Geo_mortality_count_SPLIT M
ON C.ICD_code = M.ICD AND M.Cause BETWEEN C.ICD_start AND
C.ICD_end_adjusted AND M.Output_Area = O.Output_Area AND
C.Sex_code_conversion = M.Sex
JOIN Experimental_parameters E
ON O.Geo_category_class = E.Region
WHERE E.Region = @.region AND E.Unique_identifier = @.Unique_identifier
GROUP BY /*O.Geo_category_class, */E.Region, O.Instance_name,
C.Disease_cat, M.Sex,
M.Age_start, M.Age_end, M.Age_range, C.Sex_code, C.Constraint_ref,
C.Conversion_factor, M.Person_count
ORDER BY E.Region, O.Instance_name, C.Disease_cat, M.Sex,
M.Age_start, M.Age_end, M.Age_range, C.Sex_code, C.Constraint_ref,
C.Conversion_factor, M.Person_count
But this does not work. I am trying to produce the same join as the first,
except that I want the outer join with the Geo_definitions_normalised table
--
Message posted via http://www.sqlmonster.comSorry, please replace the first statement with:
SELECT E.Region, O.Instance_name, C.Disease_cat, C.Constraint_ref, M.Sex,
C.Sex_code,
M.Age_start, M.Age_end, M.Age_range, SUM(M.Person_count) AS
Person_count, C.Conversion_factor, (SUM(M.Person_count))*C.Conversion_factor
FROM ICD_specifications_for_disease_ICD_9_ICD_10_calc_spec_SPLIT C,
Geo_mortality_count_SPLIT M,
Geo_definitions_normalised O,
Experimental_parameters E
WHERE C.ICD_code = M.ICD
AND M.Cause BETWEEN C.ICD_start AND C.ICD_end_adjusted
AND M.Output_Area = O.Output_Area
AND O.Geo_category_class = E.Region
AND C.Sex_code_conversion = M.Sex
AND E.Region = @.region
AND E.Unique_identifier = @.Unique_identifier
GROUP BY /*O.Geo_category_class, */E.Region, O.Instance_name,
C.Disease_cat, M.Sex,
M.Age_start, M.Age_end, M.Age_range, C.Sex_code, C.Constraint_ref,
C.Conversion_factor, M.Person_count
ORDER BY /* O.Geo_category_class, */E.Region, O.Instance_name,
C.Disease_cat, M.Sex,
M.Age_start, M.Age_end, M.Age_range, C.Sex_code, C.Constraint_ref,
C.Conversion_factor, M.Person_count
--
Message posted via http://www.sqlmonster.com
SELECT E.Region, O.Instance_name, C.Disease_cat, C.Constraint_ref, M.Sex,
C.Sex_code, M.Age_start, M.Age_end, M.Age_range, SUM(M.Person_count) AS
Person_count, C.Conversion_factor, (SUM(M.Person_count))*C.Conversion_factor
FROM
Geo_definitions_normalised O LEFT OUTER JOIN
ICD_specifications_for_disease_ICD_9_ICD_10_calc_spec_SPLIT C,
Geo_mortality_count_SPLIT M,
Experimental_parameters E
WHERE C.ICD_code = M.ICD
AND M.Cause BETWEEN C.ICD_start AND C.ICD_end_adjusted
AND M.Output_Area = O.Output_Area
AND O.Geo_category_class = E.Region
AND C.Sex_code_conversion = M.Sex
AND E.Region = @.region
AND E.Unique_identifier = @.Unique_identifier
GROUP BY /*O.Geo_category_class, */E.Region, O.Instance_name,
C.Disease_cat, M.Sex, M.Age_start, M.Age_end, M.Age_range, C.Sex_code,
C.Constraint_ref, C.Conversion_factor, M.Person_count
ORDER BY /* O.Geo_category_class, */E.Region, O.Instance_name,
C.Disease_cat, M.Sex, M.Age_start, M.Age_end, M.Age_range, C.Sex_code,
C.Constraint_ref, C.Conversion_factor, M.Person_count
into something like this:
SELECT /*O.Geo_category_class,*/ E.Region, O.Instance_name, C.Disease_cat,
C.Constraint_ref, M.Sex, C.Sex_code,
M.Age_start, M.Age_end, M.Age_range, SUM(M.Person_count) AS
Person_count, C.Conversion_factor, (SUM(M.Person_count))*C.Conversion_factor
FROM
Geo_definitions_normalised O LEFT OUTER JOIN
ICD_specifications_for_disease_ICD_9_ICD_10_calc_spec_SPLIT C
JOIN Geo_mortality_count_SPLIT M
ON C.ICD_code = M.ICD AND M.Cause BETWEEN C.ICD_start AND
C.ICD_end_adjusted AND M.Output_Area = O.Output_Area AND
C.Sex_code_conversion = M.Sex
JOIN Experimental_parameters E
ON O.Geo_category_class = E.Region
WHERE E.Region = @.region AND E.Unique_identifier = @.Unique_identifier
GROUP BY /*O.Geo_category_class, */E.Region, O.Instance_name,
C.Disease_cat, M.Sex,
M.Age_start, M.Age_end, M.Age_range, C.Sex_code, C.Constraint_ref,
C.Conversion_factor, M.Person_count
ORDER BY E.Region, O.Instance_name, C.Disease_cat, M.Sex,
M.Age_start, M.Age_end, M.Age_range, C.Sex_code, C.Constraint_ref,
C.Conversion_factor, M.Person_count
But this does not work. I am trying to produce the same join as the first,
except that I want the outer join with the Geo_definitions_normalised table
--
Message posted via http://www.sqlmonster.comSorry, please replace the first statement with:
SELECT E.Region, O.Instance_name, C.Disease_cat, C.Constraint_ref, M.Sex,
C.Sex_code,
M.Age_start, M.Age_end, M.Age_range, SUM(M.Person_count) AS
Person_count, C.Conversion_factor, (SUM(M.Person_count))*C.Conversion_factor
FROM ICD_specifications_for_disease_ICD_9_ICD_10_calc_spec_SPLIT C,
Geo_mortality_count_SPLIT M,
Geo_definitions_normalised O,
Experimental_parameters E
WHERE C.ICD_code = M.ICD
AND M.Cause BETWEEN C.ICD_start AND C.ICD_end_adjusted
AND M.Output_Area = O.Output_Area
AND O.Geo_category_class = E.Region
AND C.Sex_code_conversion = M.Sex
AND E.Region = @.region
AND E.Unique_identifier = @.Unique_identifier
GROUP BY /*O.Geo_category_class, */E.Region, O.Instance_name,
C.Disease_cat, M.Sex,
M.Age_start, M.Age_end, M.Age_range, C.Sex_code, C.Constraint_ref,
C.Conversion_factor, M.Person_count
ORDER BY /* O.Geo_category_class, */E.Region, O.Instance_name,
C.Disease_cat, M.Sex,
M.Age_start, M.Age_end, M.Age_range, C.Sex_code, C.Constraint_ref,
C.Conversion_factor, M.Person_count
--
Message posted via http://www.sqlmonster.com
Joins
I have two tables, one called users and the other resources.
I'd like to select several fields from users and one from Resource (called ResourceID) based on certain criteria.
My sql statement looks like this:
gstrSQL = "Select UserNum, ResourceID, UserFName, UserLName, Password, SecurityLevel, email, Telephone, ext, UserID from Users U, Resource R where "
If UserNum <> "" Then
gstrSQL = gstrSQL & "UserID='" & UserNum & "' And u.userfname = r.fname and u.userlname = r.lname"
Else
gstrSQL = gstrSQL & "UserID='" & UserID & "' And u.userfname = r.fname and u.userlname = r.lname"
End If
Before adding "ResourceID" to the selection criteria, this statement was working just fine. It's probably a silly mistake but I can't figure it out.
Thanks.You didn't let us know the DB you're working on, nor the error itself ...
In Oracle you can NOT name your table "Resource" as it is reserved word.
However, if your DB allows it, try to rewrite the query in a way to put table aliases in front of table columns, i.e.
SELECT u.UserNum, r.ResourceID, u.UserFName, ...
FROM Users U, Resource R
WHERE ...
Perhaps your tables contain the same column names and, if you don't explicitly say the table you are selecting its value from, the column becomes ambigously defined.
I'd like to select several fields from users and one from Resource (called ResourceID) based on certain criteria.
My sql statement looks like this:
gstrSQL = "Select UserNum, ResourceID, UserFName, UserLName, Password, SecurityLevel, email, Telephone, ext, UserID from Users U, Resource R where "
If UserNum <> "" Then
gstrSQL = gstrSQL & "UserID='" & UserNum & "' And u.userfname = r.fname and u.userlname = r.lname"
Else
gstrSQL = gstrSQL & "UserID='" & UserID & "' And u.userfname = r.fname and u.userlname = r.lname"
End If
Before adding "ResourceID" to the selection criteria, this statement was working just fine. It's probably a silly mistake but I can't figure it out.
Thanks.You didn't let us know the DB you're working on, nor the error itself ...
In Oracle you can NOT name your table "Resource" as it is reserved word.
However, if your DB allows it, try to rewrite the query in a way to put table aliases in front of table columns, i.e.
SELECT u.UserNum, r.ResourceID, u.UserFName, ...
FROM Users U, Resource R
WHERE ...
Perhaps your tables contain the same column names and, if you don't explicitly say the table you are selecting its value from, the column becomes ambigously defined.
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
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 to Derived Tables
How can I join to a derived table?
My derived table would look like...
(SELECT file_id,
MAX(DATETIME) AS MAXDATE
FROM DataTrac.dbo.NOTES
WHERE group_id = 'SRV'
GROUP BY file_id) MAXDTTM
and I would need to LEFT OUTER JOIN from a table called GEN matching by
file_id. A LEFT OUTER JOIN because I may not have SRV Notes for a GEN row.
Let me know...
Thanks!Try,
select ...
from gen as a left join (select ... from DataTrac.dbo.NOTES) as b on
a.file_id = b.file_id
AMB
"wnfisba" wrote:
> How can I join to a derived table?
> My derived table would look like...
> (SELECT file_id,
> MAX(DATETIME) AS MAXDATE
> FROM DataTrac.dbo.NOTES
> WHERE group_id = 'SRV'
> GROUP BY file_id) MAXDTTM
> and I would need to LEFT OUTER JOIN from a table called GEN matching by
> file_id. A LEFT OUTER JOIN because I may not have SRV Notes for a GEN row.
> Let me know...
> Thanks!|||Correction,
select ...
from
gen as a
left join
(select ... from DataTrac.dbo.NOTES ...) as b -- here goes the derived
table
on a.file_id = b.file_id
AMB
"Alejandro Mesa" wrote:
> Try,
> select ...
> from gen as a left join (select ... from DataTrac.dbo.NOTES) as b on
> a.file_id = b.file_id
>
> AMB
> "wnfisba" wrote:
>|||Many thanks Alejandro!!!
Worked like a charm!!!
"Alejandro Mesa" wrote:
> Correction,
> select ...
> from
> gen as a
> left join
> (select ... from DataTrac.dbo.NOTES ...) as b -- here goes the derived
> table
> on a.file_id = b.file_id
>
> AMB
> "Alejandro Mesa" wrote:
>
My derived table would look like...
(SELECT file_id,
MAX(DATETIME) AS MAXDATE
FROM DataTrac.dbo.NOTES
WHERE group_id = 'SRV'
GROUP BY file_id) MAXDTTM
and I would need to LEFT OUTER JOIN from a table called GEN matching by
file_id. A LEFT OUTER JOIN because I may not have SRV Notes for a GEN row.
Let me know...
Thanks!Try,
select ...
from gen as a left join (select ... from DataTrac.dbo.NOTES) as b on
a.file_id = b.file_id
AMB
"wnfisba" wrote:
> How can I join to a derived table?
> My derived table would look like...
> (SELECT file_id,
> MAX(DATETIME) AS MAXDATE
> FROM DataTrac.dbo.NOTES
> WHERE group_id = 'SRV'
> GROUP BY file_id) MAXDTTM
> and I would need to LEFT OUTER JOIN from a table called GEN matching by
> file_id. A LEFT OUTER JOIN because I may not have SRV Notes for a GEN row.
> Let me know...
> Thanks!|||Correction,
select ...
from
gen as a
left join
(select ... from DataTrac.dbo.NOTES ...) as b -- here goes the derived
table
on a.file_id = b.file_id
AMB
"Alejandro Mesa" wrote:
> Try,
> select ...
> from gen as a left join (select ... from DataTrac.dbo.NOTES) as b on
> a.file_id = b.file_id
>
> AMB
> "wnfisba" wrote:
>|||Many thanks Alejandro!!!
Worked like a charm!!!
"Alejandro Mesa" wrote:
> Correction,
> select ...
> from
> gen as a
> left join
> (select ... from DataTrac.dbo.NOTES ...) as b -- here goes the derived
> table
> on a.file_id = b.file_id
>
> AMB
> "Alejandro Mesa" wrote:
>
joining text in SP in SQL SERVER
here is my code:
declare @.strSQL nvarchar(4000);
set @.strSQL = 'select emailAddress from tblMailingList where mailingID > 0 ' ;
--send Mail
if len(@.sendMail)>0
@.strSQL+=' and sendMail =@.sendMail'
but I got this error:
Incorrect syntax near '@.strSQL'.
how can I joining the @.strSQL if the "if statement" is true?
thanks!
24sharon:
@.strSQL+=' and sendMail =@.sendMail'
Try this instead:
set @.strSQL = @.strSQL + ' and sendMail = ''' + @.sendMail + ''''
Hope this will help.
|||at last line u have some wrong code. replace last line using -->
Set @.strSQL = @.strSQL + ' and sendMail = ''' + @.sendMail + ''''
Joining tables in different databases
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.
ThanksTry '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|||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
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|||"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
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...
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
>>
>>|||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
>>
>>|||Do you mean that actually specifing servername slows down the query? Could
you explain why?
MC
"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 ;)....
MC
"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 Rows in a SubQuery
I have the following code in a stored procedure
SELECT CatID, ParentId, CategoryName, (select count(*)
from members WHERE DirectoryCat = DirectoryCats.CatID and
InFreeDirectory=1 and ApproveDirectory=1) pagecount
FROM DirectoryCats
where active=1
order by CategoryName
it's output is similar to this:
CatID | ParentId | CategoryName | pagecount
6 1 Cat1 0
4 Null Cat2 3
I would like to make a new column (say Newtext) adn Return something like th
is
CatID | ParentId | CategoryName | pagecount | NewText
6 1 Cat1 0 Cat1 (0)
4 Null Cat2 3 Cat2 (3)
How can i join the data to have this result?
I would hope that i could do something like CategoryName + "(" + pagecount +
")" in some sort of sql statement.
Thanks for any input
Lots of ways, here's one
SELECT CatID, ParentId, CategoryName,pagecount ,
CategoryName + '(' + cast(pagecount as varchar(10))+ ')' as
NewText
FROM
(
SELECT CatID, ParentId, CategoryName, (select count(*)
from members WHERE DirectoryCat = DirectoryCats.CatID and
InFreeDirectory=1 and ApproveDirectory=1) pagecount
FROM DirectoryCats
where active=1
) X
order by CategoryName|||One approach is to use a derived table, embedding the existing query
in the FROM clause of an outer query:
SELECT *,
NewText =
CategoryName + '(' + convert(varchar(8),pagecount) + ')'
FROM (<query as you stated it> ) as X
Roy Harvey
Beacon Falls, CT
On Thu, 27 Apr 2006 13:37:02 -0700, Fabuloussites
<Fabuloussites@.discussions.microsoft.com> wrote:
>I have the following code in a stored procedure
>SELECT CatID, ParentId, CategoryName, (select count(*)
>from members WHERE DirectoryCat = DirectoryCats.CatID and
>InFreeDirectory=1 and ApproveDirectory=1) pagecount
>FROM DirectoryCats
>where active=1
>order by CategoryName
>
>it's output is similar to this:
>CatID | ParentId | CategoryName | pagecount
>6 1 Cat1 0
>4 Null Cat2 3
>I would like to make a new column (say Newtext) adn Return something like t
his
>CatID | ParentId | CategoryName | pagecount | NewText
>6 1 Cat1 0 Cat1 (0)
>4 Null Cat2 3 Cat2 (3)
>How can i join the data to have this result?
>I would hope that i could do something like CategoryName + "(" + pagecount
+
>")" in some sort of sql statement.
>Thanks for any input|||thanks for the fast and helpful response.
"markc600@.hotmail.com" wrote:
>
> Lots of ways, here's one
>
> SELECT CatID, ParentId, CategoryName,pagecount ,
> CategoryName + '(' + cast(pagecount as varchar(10))+ ')' as
> NewText
> FROM
> (
> SELECT CatID, ParentId, CategoryName, (select count(*)
> from members WHERE DirectoryCat = DirectoryCats.CatID and
> InFreeDirectory=1 and ApproveDirectory=1) pagecount
> FROM DirectoryCats
> where active=1
> ) X
> order by CategoryName
>|||thanks for the fast and helpful response.
"Roy Harvey" wrote:
> One approach is to use a derived table, embedding the existing query
> in the FROM clause of an outer query:
> SELECT *,
> NewText =
> CategoryName + '(' + convert(varchar(8),pagecount) + ')'
> FROM (<query as you stated it> ) as X
> Roy Harvey
> Beacon Falls, CT
>
> On Thu, 27 Apr 2006 13:37:02 -0700, Fabuloussites
> <Fabuloussites@.discussions.microsoft.com> wrote:
>
>
SELECT CatID, ParentId, CategoryName, (select count(*)
from members WHERE DirectoryCat = DirectoryCats.CatID and
InFreeDirectory=1 and ApproveDirectory=1) pagecount
FROM DirectoryCats
where active=1
order by CategoryName
it's output is similar to this:
CatID | ParentId | CategoryName | pagecount
6 1 Cat1 0
4 Null Cat2 3
I would like to make a new column (say Newtext) adn Return something like th
is
CatID | ParentId | CategoryName | pagecount | NewText
6 1 Cat1 0 Cat1 (0)
4 Null Cat2 3 Cat2 (3)
How can i join the data to have this result?
I would hope that i could do something like CategoryName + "(" + pagecount +
")" in some sort of sql statement.
Thanks for any input
Lots of ways, here's one
SELECT CatID, ParentId, CategoryName,pagecount ,
CategoryName + '(' + cast(pagecount as varchar(10))+ ')' as
NewText
FROM
(
SELECT CatID, ParentId, CategoryName, (select count(*)
from members WHERE DirectoryCat = DirectoryCats.CatID and
InFreeDirectory=1 and ApproveDirectory=1) pagecount
FROM DirectoryCats
where active=1
) X
order by CategoryName|||One approach is to use a derived table, embedding the existing query
in the FROM clause of an outer query:
SELECT *,
NewText =
CategoryName + '(' + convert(varchar(8),pagecount) + ')'
FROM (<query as you stated it> ) as X
Roy Harvey
Beacon Falls, CT
On Thu, 27 Apr 2006 13:37:02 -0700, Fabuloussites
<Fabuloussites@.discussions.microsoft.com> wrote:
>I have the following code in a stored procedure
>SELECT CatID, ParentId, CategoryName, (select count(*)
>from members WHERE DirectoryCat = DirectoryCats.CatID and
>InFreeDirectory=1 and ApproveDirectory=1) pagecount
>FROM DirectoryCats
>where active=1
>order by CategoryName
>
>it's output is similar to this:
>CatID | ParentId | CategoryName | pagecount
>6 1 Cat1 0
>4 Null Cat2 3
>I would like to make a new column (say Newtext) adn Return something like t
his
>CatID | ParentId | CategoryName | pagecount | NewText
>6 1 Cat1 0 Cat1 (0)
>4 Null Cat2 3 Cat2 (3)
>How can i join the data to have this result?
>I would hope that i could do something like CategoryName + "(" + pagecount
+
>")" in some sort of sql statement.
>Thanks for any input|||thanks for the fast and helpful response.
"markc600@.hotmail.com" wrote:
>
> Lots of ways, here's one
>
> SELECT CatID, ParentId, CategoryName,pagecount ,
> CategoryName + '(' + cast(pagecount as varchar(10))+ ')' as
> NewText
> FROM
> (
> SELECT CatID, ParentId, CategoryName, (select count(*)
> from members WHERE DirectoryCat = DirectoryCats.CatID and
> InFreeDirectory=1 and ApproveDirectory=1) pagecount
> FROM DirectoryCats
> where active=1
> ) X
> order by CategoryName
>|||thanks for the fast and helpful response.
"Roy Harvey" wrote:
> One approach is to use a derived table, embedding the existing query
> in the FROM clause of an outer query:
> SELECT *,
> NewText =
> CategoryName + '(' + convert(varchar(8),pagecount) + ')'
> FROM (<query as you stated it> ) as X
> Roy Harvey
> Beacon Falls, CT
>
> On Thu, 27 Apr 2006 13:37:02 -0700, Fabuloussites
> <Fabuloussites@.discussions.microsoft.com> wrote:
>
>
joining results of 2 queries
Does anyone know how I can 'join' the results of
one SQL query to the bottom of another?
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
Joining on partial matches
Hi all,
I have 2 files containing Id numbers and surnames (these files
essentially contain the same data) I want to select distinct() and
join on id number to return a recordset containing every individual
listed in both the files HOWEVER, in some cases an incomplete ID
number has been collected into one of the 2 files -is there a way to
join on partial matches not just identical records in the same way as
you can select where LIKE '%blah, blah%'??
Is hash joining an option i should investigate?
I have 2 files containing Id numbers and surnames (these files
essentially contain the same data) I want to select distinct() and
join on id number to return a recordset containing every individual
listed in both the files HOWEVER, in some cases an incomplete ID
number has been collected into one of the 2 files -is there a way to
join on partial matches not just identical records in the same way as
you can select where LIKE '%blah, blah%'??
Is hash joining an option i should investigate?
TIA
MarkA join expression can include any predicates, including LIKE:
...
ON A.colx LIKE B.colx+'%'
You may also find the functions CHARINDEX and PATINDEX useful (see BOL).
--
David Portas
----
Please reply only to the newsgroup
--
"Mark" <mark@.compuchem.co.za> wrote in message
news:632892db.0310290405.4a0e06bd@.posting.google.c om...
> Hi all,
> I have 2 files containing Id numbers and surnames (these files
> essentially contain the same data) I want to select distinct() and
> join on id number to return a recordset containing every individual
> listed in both the files HOWEVER, in some cases an incomplete ID
> number has been collected into one of the 2 files -is there a way to
> join on partial matches not just identical records in the same way as
> you can select where LIKE '%blah, blah%'??
> Is hash joining an option i should investigate?
> TIA
> Mark|||A join expression can include any predicates, including LIKE:
...
ON A.colx LIKE B.colx+'%'
You may also find the functions CHARINDEX and PATINDEX useful (see BOL).
--
David Portas
----
Please reply only to the newsgroup
--
"Mark" <mark@.compuchem.co.za> wrote in message
news:632892db.0310290405.4a0e06bd@.posting.google.c om...
> Hi all,
> I have 2 files containing Id numbers and surnames (these files
> essentially contain the same data) I want to select distinct() and
> join on id number to return a recordset containing every individual
> listed in both the files HOWEVER, in some cases an incomplete ID
> number has been collected into one of the 2 files -is there a way to
> join on partial matches not just identical records in the same way as
> you can select where LIKE '%blah, blah%'??
> Is hash joining an option i should investigate?
> TIA
> Mark|||>> I have 2 files containing Id numbers and surnames (these files
essentially contain the same data) <<
Since these are files and not tables, as you just said, why not use a
file difference utility? Now if you mean that you have tables, then
we can give you a query. Please post DDL, so that people do not have
to guess what the keys, constraints, Declarative Referential
Integrity, datatypes, etc. in your schema are. Sample data is also a
good idea, along with clear specifications -- what does "partial
match" mean?? In Full SQL-92, that is a reserved word with a definite
meaning.|||>> I have 2 files containing Id numbers and surnames (these files
essentially contain the same data) <<
Since these are files and not tables, as you just said, why not use a
file difference utility? Now if you mean that you have tables, then
we can give you a query. Please post DDL, so that people do not have
to guess what the keys, constraints, Declarative Referential
Integrity, datatypes, etc. in your schema are. Sample data is also a
good idea, along with clear specifications -- what does "partial
match" mean?? In Full SQL-92, that is a reserved word with a definite
meaning.
Joining multiple tables
I need your help on this. I am not getting the results i wanted in this script.
select distinct s.firstname, s.lastname, c[coursename],(midterm+final+assignment) as "total mark"
from student as s join studentrecord as sr
on (s.student#=sr.student#)
join course as c on (sr.course#=c.course#)
join courseenrollment as ce on (s.student#=ce.student#)
where ce.[status]=1
Now the results should only pick up 10 students who are enrolled (status=1) but the program is bypassing the where clause and returns all the results even though students were enrolled or not. Let me kknow your thoughts.
Join logic is applied in order, so if you rewrite your query like this it should provide the results expected.
1st, join the student and enrollment table on the student# using only the records with a status = 1 - this will return only students
2nd, join the student record table by the student# - this joins to the resultset of the 1st join
3rd, join the course table to the resultset of the previous joins by course# to the studentrecord table AND to the enrollment table.
Code Snippet
SELECT DISTINCT s.firstname, s.lastname, c.coursename,(midterm+final+assignment) as [total mark]
FROM student s INNER JOIN courseenrollment ce on ce.[student#] = s.[student#] AND ce.status = 1
INNER JOIN studentrecord sr on s.[student#] = sr.[student#]
INNER JOIN course c on c.[course#] = sr.[course#] AND c.[course#] = ce.course#]
Try this, and repost if it doesn't work or the assumption I made about the courseenrollment table having a course# field is incorrect.
|||This is unexpected but logically true. If you add ce.status to the SELECT portion, I'm sure you'll see that the status = 1 for all records
Adamus
Joining multiple tables
I need your help on this. I am not getting the results i wanted in this script.
select distinct s.firstname, s.lastname, c[coursename],(midterm+final+assignment) as "total mark"
from student as s join studentrecord as sr
on (s.student#=sr.student#)
join course as c on (sr.course#=c.course#)
join courseenrollment as ce on (s.student#=ce.student#)
where ce.[status]=1
Now the results should only pick up 10 students who are enrolled (status=1) but the program is bypassing the where clause and returns all the results even though students were enrolled or not. Let me kknow your thoughts.
Join logic is applied in order, so if you rewrite your query like this it should provide the results expected.
1st, join the student and enrollment table on the student# using only the records with a status = 1 - this will return only students
2nd, join the student record table by the student# - this joins to the resultset of the 1st join
3rd, join the course table to the resultset of the previous joins by course# to the studentrecord table AND to the enrollment table.
Code Snippet
SELECT DISTINCT s.firstname, s.lastname, c.coursename,(midterm+final+assignment) as [total mark]
FROM student s INNER JOIN courseenrollment ce on ce.[student#] = s.[student#] AND ce.status = 1
INNER JOIN studentrecord sr on s.[student#] = sr.[student#]
INNER JOIN course c on c.[course#] = sr.[course#] AND c.[course#] = ce.course#]
Try this, and repost if it doesn't work or the assumption I made about the courseenrollment table having a course# field is incorrect.
|||This is unexpected but logically true. If you add ce.status to the SELECT portion, I'm sure you'll see that the status = 1 for all records
Adamus
Joining Many Tables in Loop
Hello,
I am very new to TSQL however I do have a little programming in my background. I am trying to run a query to select several datafields from about 45 tables within a database. I have written the below code to loop through all of the tables and extract the information however I need to find some way to join the results using PCTimeStamp as the key. Any help anyone can give me is greatly appreciated!
while (@.Table_Number<18240) /*Loop to read data from all Turbine all Tables*/
BEGIN
if (@.Table_Number=17990 or @.Table_Number=18234) /*ERROR AVOIDANCE for Non-Sequential Turbine #'s*/
BEGIN
set @.Table_Number=@.Table_Number+1;
END;
set @.Table_Insert = cast (@.Table_Number as NVARCHAR); /*Converts Table Number to Format usable in String below*/
set @.SQLString = N'SELECT Id, Status_Avg_NacellePos, Status_Avg_BladeAPos, Status_Avg_BladeBPos, Status_Avg_BladecPos, Grid_Avg_ActivePower, Status_Avg_Windspeed1, Status_Avg_Windspeed2, Status_Avg_WindDirection1, Status_Avg_WindDirection1 FROM T_'+@.Table_Insert+'_10MINDATA where PCTimeStamp=(select max(PCTimeStamp) from T_'+@.Table_Insert+'_10MINDATA)';
set @.Table_Number = @.Table_Number+1; /*Increment @.Table_Number to control Loop*/
if (@.Table_Number <18006 or @.Table_Number>18235) /*ERROR AVOIDANCE for Non-Sequential Turbine #'s*/
BEGIN
EXEC(@.SQLString); /*Execute String to retrieve data from table*/
END;
END;
I tried to format that in this text box as best I could but I apologize for the result. In summary Table_Number is used as the key to loop through the sequentially numbered tables (with some numbers missing) and extract the required fields from the most recent entry (PCTimeStamp=max).
Other notes: the code as written outputs all of the required data with no error messages but the data column titles are displayed i.e:
id windspeed direction time
1 12 000 1200
id windspeed direction time
2 12 010 1200
...................
looking for
id windspeed direction time
1 12 000 1200
2 12 010 1200
Thanks again
Consider using a temporary table. You can create the table outside the loop and change your dynamic query into an Insert...Select. You will return the combined results by selecting from the temporary table.|||Hello,
You do not need to use a temp table for this.
You can use the UNION operator as such:
SELECT TOP 1 * FROM Table1 order by PCTimeStamp DESC
UNION ALL
SELECT TOP 1 * FROM Table2 order by PCTimeStamp DESC
UNION ALL
SELECT TOP 1 * FROM Table3 order by PCTimeStamp DESCCheers,
Rob
|||Thanks to everyone for the help, I looked into the temporary table and here is my revised code:
declare @.SQLString NVARCHAR(500); /*Executable String to Run Query on Multiple Table in Looping Feature*/
declare @.WindLogics_Data TABLE(Status_Avg_NacellePos Int, Status_Avg_BladeAPos SMALLMONEY, Status_Avg_BladeBPos SMALLMONEY, Status_Avg_BladeCPos SMALLMONEY, Grid_Avg_ActivePower SMALLMONEY, Status_Avg_Windspeed1 SMALLMONEY,Status_Avg_Windspeed2 SMALLMONEY, Status_Avg_WindDirection1 SMALLMONEY, Status_Avg_WindDirection2 SMALLMONEY);
Set @.Table_Number = 17965; /*Initialize Value of Turbine Table to 17965*/
/*Practice Insert*/
Insert INTO @.WindLogics_Data (Status_Avg_NacellePos, Status_Avg_BladeAPos, Status_Avg_BladeBPos, Status_Avg_BladeCPos, Grid_Avg_ActivePower, Status_Avg_Windspeed1, Status_Avg_Windspeed2, Status_Avg_WindDirection1, Status_Avg_WindDirection2) SELECT Status_Avg_NacellePos, Status_Avg_BladeAPos,Status_Avg_BladeBPos,Status_Avg_BladecPos,Grid_Avg_ActivePower,Status_Avg_Windspeed1,Status_Avg_Windspeed2, Status_Avg_WindDirection1,Status_Avg_WindDirection2 FROM T_17965_10MINDATA where PCTimeStamp=(select max(PCTimeStamp) from T_17965_10MINDATA);
select * from @.WindLogics_Data
while (@.Table_Number<18240) /*Loop to read data from all Turbine all Tables*/
BEGIN
if (@.Table_Number=17990 or @.Table_Number=18234) /*ERROR AVOIDANCE for Non-Sequential Turbine #'s*/
BEGIN
set @.Table_Number=@.Table_Number+1;
END;
set @.Table_Insert = cast (@.Table_Number as NVARCHAR); /*Converts Table Number to Format usable in String below*/
set @.SQLString = N'Insert INTO @.WindLogics_Data (Status_Avg_NacellePos, Status_Avg_BladeAPos, Status_Avg_BladeBPos, Status_Avg_BladeCPos, Grid_Avg_ActivePower, Status_Avg_Windspeed1, Status_Avg_Windspeed2, Status_Avg_WindDirection1, Status_Avg_WindDirection2) SELECT Status_Avg_NacellePos, Status_Avg_BladeAPos,Status_Avg_BladeBPos,Status_Avg_BladecPos,Grid_Avg_ActivePower,Status_Avg_Windspeed1,Status_Avg_Windspeed2, Status_Avg_WindDirection1,Status_Avg_WindDirection2 FROM T_17965_10MINDATA where PCTimeStamp=(select max(PCTimeStamp) FROM T_'+@.Table_Insert+'_10MINDATA where PCTimeStamp=(select max(PCTimeStamp) from T_'+@.Table_Insert+'_10MINDATA)';
set @.Table_Number = @.Table_Number+1; /*Increment @.Table_Number to control Loop*/
if (@.Table_Number <18006 or @.Table_Number>18235) /*ERROR AVOIDANCE for Non-Sequential Turbine #'s*/
BEGIN
EXEC(@.SQLString); /*Execute String to retrieve data from table*/
END;
END;
**************************************
Practice insert works perfectly so I know structure of @.WindLogics_Data is correct however SQL will not recognize the database name in the SQLString. Says "Must declare @.Wind_Logics", is there a stipulation against table variable name in a string running through the exec command?
Thank you so much for the help, table var was a lifesaver!
|||I don't see the variable that you mention in the code you posted. Yes, there are restrictions as to what you can do with dynamic SQL. You cannot access variables declared outside the scope of the dynamic SQL for example. Also, using a table variable may not be the best thing to do depending on the number of rows in each table. Performance will worsen as you populate more rows into table variables. It is more suited for smaller results in the range of 1000s of rows or so. So using a temporary table will provide better performance.
In any case, it seems like you should use table partitioning in SQL Server 2005 or partitioned views in SQL Server 2000/70. With either of the approach, you don't need to write such complicated code. You can achieve what you are doing by writing simple SELECT statements. See below links for more information on partitioning. And BOL also has topics that cover this feature.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sql2k5partition.asp
http://www.microsoft.com/technet/prodtechnol/sql/2005/spdw.mspx
http://www.microsoft.com/technet/prodtechnol/sql/2000/reskit/part5/c1861.mspx
|||
Umachandar,
Thank you for replying I should have provided more background, using a temporary table seems to get me closer to working and for informational purposes the results will only be 10columns with 45 rows so in this case efficiency is not a HUGE concern (you probably disagree!) here is the code sample with highlight where problem is. Now I get error message as
Server: Msg 170, Level 15, State 1, Line 1 Line 1: Incorrect syntax near 'select'. (repeated over and over for loop)
Again my test insert into the temporary table is operational so once the above error is resolved I think I am good to go.
I looked into the partitioning and to be honest it would require me a while to absorb the structure I think, I was hoping there would be a quicker fix for the above error.
****************************************************************************************************************************************************
use Windman /*Defines database to be used for commands*/
declare @.Table_Number INT; /*Number of Table in Numerical Format for Looping*/
declare @.Table_Insert NVARCHAR(5); /*Number of Table in Char for String Insertion*/
declare @.SQLString NVARCHAR(500); /*Executable String to Run Query on Multiple Table in Looping Feature*/
create table #WindLogics_Data (Status_Avg_NacellePos Int, Status_Avg_BladeAPos SMALLMONEY, Status_Avg_BladeBPos SMALLMONEY, Status_Avg_BladeCPos SMALLMONEY, Grid_Avg_ActivePower SMALLMONEY, Status_Avg_Windspeed1 SMALLMONEY,Status_Avg_Windspeed2 SMALLMONEY, Status_Avg_WindDirection1 SMALLMONEY, Status_Avg_WindDirection2 SMALLMONEY);
Set @.Table_Number = 17965; /*Initialize Value of Turbine Table to 17965*/
Insert INTO #WindLogics_Data (Status_Avg_NacellePos, Status_Avg_BladeAPos, Status_Avg_BladeBPos, Status_Avg_BladeCPos, Grid_Avg_ActivePower, Status_Avg_Windspeed1, Status_Avg_Windspeed2, Status_Avg_WindDirection1, Status_Avg_WindDirection2) SELECT Status_Avg_NacellePos, Status_Avg_BladeAPos,Status_Avg_BladeBPos,Status_Avg_BladecPos,Grid_Avg_ActivePower,Status_Avg_Windspeed1,Status_Avg_Windspeed2, Status_Avg_WindDirection1,Status_Avg_WindDirection2 FROM T_17965_10MINDATA where PCTimeStamp=(select max(PCTimeStamp) from T_17965_10MINDATA);
select * from #WindLogics_Data
while (@.Table_Number<18240) /*Loop to read data from all Turbine all Tables*/
BEGIN
if (@.Table_Number=17990 or @.Table_Number=18234) /*ERROR AVOIDANCE for Non-Sequential Turbine #'s*/
BEGIN
set @.Table_Number=@.Table_Number+1;
END;
set @.Table_Insert = cast (@.Table_Number as NVARCHAR); /*Converts Table Number to Format usable in String below*/
set @.SQLString = N'INSERT INTO #WindLogics_Data (Status_Avg_NacellePos, Status_Avg_BladeAPos, Status_Avg_BladeBPos, Status_Avg_BladeCPos, Grid_Avg_ActivePower, Status_Avg_Windspeed1, Status_Avg_Windspeed2, Status_Avg_WindDirection1, Status_Avg_WindDirection2) SELECT Status_Avg_NacellePos, Status_Avg_BladeAPos,Status_Avg_BladeBPos,Status_Avg_BladecPos,Grid_Avg_ActivePower,Status_Avg_Windspeed1,Status_Avg_Windspeed2, Status_Avg_WindDirection1,Status_Avg_WindDirection2 FROM T_'+@.Table_Insert+'10MINDATA where PCTimeStamp=(select max(PCTimeStamp) from T_'+@.Table_Insert+'_10MINDATA)';
if (@.Table_Number <18006 or @.Table_Number>18235) /*ERROR AVOIDANCE for Non-Sequential Turbine #'s*/
BEGIN
EXEC(@.SQLString); /*Execute String to retrieve data from table*/
END;
set @.Table_Number = @.Table_Number+1; /*Increment @.Table_Number to control Loop*/
END;
Drop Table #WindLogics_Data
/*END OF QUERY******************************************************************************/
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
>
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
>
Friday, March 9, 2012
Join.
select e.dname, count(e.sid) as total_num from enroll e
group by e.dname
select e.dname, count(e.sid) as "major_enroll"
from enroll e, major m
where e.sid = m.sid and e.dname = m.dname
group by e.dname
----------------------
Hi, buddys,
How can I join above two results of queries by dname if I don't have any create temporary table permission?
Thx,
Neilselect e.dname, count(e.sid) as total_num
from enroll e
group by e.dname
UNION
select e.dname, count(e.sid) as "major_enroll"
from enroll e, major m
where e.sid = m.sid
and e.dname = m.dname
group by e.dname;
It works with Oracle ...|||Originally posted by Littlefoot
select e.dname, count(e.sid) as total_num
from enroll e
group by e.dname
UNION
select e.dname, count(e.sid) as "major_enroll"
from enroll e, major m
where e.sid = m.sid
and e.dname = m.dname
group by e.dname;
It works with Oracle ...|||I don't need to union set, I mean.
I want total_num and major_enroll are two columns in the same table.
thx.
neil
Originally posted by Littlefoot
select e.dname, count(e.sid) as total_num
from enroll e
group by e.dname
UNION
select e.dname, count(e.sid) as "major_enroll"
from enroll e, major m
where e.sid = m.sid
and e.dname = m.dname
group by e.dname;
It works with Oracle ...
group by e.dname
select e.dname, count(e.sid) as "major_enroll"
from enroll e, major m
where e.sid = m.sid and e.dname = m.dname
group by e.dname
----------------------
Hi, buddys,
How can I join above two results of queries by dname if I don't have any create temporary table permission?
Thx,
Neilselect e.dname, count(e.sid) as total_num
from enroll e
group by e.dname
UNION
select e.dname, count(e.sid) as "major_enroll"
from enroll e, major m
where e.sid = m.sid
and e.dname = m.dname
group by e.dname;
It works with Oracle ...|||Originally posted by Littlefoot
select e.dname, count(e.sid) as total_num
from enroll e
group by e.dname
UNION
select e.dname, count(e.sid) as "major_enroll"
from enroll e, major m
where e.sid = m.sid
and e.dname = m.dname
group by e.dname;
It works with Oracle ...|||I don't need to union set, I mean.
I want total_num and major_enroll are two columns in the same table.
thx.
neil
Originally posted by Littlefoot
select e.dname, count(e.sid) as total_num
from enroll e
group by e.dname
UNION
select e.dname, count(e.sid) as "major_enroll"
from enroll e, major m
where e.sid = m.sid
and e.dname = m.dname
group by e.dname;
It works with Oracle ...
join vs where
1.
select ...
from T1 inner join T2 on T1.c=T2.c
where T2.c1 = '...'
2.
select ...
from T1 inner join T2 on T1.c=T2.c and T2.c1 = '...'
Any reason not use the approach 2?nick wrote:
> 1.
> select ...
> from T1 inner join T2 on T1.c=T2.c
> where T2.c1 = '...'
> 2.
> select ...
> from T1 inner join T2 on T1.c=T2.c and T2.c1 = '...'
> Any reason not use the approach 2?
>
Compare the execution plans and query stats for each and see which looks
better.|||nick,
No differences when applying an inner join, except when grouping and using
"group by all". This behaviour is caused by the way SQL Server process the
query. Another different meaning when using outer join operators.
Example:
use northwind
go
select
a.orderid,
count(*)
from
orders as a
inner join
[order details] as b
on a.orderid = b.orderid
where
a.orderdate between '19980101' and '19981231'
group by all
a.orderid
select
a.orderid,
count(*)
from
orders as a
inner join
[order details] as b
on a.orderid = b.orderid
and a.orderdate between '19980101' and '19981231'
group by all
a.orderid
select
a.*
from
orders as a
left join
[order details] as b
on a.orderid = b.orderid
where
b.productid = 10
select
a.*
from
orders as a
left join
[order details] as b
on a.orderid = b.orderid
and b.productid = 10
go
AMB
"nick" wrote:
> 1.
> select ...
> from T1 inner join T2 on T1.c=T2.c
> where T2.c1 = '...'
> 2.
> select ...
> from T1 inner join T2 on T1.c=T2.c and T2.c1 = '...'
> Any reason not use the approach 2?
>|||For an equi-join, I don't think it matters; however, a LEFT JOIN will
give you very different results (it would return all the rows from T1,
and only the rows from T2 that it could match and have valid data in
T2.c1).
Does that help?
Stu
nick wrote:
> 1.
> select ...
> from T1 inner join T2 on T1.c=T2.c
> where T2.c1 = '...'
> 2.
> select ...
> from T1 inner join T2 on T1.c=T2.c and T2.c1 = '...'
> Any reason not use the approach 2?|||nick wrote:
> 1.
> select ...
> from T1 inner join T2 on T1.c=T2.c
> where T2.c1 = '...'
> 2.
> select ...
> from T1 inner join T2 on T1.c=T2.c and T2.c1 = '...'
> Any reason not use the approach 2?
From a technical point of view, they specify the same resultset.
However, I consider it a good practice to use the primary key/foreign
key columns in the JOIN clause, and all filtering conditions in the
WHERE clause.
This makes the query easier to read and understand, because it is in
line with the mental model of the query processing: the tables are
joined first. Only then are irrelevant rows filtered out. Also, it is
easier to understand, because the joins are usually pretty standard,
which means the real "work" is done in the WHERE clause.
So, asuming that the relation between table T1 and T2 is based on column
T1.c and T2.c, then I would never use syntax 2. I would change the
question: is there any reason not to use approach 1?
Gert-Jan
select ...
from T1 inner join T2 on T1.c=T2.c
where T2.c1 = '...'
2.
select ...
from T1 inner join T2 on T1.c=T2.c and T2.c1 = '...'
Any reason not use the approach 2?nick wrote:
> 1.
> select ...
> from T1 inner join T2 on T1.c=T2.c
> where T2.c1 = '...'
> 2.
> select ...
> from T1 inner join T2 on T1.c=T2.c and T2.c1 = '...'
> Any reason not use the approach 2?
>
Compare the execution plans and query stats for each and see which looks
better.|||nick,
No differences when applying an inner join, except when grouping and using
"group by all". This behaviour is caused by the way SQL Server process the
query. Another different meaning when using outer join operators.
Example:
use northwind
go
select
a.orderid,
count(*)
from
orders as a
inner join
[order details] as b
on a.orderid = b.orderid
where
a.orderdate between '19980101' and '19981231'
group by all
a.orderid
select
a.orderid,
count(*)
from
orders as a
inner join
[order details] as b
on a.orderid = b.orderid
and a.orderdate between '19980101' and '19981231'
group by all
a.orderid
select
a.*
from
orders as a
left join
[order details] as b
on a.orderid = b.orderid
where
b.productid = 10
select
a.*
from
orders as a
left join
[order details] as b
on a.orderid = b.orderid
and b.productid = 10
go
AMB
"nick" wrote:
> 1.
> select ...
> from T1 inner join T2 on T1.c=T2.c
> where T2.c1 = '...'
> 2.
> select ...
> from T1 inner join T2 on T1.c=T2.c and T2.c1 = '...'
> Any reason not use the approach 2?
>|||For an equi-join, I don't think it matters; however, a LEFT JOIN will
give you very different results (it would return all the rows from T1,
and only the rows from T2 that it could match and have valid data in
T2.c1).
Does that help?
Stu
nick wrote:
> 1.
> select ...
> from T1 inner join T2 on T1.c=T2.c
> where T2.c1 = '...'
> 2.
> select ...
> from T1 inner join T2 on T1.c=T2.c and T2.c1 = '...'
> Any reason not use the approach 2?|||nick wrote:
> 1.
> select ...
> from T1 inner join T2 on T1.c=T2.c
> where T2.c1 = '...'
> 2.
> select ...
> from T1 inner join T2 on T1.c=T2.c and T2.c1 = '...'
> Any reason not use the approach 2?
From a technical point of view, they specify the same resultset.
However, I consider it a good practice to use the primary key/foreign
key columns in the JOIN clause, and all filtering conditions in the
WHERE clause.
This makes the query easier to read and understand, because it is in
line with the mental model of the query processing: the tables are
joined first. Only then are irrelevant rows filtered out. Also, it is
easier to understand, because the joins are usually pretty standard,
which means the real "work" is done in the WHERE clause.
So, asuming that the relation between table T1 and T2 is based on column
T1.c and T2.c, then I would never use syntax 2. I would change the
question: is there any reason not to use approach 1?
Gert-Jan
join two different databases where they r in different servers
i am fresher i want to know write one query where i want to select two tables from two different databases that are in different servers.
eg : it is first server details
sqlserver1 -servername
EmployeDet -Database Name
Emp_Mas - table name
colums - empno, empname,.....
it is second server details
sqlserver2 -servername
IMS- Database Name
IMSTable - table name
Colums- empno,locid,extno......
by combining above two databases i want details please help me in this issue it is urgent
You can do that using linked servers. Books online has quite a bit of
information about creating and using a linked server.
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Naga vinay kumar .K" <anonymous@.discussions.microsoft.com> wrote in message
news:8F753D93-EB6A-4436-977D-436D92EE99B8@.microsoft.com...
> i am fresher i want to know write one query where i want to select two
tables from two different databases that are in different servers.
> eg : it is first server details
> sqlserver1 -servername
> EmployeDet -Database Name
> Emp_Mas - table name
> colums - empno, empname,.....
> it is second server details
> sqlserver2 -servername
> IMS- Database Name
> IMSTable - table name
> Colums- empno,locid,extno......
> by combining above two databases i want details please help me in this
issue it is urgent
>
>
eg : it is first server details
sqlserver1 -servername
EmployeDet -Database Name
Emp_Mas - table name
colums - empno, empname,.....
it is second server details
sqlserver2 -servername
IMS- Database Name
IMSTable - table name
Colums- empno,locid,extno......
by combining above two databases i want details please help me in this issue it is urgent
You can do that using linked servers. Books online has quite a bit of
information about creating and using a linked server.
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Naga vinay kumar .K" <anonymous@.discussions.microsoft.com> wrote in message
news:8F753D93-EB6A-4436-977D-436D92EE99B8@.microsoft.com...
> i am fresher i want to know write one query where i want to select two
tables from two different databases that are in different servers.
> eg : it is first server details
> sqlserver1 -servername
> EmployeDet -Database Name
> Emp_Mas - table name
> colums - empno, empname,.....
> it is second server details
> sqlserver2 -servername
> IMS- Database Name
> IMSTable - table name
> Colums- empno,locid,extno......
> by combining above two databases i want details please help me in this
issue it is urgent
>
>
join two different databases where they r in different servers
i am fresher i want to know write one query where i want to select two table
s from two different databases that are in different servers.
eg : it is first server details
sqlserver1 -servername
EmployeDet -Database Name
Emp_Mas - table name
colums - empno, empname,.....
it is second server details
sqlserver2 -servername
IMS- Database Name
IMSTable - table name
Colums- empno,locid,extno......
by combining above two databases i want details please help me in this issue
it is urgentYou can do that using linked servers. Books online has quite a bit of
information about creating and using a linked server.
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Naga vinay kumar .K" <anonymous@.discussions.microsoft.com> wrote in message
news:8F753D93-EB6A-4436-977D-436D92EE99B8@.microsoft.com...
> i am fresher i want to know write one query where i want to select two
tables from two different databases that are in different servers.
> eg : it is first server details
> sqlserver1 -servername
> EmployeDet -Database Name
> Emp_Mas - table name
> colums - empno, empname,.....
> it is second server details
> sqlserver2 -servername
> IMS- Database Name
> IMSTable - table name
> Colums- empno,locid,extno......
> by combining above two databases i want details please help me in this
issue it is urgent
>
>
s from two different databases that are in different servers.
eg : it is first server details
sqlserver1 -servername
EmployeDet -Database Name
Emp_Mas - table name
colums - empno, empname,.....
it is second server details
sqlserver2 -servername
IMS- Database Name
IMSTable - table name
Colums- empno,locid,extno......
by combining above two databases i want details please help me in this issue
it is urgentYou can do that using linked servers. Books online has quite a bit of
information about creating and using a linked server.
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Naga vinay kumar .K" <anonymous@.discussions.microsoft.com> wrote in message
news:8F753D93-EB6A-4436-977D-436D92EE99B8@.microsoft.com...
> i am fresher i want to know write one query where i want to select two
tables from two different databases that are in different servers.
> eg : it is first server details
> sqlserver1 -servername
> EmployeDet -Database Name
> Emp_Mas - table name
> colums - empno, empname,.....
> it is second server details
> sqlserver2 -servername
> IMS- Database Name
> IMSTable - table name
> Colums- empno,locid,extno......
> by combining above two databases i want details please help me in this
issue it is urgent
>
>
Subscribe to:
Posts (Atom)