Jump to URL Error

I'm trying to put a hyperlink in a field on a report. I've found the action property and set the "Jump To" box with the following:

="http://dveowb01.wbhq.com/UnitsStatusLog/StatusEntry.aspx?Unit=" & Fields!Unit.Value

I'm getting this error message:

The Hyperlink expression for the textbox ‘textbox24’ refers to the field ‘Unit’. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope.

Please help.

Thanks,

Jennifer

1. Click View -> Datasets from within your report server project.
2. Expand the dataset that is associated with the table or matrix in your report that this field references.

3. If the Unit field is not in the list for that dataset, right click the dataset and then click refresh. The field should now appear in the list.

4. Rebuild and deploy the report.

|||

If the text box that you have set the property on is not in a data region (list, table, or matrix) you will need to use an aggregator to access values from a dataset. Either add the text box to a list and let the list provide you with one copy of the text box for each item in the data set or change your formula to:

Code Snippet

="http://dveowb01.wbhq.com/UnitsStatusLog/StatusEntry.aspx?Unit="&First(Fields!Unit.Value)

and the first value will be used. A real easy way to get the expression, including scoping string, is to drag the field onto your report from the Datasets tab to your report. This will create a text box with an aggregator expression (first or sum) as the value.

To create a list and bind it to your data set, add the list and then on the properties tab (hit F4 if you don't see it) set the DataSetName property to be the data set with the field 'Unit'. Add your current text box to the list by dragging it over the list and dropping it once the pointer changes to the arrow with the attached small box.

Good luck!

Larry

Jump to URL and pass parameter

I would like to jump to a URL like UPS and pass in a value (tracking number).
Is there a way to do this under the "action" of the field I would like to
pass? "Jump to Report" allows parameters but "Jump to URL" does not resolve
the actual field values.
--
Thanks in advance,
JohnHere is the specific example for the issue described. In ther Report Layout,
I select a specific field and in the properties window "action" I enter the
URL below in the "Jump to URL" option.
http://track.dhl-usa.com/TrackByNbr.asp?ShipmentNumber=%20Fields!TrackingNumber.Value
When executing the report, clicking in the actual value passes the URL
exactly as shown above instead of converting Fields!TrackingNumber.Value to
the actual value.
Is there an alternate way to accomplish this?
Thanks,
John|||You have to turn this into an expression:
="http://track.dhl-usa.com/TrackByNbr.asp?ShipmentNumber=" &
Fields!TrackingNumber.Value
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"John K" <JohnK@.discussions.microsoft.com> wrote in message
news:EABF40D1-D597-4575-B149-5FDB74FC6D0F@.microsoft.com...
> Here is the specific example for the issue described. In ther Report
Layout,
> I select a specific field and in the properties window "action" I enter
the
> URL below in the "Jump to URL" option.
>
http://track.dhl-usa.com/TrackByNbr.asp?ShipmentNumber=%20Fields!TrackingNumber.Value
> When executing the report, clicking in the actual value passes the URL
> exactly as shown above instead of converting Fields!TrackingNumber.Value
to
> the actual value.
> Is there an alternate way to accomplish this?
> Thanks,
> John
>|||Thanks Bruce, that did it.
"Bruce L-C [MVP]" wrote:
> You have to turn this into an expression:
> ="http://track.dhl-usa.com/TrackByNbr.asp?ShipmentNumber=" &
> Fields!TrackingNumber.Value
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "John K" <JohnK@.discussions.microsoft.com> wrote in message
> news:EABF40D1-D597-4575-B149-5FDB74FC6D0F@.microsoft.com...
> > Here is the specific example for the issue described. In ther Report
> Layout,
> > I select a specific field and in the properties window "action" I enter
> the
> > URL below in the "Jump to URL" option.
> >
> >
> http://track.dhl-usa.com/TrackByNbr.asp?ShipmentNumber=%20Fields!TrackingNumber.Value
> >
> > When executing the report, clicking in the actual value passes the URL
> > exactly as shown above instead of converting Fields!TrackingNumber.Value
> to
> > the actual value.
> >
> > Is there an alternate way to accomplish this?
> >
> > Thanks,
> > John
> >
> >
>
>

Jump to URL - check if valid file

Hello
I have a field in my dataset which could hold the path of a file
sometimes or would have some plain text. Before I give set that field
in the "Jump to URL", I want to check to see if it's a file and if so,
then assign that field for "Jump to URL" or leave it blank.
Is there any function to check if the value in the field is a file?
TIA
Sue..File you mean some text with ".extension" is that right ? then you can check
whether that extension exists using "INSTR" or if you are searching for the
path then you can search for "/" sign and then use "iif"
Amarnath, MCTS
"Sue" wrote:
> Hello
> I have a field in my dataset which could hold the path of a file
> sometimes or would have some plain text. Before I give set that field
> in the "Jump to URL", I want to check to see if it's a file and if so,
> then assign that field for "Jump to URL" or leave it blank.
> Is there any function to check if the value in the field is a file?
> TIA
> Sue..
>

Jump to URL

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

Jump to URL

Hi,

I want to navigate to a webpage from my report. On clicking a report field that web page should be opened. I have written the following code,

Function OpenWindow (ByVal DealerId as Integer,ByVal CustomerNo as string,ByVal InvoiceNo as string, ByVal CustomerType as string,ByVal CustomerStatus as string,ByVal AppilcationName as string,ByVal UserId as string,ByVal CallStatus as string)as String

Dim strURL as String
Dim strTargetURL as String
Dim strFeatures as String

If CustomerType = "EXTERNAL" and CustomerStatus <> "Completed"
strURL = AppilcationName & "?" & "DealerId="& DealerId &"&CustomerNo="&CustomerNo &"&InvoiceNo="&InvoiceNo & "&UserId=" & UserId & "&CallStatus=" &CallStatus

strTargetURL = "javascript:void(window.open('"
strTargetURL = strTargetURL & strURL & "'"
strTargetURL = strTargetURL & ",'',"

strFeatures = "' width=1000,height=720,scrollbars=yes, status=no, toolbar=no, resizable=no, left=0, top=0'"

strTargetURL = strTargetURL & strFeatures
strTargetURL = strTargetURL & ",'_blank'));"

End If
Return strTargetURL

End Function

Now If I click the report field my web page is opening in a new page. But if I select the option "Open in New Window" an empty page as well as my web page is being opened. How to avoid the opening of empty page.

Help of any kind to this problem is welcome

Thanks in Advance,

Sivaatzenith

It seems that you should maybe just assign the href of the url, and not use the javascript. Personally, I don't like it when a site wants to size and/or position a window with/without various options... I guess I kookie like that :)

|||

Hi Ben,

Thanks for your reply

Can you pls give a sample containing the href in jump to url code

Regards

Sivaatzenith

|||

Sivaatzenith,

My thought was that in your OpenWindow code, you omit the javascript code...

So perhaps:
strTargetURL = "javascript:void(window.open('"
strTargetURL = strTargetURL & strURL & "'"
strTargetURL = strTargetURL & ",'',"

strFeatures = "' width=1000,height=720,scrollbars=yes, status=no, toolbar=no, resizable=no, left=0, top=0'"

strTargetURL = strTargetURL & strFeatures
strTargetURL = strTargetURL & ",'_blank'));"

becomes:
strTargetURL = strURL

I am not really sure if this will work in Sql Reporting Services or not...

Good Luck

|||

hi,

Thanks for ur reply... First I had given the following code in jump to URL

STRURL ="http:mail.yahoo.com"

Mine is a web application. The reports will be launched by web application which is in .net.These reports will be shown in report viewer ctrl. This report will also contain subreport, which will be displayed on clicking of a textbox. When I click this textbox to view the sub report and after that press the link (jump to url) I am getting a java script error... Actually thats a problem with report viewer after googling I found out that microsoft has provided a hot fix for this which is extra money. So I tried this.

Any other solution is welcome.. I tired using <a href also. but of no use... I am getting a run time error.

Thanks in advance

Sivaatzenith

|||

I apologize that I am not more familiar with reporting services, what is your specific javascript error?

|||

hi Ben,

If I click the link, my desired web page is opened in a new browser. But If I select the option "Open in a new page" an empty browser as well as my page is opened. I need to avoid this empty page.

Thanks in Advance,

Sivaatzenith.

Jump to report value for parameter, cube data

Have a problem with passing information between reports using Jump to report
facility.
User clicking on field value in report to pass to second report. This works
with the second report showing the value of field user has selected in the
parameter box (Available Values set to non-queried) but report shows an error
of:
restrictions imposed by the constrained flag in the STRTOSET function were
violated
Have tried many settings to correct this but with no luck. Does anybody
know how to correct this problem.
Thanks
IanIn case this is helpful to others I have found that the field parameter was
causing the problem. I was using Fields!FieldName.Value and should have used
Fields!FieldName.UniqueName
Ian
"IanP" wrote:
> Have a problem with passing information between reports using Jump to report
> facility.
> User clicking on field value in report to pass to second report. This works
> with the second report showing the value of field user has selected in the
> parameter box (Available Values set to non-queried) but report shows an error
> of:
> restrictions imposed by the constrained flag in the STRTOSET function were
> violated
> Have tried many settings to correct this but with no luck. Does anybody
> know how to correct this problem.
> Thanks
> Ian

jump to report expression

Hello,
I have a field in my report that I want to jump to
another report but I want it to jump to different reports
depending on what is in that field. For example I click
the jump to report and put something like this in the
expression builder: Iif(Fields!Type = "a", ReportA,
ReportB) where ReportA and ReportB are two different
reports in my project. Can I do this? Everytime I try
to run it I get an error that says ReportA is not
defined. I can see it in the constants and just setting
the expression to ReportA jumps to that report. Thanks
for any help!please try IIF(Fields!Type = "a", "ReportA", "ReportB")
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Melissa" <anonymous@.discussions.microsoft.com> wrote in message
news:03eb01c49074$90d0ddf0$a401280a@.phx.gbl...
> Hello,
> I have a field in my report that I want to jump to
> another report but I want it to jump to different reports
> depending on what is in that field. For example I click
> the jump to report and put something like this in the
> expression builder: Iif(Fields!Type = "a", ReportA,
> ReportB) where ReportA and ReportB are two different
> reports in my project. Can I do this? Everytime I try
> to run it I get an error that says ReportA is not
> defined. I can see it in the constants and just setting
> the expression to ReportA jumps to that report. Thanks
> for any help!|||Cool Cool!! Thanks so much Lev.
>--Original Message--
>please try IIF(Fields!Type = "a", "ReportA", "ReportB")
>--
>This posting is provided "AS IS" with no warranties, and
confers no rights.
>
>"Melissa" <anonymous@.discussions.microsoft.com> wrote in
message
>news:03eb01c49074$90d0ddf0$a401280a@.phx.gbl...
>> Hello,
>> I have a field in my report that I want to jump to
>> another report but I want it to jump to different
reports
>> depending on what is in that field. For example I
click
>> the jump to report and put something like this in the
>> expression builder: Iif(Fields!Type = "a", ReportA,
>> ReportB) where ReportA and ReportB are two different
>> reports in my project. Can I do this? Everytime I try
>> to run it I get an error that says ReportA is not
>> defined. I can see it in the constants and just
setting
>> the expression to ReportA jumps to that report. Thanks
>> for any help!
>
>.
>

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.

Julian Dates

I want to derive a date from a string field where the string will have the date embeded in the following format YYJJJ, for example 02364 = Dec 30 2002. Is there a way to do this using Transact-Sql via Sql Server?-- If it where in a procedure
DECLARE @.strDate varchar(5)
DECLARE @.dtDate smalldatetime

SET @.strDate = '02364'

SET @.dtDate = DATEADD(dd, CAST(RIGHT(@.strDate, 3) AS integer) - 1, CONVERT(smalldatetime,'01/01/' + LEFT(@.strDate, 2), 3))

SELECT @.dtDate

-- Using within a query
CREATE TABLE #tmp_mydates (mydate varchar(5))

INSERT INTO #tmp_mydates VALUES ('02364')
INSERT INTO #tmp_mydates VALUES ('02264')
INSERT INTO #tmp_mydates VALUES ('02164')
INSERT INTO #tmp_mydates VALUES ('02004')
INSERT INTO #tmp_mydates VALUES ('02002')

SELECT mydate, DATEADD(dd, CAST(RIGHT(mydate, 3) AS integer) - 1, CONVERT(smalldatetime,'01/01/' + LEFT(mydate, 2), 3)) AS myconverteddate FROM #tmp_mydates|||I want to derive a date from a string field where the string will have the date embeded in the following format YYJJJ, for example 02364 = Dec 30 2002. Is there a way to do this using Transact-Sql via Sql Server?

Q1 [Is there a way to do this using Transact-Sql via Sql Server?]
A1 You may consider creating your own derived "JulianDate" user datatype; and also creating your own user functions or stored procedures to properly interpet, convert to and from, and generally handle them as you might require. (I've found the strategy helpful in handling similar customized Julian date formats for special applications) For example:
dbo.fn_MyJulianDate_Add,
dbo.fn_MyJulianDate_Diff,
dbo.fn_ConvertStandardDate_To_MyJulianDate, dbo.fn_ConvertMyJulianDate_To_StandardDate,
etc.

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

jtds blob size

When I try to insert more then 200Mb data into blob field (varbinary) using jtds jdbc-driver I've got an error. Connection is going closed. In fact I cant insert so much data with other tools too (TOAD for MSSQL).Could you try Microsoft JDBC driver for SQLServer 2005? This may work better. In fact we are planning a v 1.1 release this August. This release can handle larger datatypes better.

Joining two fields to single field

If I have a database table with the following columns:
ID
Other_ID
Description

And I want to join the two ID fields to one field in another table that contains the following fields:
ID
Name

How would i do that?

Here is some sample data and what I would like returned

TABLE1

ID Other_ID Description
row 1 1 2 Number1
row 2 3 1 Number2

TABLE2

ID Name
row 1 1 John
row 2 2 Bob
row 3 3 Bill

I want to query TABLE1, row 1 so that I pull back the Names for the values stored in the ID and Other_ID fields so that my results are like:
John Bob Number1

The only way around it now is that I store Other_Name in Table1.

Thanks.

try this

select (select name from Table2 t2 where t1.id=t2.id) ,
(select name from Table2 t2 where t1.Other_id=t2.id) ,Description
from Table1 t1|||

I'll take a stab at it:

SELECT T2.Name as Name1, T3.Name as Name2, Description FROM TABLE1 INNER JOIN TABLE2 AS T2 ON TABLE1.ID=T2.ID INNER JOIN TABLE2 AS T3 ON TABLE1.Other_ID=T3.ID

|||Thanks. This worked great.sql

Joining to large tables to perfrom update

I have 2 large tables that are over 11 million records each. I need to join
them on 1 field and then update 4 fields. So my script is this
update a
set a.field1= b.field1,
a.field2= b.field2,
a.field3 = b.field3,
a.field4 = bfield4
from a inner join b
on a.field5= b.field5
This query is taking a long time to run and I am wondering if there are any
join hints or lock hints that I can put in there to make it more efficient.
Any help is appreciated.an index on b(field5, field1, field2, field3, field4) might help with
this particular update.
Considering the performance of the whole system, it might or might not
be worth keeping, depending on your priorities.|||You can use
update a
set a.field1= b.field1,
a.field2= b.field2,
a.field3 = b.field3,
a.field4 = bfield4
from a inner join b with (nolock)
on a.field5= b.field5
however, for 11 million rows, it will still take a lot of time.
I would create script that executes the update in batches (Example: 1
million per batch based on field5). In other words, I would create a
"control" table where I can store the field5, the bacth number and when was
updated. This way even if any of the batch updates do not complete (for any
reason), you can start where you left off rather than start all over again.
"Andy" wrote:

> I have 2 large tables that are over 11 million records each. I need to jo
in
> them on 1 field and then update 4 fields. So my script is this
> update a
> set a.field1= b.field1,
> a.field2= b.field2,
> a.field3 = b.field3,
> a.field4 = bfield4
> from a inner join b
> on a.field5= b.field5
> This query is taking a long time to run and I am wondering if there are an
y
> join hints or lock hints that I can put in there to make it more efficient
.
> Any help is appreciated.

Joining the table relationship from different database

Hi,
Did someone know how to join the table relationship
from different database.
DB1 - Database
Customer - Table
CustID _- Field
Cust_Name - Field
DB2 - Database
CustHist - Table
HistID - Field
Cust_ID - Field
I want to enforce the relationship for this two table in
different database. Let said, I delete one customer
record in DB1. The sql server will enforce the data
integity for me.
Any ideas, Thanks
DaronHi,
You can't enforce the foreign key relationship over two separate databases.
What you need to do is create insert and update triggers on the table and do
it that way.
I hope this helps
regards
Greg O MCSD
http://www.ag-software.com/ags_scribe_index.asp. SQL Scribe Documentation
Builder, the quickest way to document your database
http://www.ag-software.com/ags_SSEPE_index.asp. AGS SQL Server Extended
Property Extended properties manager for SQL 2000
http://www.ag-software.com/IconExtractionProgram.asp. Free icon extraction
program
http://www.ag-software.com. Free programming tools
"Daron" <darontcw@.hotmail.com> wrote in message
news:104101c38e07$c11ec3c0$a001280a@.phx.gbl...
> Hi,
> Did someone know how to join the table relationship
> from different database.
> DB1 - Database
> Customer - Table
> CustID _- Field
> Cust_Name - Field
> DB2 - Database
> CustHist - Table
> HistID - Field
> Cust_ID - Field
> I want to enforce the relationship for this two table in
> different database. Let said, I delete one customer
> record in DB1. The sql server will enforce the data
> integity for me.
> Any ideas, Thanks
> Daron|||Thanks, I dont think this is an efficient way to enforce
the table relationship. Because I need to write the insert
and update triggers to my related table. How about my
related tables are many. This really make me headache.
Welcome more ideas.
Daron
>--Original Message--
>Hi,
>You can't enforce the foreign key relationship over two
separate databases.
>What you need to do is create insert and update triggers
on the table and do
>it that way.
>
>--
>I hope this helps
>regards
>Greg O MCSD
>http://www.ag-software.com/ags_scribe_index.asp. SQL
Scribe Documentation
>Builder, the quickest way to document your database
>http://www.ag-software.com/ags_SSEPE_index.asp. AGS SQL
Server Extended
>Property Extended properties manager for SQL 2000
>http://www.ag-software.com/IconExtractionProgram.asp.
Free icon extraction
>program
>http://www.ag-software.com. Free programming tools
>
>"Daron" <darontcw@.hotmail.com> wrote in message
>news:104101c38e07$c11ec3c0$a001280a@.phx.gbl...
>> Hi,
>> Did someone know how to join the table relationship
>> from different database.
>> DB1 - Database
>> Customer - Table
>> CustID _- Field
>> Cust_Name - Field
>> DB2 - Database
>> CustHist - Table
>> HistID - Field
>> Cust_ID - Field
>> I want to enforce the relationship for this two table in
>> different database. Let said, I delete one customer
>> record in DB1. The sql server will enforce the data
>> integity for me.
>> Any ideas, Thanks
>> Daron
>
>.
>|||Daron,
There is no other way to enforce relationships over different databases.
I hope this helps
regards
Greg O MCSD
http://www.ag-software.com/ags_scribe_index.asp. SQL Scribe Documentation
Builder, the quickest way to document your database
http://www.ag-software.com/ags_SSEPE_index.asp. AGS SQL Server Extended
Property Extended properties manager for SQL 2000
http://www.ag-software.com/IconExtractionProgram.asp. Free icon extraction
program
http://www.ag-software.com. Free programming tools
"Daron" <darontcw@.hotmail.com> wrote in message
news:075001c38e10$ece30df0$a301280a@.phx.gbl...
> Thanks, I dont think this is an efficient way to enforce
> the table relationship. Because I need to write the insert
> and update triggers to my related table. How about my
> related tables are many. This really make me headache.
> Welcome more ideas.
> Daron
> >--Original Message--
> >Hi,
> >You can't enforce the foreign key relationship over two
> separate databases.
> >What you need to do is create insert and update triggers
> on the table and do
> >it that way.
> >
> >
> >--
> >I hope this helps
> >regards
> >Greg O MCSD
> >http://www.ag-software.com/ags_scribe_index.asp. SQL
> Scribe Documentation
> >Builder, the quickest way to document your database
> >http://www.ag-software.com/ags_SSEPE_index.asp. AGS SQL
> Server Extended
> >Property Extended properties manager for SQL 2000
> >http://www.ag-software.com/IconExtractionProgram.asp.
> Free icon extraction
> >program
> >http://www.ag-software.com. Free programming tools
> >
> >
> >"Daron" <darontcw@.hotmail.com> wrote in message
> >news:104101c38e07$c11ec3c0$a001280a@.phx.gbl...
> >> Hi,
> >> Did someone know how to join the table relationship
> >> from different database.
> >>
> >> DB1 - Database
> >> Customer - Table
> >> CustID _- Field
> >> Cust_Name - Field
> >>
> >> DB2 - Database
> >> CustHist - Table
> >> HistID - Field
> >> Cust_ID - Field
> >>
> >> I want to enforce the relationship for this two table in
> >> different database. Let said, I delete one customer
> >> record in DB1. The sql server will enforce the data
> >> integity for me.
> >>
> >> Any ideas, Thanks
> >> Daron
> >
> >
> >.
> >sql

Joining Tables..

Hai all,
I am having three table and i need to join them...
First table : client [key field : clientid]
Second Table : Address [key field : addressid]
Third Table : contact [key field : forid]
the problem is having the third table like this :
ForId ContactTypeId ContactNo
-- -- --
ABC Phone 123
ABC Email abc@.abc.com
ABC Fax 00123456
XYZ Phone 123
XYZ Email xyz@.xyz.com
XYZ Fax 00123456
on joining i need the result should like
Clientid Address Phone Email Fax
-- -- -- -- --
123 asdcvb 123 abc@.abc.com 00123123
576 sdfsds 123 xyz@.xyz.com 00123456
Can anyone provide me the query plz?
Looking forward for the reply...
Thanx in advance..hi
send us the complete DDL and referencing key, so that we can give u an
accurate solution
best Regards,
Chandra
http://www.SQLResource.com/
http://chanduas.blogspot.com/
---
*** Sent via Developersdex http://www.examnotes.net ***|||>> Third Table : contact [key field : forid]
It is a huge problem so rather than masking the flaw with a complicated
query, restructuring the schema would be a better solution. Based on your
narrative, here is how:
CREATE TABLE new_tbl (
customer_id CHAR(3) NOT NULL PRIMARY KEY,
phone_nbr CHAR(10) NOT NULL,
email CHAR(40) NOT NULL
CHECK ( CHARINDEX( '@.', email ) > 1 )
fax CHAR(10) NOT NULL );
Now do:
INSERT new_tbl ( customer_id, phone_nbr, email, fax )
SELECT ForId,
MAX( CASE ContactTypeId WHEN 'Phone' THEN ContactNo
END ) AS "Phone",
MAX( CASE ContactTypeId WHEN 'Email' THEN ContactNo
END ) AS "Email",
MAX( CASE ContactTypeId WHEN 'Fax' THEN ContactNo
END ) AS "Fax"
FROM tbl
GROUP BY ForId ;
Once this is done sucessfully, dump the ill-designed table:
DROP TABLE contact ;
Once you have this new schema, your query should be as simple as having a
join. If due to some reason the table cannot be changed/deleted, then
consider using the SELECT portion in the above INSERT statement for warping
a short term kludge.
Anith

Joining Tables

Hi,
I have a table with fields as partnerid, contractno.
The partnerid field has the Id number which can be a supplier or a customer.
I need to get the partner id(supplier) and the partner id (customers) of that particular supplier only. I tried with self join but the data is data is replicating.

Data in table
PId ContractNo
20045 1567
435 1567
123 1567
345 1678
1004 1678

I need to display the data in the following format.

PId(Supplier) PId(Customer)
20045 1567
20045 435
20045 123
345 1678
345 1004

But I'm getting the data replicated with all records joined every record.
Give the suggestion.Your data doesn't make sense in any way that would give you the query you want? How do you know which PId is a supplier or Customer? Without some kind of a key to indicate that, there's nothing you can do about your issue.|||Hai Madhavi,
Can you show us the query that you have written?

Madhivanan

Joining tables

Hi
I would like to join two tables, one containig the names of loaded datafiles
and the date of the loaded datafile i a field called RealDate. The other
tabel is a table containg a list of all the "missing" datafiles which for
some reason wasn't loaded into the database.
The result I would like is a resultset where I get all the loaded
datafilenames as well as all the missing datafilesnames, the later must have
a flag set so I can tell them apart, the field siteid can be used for that
where e.g. -1 or -3 flags indicates not loaded and 1 or 3 flags loaded.
V2Statistik definition
CREATE TABLE [dbo].[Statistik] (
[SiteID] [int] NOT NULL ,
[TicketFileName] [char] (12) COLLATE Danish_Norwegian_CI_AS NOT NULL ,
[Realdate] [datetime] NULL ,
[Loaddate] [datetime] NULL ,
[TotalRecords] [int] NOT NULL ,
[SkippedRecords] [int] NOT NULL
) ON [PRIMARY]
a result set could look like this
1 38566.txt 2005-08-02 2005-08-25 16:14:36.863 82462 31435
-1 38572.txt 2005-08-08 0 0 0
-1 38589.txt 2005-08-25 0 0 0
3 38590.txt 2005-08-26 2005-09-12 12:44:00.557 80053 31306
1 38591.txt 2005-08-27 2005-09-12 12:44:33.997 35052 31174
-3 38592.txt 2005-08-28 0 0 0
What I have managed so far is this (the join is not correct)
DECLARE @.DateTable TABLE (RealDate DATETIME)
DECLARE @.RealDate DATETIME
SET @.RealDate = '01/01/2005'
WHILE @.RealDate BETWEEN '01/01/2005' AND GETDATE()
BEGIN INSERT INTO @.DateTable (RealDate) VALUES (@.RealDate) SET @.RealDate =
DATEADD(DAY, 1, @.RealDate) END
select * from V2Statistik where siteid=1
join '?
SELECT RealDate FROM @.DateTable dt
WHERE NOT EXISTS (SELECT * FROM V2Statistik stk WHERE stk.RealDate =
dt.RealDate)
I not sure how to join these to different tables.
Can you help?
HenryHenry,
Either you failed to include the table structure for the "other table" that
lists the missing datafiles, or I don't understand where that data is. And
I'm not sure whether [Statistik] is the first table you mention, since you
don't explain LoadDate.
If you can post more information, it would help - specifically, it would
help to post the source data that would give the result you want. You
posted the result you want, which is good, but it's impossible to suggest
a query that will give it to you without knowing how your source data
is stored.
Probably, you need something like a UNION ALL query
select ..., Realdate, Loaddate, ...
from <your first table, which contains information on loaded files>
union all
select ..., Realdate, NULL, ...
from <your second table, which contains information on not-loaded files>
The two parts of the union must have the same column structure, so you
will want to put NULL in the select list for Loaddate in the second query.
You can add a column to indicate whether the file was loaded or not, but
I don't think that's necessary, since you can tell if a file was loaded or
not by looking at whether Loaddate is NULL or not.
I don't see any need for a calendar table here, but more information may
make your requirements clearer.
Steve Kass
Drew University
Henry wrote:

>Hi
>I would like to join two tables, one containig the names of loaded datafile
s
>and the date of the loaded datafile i a field called RealDate. The other
>tabel is a table containg a list of all the "missing" datafiles which for
>some reason wasn't loaded into the database.
>The result I would like is a resultset where I get all the loaded
>datafilenames as well as all the missing datafilesnames, the later must hav
e
>a flag set so I can tell them apart, the field siteid can be used for that
>where e.g. -1 or -3 flags indicates not loaded and 1 or 3 flags loaded.
>V2Statistik definition
>CREATE TABLE [dbo].[Statistik] (
> [SiteID] [int] NOT NULL ,
> [TicketFileName] [char] (12) COLLATE Danish_Norwegian_CI_AS NOT NULL ,
> [Realdate] [datetime] NULL ,
> [Loaddate] [datetime] NULL ,
> [TotalRecords] [int] NOT NULL ,
> [SkippedRecords] [int] NOT NULL
> ) ON [PRIMARY]
>
>a result set could look like this
>1 38566.txt 2005-08-02 2005-08-25 16:14:36.863 82462 31435
>-1 38572.txt 2005-08-08 0 0 0
>-1 38589.txt 2005-08-25 0 0 0
>3 38590.txt 2005-08-26 2005-09-12 12:44:00.557 80053 31306
>1 38591.txt 2005-08-27 2005-09-12 12:44:33.997 35052 31174
>-3 38592.txt 2005-08-28 0 0 0
>What I have managed so far is this (the join is not correct)
>DECLARE @.DateTable TABLE (RealDate DATETIME)
>DECLARE @.RealDate DATETIME
>SET @.RealDate = '01/01/2005'
>WHILE @.RealDate BETWEEN '01/01/2005' AND GETDATE()
>BEGIN INSERT INTO @.DateTable (RealDate) VALUES (@.RealDate) SET @.RealDate =
>DATEADD(DAY, 1, @.RealDate) END
>
>select * from V2Statistik where siteid=1
>join '?
>SELECT RealDate FROM @.DateTable dt
>WHERE NOT EXISTS (SELECT * FROM V2Statistik stk WHERE stk.RealDate =
>dt.RealDate)
>
>I not sure how to join these to different tables.
>Can you help?
>Henry
>
>
>|||I think we are missing a table with the "missing stuff" in it.
Do you know the names of the files to be loaded into the database in
advance? I would assume so, if you have set up proper system for a
data warehouse load.|||Hi
Sorry that I'm unclear about this.
I have one table which is a log of all the tables which has been
successfully loaded into the database.
SiteID, TicketFileName and RealDate are the important fields at present.
It looks like this
CREATE TABLE [dbo].[V2Statistik] (
[SiteID] [int] NOT NULL ,
[TicketFileName] [char] (12) COLLATE Danish_Norwegian_CI_AS NOT NULL ,
[Realdate] [datetime] NULL ,
[Loaddate] [datetime] NULL ,
[TotalRecords] [int] NOT NULL ,
[SkippedRecords] [int] NOT NULL
) ON [PRIMARY]
I have another table where I simpely shows the missing files from a given
date, that is files which for some reasons could not be read or loaded into
the database.
That table is a date table purpolated with all dates from the given date.
DECLARE @.DateTable TABLE (RealDate DATETIME)
I then select all dates where realdate does not exist in the V2statistik
(previously called statistik by mistake)
The result set from a select * from V2statistik would look like this
1 38588.txt 2005-08-24 2005-08-25 16:14:36.863 82462 31435
1 38590.txt 2005-08-26 2005-09-12 12:44:00.557 80053 31306
1 38591.txt 2005-08-27 2005-09-12 12:44:33.997 35052 31174
3 38588.txt 2005-08-24 2005-08-25 16:14:36.863 82462 31435
3 38590.txt 2005-08-26 2005-09-12 12:44:00.557 80053 31306
3 38591.txt 2005-08-27 2005-09-12 12:44:33.997 35052 31174
The result set from DateTable would look like this
2005-01-01
2005-01-02
2005-01-03
...
2005-10-23
I would like a result set of the joined or unioned tables that looks like
this
1 38588.txt 2005-08-24 2005-08-25 16:14:36.863 82462 31435
1 38589.txt 2005-08-25 0 0 0
1 38590.txt 2005-08-26 2005-09-12 12:44:00.557 80053 31306
1 38591.txt 2005-08-27 2005-09-12 12:44:33.997 35052 31174
3 38566.txt 2005-08-24 2005-08-25 16:14:36.863 82462 31435
3 38589.txt 2005-08-25 0 0 0
3 38590.txt 2005-08-26 2005-09-12 12:44:00.557 80053 31306
3 38591.txt 2005-08-27 2005-09-12 12:44:33.997 35052 31174
So I would like to select * from V2Statistik and "merge" it with dates from
the DateTable where the date doesnt exist in V2statistik, the merged
(missing dates) row should have the
Loaddate set to 0 (zero, null, nil) I have changed strategy on the I think
it's easier.
But I see a problem since the realdate will occur as many times as there are
sites (siteid) siteid + realdate is the unique primary key.
Does this clarify it?
regards
Henry|||On Mon, 24 Oct 2005 14:16:58 +0200, henry wrote:
(snip)
>I would like a result set of the joined or unioned tables that looks like
>this
>1 38588.txt 2005-08-24 2005-08-25 16:14:36.863 82462 31435
>1 38589.txt 2005-08-25 0 0 0
>1 38590.txt 2005-08-26 2005-09-12 12:44:00.557 80053 31306
>1 38591.txt 2005-08-27 2005-09-12 12:44:33.997 35052 31174
>3 38566.txt 2005-08-24 2005-08-25 16:14:36.863 82462 31435
>3 38589.txt 2005-08-25 0 0 0
>3 38590.txt 2005-08-26 2005-09-12 12:44:00.557 80053 31306
>3 38591.txt 2005-08-27 2005-09-12 12:44:33.997 35052 31174
(snip)
Hi Henry,
I think that the query below is quite close (*) to what you want. It's
too bad that you didn't post any INSERT statements to give me working
data, so I couldn;t test it - but check if it suits your need.
(*) I deliberately left the filename column NULL for the missing files,
for two reasons:
a. logical (if the file is missing, you obviously can't tell the name)
b. attempting to find the numeric part of another file name, increasing
that with the result of a DATEDIFF function and adding back the .txt
part, though possible, would be extremely messy, and it would make the
query very vulnerable for malformed filenames.
SELECT s.SiteID, v.TicketFileName, d.RealDate,
COALESCE(v.LoadDate, 0) AS LoadDate,
COALESCE(v.TotalRecords, 0) AS TotalRecords,
COALESCE(v.SkippedRecords, 0) AS SkippedRecords
FROM (SELECT DISTINCT SiteID
FROM V2Statistik) AS s
CROSS JOIN @.DateTable AS d
LEFT JOIN V2Statistik AS v
ON v.SiteID = s.SiteID
AND v.RealDate = d.RealDate
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hi Hugo
Thanks for your help ;o)
However, when I use that script the I don't get any output at all, only
"The command(s) completed successfully."
Which of cause is a positiv thing, not syntax error.
I can send the data as a seperate post with CSV data (don't realy know how
to make the insert data) the table structure is already posted above, do you
think you can use that?
This the complete script so far.
DECLARE @.DateTable TABLE (RealDate DATETIME)
DECLARE @.RealDate DATETIME
SET @.RealDate = '01/01/2005'
WHILE @.RealDate BETWEEN '01/01/2005' AND GETDATE()
BEGIN INSERT INTO @.DateTable (RealDate) VALUES (@.RealDate) SET @.RealDate =
DATEADD(DAY, 1, @.RealDate) END
SELECT s.SiteID, v.TicketFileName, d.RealDate,
COALESCE(v.LoadDate, 0) AS LoadDate,
COALESCE(v.TotalRecords, 0) AS TotalRecords,
COALESCE(v.SkippedRecords, 0) AS SkippedRecords
FROM (SELECT DISTINCT SiteID
FROM V2Statistik) AS s
CROSS JOIN @.DateTable AS d
LEFT JOIN V2Statistik AS v
ON v.SiteID = s.SiteID
AND v.RealDate = d.RealDate
Cheers
Henry|||On Wed, 26 Oct 2005 17:10:19 +0200, Henry wrote:

>Hi Hugo
>Thanks for your help ;o)
>However, when I use that script the I don't get any output at all, only
>"The command(s) completed successfully."
>Which of cause is a positiv thing, not syntax error.
Hi Henry,
Whether syntax error or incorrect results - a bug is a bug, and in dire
need of squashing. (In fact, syntax errors are often EASIER to locate
and correct).

>I can send the data as a seperate post with CSV data (don't realy know how
>to make the insert data) the table structure is already posted above, do yo
u
>think you can use that?
I possibly could, if I could afford to spend a few hours playing around
with bcp or trying to import through Excel to SQL Server - but there is
only so much time I can spend in these groups, and I like to help as
many people as I can in that limited time.
Below is a link to a script that will generate INSERT statements from
the data currently in your table. See if that helps you (and if not,
then you can always manually type the INSERT statements for five or ten
rows of sample data).
http://vyaskn.tripod.com/code.htm#inserts
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Joining on more than 1 field ?

Hi,
Is it possilbe to join 2 tables on more than one field ?
Like
SELECT *
FROM tblONE A JOIN tblTWO B ON
(a.one = b.one AND a.two = b.two AND a.three = c.three);
I tried this and would not run.
ANy suggestions ?What was the error?

Are table names case sensitive in SQL Server? If so, you may need to edit your alias'.

Matt. :)|||Originally posted by Mincer
What was the error?

Are table names case sensitive in SQL Server? If so, you may need to edit your alias'.

Matt. :)

Oh

thanks..
must've just mistyped...
tired =)

thanks again..

u have any suggestions for my other post 'finding distinct combinations'

http://dbforums.com/t916080.html|||SELECT *
FROM tblONE A JOIN tblTWO B ON
(a.one = b.one AND a.two = b.two AND a.three = c.three);

You're referencing an alias 'c' ... 'a.three = c.three' that you haven't created. Should it be 'b' instead? I don't think case is an issue with an Alias, and I don't think you need the parens either.

JOINing on derived tables?

I have an UPDATE query that sets a "quantity" field in a table based
on the sum of events in another table. Those events are "basketed"
into different accounts through a four-way tupple, (acctId, portcode,
mgrgpcode, invpgm).
UPDATE requires the use of a derived table when using aggregates, fair
enough. The problem is that the interior derived table query is very
expensive, yet only a few rows of the returned recordset match in the
outer table. Without artificial limits, the query takes on the order
of 30 seconds, when the entire query batch otherwise takes about 5 to
10.
Here is the query in question (tpPNL means "temporary profit 'n
loss") . tpHPL already contains a number of rows for various accounts,
ONE of these rows is in an account that needs the complex calculation
of the inner query. Yet when the query runs, it does so for every
record in tblTrades, which has 2 million+ rows. I have artificially
introduced a WHERE constraint to limit this down for testing purposes,
but this is far from ideal. What should happen is that the inner query
will return one row for every (acctId, portcode, mgrgpcode, invpgm)
tupple in the outer table (tpHPL).
I realize I can do another sub-select on tpHPL and return a list of
which of those tupples is being used, but this strikes me as yet
another performance hit. Is there some easy way to have the inner
JOINed on the outer so this "just happens"?
UPDATE tpPNL SET
openingMVLocalCcy = s.openingMV,
closingMVLocalCcy = s.closingMV,
openingMVAcctCcy = s.openingMV * h.openingFX,
closingMVAcctCcy = s.closingMV * h.closingFX
FROM tpPNL h JOIN
(SELECT acctId, portcode, mgrgpcode, invpgm,
SUM(
CASE
WHEN TranDate>@.startDate THEN 0
ELSE amount
END) as openingMV,
SUM(
CASE
WHEN TranDate>@.endDate THEN 0
ELSE amount
END) as closingMV
FROM tblTrades
WHERE deleted=0
AND portcode=400
GROUP BY acctId, portcode, mgrgpcode, invpgm) as s
ON s.acctId=h.accountId AND s.portcode=h.portfolioId AND
s.mgrgpcode=h.groupIdI would write an EXISTS test in the subquery that checks for matches
in tpHPL. If there are really as few matches as you say it should pay
off. Alternately, it might be possible to simply JOIN tblTrades and
tpHPL in the subquery, though that would only work if the set of join
columns constitutes the full key to tpHPL.
Not that it sounds like you need me to tell you how to do that, just
saying that is what I would do.
Roy Harvey
Beacon Falls, CT
On Wed, 30 Apr 2008 08:02:13 -0700 (PDT), Maury Markowitz
<maury.markowitz@.gmail.com> wrote:
>I have an UPDATE query that sets a "quantity" field in a table based
>on the sum of events in another table. Those events are "basketed"
>into different accounts through a four-way tupple, (acctId, portcode,
>mgrgpcode, invpgm).
>UPDATE requires the use of a derived table when using aggregates, fair
>enough. The problem is that the interior derived table query is very
>expensive, yet only a few rows of the returned recordset match in the
>outer table. Without artificial limits, the query takes on the order
>of 30 seconds, when the entire query batch otherwise takes about 5 to
>10.
>Here is the query in question (tpPNL means "temporary profit 'n
>loss") . tpHPL already contains a number of rows for various accounts,
>ONE of these rows is in an account that needs the complex calculation
>of the inner query. Yet when the query runs, it does so for every
>record in tblTrades, which has 2 million+ rows. I have artificially
>introduced a WHERE constraint to limit this down for testing purposes,
>but this is far from ideal. What should happen is that the inner query
>will return one row for every (acctId, portcode, mgrgpcode, invpgm)
>tupple in the outer table (tpHPL).
>I realize I can do another sub-select on tpHPL and return a list of
>which of those tupples is being used, but this strikes me as yet
>another performance hit. Is there some easy way to have the inner
>JOINed on the outer so this "just happens"?
>UPDATE tpPNL SET
> openingMVLocalCcy = s.openingMV,
> closingMVLocalCcy = s.closingMV,
> openingMVAcctCcy = s.openingMV * h.openingFX,
> closingMVAcctCcy = s.closingMV * h.closingFX
>FROM tpPNL h JOIN
>(SELECT acctId, portcode, mgrgpcode, invpgm,
> SUM(
> CASE
> WHEN TranDate>@.startDate THEN 0
> ELSE amount
> END) as openingMV,
>SUM(
> CASE
> WHEN TranDate>@.endDate THEN 0
> ELSE amount
> END) as closingMV
> FROM tblTrades
> WHERE deleted=0
> AND portcode=400
>GROUP BY acctId, portcode, mgrgpcode, invpgm) as s
> ON s.acctId=h.accountId AND s.portcode=h.portfolioId AND
>s.mgrgpcode=h.groupId|||On Apr 30, 11:41=A0am, "Roy Harvey (SQL Server MVP)"
<roy_har...@.snet.net> wrote:
> off. =A0Alternately, it might be possible to simply JOIN tblTrades and
> tpHPL in the subquery, though that would only work if the set of join
> columns constitutes the full key to tpHPL.
Can you give me a simple example of this? I was thinking of something
like...
WHERE acctId IN (select distinct acctId from tpHPL)
AND portcode IN (select distinct portfolio from tpHPL)
but that seems expensive!
Maury|||Your approach of using two independent IN clauses is incorrect.
Imagine that we had two rows of data in tpHPL:
acctId portcode
ABC XYZ
BCD MNO
Using two independent IN clauses that would match any of four
combinations:
ABC XYZ
ABC MNO
BCD MNO
BCD XYZ
What I suggest instead is to use an EXISTS test in the subquery.
UPDATE tpPNL
SET openingMVLocalCcy = s.openingMV,
closingMVLocalCcy = s.closingMV,
openingMVAcctCcy = s.openingMV * h.openingFX,
closingMVAcctCcy = s.closingMV * h.closingFX
FROM tpPNL h
JOIN (SELECT acctId, portcode, mgrgpcode, invpgm,
SUM(CASE WHEN TranDate > @.startDate
THEN 0
ELSE amount
END) as openingMV,
SUM(CASE WHEN TranDate > @.endDate
THEN 0
ELSE amount
END) as closingMV
FROM tblTrades
WHERE deleted = 0
AND portcode = 400
AND EXISTS
(SELECT * FROM tpPNL as X
WHERE tblTrades.acctId = X.accountId
AND tblTrades.portcode = X.portfolioId
AND tblTrades.mgrgpcode = X.groupId)
GROUP BY acctId, portcode, mgrgpcode, invpgm) as s
ON s.acctId = h.accountId
AND s.portcode = h.portfolioId
AND s.mgrgpcode = h.groupId
Roy Harvey
Beacon Falls, CT
On Wed, 30 Apr 2008 11:28:02 -0700 (PDT), Maury Markowitz
<maury.markowitz@.gmail.com> wrote:
>On Apr 30, 11:41 am, "Roy Harvey (SQL Server MVP)"
><roy_har...@.snet.net> wrote:
>> off. Alternately, it might be possible to simply JOIN tblTrades and
>> tpHPL in the subquery, though that would only work if the set of join
>> columns constitutes the full key to tpHPL.
>Can you give me a simple example of this? I was thinking of something
>like...
>WHERE acctId IN (select distinct acctId from tpHPL)
> AND portcode IN (select distinct portfolio from tpHPL)
>but that seems expensive!
>Maury

Joining fields

Hello,
I Hope one of you can help me with the following:
I try to join three fields from a table into one output field: month,
day, year ==> date.
And I can't get it tow work right. The datatype of the fields is
numeric. Since this query is nested into another one the datatype of
the outputfield should be DATETIME.
I already tried google but it could not help me,
Thanks in advance
Jean-Paul Rijnsburger (Netherlands)Try:
declare
@.year numeric
, @.month numeric
, @.day numeric
select
@.year = 2006
, @.month = 1
, @.day = 2
select
convert (datetime, str (@.year) +'/' + str (@.month) + '/' + str (@.day))
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Jean-Paul Rijnsburger" <Jeepee75@.gmail.com> wrote in message
news:1136207889.492149.133070@.o13g2000cwo.googlegroups.com...
Hello,
I Hope one of you can help me with the following:
I try to join three fields from a table into one output field: month,
day, year ==> date.
And I can't get it tow work right. The datatype of the fields is
numeric. Since this query is nested into another one the datatype of
the outputfield should be DATETIME.
I already tried google but it could not help me,
Thanks in advance
Jean-Paul Rijnsburger (Netherlands)|||It works,
Thanks Tom|||Tom Moreau (tom@.dont.spam.me.cips.ca) writes:
> Try:
> declare
> @.year numeric
> , @.month numeric
> , @.day numeric
> select
> @.year = 2006
> , @.month = 1
> , @.day = 2
> select
> convert (datetime, str (@.year) +'/' + str (@.month) + '/' + str (@.day))
This may produce different result depending on language and dateformat
settings. Add a third parameter to control the interpreration:
select
convert (datetime, str (@.year) +'/' + str (@.month) + '/' + str (@.day),
111)
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