join or subselect: table

Showing posts with label table. Show all posts

Friday, March 30, 2012

Jump to URL in ReportViewer- Underline not displaying

Hello,
I have a text box in a table that has its text-decoration property set to
Underline. The textbox has its value set from a column in a dataset. I have
also defined the following as its Jump to URL expression:
="http://" & Parameters!WebServerName.Value &
"/Policy/Reports/AdvisorAccountPolicyStatus.aspx?asofdate=" &
Parameters!dAsOfDate.Value & "&AdvId=" & Fields!ENCRYPTED_USER_ID.Value
The second I define the Jump to URL expression and deploy the report, the
Underline stops being displayed. If I remove the Jump to URL expression, the
Underline is displayed.
I am using the .Net ReportViewer control. I know that the HTML is being
rendered properly for the text-decoration, but the reportviewer control
refuses to display it. If I view the report using the SQL Reporting Services
management tool, the underline is displayed.
Does anyone have a solution?
Thanks,
Keith MisegadesIf anyone has a solution to this, it would be greatly appreciated.
Everything I have tried has failed and we really need a solution to this
issue.
Thanks,
Keith
"Keith" wrote:
> Hello,
> I have a text box in a table that has its text-decoration property set to
> Underline. The textbox has its value set from a column in a dataset. I have
> also defined the following as its Jump to URL expression:
> ="http://" & Parameters!WebServerName.Value &
> "/Policy/Reports/AdvisorAccountPolicyStatus.aspx?asofdate=" &
> Parameters!dAsOfDate.Value & "&AdvId=" & Fields!ENCRYPTED_USER_ID.Value
> The second I define the Jump to URL expression and deploy the report, the
> Underline stops being displayed. If I remove the Jump to URL expression, the
> Underline is displayed.
> I am using the .Net ReportViewer control. I know that the HTML is being
> rendered properly for the text-decoration, but the reportviewer control
> refuses to display it. If I view the report using the SQL Reporting Services
> management tool, the underline is displayed.
> Does anyone have a solution?
> Thanks,
> Keith Misegades
>sql

Wednesday, March 28, 2012

Jump to report and parameters

I have a table where I want to jump to a different report based on the value
in the table cell. The report that I am jumping to has a parameter, and the
parameter value that I need to pass is based on the table cell's value. I
know how to set this up in the report but can't figure out how filter the
dataset that has my parameter values, and I can't filter the dataset using
ReportItems.
If I were to write some pseudocode using SQL it would look something like
this:
PopupReport(Select Param From ParamTable Where ParamKey = Fields!MyTextBox.Value)
Thanks...
Regards,
SteveIf the report you are jumping to has parameters then I am confused on the
issue. When setting up the jump to report you set the parameters of the
report you are jumping to, in this case to the field. That being the case,
what is the problem? The report you are jumping to must be able to accept
the parameter and return the correct data.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Steve" <MyNoSpam@.NoSpam.org> wrote in message
news:00B417ED-8954-4CAC-A07B-48FD361A3575@.microsoft.com...
>I have a table where I want to jump to a different report based on the
>value
> in the table cell. The report that I am jumping to has a parameter, and
> the
> parameter value that I need to pass is based on the table cell's value. I
> know how to set this up in the report but can't figure out how filter the
> dataset that has my parameter values, and I can't filter the dataset using
> ReportItems.
> If I were to write some pseudocode using SQL it would look something like
> this:
> PopupReport(Select Param From ParamTable Where ParamKey => Fields!MyTextBox.Value)
> Thanks...
> Regards,
> Steve|||Hi Bruce,
The problem I have is that the parameter value that I need to pass is in a
SQL table and I need to select the correct parameter based on the cell I am
clicking on.
For instance, the textbox value I am clicking on contains a sales region,
e.g. Central, Northeast, etc, and I want to pass a list of the salereps for
that region into another report. The list of salesreps for that region is
contained in a separate sql table and the sql to find the appropriate list of
salesreps would be something like "Select RepList From Configs Where Region ='Central'", except that the region varies with the row I am clicking on in
the Reporting Services table.
I can make this work if I do something like this for the parameter
expression in the report:
iif(Fields!Region.Value="Central","1,2,3",iif(Fields!Region.Value="Northeast","4,5,6",....))
, but it's kind of ugly and hardcoded...
Hope this is clearer and thanks for your response.
Regards,
Steve
"Bruce L-C [MVP]" wrote:
> If the report you are jumping to has parameters then I am confused on the
> issue. When setting up the jump to report you set the parameters of the
> report you are jumping to, in this case to the field. That being the case,
> what is the problem? The report you are jumping to must be able to accept
> the parameter and return the correct data.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Steve" <MyNoSpam@.NoSpam.org> wrote in message
> news:00B417ED-8954-4CAC-A07B-48FD361A3575@.microsoft.com...
> >I have a table where I want to jump to a different report based on the
> >value
> > in the table cell. The report that I am jumping to has a parameter, and
> > the
> > parameter value that I need to pass is based on the table cell's value. I
> > know how to set this up in the report but can't figure out how filter the
> > dataset that has my parameter values, and I can't filter the dataset using
> > ReportItems.
> >
> > If I were to write some pseudocode using SQL it would look something like
> > this:
> >
> > PopupReport(Select Param From ParamTable Where ParamKey => > Fields!MyTextBox.Value)
> >
> > Thanks...
> >
> > Regards,
> > Steve
>
>

Jump to next record insertion.

Hi Fellows,
I am trying to update some records for simplicity my table is as
follow
ProgId PrgOccur UPC
CircD 100 235689748965
EDL 100 526396856971
CircD 100 56985636258
each record is unique means by combination of these 3 fields.
now we need to moce couple of UPCs in EDL 100 to Circ 100. if a UPC
already exists with in that CircD 100 it will simply insert that UPC
into some table and keep on inserting next records.
simply i dont want to stop update process. but during insertiong if
any duplicates found, put them separately and insert others.
Regards,
Bilalbsheikh wrote:
> Hi Fellows,
> I am trying to update some records for simplicity my table is as
> follow
> ProgId PrgOccur UPC
> CircD 100 235689748965
> EDL 100 526396856971
> CircD 100 56985636258
> each record is unique means by combination of these 3 fields.
> now we need to moce couple of UPCs in EDL 100 to Circ 100. if a UPC
> already exists with in that CircD 100 it will simply insert that UPC
> into some table and keep on inserting next records.
> simply i dont want to stop update process. but during insertiong if
> any duplicates found, put them separately and insert others.
> Regards,
> Bilal
Try this:
INSERT INTO tbl (ProgId, PrgOccur, UPC)
SELECT 'Circ', PrgOccur, UPC
FROM tbl AS t
WHERE UPC IN (1234567890,9999999999)
AND ProgId = 'EDL'
AND PrgOccur = 100
AND NOT EXISTS
(SELECT *
FROM tbl
WHERE ProgId = 'Circ'
AND PrgOccur = t.PrgOccur
AND UPC = t.UPC);
INSERT INTO some_other_table (ProgId, PrgOccur, UPC)
SELECT 'Circ', PrgOccur, UPC
FROM tbl AS t
WHERE UPC IN (1234567890,9999999999)
AND ProgId = 'EDL'
AND PrgOccur = 100
AND EXISTS
(SELECT *
FROM tbl
WHERE ProgId = 'Circ'
AND PrgOccur = t.PrgOccur
AND UPC = t.UPC);
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Jump to next record insertion.

Hi Fellows,
I am trying to update some records for simplicity my table is as
follow
ProgId PrgOccur UPC
CircD 100 235689748965
EDL 100 526396856971
CircD 100 56985636258
each record is unique means by combination of these 3 fields.
now we need to moce couple of UPCs in EDL 100 to Circ 100. if a UPC
already exists with in that CircD 100 it will simply insert that UPC
into some table and keep on inserting next records.
simply i dont want to stop update process. but during insertiong if
any duplicates found, put them separately and insert others.
Regards,
Bilalbsheikh wrote:
> Hi Fellows,
> I am trying to update some records for simplicity my table is as
> follow
> ProgId PrgOccur UPC
> CircD 100 235689748965
> EDL 100 526396856971
> CircD 100 56985636258
> each record is unique means by combination of these 3 fields.
> now we need to moce couple of UPCs in EDL 100 to Circ 100. if a UPC
> already exists with in that CircD 100 it will simply insert that UPC
> into some table and keep on inserting next records.
> simply i dont want to stop update process. but during insertiong if
> any duplicates found, put them separately and insert others.
> Regards,
> Bilal
Try this:
INSERT INTO tbl (ProgId, PrgOccur, UPC)
SELECT 'Circ', PrgOccur, UPC
FROM tbl AS t
WHERE UPC IN (1234567890,9999999999)
AND ProgId = 'EDL'
AND PrgOccur = 100
AND NOT EXISTS
(SELECT *
FROM tbl
WHERE ProgId = 'Circ'
AND PrgOccur = t.PrgOccur
AND UPC = t.UPC);
INSERT INTO some_other_table (ProgId, PrgOccur, UPC)
SELECT 'Circ', PrgOccur, UPC
FROM tbl AS t
WHERE UPC IN (1234567890,9999999999)
AND ProgId = 'EDL'
AND PrgOccur = 100
AND EXISTS
(SELECT *
FROM tbl
WHERE ProgId = 'Circ'
AND PrgOccur = t.PrgOccur
AND UPC = t.UPC);
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Jump to different table within the same report

My report has 5 tables that are populated from 5 different datasets and each
table is seperated by a page break so that they get exported to different
worksheets in excel. My question is....is it possible to create a
link/bookmark that can jump from one table to another in the same report and
if it is possible will this functionality export to excel? For
example...when clicking a cell in worksheet1 it will jump to the first value
found in worksheet2. Does that make any sense?Yes, these are called bookmarks. They work in HTML and Excel. See
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_interactive_v1_8j57.asp?frame=true.
--
Brian Welcker
Group Program Manager
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Shoeman" <Shoeman@.discussions.microsoft.com> wrote in message
news:D7529257-E5D9-461F-BD99-2E9A2603963C@.microsoft.com...
> My report has 5 tables that are populated from 5 different datasets and
> each
> table is seperated by a page break so that they get exported to different
> worksheets in excel. My question is....is it possible to create a
> link/bookmark that can jump from one table to another in the same report
> and
> if it is possible will this functionality export to excel? For
> example...when clicking a cell in worksheet1 it will jump to the first
> value
> found in worksheet2. Does that make any sense?|||Thanks Brian,
That worked!
"Brian Welcker [MSFT]" wrote:
> Yes, these are called bookmarks. They work in HTML and Excel. See
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_interactive_v1_8j57.asp?frame=true.
> --
> Brian Welcker
> Group Program Manager
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Shoeman" <Shoeman@.discussions.microsoft.com> wrote in message
> news:D7529257-E5D9-461F-BD99-2E9A2603963C@.microsoft.com...
> > My report has 5 tables that are populated from 5 different datasets and
> > each
> > table is seperated by a page break so that they get exported to different
> > worksheets in excel. My question is....is it possible to create a
> > link/bookmark that can jump from one table to another in the same report
> > and
> > if it is possible will this functionality export to excel? For
> > example...when clicking a cell in worksheet1 it will jump to the first
> > value
> > found in worksheet2. Does that make any sense?
>
>

Jump to "No" Report

Hi all,
I'm designing a report that requires a table cell to be able to jump to
another report. This cell needs to be conditional so that if a numeric
value is present, it blocks the ability to jump to another report (and takes
away the link mouse cursor), and if a non numeric value is present (in this
case, a null) it allows jumping to another report.
I can work around by simply having another report page with an error
message, but i'd rather take away the option of making an illegal jump (ie.
the value is numeric) from the end user for ease of use.
eg. Expression in Advanced Properties -> Navigation -> Jump to Report
=IIF(ISNUMERIC(Fields!BaseData.Value), 'No Jump', MyReportName)
Is anyone aware of an phrase/expression i can substitute in for 'No Jump' to
allow this functionality?
Thanks in advance.
JonTry this:
=IIF(ISNUMERIC(Fields!BaseData.Value), Nothing, MyReportName)
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jonathan Martin" <jonathan.martin@.pcservicecall.co.uk> wrote in message
news:uKcYleHNFHA.3928@.TK2MSFTNGP09.phx.gbl...
> Hi all,
> I'm designing a report that requires a table cell to be able to jump to
> another report. This cell needs to be conditional so that if a numeric
> value is present, it blocks the ability to jump to another report (and
> takes away the link mouse cursor), and if a non numeric value is present
> (in this case, a null) it allows jumping to another report.
> I can work around by simply having another report page with an error
> message, but i'd rather take away the option of making an illegal jump
> (ie. the value is numeric) from the end user for ease of use.
> eg. Expression in Advanced Properties -> Navigation -> Jump to Report
> =IIF(ISNUMERIC(Fields!BaseData.Value), 'No Jump', MyReportName)
> Is anyone aware of an phrase/expression i can substitute in for 'No Jump'
> to allow this functionality?
> Thanks in advance.
>
> Jon
>|||Thanks for getting back to me.
That worked fine with one slight alteration. The final syntax was as
follows. Note the speech marks around the valid jump report's name.
=IIF(ISNUMERIC(Fields!BaseData.Value), Nothing, "MyReportName")
Thanks!
Jon
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:uv3uiHJNFHA.2132@.TK2MSFTNGP14.phx.gbl...
> Try this:
> =IIF(ISNUMERIC(Fields!BaseData.Value), Nothing, MyReportName)
>
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> "Jonathan Martin" <jonathan.martin@.pcservicecall.co.uk> wrote in message
> news:uKcYleHNFHA.3928@.TK2MSFTNGP09.phx.gbl...
>> Hi all,
>> I'm designing a report that requires a table cell to be able to jump to
>> another report. This cell needs to be conditional so that if a numeric
>> value is present, it blocks the ability to jump to another report (and
>> takes away the link mouse cursor), and if a non numeric value is present
>> (in this case, a null) it allows jumping to another report.
>> I can work around by simply having another report page with an error
>> message, but i'd rather take away the option of making an illegal jump
>> (ie. the value is numeric) from the end user for ease of use.
>> eg. Expression in Advanced Properties -> Navigation -> Jump to Report
>> =IIF(ISNUMERIC(Fields!BaseData.Value), 'No Jump', MyReportName)
>> Is anyone aware of an phrase/expression i can substitute in for 'No Jump'
>> to allow this functionality?
>> Thanks in advance.
>>
>> Jon
>>
>

Monday, March 26, 2012

Julian Day

I have created an auxiliary calendar table with the help of
http://www.aspfaq.com/2519
I would like to add a column for the daynumber of year (e.g. 1-365), and
populate it. I am new to sql and am not finding help with this. Any help
would be greatly appreciated.select datepart(dayofyear, getdate())
-- or
select datepart(dy, getdate())
"Nice_Out" wrote:

> I have created an auxiliary calendar table with the help of
> http://www.aspfaq.com/2519
> I would like to add a column for the daynumber of year (e.g. 1-365), and
> populate it. I am new to sql and am not finding help with this. Any help
> would be greatly appreciated.|||Thanks.
"KH" wrote:
> select datepart(dayofyear, getdate())
> -- or
> select datepart(dy, getdate())
>
> "Nice_Out" wrote:
>sql

Julian Date

A table that I am querying from has the date field in julian date format "04194". I am only wanting to pull the information from this table that is => than todays date. Maybe some of you have done this before and can give a little help in finding the best way to do this?You could take today's date and subtract it from the first day of the year (DATEDIFF ( datepart , startdate , enddate ) ) to give you an int value for the day of the year (actually day of year -1 since 20040101 - 20040101 = 0). Then convert to a 3 character value and concatinate the two digit year to the front and ... tadaaa ... the julian date.|||? I am not following you?|||You can get the Julian date using:SELECT 1000 * (DatePart(yy, GetDate()) % 100) + DatePart(dy, GetDate())If you need it in character format, you can use:SELECT Replace(Str(
1000 * (DatePart(yy, GetDate()) % 100) + DatePart(dy, GetDate()), 5), ' ', '0')After you've got that, the rest should just be a simple compare.

-PatP|||Or you could go the other way so you can utilize all of SQL Server's functions

DECLARE @.julian char(5), @.gregorian datetime
SELECT @.julian = '04194'
SELECT @.gregorian = DATEADD(dd,CONVERT(int,SUBSTRING(@.julian,3,3)),CON VERT(datetime,'20'+SUBSTRING(@.julian,1,2)+'/01/01'))
SELECT DATEDIFF(dd,GetDate(),@.gregorian)|||Perfect!...Thanks for your help. I was going about it the wrong way. I was trying to convert the julian date and then compare.|||Thanks also Brett That was a road I was eventually going to have to cross.|||udf's...Makes a perfect house warming gift

CREATE FUNCTION udf_JulianToGregorian(@.julian char(5))
RETURNS datetime
AS
BEGIN
DECLARE @.gregorian datetime
SELECT @.gregorian =
DATEADD(dd,CONVERT(int,SUBSTRING(@.julian,3,3))
,CONVERT(datetime
,CASE WHEN SUBSTRING(@.julian,1,2) BETWEEN '00' AND '50'
THEN '20'
ELSE '19'
END
+SUBSTRING(@.julian,1,2)+'/01/01'))
RETURN @.gregorian
END
GO

DECLARE @.julian char(5)
SELECT @.julian = '04194'
SELECT dbo.udf_JulianToGregorian(@.julian)
GO

Journal table for a database

Hi,
I am using a SQL Server 2000 database with a VB.net program and uses
Integerated security. I want to log all the changes made to any table
data(whether addition, deletion, updation) to a single table by all the
users.
Is this possible by way of a common trigger. I dont want to use the log file
to know since i have to buy a seperate program to explore the log file. How
can achieve this.
Thanks for all the answers.
Senthilkumar> Is this possible by way of a common trigger.
NO.
Another option is running a profiler trace to log these changes.
But that has a performance impact.
Regards
Roji. P. Thomas
http://toponewithties.blogspot.com
"Senthilkumar" <kesk32@.yahoo.co.in> wrote in message
news:ewqweKAGGHA.1124@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I am using a SQL Server 2000 database with a VB.net program and uses
> Integerated security. I want to log all the changes made to any table
> data(whether addition, deletion, updation) to a single table by all the
> users.
> Is this possible by way of a common trigger. I dont want to use the log
> file to know since i have to buy a seperate program to explore the log
> file. How can achieve this.
> Thanks for all the answers.
> Senthilkumar
>|||hi Senthilkumar,
What about the creation of fields which store domain user or updating time
in every row
"Senthilkumar" wrote:

> Hi,
> I am using a SQL Server 2000 database with a VB.net program and uses
> Integerated security. I want to log all the changes made to any table
> data(whether addition, deletion, updation) to a single table by all the
> users.
> Is this possible by way of a common trigger. I dont want to use the log fi
le
> to know since i have to buy a seperate program to explore the log file. Ho
w
> can achieve this.
> Thanks for all the answers.
> Senthilkumar
>
>|||You can however implement something generic as example below for all
your tables:
CREATE TRIGGER logChanges
ON [any_table]
FOR INSERT, UPDATE, DELETE
AS
DECLARE @.trigType char(1), @.rwCnt int
SET @.rwCnt = @.@.ROWCOUNT
IF @.rwCnt = 0 RETURN
IF(select count(*) from inserted) > 0
BEGIN
SET @.trigType = 'i'
IF(select count(*) from deleted) > 0
BEGIN
SET @.trigType = 'u'
END
END
ELSE
BEGIN
SET @.trigType = 'd'
END
IF @.trigType = 'i'
BEGIN
INSERT INTO
aLogTableWithSameColumnsAsOrigTablePlusL
ogTypeAndLogDateColumn (val1,
val2, val3, LogType, LogDate)
SELECT val1, val2, val3, 'i', GETDATE() from inserted
END
IF @.trigType = 'u'
BEGIN
INSERT INTO
aLogTableWithSameColumnsAsOrigTablePlusL
ogTypeAndLogDateColumn (val1,
val2, val3, LogType, LogDate)
SELECT val1, val2, val3, 'ui', GETDATE() from inserted
UNION
SELECT val1, val2, val3, 'ud', GETDATE() from deleted
END
IF @.trigType = 'd'
BEGIN
INSERT INTO
aLogTableWithSameColumnsAsOrigTablePlusL
ogTypeAndLogDateColumn (val1,
val2, val3, LogType, LogDate)
SELECT val1, val2, val3, 'd', GETDATE() from inserted
END
GO|||Thanks for all those who replied.
I was creating a trigger for each table to store the values in a different
table. but then i found a tool ApexAudit which exactly does what i want. I
have used it now.
Once again thanks for all the answers.
Senthilkumar
"Senthilkumar" <kesk32@.yahoo.co.in> wrote in message
news:ewqweKAGGHA.1124@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I am using a SQL Server 2000 database with a VB.net program and uses
> Integerated security. I want to log all the changes made to any table
> data(whether addition, deletion, updation) to a single table by all the
> users.
> Is this possible by way of a common trigger. I dont want to use the log
> file to know since i have to buy a seperate program to explore the log
> file. How can achieve this.
> Thanks for all the answers.
> Senthilkumar
>

Journal table for a database

Hi,
I am using a SQL Server 2000 database with a VB.net program and uses
Integerated security. I want to log all the changes made to any table
data(whether addition, deletion, updation) to a single table by all the
users.
Is this possible by way of a common trigger. I dont want to use the log file
to know since i have to buy a seperate program to explore the log file. How
can achieve this.
Thanks for all the answers.
Senthilkumar
> Is this possible by way of a common trigger.
NO.
Another option is running a profiler trace to log these changes.
But that has a performance impact.
Regards
Roji. P. Thomas
http://toponewithties.blogspot.com
"Senthilkumar" <kesk32@.yahoo.co.in> wrote in message
news:ewqweKAGGHA.1124@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I am using a SQL Server 2000 database with a VB.net program and uses
> Integerated security. I want to log all the changes made to any table
> data(whether addition, deletion, updation) to a single table by all the
> users.
> Is this possible by way of a common trigger. I dont want to use the log
> file to know since i have to buy a seperate program to explore the log
> file. How can achieve this.
> Thanks for all the answers.
> Senthilkumar
>
|||hi Senthilkumar,
What about the creation of fields which store domain user or updating time
in every row
"Senthilkumar" wrote:

> Hi,
> I am using a SQL Server 2000 database with a VB.net program and uses
> Integerated security. I want to log all the changes made to any table
> data(whether addition, deletion, updation) to a single table by all the
> users.
> Is this possible by way of a common trigger. I dont want to use the log file
> to know since i have to buy a seperate program to explore the log file. How
> can achieve this.
> Thanks for all the answers.
> Senthilkumar
>
>
|||You can however implement something generic as example below for all
your tables:
CREATE TRIGGER logChanges
ON [any_table]
FOR INSERT, UPDATE, DELETE
AS
DECLARE @.trigType char(1), @.rwCnt int
SET @.rwCnt = @.@.ROWCOUNT
IF @.rwCnt = 0 RETURN
IF(select count(*) from inserted) > 0
BEGIN
SET @.trigType = 'i'
IF(select count(*) from deleted) > 0
BEGIN
SET @.trigType = 'u'
END
END
ELSE
BEGIN
SET @.trigType = 'd'
END
IF @.trigType = 'i'
BEGIN
INSERT INTO
aLogTableWithSameColumnsAsOrigTablePlusLogTypeAndL ogDateColumn (val1,
val2, val3, LogType, LogDate)
SELECT val1, val2, val3, 'i', GETDATE() from inserted
END
IF @.trigType = 'u'
BEGIN
INSERT INTO
aLogTableWithSameColumnsAsOrigTablePlusLogTypeAndL ogDateColumn (val1,
val2, val3, LogType, LogDate)
SELECT val1, val2, val3, 'ui', GETDATE() from inserted
UNION
SELECT val1, val2, val3, 'ud', GETDATE() from deleted
END
IF @.trigType = 'd'
BEGIN
INSERT INTO
aLogTableWithSameColumnsAsOrigTablePlusLogTypeAndL ogDateColumn (val1,
val2, val3, LogType, LogDate)
SELECT val1, val2, val3, 'd', GETDATE() from inserted
END
GO
|||Thanks for all those who replied.
I was creating a trigger for each table to store the values in a different
table. but then i found a tool ApexAudit which exactly does what i want. I
have used it now.
Once again thanks for all the answers.
Senthilkumar
"Senthilkumar" <kesk32@.yahoo.co.in> wrote in message
news:ewqweKAGGHA.1124@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I am using a SQL Server 2000 database with a VB.net program and uses
> Integerated security. I want to log all the changes made to any table
> data(whether addition, deletion, updation) to a single table by all the
> users.
> Is this possible by way of a common trigger. I dont want to use the log
> file to know since i have to buy a seperate program to explore the log
> file. How can achieve this.
> Thanks for all the answers.
> Senthilkumar
>

Journal table for a database

Hi,
I am using a SQL Server 2000 database with a VB.net program and uses
Integerated security. I want to log all the changes made to any table
data(whether addition, deletion, updation) to a single table by all the
users.
Is this possible by way of a common trigger. I dont want to use the log file
to know since i have to buy a seperate program to explore the log file. How
can achieve this.
Thanks for all the answers.
Senthilkumar> Is this possible by way of a common trigger.
NO.
Another option is running a profiler trace to log these changes.
But that has a performance impact.
--
Regards
Roji. P. Thomas
http://toponewithties.blogspot.com
"Senthilkumar" <kesk32@.yahoo.co.in> wrote in message
news:ewqweKAGGHA.1124@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I am using a SQL Server 2000 database with a VB.net program and uses
> Integerated security. I want to log all the changes made to any table
> data(whether addition, deletion, updation) to a single table by all the
> users.
> Is this possible by way of a common trigger. I dont want to use the log
> file to know since i have to buy a seperate program to explore the log
> file. How can achieve this.
> Thanks for all the answers.
> Senthilkumar
>|||hi Senthilkumar,
What about the creation of fields which store domain user or updating time
in every row
"Senthilkumar" wrote:
> Hi,
> I am using a SQL Server 2000 database with a VB.net program and uses
> Integerated security. I want to log all the changes made to any table
> data(whether addition, deletion, updation) to a single table by all the
> users.
> Is this possible by way of a common trigger. I dont want to use the log file
> to know since i have to buy a seperate program to explore the log file. How
> can achieve this.
> Thanks for all the answers.
> Senthilkumar
>
>|||You can however implement something generic as example below for all
your tables:
CREATE TRIGGER logChanges
ON [any_table]
FOR INSERT, UPDATE, DELETE
AS
DECLARE @.trigType char(1), @.rwCnt int
SET @.rwCnt = @.@.ROWCOUNT
IF @.rwCnt = 0 RETURN
IF(select count(*) from inserted) > 0
BEGIN
SET @.trigType = 'i'
IF(select count(*) from deleted) > 0
BEGIN
SET @.trigType = 'u'
END
END
ELSE
BEGIN
SET @.trigType = 'd'
END
IF @.trigType = 'i'
BEGIN
INSERT INTO
aLogTableWithSameColumnsAsOrigTablePlusLogTypeAndLogDateColumn (val1,
val2, val3, LogType, LogDate)
SELECT val1, val2, val3, 'i', GETDATE() from inserted
END
IF @.trigType = 'u'
BEGIN
INSERT INTO
aLogTableWithSameColumnsAsOrigTablePlusLogTypeAndLogDateColumn (val1,
val2, val3, LogType, LogDate)
SELECT val1, val2, val3, 'ui', GETDATE() from inserted
UNION
SELECT val1, val2, val3, 'ud', GETDATE() from deleted
END
IF @.trigType = 'd'
BEGIN
INSERT INTO
aLogTableWithSameColumnsAsOrigTablePlusLogTypeAndLogDateColumn (val1,
val2, val3, LogType, LogDate)
SELECT val1, val2, val3, 'd', GETDATE() from inserted
END
GO|||Thanks for all those who replied.
I was creating a trigger for each table to store the values in a different
table. but then i found a tool ApexAudit which exactly does what i want. I
have used it now.
Once again thanks for all the answers.
Senthilkumar
"Senthilkumar" <kesk32@.yahoo.co.in> wrote in message
news:ewqweKAGGHA.1124@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I am using a SQL Server 2000 database with a VB.net program and uses
> Integerated security. I want to log all the changes made to any table
> data(whether addition, deletion, updation) to a single table by all the
> users.
> Is this possible by way of a common trigger. I dont want to use the log
> file to know since i have to buy a seperate program to explore the log
> file. How can achieve this.
> Thanks for all the answers.
> Senthilkumar
>

Joins with XQuery

Hello,
I have a very simple data table:

CREATE TABLE [ALMPayloads]([ID] [int] NOT NULL,[OutputPayload] [xml] NOT NULL)

with the following content:

ID = 1
OutputPayload:

<ReportDocument>
<ALMSimulationResult>
<selectedModelAssets>
<modelAsset ID="8bc798ae-cc15-4807-8805-61ecfc8f3c01" description="Global Bond" internationalCode=" " minimumLimit="0" maximumLimit="1" annualManagementFee="0" annualPerformanceFee="0" initialCostUpFront="0.02" regularCostUpFront="0.015" withdrawingCommission="0" switchCostPercentage="0" switchCostAmount="0" color="#FF00FFD4" stochasticDuration="5">
<models>
<model ID="0e70216f-48ce-4f6c-b2d6-519a5cdfd246" type="corporate grade bond" description="Eurozone Corporate Bond Intermediate (D=5Years)" weight="1" />
</models>
</modelAsset>
<modelAsset ID="eab258b2-57ba-4d67-9f36-ee4e17c10dec" description="America Value Fund" internationalCode=" " minimumLimit="0" maximumLimit="1" annualManagementFee="0" annualPerformanceFee="0.005" initialCostUpFront="0.03" regularCostUpFront="0.03" withdrawingCommission="0" switchCostPercentage="0" switchCostAmount="0" color="#FF3B00FF" stochasticDuration="13.55">
<models>
<model ID="b0817f64-5090-48a3-b58c-aa8f6e5bbdc1" type="equity" description="US Value Style (Eur)" weight="0.9" />
<model ID="677e8aae-7b32-4dc3-88c5-e9302dddad8f" type="conventional bond" description="Euro Cash (TBill)" weight="0.1" />
</models>
</modelAsset>
<modelAsset ID="0e2e95bb-bec0-4dcb-bb13-2032f3ed0978" description="Europa Value Fund" internationalCode=" " minimumLimit="0" maximumLimit="1" annualManagementFee="0.001" annualPerformanceFee="0.001" initialCostUpFront="0.03" regularCostUpFront="0.03" withdrawingCommission="0" switchCostPercentage="0" switchCostAmount="0" color="#FF9D00FF" stochasticDuration="17.5">
<models>
<model ID="86fedd24-2a92-422c-b733-17c60105ff81" type="equity" description="Asia Value Style (Eur)" weight="0.1" />
<model ID="55425529-8adc-47d8-a36d-8cfd9da34880" type="conventional bond" description="Italian Long Term Gov Bond" weight="0.1" />
<model ID="fea29db9-cf0e-4802-bcbe-e2b8d367f0ca" type="cash" description="Euro Cash (Euribor 1m)" weight="0.1" />
<model ID="8e58d785-5fc5-4ede-8ec8-eb1af8e62541" type="equity" description="Eurozone Value Style" weight="0.7" />
</models>
</modelAsset>
</selectedModelAssets>
<savingModelAsset ID="0e2e95bb-bec0-4dcb-bb13-2032f3ed0978" />
<surplusModelAsset ID="0e2e95bb-bec0-4dcb-bb13-2032f3ed0978" />
<modelAssetTimeSeries>
<modelAsset ID="8bc798ae-cc15-4807-8805-61ecfc8f3c01">
<tValues t="0" value="0" annualYield="0" />
<tValues t="1" value="0" annualYield="0.027353" />
<tValues t="2" value="0" annualYield="0.027288" />
<tValues t="3" value="0" annualYield="0.027237" />
<tValues t="4" value="0" annualYield="0.027274" />
<tValues t="5" value="0" annualYield="0.027262" />
<tValues t="6" value="0" annualYield="0.02722" />
<tValues t="7" value="1453" annualYield="0.027258" />
<tValues t="8" value="1457" annualYield="0.027258" />
<tValues t="9" value="1460" annualYield="0.027219" />
<tValues t="10" value="1463" annualYield="0.027259" />
</modelAsset>
<modelAsset ID="eab258b2-57ba-4d67-9f36-ee4e17c10dec">
<tValues t="0" value="0" annualYield="0" />
<tValues t="1" value="0" annualYield="0.065466" />
<tValues t="2" value="0" annualYield="0.063841" />
<tValues t="3" value="0" annualYield="0.063707" />
<tValues t="4" value="0" annualYield="0.063692" />
<tValues t="5" value="0" annualYield="0.062438" />
<tValues t="6" value="0" annualYield="0.064081" />
<tValues t="7" value="0" annualYield="0.063476" />
<tValues t="8" value="0" annualYield="0.064294" />
<tValues t="9" value="0" annualYield="0.062034" />
<tValues t="10" value="0" annualYield="0.065144" />
</modelAsset>
<modelAsset ID="0e2e95bb-bec0-4dcb-bb13-2032f3ed0978">
<tValues t="0" value="830" annualYield="0" />
<tValues t="1" value="1641" annualYield="0.06504" />
<tValues t="2" value="2456" annualYield="0.063229" />
<tValues t="3" value="3278" annualYield="0.062939" />
<tValues t="4" value="4104" annualYield="0.062825" />
<tValues t="5" value="4935" annualYield="0.061233" />
<tValues t="6" value="5772" annualYield="0.063522" />
<tValues t="7" value="5155" annualYield="0.062448" />
<tValues t="8" value="5994" annualYield="0.063548" />
<tValues t="9" value="6837" annualYield="0.061053" />
<tValues t="10" value="7688" annualYield="0.06525" />
</modelAsset>
</modelAssetTimeSeries>
</ALMSimulationResult>
</ReportDocument>

When I run the following command from SQL server:

SELECT
N.ma.value('@.ID', 'uniqueidentifier') as ModelAssetID,
N.ma.value('@.description', 'nvarchar(255)') as ModelAssetDescription,
N.ma.value('@.minimumLimit', 'float') as ModelAssetMinLimit,
N.ma.value('@.maximumLimit', 'float') as ModelAssetMaxLimit,
N.ma.value('@.stochasticDuration', 'float') as ModelAssetDuration,
N.ma.value('@.color', 'char(9)') as Color,
N1.ma1.value('tValues[1]/@.value', 'float') as ActualAssetMix
FROM ALMPayloads A1 CROSS APPLY OutputPayload.nodes('/ReportDocument/ALMSimulationResult/selectedModelAssets/modelAsset') N(ma)
LEFT JOIN ALMPayloads A2 CROSS APPLY OutputPayload.nodes('/ReportDocument/ALMSimulationResult/modelAssetTimeSeries/modelAsset') N1(ma1)
ON N.ma.value('@.ID', 'uniqueidentifier') = N1.ma1.value('@.ID', 'uniqueidentifier')
WHERE A1.ID = 1000
ORDER BY ModelAssetDuration

I get the following result:

ModelAssetID ModelAssetDescription Min Max Dur. Color Value
8BC798AE-CC15-4807-8805-61ECFC8F3C01 Global Bond 0 1 5 #FF00FFD4 0
8BC798AE-CC15-4807-8805-61ECFC8F3C01 Global Bond 0 1 5 #FF00FFD4 0
EAB258B2-57BA-4D67-9F36-EE4E17C10DEC America Value Fund 0 1 13.55 #FF3B00FF 0
EAB258B2-57BA-4D67-9F36-EE4E17C10DEC America Value Fund 0 1 13.55 #FF3B00FF 0
0E2E95BB-BEC0-4DCB-BB13-2032F3ED0978 Europa Value Fund 0 1 17.5 #FF9D00FF 96803
0E2E95BB-BEC0-4DCB-BB13-2032F3ED0978 Europa Value Fund 0 1 17.5 #FF9D00FF 830
0E2E95BB-BEC0-4DCB-BB13-2032F3ED0978 Europa Value Fund 0 1 17.5 #FF9D00FF 830

Instead of

ModelAssetID ModelAssetDescription Min Max Dur. Color Value
8BC798AE-CC15-4807-8805-61ECFC8F3C01 Global Bond 0 1 5 #FF00FFD4 0
EAB258B2-57BA-4D67-9F36-EE4E17C10DEC America Value Fund 0 1 13.55 #FF3B00FF 0
0E2E95BB-BEC0-4DCB-BB13-2032F3ED0978 Europa Value Fund 0 1 17.5 #FF9D00FF 830

Why do I have so much duplicates and some random results (I mean the value where I get 96803) ?

Thanks,
Pierre

Pierre, I just tried running the following query. At first, I got no results. When I changed the AI.ID predicate to AI.ID = 1 then I got three rows back (your expected results).

Are you sure there is not other data in the table? I notice that you are doing a self join on the the table [ALMPayloads] but I don't see any predicate on the A2 table alias. I suspect that this could be the reason why you are seeing the additional rows in your results if in fact there are other rows in that table.|||Hi John,
try to add another record (the same xml content with 2 different IDs) and the result will be duplicated.

How can I make the join (on xml data) with the expected result ?

Thanks,
Pierre|||You need to add "AND A1.ID = A2.ID" to your join condition. If you don't need the LEFT JOIN semantics, you can do away with the self join and add another CROSS APPLY.

ALMPayloads A1
CROSS APPLY OutputPayload.nodes('/ReportDocument/ALMSimulationResult/selectedModelAssets/modelAsset') N(ma)CROSS APPLY OutputPayload.nodes('/ReportDocument/ALMSimulationResult/modelAssetTimeSeries/modelAsset') N1(ma1)

sql

Joins with XQuery

Hello,
I have a very simple data table:

CREATE TABLE [ALMPayloads]([ID] [int] NOT NULL,[OutputPayload] [xml] NOT NULL)

Why do I have so much duplicates and some random results (I mean the value where I get 96803) ?

Thanks,
Pierre

Pierre, I just tried running the following query. At first, I got no results. When I changed the AI.ID predicate to AI.ID = 1 then I got three rows back (your expected results).

How can I make the join (on xml data) with the expected result ?

Thanks,
Pierre|||You need to add "AND A1.ID = A2.ID" to your join condition. If you don't need the LEFT JOIN semantics, you can do away with the self join and add another CROSS APPLY.

Joins on same table

I'm having two general problems trying to do a JOIN. I have a table with
three fields {Code, Date, Amount}. Code+Date is a unique key. I'm trying
to get a rowset with 1) one row for each unique Code+Date pair, 2) and
with each row containing, {Code, Amount for Date-A and Amount for
Date-B}. Basically, I want to create two temp tables with the Amounts for
a specified Date and then Join them.

The problems are
1) I'm trying to do this in SQL-Server 7 with a single stantment, and
2) If a Code+Date pair doesn't have any Amounts, I'd still like a row
returned with NULLs.

Anybody have any wisdom on this??
ThanksThe following gets me what I want, using Temp tables. I'm just trying to
figure out how to combine the Selects into a single statment.

Thanks

----------------

--temp with each Code
Drop Table #T0;
Select Code
Into #T0
From tblSearch
Order by Code;

--temp with amounts for 1st date
Drop Table #T1;
Select Code, Date, Amount
Into #T1
From tblSearch
Where Date = 20031102
Order by Code, Date;

-- amounts for 2nd date
Drop Table #T2;
Select Code, Date, Amount
Into #T2
From tblSearch
Where Date = 20031103
Order by Code, Date;

--put everything together
Select Distinct #T0.Code, #T1.Date, #T1.Amount 'd1', #T2.Date, #T2.Amount
'd2' from #T0
Left Outer Join #T1
On #T0.Code = #T1.Code
Left Outer Join #T2
On #T0.Code = #T2.Code
Order By #T0.Code|||[posted and mailed, please reply in news]

Chris (chris@.hicom.net) writes:
> I'm having two general problems trying to do a JOIN. I have a table
> with three fields {Code, Date, Amount}. Code+Date is a unique key.
> I'm trying to get a rowset with 1) one row for each unique Code+Date
> pair, 2) and with each row containing, {Code, Amount for Date-A and
> Amount for Date-B}. Basically, I want to create two temp tables with
> the Amounts for a specified Date and then Join them.
> The problems are
> 1) I'm trying to do this in SQL-Server 7 with a single stantment, and
> 2) If a Code+Date pair doesn't have any Amounts, I'd still like a row
> returned with NULLs.

Just rewriting the temp-table thing you had with derived tables
gives:

SELECT DISTINCT #T0.Code, #T1.Date, #T1.Amount 'd1',
#T2.Date, #T2.Amount 'd2'
FROM tblSearch #T0
LEFT JOIN (SELECT Code, Date, Amount
FROM tblSearch
WHERE Date = '20031102') AS #T1
ON #T0.Code = #T1.Code
LEFT JOIN (SELECT Code, Date, Amount
FROM tblSearch
WHERE Date = '20031103') AS #T2
ON #T0.Code = #T2.Code
ORDER BY #T0.Code

But if I understand this correctly, it seems that you could get away with:

SELECT Code = coalesce(a.Code, b.Code), a.Date, d1 = a.Amount,
b.Date, d2 = b.Amount
FROM tblSearch a
FULL JOIN tblSearch b ON a.Code = b.Code
AND a.Date = b.Date
AND a.Date = '20031102'
AND b.Date = '20031103'

All this works on SQL7.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi Chris,

You can replace temp tables with derived tables. Alternatively, as
the temp tables are selecting from the same table tblSearch, you can
also re-write the query using CASE. Note I'm using a mssqlserver
non-standard syntax. I just find it easier to read.

"Date=CASE when Date = 20031102 then Date else null end"
instead of
"CASE when Date = 20031102 then Date else null end as Date"

SELECT
Distinct
Code,
Date=CASE when Date = 20031102 then Date else null end,
Amount=CASE when Date = 20031102 then Amount else null end,
Date=CASE when Date = 20031103 then Date else null end,
Amount=CASE when Date = 20031103 then Amount else null end,
FROM tblSearch
ORDER BY code

> --temp with each Code
> Drop Table #T0;
> Select Code
> Into #T0
> From tblSearch
> Order by Code;
> --temp with amounts for 1st date
> Drop Table #T1;
> Select Code, Date, Amount
> Into #T1
> From tblSearch
> Where Date = 20031102
> Order by Code, Date;
> -- amounts for 2nd date
> Drop Table #T2;
> Select Code, Date, Amount
> Into #T2
> From tblSearch
> Where Date = 20031103
> Order by Code, Date;
> --put everything together
> Select Distinct #T0.Code, #T1.Date, #T1.Amount 'd1', #T2.Date, #T2.Amount
> 'd2' from #T0
> Left Outer Join #T1
> On #T0.Code = #T1.Code
> Left Outer Join #T2
> On #T0.Code = #T2.Code
> Order By #T0.Code|||The derived table approach gets me what I want -- one row per Code.

It seems that Coalesce doesn't help reduce the normal number of rows from
the Join.

Thanks very much for looking for a solution.|||The derived tables gets both Amounts into the same row, while the Case
still results in two (Distinct) rows.

I need to get a better SQL reference -- the book I'm using does not cover
derived tables.

Thanks very much.|||"Chris" <chris@.hicom.net> wrote in message news:<2bcNb.32425$G04.6661104@.news4.srv.hcvlny.cv.net>...
> The derived tables gets both Amounts into the same row, while the Case
> still results in two (Distinct) rows.
> I need to get a better SQL reference -- the book I'm using does not cover
> derived tables.
> Thanks very much.

Use GROUP if you want combine them into the same row. DISTINCT only
filters the rows.

SELECT
Code,
Date=max(CASE when Date = 20031102 then Date else null end),
Amount=max(CASE when Date = 20031102 then Amount else null end),
Date=max(CASE when Date = 20031103 then Date else null end),
Amount=max(CASE when Date = 20031103 then Amount else null end)
FROM tblSearch
GROUP BY code
ORDER BY code|||Chris (chris@.hicom.net) writes:
> It seems that Coalesce doesn't help reduce the normal number of rows
> from the Join.

That's right. The coalesce() function takes a list of values as parameters,
and return the first value in the list that is not NULL. Since the second
query included a full join, any of a.code and b.code could be NULL, so be
sure that we had a value here, I used coalesce(a.Code, b.Code).

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Excellent!

-- Thanks|||louis nguyen (louisducnguyen@.hotmail.com) writes:
> SELECT
> Code,
> Date=max(CASE when Date = 20031102 then Date else null end),
> Amount=max(CASE when Date = 20031102 then Amount else null end),
> Date=max(CASE when Date = 20031103 then Date else null end),
> Amount=max(CASE when Date = 20031103 then Amount else null end)
> FROM tblSearch
> GROUP BY code
> ORDER BY code

Note that date literals requires quotes. 20031103 is a number, and
attempt to convert it to datetime results in overflow.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Joins on 3 tables

Hi All,

I require to perfom a join on 3 tables within the same query . To explain myself better i have 3 tables

Main table

Label table

textbox table

The Main table contains the common fields in both the label and textbox table. While the label and textox table contain the fields that are sepcfic to them .

MAIN Table

pk Moduleid ItemName itemtype

36 372 test1 4 37 372 test2 4 38 372 test3 4 39 372 test4 6 40 372 test5 4

label

pk Main_fk labeltext

4 36 labeltext1 5 37 labeltext2 6 38 labeltext3 7 40 labeltext4

Textbox

pk Main_fk textboxtext

1 39 textbox1

I did infact manage to perform a join on these these tables.

Select * From tb_Main

inner join tb_Label

on tb_Main.pk = tb_Label.main_fk

where moduleID = @.moduleID

Select * From tb_Main

inner join tb_textbox

on tb_Main.pk = tb_textbox.main_fk

where moduleID = @.moduleID

The problem is that it returns two separate results . I require a join on the label and textbox table within the same query to return one result.

Is what im asking possible? I would appreciate if some exmaples are posted

I have no control on the design of the tables as i didnt create them but still if anyone has a suggestion on improving them please do ,so i can tell my colleague that they aren't designed well !!!!

Thanks in advance

Matt

Hai,

You can try this query, hope this will work.

DECLARE @.ModuleID int

SET @.ModuleID = 372

SELECT

*

FROM tb_Main AS M

JOIN tb_Label AS L

ON M.pk = L.main_fK

JOIN tb_textbox AS T

ON M.pk = T.main_fk

WHERE ModuleID = @.ModuleID

Regards,

Kiran.Y

|||

Thanks Y.Kiran for your suggestion.

I had already tried that out, it returns no rows.

Regards,

Matt

|||

Hai,

I didn't change any thing in the given query. Now I'm giving you the table definitions of all tables, Main, Label, TextBox. Check this one.

-- Create Main, Label, TextBox Tables.

CREATE TABLE tb_Main

( PK int,

Moduleid int,

ItemName varchar(50),

itemtype int,

CONSTRAINT PK_tb_Main_PK PRIMARY KEY CLUSTERED

(

PK ASC

)

CREATE TABLE tb_Label

(

PK int,

Main_fk int CONSTRAINT FK_tb_Main_Main_fk REFERENCES tb_Main(PK),

labeltext varchar(50),

CONSTRAINT PK_tb_Label_PK PRIMARY KEY CLUSTERED

(

PK ASC

)

CREATE TABLE tb_TextBox

(

PK int,

Main_fk int CONSTRAINT FK_tb_TextBox_Main_fk REFERENCES tb_Main(pk),

TextBoxText varchar(50),

CONSTRAINT PK_tb_TextBox_PK PRIMARY KEY CLUSTERED

(

PK ASC

)

-- Insert data into Main, Label, TextBox tables.

INSERT INTO tb_Main(pk , Moduleid , ItemName, itemtype) VALUES(36,372,'test1',4)

INSERT INTO tb_Main(pk , Moduleid , ItemName, itemtype) VALUES(37,372,'test2',4)

INSERT INTO tb_Main(pk , Moduleid , ItemName, itemtype) VALUES(38,372,'test3',4)

INSERT INTO tb_Main(pk , Moduleid , ItemName, itemtype) VALUES(39,372,'test4',6)

INSERT INTO tb_Main(pk , Moduleid , ItemName, itemtype) VALUES(40,372,'test5',4)

INSERT INTO tb_Label(pk,Main_fk, labeltext) VALUES(4,36,'labeltext1')

INSERT INTO tb_Label(pk,Main_fk, labeltext) VALUES(5,37,'labeltext2')

INSERT INTO tb_Label(pk,Main_fk, labeltext) VALUES(6,38,'labeltext3')

INSERT INTO tb_Label(pk,Main_fk, labeltext) VALUES(7,40,'labeltext4')

INSERT INTO tb_Label(pk,Main_fk, labeltext) VALUES(8,39,'labeltext5')

INSERT INTO tb_textbox(pk, Main_fk, textboxtext) VALUES(1,39,'textbox1')

-- Get the records based on the @.ModuleID variable.

DECLARE @.ModuleID int

SET @.ModuleID = 372

SELECT

FROM tb_Main AS M

JOIN tb_Label AS L

ON M.pk = L.main_fK

JOIN tb_textbox AS T

ON M.pk = T.main_fk

WHERE ModuleID = @.moduleID

Let me know, If I did any wrong.

Regards,

Kiran.Y

|||

You should be able to run the following statement to get what you need.

SELECT
tb_Main.pk, tb_ModuleID, tb_ItemName, tb_ItemType,
tb_Label.LabelText, tb_Textbox.Textboxtext
FROM
tb_Main
INNER_JOIN tb_Label ON tb_Main.pk = tb_Label.FK
INNER_JOIN tb_Textbox ON tb_Main.pk = tb_Textbox
WHERE tb_Main.ModuleID = @.ModuleID

The problem that I see is in your data. If this is a true representation of you data then an INNER JOIN is not going to return anything because when you join to the third table the only match you will find is 39, which is not in the second table, hence you get no results. If you were to do a LEFT OUTER JOIN, you could get all of the results with NULL values also represented. Try the query below to attempt to get results.

SELECT
tb_Main.pk, tb_ModuleID, tb_ItemName, tb_ItemType,
tb_Label.LabelText, tb_Textbox.Textboxtext
FROM
tb_Main
LEFT OUTER_JOIN tb_Label ON tb_Main.pk = tb_Label.FK
LEFT OUTER_JOiN tb_Textbox ON tb_Main.pk = tb_Textbox
WHERE tb_Main.ModuleID = @.ModuleID

Results:

pk ModuleID ItemName ItemType LabelText TextBox
-- -- -- -- -
36 372 test1 4 labeltext1 NULL
37 372 test2 4 labeltext2 NULL
38 372 test3 4 labeltext3 NULL
39 372 test4 6 NULL textbox1
40 372 test5 4 labeltext4 NULL

Hope this helps. Anyone feel free to correct if there are any inaccuracies. I'm relatively new to SQL Server.

|||

No Data was returned from the query that Kumar provided, BECAUSE there is NO common data between all three tables.

MAIN Table

pk Moduleid ItemName itemtype

36 372 test1 4 37 372 test2 4 38 372 test3 4 39 372 test4 6 40 372 test5 4

label

pk Main_fk labeltext

4 36 labeltext1 5 37 labeltext2 6 38 labeltext3 7 40 labeltext4

Textbox

pk Main_fk textboxtext

1 39 textbox1

There is NO [Mail_fk] for 39 in the table [Label], therefore no matching link between [Textbox], [Label] and [Main]

You 'could' use LEFT JOIN in both of the joins to have a resultset that includes ALL rows from [MAIN] even if there is NO matching links in the other tables.

|||

DBaker,

Excellent explanation and corrected query!

|||

Thanks guys for your help it worked great !

Matt

Friday, March 23, 2012

JOINs and SUMs Not Giving Expected Results

I have just added a third table to a query and I am no longer getting
the results I am expecting.

Three Tables:
CUSTINVOICEJOUR (Header Table)
CUSTINVOICETRANS (Line Item Table)
MARKUPTRANS (Additional Header Info)

CUSTINVOICEJOUR has a one to many relationship to CUSTINVOICETRANS.

CUSTINVOICEJOUR has a one to many relationship to MARKUPTRANS.

I need to sum an integer column from MARKUPTRANS, in rows that are
related to CUSTINVOICEJOUR, and include that output with my query
below, which right now has a row for each CUSTINVOICETRANS record:

SELECT CUSTINVOICEJOUR.INVOICEAMOUNT, CUSTINVOICETRANS.QTY
FROM CUSTINVOICEJOUR INNER JOIN
CUSTINVOICETRANS ON CUSTINVOICEJOUR.INVOICEID =
CUSTINVOICETRANS.INVOICEID
WHERE (CUSTINVOICEJOUR.DATAAREAID = 'acm') AND
(CUSTINVOICETRANS.DATAAREAID = 'acm')

The above works fine - a row for each record in CUSTINVOICETRANS with
the header info in there as well.

I tried the query below to add a SUM() from MARKUPTRANS, but when I run
it, I get one row with strange results in it - not what I expected.
What am I doing wrong?

SELECT CUSTINVOICEJOUR.INVOICEAMOUNT, CUSTINVOICETRANS.QTY,
SUM(MARKUPTRANS.VALUE) AS FreightValue
FROM CUSTINVOICEJOUR INNER JOIN
CUSTINVOICETRANS ON CUSTINVOICEJOUR.INVOICEID =
CUSTINVOICETRANS.INVOICEID INNER JOIN
MARKUPTRANS ON CUSTINVOICEJOUR.RECID =
MARKUPTRANS.TRANSRECID
WHERE (CUSTINVOICEJOUR.DATAAREAID = 'acm') AND
(CUSTINVOICETRANS.DATAAREAID = 'acm') AND (MARKUPTRANS.DATAAREAID =
'acm')
GROUP BY CUSTINVOICEJOUR.INVOICEAMOUNT, CUSTINVOICETRANS.QTY,
MARKUPTRANS.MARKUPCODE
HAVING (MARKUPTRANS.MARKUPCODE = 'Freight')"Hunter Hillegas" <hunter.hillegas@.gmail.com> wrote in message news:ci581o$h9v@.odak26.prod.google.com...
> I have just added a third table to a query and I am no longer getting
> the results I am expecting.
<snip
If you're aggregating a column from MARKUPTRANS, then you shouldn't include another column from that same table in the
GROUP BY clause. I would rewrite that as a correlated subquery in the SELECT list.

SELECT
CUSTINVOICEJOUR.INVOICEAMOUNT,
CUSTINVOICETRANS.QTY,
(Select SUM(MARKUPTRANS.VALUE)
FROM MARKUPTRANS
WHERE CUSTINVOICEJOUR.RECID = MARKUPTRANS.TRANSRECID
and MARKUPTRANS.DATAAREAID = 'acm'
and MARKUPTRANS.MARKUPCODE = 'Freight' ) AS FreightValue

FROM
CUSTINVOICEJOUR
INNER JOIN
CUSTINVOICETRANS
ON CUSTINVOICEJOUR.INVOICEID = CUSTINVOICETRANS.INVOICEID
WHERE
(CUSTINVOICEJOUR.DATAAREAID = 'acm') AND
(CUSTINVOICETRANS.DATAAREAID = 'acm') ;

--
Paul Horan[TeamSybase]

Joins and inices

I can't understand this behavior:
I'm doing a join on a large table, and I created a
clustered index on the column in the join condition.
I would expect that this should enhance performance,
because of the merge join advantage.
It didn't perform well.
I then tried making that index non-clustered, and doing
a clustered index on an identity column (as a primary
key).
It's weird, but the join performs far better this way.
The only way I can explain this is that the non-clustered
index keeps the values of the join column in fewer pages,
and therefore closer together on disk (fewer reads).
Then again, there must still be the additional seek to
get to the rest of the "data" that row contains.
Even stranger, if the clustered index on the identity col-
umn (PK) is not there, the join doesn't perform well also.
If anyone has any experience with this, or suggestions,
I'd be really grateful for some help.
Thanks in advance,
Andrew.It would be easier to offer ideas if you posted DDL and ideally insert
statements to load some sample data...
this doesn't answer all of your questions but as an FYI...
<<
Even stranger, if the clustered index on the identity col-
umn (PK) is not there, the join doesn't perform well also.
the NC index actually keeps the clustering key as part of it's own key
information at the leaf level of the NC index. So... you're actually
changing the contents of the NC index if you get rid of the clustering key.
Also, NC index is stored entirely differently if there is no clustered index
at all...
--
Brian
"Andrew" <a@.b.com> wrote in message
news:01a401c38dc5$d91d84d0$a401280a@.phx.gbl...
> I can't understand this behavior:
> I'm doing a join on a large table, and I created a
> clustered index on the column in the join condition.
> I would expect that this should enhance performance,
> because of the merge join advantage.
> It didn't perform well.
> I then tried making that index non-clustered, and doing
> a clustered index on an identity column (as a primary
> key).
> It's weird, but the join performs far better this way.
> The only way I can explain this is that the non-clustered
> index keeps the values of the join column in fewer pages,
> and therefore closer together on disk (fewer reads).
> Then again, there must still be the additional seek to
> get to the rest of the "data" that row contains.
> Even stranger, if the clustered index on the identity col-
> umn (PK) is not there, the join doesn't perform well also.
> If anyone has any experience with this, or suggestions,
> I'd be really grateful for some help.
> Thanks in advance,
> Andrew.|||Still...
><<
>Even stranger, if the clustered index on the identity
col-
>umn (PK) is not there, the join doesn't perform well
also.
>the NC index actually keeps the clustering key as part
of it's own key
>information at the leaf level of the NC index. So...
you're actually
>changing the contents of the NC index if you get rid of
the clustering key.
>Also, NC index is stored entirely differently if there
is no clustered index
>at all...
>--
I realize the NC index keeps the clustering key as part
of it's own key, but I had understood that SQL server
keeps a RID (row identifier) internally when there is no
clustered index... so rather thank keeping the clustered
index in the leaf, the RID is kept.
Now, if accessing that RID is slow, I can understand, but
I'd imagined it as a kind of internal indexed identity
of its own. I guess this doesn't make sense, though.
As a follow up, though, is there no use for a NC index
without a clustered index when you want to access columns
outside of the NC indexed column?
Also, can a column be both a clustered and a non-clustered
index (would this help on join performance to access those
other columns?).
Thanks again,
Andrew
>Brian
>
>"Andrew" <a@.b.com> wrote in message
>news:01a401c38dc5$d91d84d0$a401280a@.phx.gbl...
>> I can't understand this behavior:
>> I'm doing a join on a large table, and I created a
>> clustered index on the column in the join condition.
>> I would expect that this should enhance performance,
>> because of the merge join advantage.
>> It didn't perform well.
>> I then tried making that index non-clustered, and doing
>> a clustered index on an identity column (as a primary
>> key).
>> It's weird, but the join performs far better this way.
>> The only way I can explain this is that the non-
clustered
>> index keeps the values of the join column in fewer
pages,
>> and therefore closer together on disk (fewer reads).
>> Then again, there must still be the additional seek to
>> get to the rest of the "data" that row contains.
>> Even stranger, if the clustered index on the identity
col-
>> umn (PK) is not there, the join doesn't perform well
also.
>> If anyone has any experience with this, or suggestions,
>> I'd be really grateful for some help.
>> Thanks in advance,
>> Andrew.
>
>.
>

JOINS / Exclude rows

Help:
How do I constuct a queery returning all the rows from table A that do
NOT have a match in table B for a given column?
To be more specific, I am pulling a copy of the sysprcesses table. I
then want to report out the rows that ARE NOT in my permitted logins
(i.e., potiential problems)
PermittedUsers
==============
ID LoginName
-- --
01 johnsmith
02 davebarry
-- GET A SNAPSHOT OF THE ALL PROCESSESS AND STORE THEM IN THE TEMP
TABLE #processlist
Select @.@.SERVERNAME AS [SERVERNAME], GETDATE() AS [SNAPTIME], * into
#processlist From master..sysprocesses (nolock)
-- This will give me a list of all the processes running by permitted
users:
select * INTO #goodprocesses from #processlist PL INNER JOIN
PermittedUsers PU ON (PL.loginame =PU.LoginName collate
Latin1_General_CI_AS)
-- I need a query giving me the "opposite"
select * INTO #suspectprocesses from #processlist PL INNER JOIN
PermittedUsers PU ON (PL.loginame <> PU.LoginName collate
Latin1_General_CI_AS)
-- but the above is not it.
Can anyone help?
THANKS!
d.Try:
SELECT COLUMNLIST
FROM TABLE1
WHERE NOT EXISTS (SELECT * FROM TABLE2 WHERE TABLE1.COLID = TABLE2.COLID)
HTH
Jerry
<google@.dcbarry.com> wrote in message
news:1128642174.792462.153940@.g49g2000cwa.googlegroups.com...
> Help:
> How do I constuct a queery returning all the rows from table A that do
> NOT have a match in table B for a given column?
>
> To be more specific, I am pulling a copy of the sysprcesses table. I
> then want to report out the rows that ARE NOT in my permitted logins
> (i.e., potiential problems)
>
> PermittedUsers
> ==============
> ID LoginName
> -- --
> 01 johnsmith
> 02 davebarry
>
>
> -- GET A SNAPSHOT OF THE ALL PROCESSESS AND STORE THEM IN THE TEMP
> TABLE #processlist
> Select @.@.SERVERNAME AS [SERVERNAME], GETDATE() AS [SNAPTIME], * into
> #processlist From master..sysprocesses (nolock)
>
> -- This will give me a list of all the processes running by permitted
> users:
> select * INTO #goodprocesses from #processlist PL INNER JOIN
> PermittedUsers PU ON (PL.loginame =PU.LoginName collate
> Latin1_General_CI_AS)
> -- I need a query giving me the "opposite"
> select * INTO #suspectprocesses from #processlist PL INNER JOIN
> PermittedUsers PU ON (PL.loginame <> PU.LoginName collate
> Latin1_General_CI_AS)
> -- but the above is not it.
>
> Can anyone help?
>
> THANKS!
> d.
>|||Hi
SELECT <column list> FROM TableA LEFT JOIN TableB ON TableA.pk=TableB.pk
WHERE TableB.pk IS NULL
<google@.dcbarry.com> wrote in message
news:1128642174.792462.153940@.g49g2000cwa.googlegroups.com...
> Help:
> How do I constuct a queery returning all the rows from table A that do
> NOT have a match in table B for a given column?
>
> To be more specific, I am pulling a copy of the sysprcesses table. I
> then want to report out the rows that ARE NOT in my permitted logins
> (i.e., potiential problems)
>
> PermittedUsers
> ==============
> ID LoginName
> -- --
> 01 johnsmith
> 02 davebarry
>
>
> -- GET A SNAPSHOT OF THE ALL PROCESSESS AND STORE THEM IN THE TEMP
> TABLE #processlist
> Select @.@.SERVERNAME AS [SERVERNAME], GETDATE() AS [SNAPTIME], * into
> #processlist From master..sysprocesses (nolock)
>
> -- This will give me a list of all the processes running by permitted
> users:
> select * INTO #goodprocesses from #processlist PL INNER JOIN
> PermittedUsers PU ON (PL.loginame =PU.LoginName collate
> Latin1_General_CI_AS)
> -- I need a query giving me the "opposite"
> select * INTO #suspectprocesses from #processlist PL INNER JOIN
> PermittedUsers PU ON (PL.loginame <> PU.LoginName collate
> Latin1_General_CI_AS)
> -- but the above is not it.
>
> Can anyone help?
>
> THANKS!
> d.
>

joins

hello,
I have a noob question
I have a table called Alpha
aplha_id team1 team2
and a table called Team
teamid teamname
I cannot get the correct query in order to retrieve all the data from table
Alpha where team1 to exist in table Team and team2 also to exist in table
Team
(I used the keyword in plus a subquery but it is very slow)
can u help?
Thanx in advanceP wrote on Thu, 9 Mar 2006 15:22:04 +0200:

> hello,
> I have a noob question
> I have a table called Alpha
> aplha_id team1 team2
> and a table called Team
> teamid teamname
> I cannot get the correct query in order to retrieve all the data from
> table Alpha where team1 to exist in table Team and team2 also to exist in
> table Team
> (I used the keyword in plus a subquery but it is very slow)
> can u help?
> Thanx in advance
How about
SELECT a.*
FROM Alpha a
INNER JOIN Team t1 on a.team1 = t1.teamid
INNER JOIN Team t2 on a.team2 = t2.teamid
Dan|||THANX A LOT!!!
"Daniel Crichton" <msnews@.worldofspack.co.uk> wrote in message
news:udwRIB4QGHA.5296@.tk2msftngp13.phx.gbl...
>P wrote on Thu, 9 Mar 2006 15:22:04 +0200:
>
> How about
> SELECT a.*
> FROM Alpha a
> INNER JOIN Team t1 on a.team1 = t1.teamid
> INNER JOIN Team t2 on a.team2 = t2.teamid
>
> Dan
>

Friday, March 30, 2012

Jump to URL in ReportViewer- Underline not displaying

Wednesday, March 28, 2012

Jump to report and parameters

Jump to next record insertion.

Jump to next record insertion.

Jump to different table within the same report

Jump to "No" Report

Monday, March 26, 2012

Julian Day

Julian Date

Journal table for a database

Journal table for a database

Journal table for a database

Journal table for a database

Joins with XQuery

Joins with XQuery

Joins on same table

Joins on 3 tables

Friday, March 23, 2012

JOINs and SUMs Not Giving Expected Results

Joins and inices

JOINS / Exclude rows

joins

join or subselect

Blog Archive

About Me