Jump to URL - Passing Parameter Issue with special symbols

Hi Microsoft,
I've currently using the Jump to URL Syntax (see below) to open a new window
and pop up a drilldown report in IE.
The main problem i'm having with the syntax below iswhen the account manager
name (ACCTMGR Parameter string) contains a umlaut (german name with a double
o on top of a letter), the drilldown report runs but it doesn't run the
report details (report is blank) when information does exist. I've tried
search for resolutions online and cam up with nothing.
In addition, when a parenthesis and or an ampersand is present in the
parameter, it does not run the drilldown subreport. Your help would be
greatly appreciated.
Can you help?
=iif(sum(Fields!TotalAmount.Value)=0,"","javascript:void(window.open('http://ourreportserveralias/reportserver?%2fDrillDown+Reports%2fMarketing+Campaign+Report+Details&rc:Parameters=false&rptCurrency="
& Parameters!rptCurrency.Value & "&Division=" &
Join(Parameters!Division.Value, "&Division=") & "&DivSales=" &
Join(Parameters!DivSales.Value, "&DivSales=") & "&DeptSales=" &
Join(Parameters!DeptSales.Value, "&DeptSales=") & "&ProductLIne=" &
Join(Parameters!ProductLIne.Value, "&ProductLIne=") & "&startdate=" &
Parameters!startdate.Value & "&enddate=" & Parameters!enddate.Value &
"&ACCTMGR=" & ReportItems("ACCTMGR1").Value & "&CAMP=LEFT+BLANK" &
"','_blank','location=no,toolbar=no,left=100,top=100,height=600,width=800,resizable=yes'))")My guess is you have to URL encode this. Here is a link. You might be able
to use HttpUtility.UrlEncode in your expression. However, if it doesn't then
you will need to use code behind reports.
http://weblogs.asp.net/psteele/pages/7509.aspx
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"RS2005Developer" <RS2005Developer@.discussions.microsoft.com> wrote in
message news:2BDFEDDE-C685-4224-91A1-489DF67E757D@.microsoft.com...
> Hi Microsoft,
> I've currently using the Jump to URL Syntax (see below) to open a new
> window
> and pop up a drilldown report in IE.
> The main problem i'm having with the syntax below iswhen the account
> manager
> name (ACCTMGR Parameter string) contains a umlaut (german name with a
> double
> o on top of a letter), the drilldown report runs but it doesn't run the
> report details (report is blank) when information does exist. I've tried
> search for resolutions online and cam up with nothing.
> In addition, when a parenthesis and or an ampersand is present in the
> parameter, it does not run the drilldown subreport. Your help would be
> greatly appreciated.
> Can you help?
> =iif(sum(Fields!TotalAmount.Value)=0,"","javascript:void(window.open('http://ourreportserveralias/reportserver?%2fDrillDown+Reports%2fMarketing+Campaign+Report+Details&rc:Parameters=false&rptCurrency="
> & Parameters!rptCurrency.Value & "&Division=" &
> Join(Parameters!Division.Value, "&Division=") & "&DivSales=" &
> Join(Parameters!DivSales.Value, "&DivSales=") & "&DeptSales=" &
> Join(Parameters!DeptSales.Value, "&DeptSales=") & "&ProductLIne=" &
> Join(Parameters!ProductLIne.Value, "&ProductLIne=") & "&startdate=" &
> Parameters!startdate.Value & "&enddate=" & Parameters!enddate.Value &
> "&ACCTMGR=" & ReportItems("ACCTMGR1").Value & "&CAMP=LEFT+BLANK" &
> "','_blank','location=no,toolbar=no,left=100,top=100,height=600,width=800,resizable=yes'))")|||Hi Bruce,
Thanks for the quick reply!
I've tried referencing System.Web.HttpUtility.UrlEncode (and also just
HttpUtility.UrlEncode) in the Jump to URL syntax as such:
=iif(sum(Fields!TotalAmount.Value)=0,"","javascript:void(window.open('http://myserveralias/reportserver?%2fDrillDown+Reports%2fMarketing+Campaign+Opportunity+Report+Details&rc:Parameters=false&rptCurrency="
& Parameters!rptCurrency.Value & "&Division=" &
Join(Parameters!Division.Value, "&Division=") & "&DivSales=" &
Join(Parameters!DivSales.Value, "&DivSales=") & "&DeptSales=" &
Join(Parameters!DeptSales.Value, "&DeptSales=") & "&ProductLIne=" &
Join(Parameters!ProductLIne.Value, "&ProductLIne=") & "&startdate=" &
Parameters!startdate.Value & "&enddate=" & Parameters!enddate.Value &
"&ACCTMGR=" & System.Web.HttpUtility.UrlEncode(ReportItems("ACCTMGR1").Value)
& "&CAMP=LEFT+BLANK" &
"','_blank','location=no,toolbar=no,left=100,top=100,height=600,width=1200,resizable=yes'))")
But no luck it doesn't work. It actually doesn't recognize the UrlEncode
syntax and I've also added the Reference System.Web as well to ensure
everything was referenced properly.
I'm a little new to VB so if you don't mind, I would greatly appreciate it
if you can be a little more specific on what needs to be completed iwth the
code and show an example of how would i reference it in the Jump to URL
expression below?
Thank you in advance for your help.
"Bruce L-C [MVP]" wrote:
> My guess is you have to URL encode this. Here is a link. You might be able
> to use HttpUtility.UrlEncode in your expression. However, if it doesn't then
> you will need to use code behind reports.
> http://weblogs.asp.net/psteele/pages/7509.aspx
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "RS2005Developer" <RS2005Developer@.discussions.microsoft.com> wrote in
> message news:2BDFEDDE-C685-4224-91A1-489DF67E757D@.microsoft.com...
> > Hi Microsoft,
> >
> > I've currently using the Jump to URL Syntax (see below) to open a new
> > window
> > and pop up a drilldown report in IE.
> >
> > The main problem i'm having with the syntax below iswhen the account
> > manager
> > name (ACCTMGR Parameter string) contains a umlaut (german name with a
> > double
> > o on top of a letter), the drilldown report runs but it doesn't run the
> > report details (report is blank) when information does exist. I've tried
> > search for resolutions online and cam up with nothing.
> >
> > In addition, when a parenthesis and or an ampersand is present in the
> > parameter, it does not run the drilldown subreport. Your help would be
> > greatly appreciated.
> >
> > Can you help?
> >
> > =iif(sum(Fields!TotalAmount.Value)=0,"","javascript:void(window.open('http://ourreportserveralias/reportserver?%2fDrillDown+Reports%2fMarketing+Campaign+Report+Details&rc:Parameters=false&rptCurrency="
> > & Parameters!rptCurrency.Value & "&Division=" &
> > Join(Parameters!Division.Value, "&Division=") & "&DivSales=" &
> > Join(Parameters!DivSales.Value, "&DivSales=") & "&DeptSales=" &
> > Join(Parameters!DeptSales.Value, "&DeptSales=") & "&ProductLIne=" &
> > Join(Parameters!ProductLIne.Value, "&ProductLIne=") & "&startdate=" &
> > Parameters!startdate.Value & "&enddate=" & Parameters!enddate.Value &
> > "&ACCTMGR=" & ReportItems("ACCTMGR1").Value & "&CAMP=LEFT+BLANK" &
> > "','_blank','location=no,toolbar=no,left=100,top=100,height=600,width=800,resizable=yes'))")
>
>|||I suggest creating a report with a single textbox in it. Set your expression
so you can see the result. Next read up on using the code behind reports:
Using Custom Code References in Expressions (Reporting Services)
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/rptsrvr9/html/13fa8c92-0471-4b2f-b97c-9ebbcb13797f.htm
Your expression will look like this:
=Code.URLEncode("This is a test")URLEncode is a function you have written
that takes a single parameter and returns the encoded string.-- Bruce
Loehle-CongerMVP SQL Server Reporting Services
"RS2005Developer" <RS2005Developer@.discussions.microsoft.com> wrote in
message news:AA9EC05C-F668-4E99-AB58-48968CF9E909@.microsoft.com...
> Hi Bruce,
> Thanks for the quick reply!
> I've tried referencing System.Web.HttpUtility.UrlEncode (and also just
> HttpUtility.UrlEncode) in the Jump to URL syntax as such:
> =iif(sum(Fields!TotalAmount.Value)=0,"","javascript:void(window.open('http://myserveralias/reportserver?%2fDrillDown+Reports%2fMarketing+Campaign+Opportunity+Report+Details&rc:Parameters=false&rptCurrency="
> & Parameters!rptCurrency.Value & "&Division=" &
> Join(Parameters!Division.Value, "&Division=") & "&DivSales=" &
> Join(Parameters!DivSales.Value, "&DivSales=") & "&DeptSales=" &
> Join(Parameters!DeptSales.Value, "&DeptSales=") & "&ProductLIne=" &
> Join(Parameters!ProductLIne.Value, "&ProductLIne=") & "&startdate=" &
> Parameters!startdate.Value & "&enddate=" & Parameters!enddate.Value &
> "&ACCTMGR=" &
> System.Web.HttpUtility.UrlEncode(ReportItems("ACCTMGR1").Value)
> & "&CAMP=LEFT+BLANK" &
> "','_blank','location=no,toolbar=no,left=100,top=100,height=600,width=1200,resizable=yes'))")
> But no luck it doesn't work. It actually doesn't recognize the UrlEncode
> syntax and I've also added the Reference System.Web as well to ensure
> everything was referenced properly.
> I'm a little new to VB so if you don't mind, I would greatly appreciate it
> if you can be a little more specific on what needs to be completed iwth
> the
> code and show an example of how would i reference it in the Jump to URL
> expression below?
> Thank you in advance for your help.
> "Bruce L-C [MVP]" wrote:
>> My guess is you have to URL encode this. Here is a link. You might be
>> able
>> to use HttpUtility.UrlEncode in your expression. However, if it doesn't
>> then
>> you will need to use code behind reports.
>> http://weblogs.asp.net/psteele/pages/7509.aspx
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "RS2005Developer" <RS2005Developer@.discussions.microsoft.com> wrote in
>> message news:2BDFEDDE-C685-4224-91A1-489DF67E757D@.microsoft.com...
>> > Hi Microsoft,
>> >
>> > I've currently using the Jump to URL Syntax (see below) to open a new
>> > window
>> > and pop up a drilldown report in IE.
>> >
>> > The main problem i'm having with the syntax below iswhen the account
>> > manager
>> > name (ACCTMGR Parameter string) contains a umlaut (german name with a
>> > double
>> > o on top of a letter), the drilldown report runs but it doesn't run the
>> > report details (report is blank) when information does exist. I've
>> > tried
>> > search for resolutions online and cam up with nothing.
>> >
>> > In addition, when a parenthesis and or an ampersand is present in the
>> > parameter, it does not run the drilldown subreport. Your help would be
>> > greatly appreciated.
>> >
>> > Can you help?
>> >
>> > =iif(sum(Fields!TotalAmount.Value)=0,"","javascript:void(window.open('http://ourreportserveralias/reportserver?%2fDrillDown+Reports%2fMarketing+Campaign+Report+Details&rc:Parameters=false&rptCurrency="
>> > & Parameters!rptCurrency.Value & "&Division=" &
>> > Join(Parameters!Division.Value, "&Division=") & "&DivSales=" &
>> > Join(Parameters!DivSales.Value, "&DivSales=") & "&DeptSales=" &
>> > Join(Parameters!DeptSales.Value, "&DeptSales=") & "&ProductLIne=" &
>> > Join(Parameters!ProductLIne.Value, "&ProductLIne=") & "&startdate=" &
>> > Parameters!startdate.Value & "&enddate=" & Parameters!enddate.Value &
>> > "&ACCTMGR=" & ReportItems("ACCTMGR1").Value & "&CAMP=LEFT+BLANK" &
>> > "','_blank','location=no,toolbar=no,left=100,top=100,height=600,width=800,resizable=yes'))")
>>

Join two table from different database. URGENT!

Hi,

anyone could help me on how to do the inner join of two table from two different database. Below is my scripts:

Dim sqlconnection1 As New SqlConnection(ConfigurationSettings.AppSettings("appDSN"))

Dim sqlCommand2 As New SqlCommand("", SqlConnection1)

Dim sqlconnection2 As New SqlConnection(ConfigurationSettings.AppSettings("smDSN"))

Dim sqlCommandSM2 as new sqlCommand("", sqlconnectionSM2)

Sub Page_Load(Source as Object, E as EventArgs)

sqlCommand2.CommandText = "Select * from invitation inner join Guest on invitation.guestid = Guest.Guestidinner join Department on Department.departmentcode = Guest.deptCode where eventcode = '" & ecode & "'"

sqlconnection1.Open()

.....................

The invitation table is from SqlConnection1 and the Department table is from the sqlconnection2. How to do this . Pls .. pls help me.

Ann123

In SQL Server, you can refer to tables in a different database inside the SQL using the appropriate syntax.

DatabaseName.Owner.TableName

For instance:

MyOtherDatabase.dbo.Guest

This presumes that the user specified has rights to both the default database in the connection as well as the second database.

|||

thanks so much douglas for your reply.

I know what you mean. In fact, i've done that in asp and its working well. However, i don't know how to do the syntax inasp.NET since i have to do 2 different connection and join them together in select statement using the inner join.

I ever try below syntax, but still got error.

Dim sqlconnection1 As New SqlConnection(ConfigurationSettings.AppSettings("appDSN"))

Dim sqlCommand2 As New SqlCommand("", SqlConnection1)

Dim sqlconnectionSM2 As New SqlConnection(ConfigurationSettings.AppSettings("smDSN"))

Dim sqlCommandSM2 as new sqlCommand("", sqlconnectionSM2)

sqlCommand2.CommandText = "Select * from invitation inner join Guest on invitation.guestid = Guest.Guestid inner joinsqlCommandSM2.SM2 .dbo.Department on Department.departmentcode = Guest.deptCode where eventcode = '" & ecode & "'"

TheSM2 is my Database andDepartment is refer to the table in SM2 Db.

Anybody know how to do the correct syntax??

thnks

Ann123

|||

Inside your SQL statement, you cannot reference you sqlCommandSM2, because that is a VB.NET variable, and the SQL Server getting the command will not know what that is. A simplified SQL statement using two databases is (presuming SM2 is the name of your other database):

sqlCommand2.CommandText = "Select * from invitation inner join Guest on invitation.guestid = Guest.Guestid inner joinSM2.dbo.Department onSM2.dbo.Department.departmentcode = Guest.deptCode where eventcode = '" & ecode & "'"

|||

hi douglas.. thanks once again for your reply.

i've done that but the problem now is,how to bindthe 2 sqlcommand when it executed since it connected to different sqlconnection and sqlcommand.

sqlCommand2.CommandText = "Select * from invitation inner join Guest on invitation.guestid = Guest.Guestid inner joinSM2.dbo.DepartmentonSM2.dbo.Department.departmentcode = Guest.deptCode where eventcode = '" & ecode & "'"

sqlconnection1.Open()

Dim employee2 As SqlDataReader =sqlCommand2.ExecuteReader()

.....

The sqlCommand2 is refer to sqlconnection1 whereas the second sqlCommandSM is refer to sqlconnection2 (connection for the SM2).

i've tried to do like this (below), so that it execute the same connection concurrently but got error.

Dim employee2 As SqlDataReader =sqlCommand2.ExecuteReader()

Dim employee2 As SqlDataReader =sqlCommandSM2.ExecuteReader()

How should i do the syntax ?

thanks.

Ann123

Join tables across databases?

In my SProc, I am trying to perform a JOIN on 2 tables that are located in
seperate databases...
...Is this possible?
In the SProc below, the focus is on joining dbo.customer.postalcode WITH pubs.
city_zipcodes.ZIP_CODE... That is, I have a table of zipcodes and cities
located in the "Pubs" database, while the "Customer.postalcode" table is
located in the "VCMS" database. During testing in my VBA application, I am
returning an error of: "Invalid Object Name city_zipcodes" -- which leads me
to believe that I am incorrectly performing this table join.
Any help or suggestions are greatly appreciated!
James
--***StoredProc Sample
CREATE PROCEDURE AO_GetServiceRequests
(@.Date1datetime=null,
@.Date2datetime=null)
AS Set Nocount On
--return specified ServiceRequest if Date and Status is supplied
if @.Date1 is not null
begin
SELECT
service_request.serial,
service_request.record_date,
service_request.customer,
location.latitude,
location.longitude,
location.zone,
customer.first_name,
customer.last_name,
customer.phone_day,
customer.phone_eve,
customer.e_mail,
customer.address,
customer.postalcode,
customer.primary_loc,
supervisor.shortkey AS supervisor,
technician.shortkey AS tech,
pubs.city_zipcodes.ZIP_CODE, --from the zipcode tables in the
pubs db
pubs.city_zipcodes.CITY --from the zipcode tables
in the pubs db
FROM
dbo.service_request
INNER JOIN dbo.customer ON dbo.service_request.customer = dbo.
customer.serial
INNER JOIN dbo.location ON dbo.service_request.location = dbo.
location.loc_no
INNER JOIN dbo.employee As technician ON dbo.service_request.
serv_by = technician.serial
INNER JOIN dbo.employee As supervisor ON dbo.service_request.
serv_supervisor = supervisor.serial
INNER JOIN city_zipcodes ON dbo.customer.postalcode =
city_zipcodes.zipcode --this is the join I am trying to accomplish
where
(dbo.service_request.record_date >= CONVERT(DATETIME, @.Date1, 102)) AND (dbo.
service_request.record_date <= CONVERT(DATETIME, @.Date2, 102))
Set Nocount Off
return 0
end
GO
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200507/1
Try these examples:
http://vyaskn.tripod.com/programming_faq.htm#q13
http://vyaskn.tripod.com/programming_faq.htm#q14
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"j c via droptable.com" <forum@.droptable.com> wrote in message
news:514309300509D@.droptable.com...
In my SProc, I am trying to perform a JOIN on 2 tables that are located in
seperate databases...
...Is this possible?
In the SProc below, the focus is on joining dbo.customer.postalcode WITH
pubs.
city_zipcodes.ZIP_CODE... That is, I have a table of zipcodes and cities
located in the "Pubs" database, while the "Customer.postalcode" table is
located in the "VCMS" database. During testing in my VBA application, I am
returning an error of: "Invalid Object Name city_zipcodes" -- which leads
me
to believe that I am incorrectly performing this table join.
Any help or suggestions are greatly appreciated!
James
--***StoredProc Sample
CREATE PROCEDURE AO_GetServiceRequests
(@.Date1 datetime=null,
@.Date2 datetime=null)
AS Set Nocount On
--return specified ServiceRequest if Date and Status is supplied
if @.Date1 is not null
begin
SELECT
service_request.serial,
service_request.record_date,
service_request.customer,
location.latitude,
location.longitude,
location.zone,
customer.first_name,
customer.last_name,
customer.phone_day,
customer.phone_eve,
customer.e_mail,
customer.address,
customer.postalcode,
customer.primary_loc,
supervisor.shortkey AS supervisor,
technician.shortkey AS tech,
pubs.city_zipcodes.ZIP_CODE, --from the zipcode tables in the
pubs db
pubs.city_zipcodes.CITY --from the zipcode tables
in the pubs db
FROM
dbo.service_request
INNER JOIN dbo.customer ON dbo.service_request.customer = dbo.
customer.serial
INNER JOIN dbo.location ON dbo.service_request.location = dbo.
location.loc_no
INNER JOIN dbo.employee As technician ON dbo.service_request.
serv_by = technician.serial
INNER JOIN dbo.employee As supervisor ON dbo.service_request.
serv_supervisor = supervisor.serial
INNER JOIN city_zipcodes ON dbo.customer.postalcode =
city_zipcodes.zipcode --this is the join I am trying to accomplish
where
(dbo.service_request.record_date >= CONVERT(DATETIME, @.Date1, 102)) AND
(dbo.
service_request.record_date <= CONVERT(DATETIME, @.Date2, 102))
Set Nocount Off
return 0
end
GO
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200507/1
|||> pubs.city_zipcodes.ZIP_CODE, --from the zipcode tables in
the
> pubs.city_zipcodes.CITY --from the zipcode
tables
The selected columns

> FROM
> INNER JOIN city_zipcodes ON dbo.customer.postalcode =
> city_zipcodes.zipcode --this is the join I am trying to accomplish
from the selected table.
You made two mistakes. First, you did not qualify the table correctly in
the join. Second, you did not qualify the appopriate columns correctly
within the select list. Your select list should qualify the columns using
the same table qualification used in the join(s) - these should always match
EXACTLY. Often, it is best to use an alias for the table (as Vyas has done
in his examples). This lets you change (or correct) the selected table
without having to change the associated columns in the remainder of the
statement.

> where
> (dbo.service_request.record_date >= CONVERT(DATETIME, @.Date1, 102)) AND
(dbo.
> service_request.record_date <= CONVERT(DATETIME, @.Date2, 102))
This logic should NOT be needed. The arguments are already datetime
variables - converting them to the same datatype does nothing beneficial.
If record_date is a datetime, then nothing is gained with the CONVERT
statement. If record_date is char or varchar, then your logic is incorrect
since you did not avoid the implicit conversion (which is what you were
apparently trying to do with convert).
|||Thanks for the link. That cleared everything up!
j
Message posted via http://www.droptable.com

Join tables across databases?

In my SProc, I am trying to perform a JOIN on 2 tables that are located in
seperate databases...
...Is this possible?
In the SProc below, the focus is on joining dbo.customer.postalcode WITH pub
s.
city_zipcodes.ZIP_CODE... That is, I have a table of zipcodes and cities
located in the "Pubs" database, while the "Customer.postalcode" table is
located in the "VCMS" database. During testing in my VBA application, I am
returning an error of: "Invalid Object Name city_zipcodes" -- which leads m
e
to believe that I am incorrectly performing this table join.
Any help or suggestions are greatly appreciated!
James
--***StoredProc Sample
CREATE PROCEDURE AO_GetServiceRequests
(@.Date1 datetime=null,
@.Date2 datetime=null)
AS Set Nocount On
--return specified ServiceRequest if Date and Status is supplied
if @.Date1 is not null
begin
SELECT
service_request.serial,
service_request.record_date,
service_request.customer,
location.latitude,
location.longitude,
location.zone,
customer.first_name,
customer.last_name,
customer.phone_day,
customer.phone_eve,
customer.e_mail,
customer.address,
customer.postalcode,
customer.primary_loc,
supervisor.shortkey AS supervisor,
technician.shortkey AS tech,
pubs.city_zipcodes.ZIP_CODE, --from the zipcode tables in the
pubs db
pubs.city_zipcodes.CITY --from the zipcode tables
in the pubs db
FROM
dbo.service_request
INNER JOIN dbo.customer ON dbo.service_request.customer = dbo.
customer.serial
INNER JOIN dbo.location ON dbo.service_request.location = dbo.
location.loc_no
INNER JOIN dbo.employee As technician ON dbo.service_request.
serv_by = technician.serial
INNER JOIN dbo.employee As supervisor ON dbo.service_request.
serv_supervisor = supervisor.serial
INNER JOIN city_zipcodes ON dbo.customer.postalcode =
city_zipcodes.zipcode --this is the join I am trying to accomplish
where
(dbo.service_request.record_date >= CONVERT(DATETIME, @.Date1, 102)) AND (dbo
.
service_request.record_date <= CONVERT(DATETIME, @.Date2, 102))
Set Nocount Off
return 0
end
GO
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200507/1Try these examples:
http://vyaskn.tripod.com/programming_faq.htm#q13
http://vyaskn.tripod.com/programming_faq.htm#q14
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"j c via droptable.com" <forum@.droptable.com> wrote in message
news:514309300509D@.droptable.com...
In my SProc, I am trying to perform a JOIN on 2 tables that are located in
seperate databases...
...Is this possible?
In the SProc below, the focus is on joining dbo.customer.postalcode WITH
pubs.
city_zipcodes.ZIP_CODE... That is, I have a table of zipcodes and cities
located in the "Pubs" database, while the "Customer.postalcode" table is
located in the "VCMS" database. During testing in my VBA application, I am
returning an error of: "Invalid Object Name city_zipcodes" -- which leads
me
to believe that I am incorrectly performing this table join.
Any help or suggestions are greatly appreciated!
James
--***StoredProc Sample
CREATE PROCEDURE AO_GetServiceRequests
(@.Date1 datetime=null,
@.Date2 datetime=null)
AS Set Nocount On
--return specified ServiceRequest if Date and Status is supplied
if @.Date1 is not null
begin
SELECT
service_request.serial,
service_request.record_date,
service_request.customer,
location.latitude,
location.longitude,
location.zone,
customer.first_name,
customer.last_name,
customer.phone_day,
customer.phone_eve,
customer.e_mail,
customer.address,
customer.postalcode,
customer.primary_loc,
supervisor.shortkey AS supervisor,
technician.shortkey AS tech,
pubs.city_zipcodes.ZIP_CODE, --from the zipcode tables in the
pubs db
pubs.city_zipcodes.CITY --from the zipcode tables
in the pubs db
FROM
dbo.service_request
INNER JOIN dbo.customer ON dbo.service_request.customer = dbo.
customer.serial
INNER JOIN dbo.location ON dbo.service_request.location = dbo.
location.loc_no
INNER JOIN dbo.employee As technician ON dbo.service_request.
serv_by = technician.serial
INNER JOIN dbo.employee As supervisor ON dbo.service_request.
serv_supervisor = supervisor.serial
INNER JOIN city_zipcodes ON dbo.customer.postalcode =
city_zipcodes.zipcode --this is the join I am trying to accomplish
where
(dbo.service_request.record_date >= CONVERT(DATETIME, @.Date1, 102)) AND
(dbo.
service_request.record_date <= CONVERT(DATETIME, @.Date2, 102))
Set Nocount Off
return 0
end
GO
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200507/1|||> pubs.city_zipcodes.ZIP_CODE, --from the zipcode tables in
the
> pubs.city_zipcodes.CITY --from the zipcode
tables
The selected columns

> FROM
> INNER JOIN city_zipcodes ON dbo.customer.postalcode =
> city_zipcodes.zipcode --this is the join I am trying to accomplish
from the selected table.
You made two mistakes. First, you did not qualify the table correctly in
the join. Second, you did not qualify the appopriate columns correctly
within the select list. Your select list should qualify the columns using
the same table qualification used in the join(s) - these should always match
EXACTLY. Often, it is best to use an alias for the table (as Vyas has done
in his examples). This lets you change (or correct) the selected table
without having to change the associated columns in the remainder of the
statement.

> where
> (dbo.service_request.record_date >= CONVERT(DATETIME, @.Date1, 102)) AND
(dbo.
> service_request.record_date <= CONVERT(DATETIME, @.Date2, 102))
This logic should NOT be needed. The arguments are already datetime
variables - converting them to the same datatype does nothing beneficial.
If record_date is a datetime, then nothing is gained with the CONVERT
statement. If record_date is char or varchar, then your logic is incorrect
since you did not avoid the implicit conversion (which is what you were
apparently trying to do with convert).|||Thanks for the link. That cleared everything up!
j
Message posted via http://www.droptable.com

Join tables across databases?

In my SProc, I am trying to perform a JOIN on 2 tables that are located in
seperate databases...
...Is this possible?
In the SProc below, the focus is on joining dbo.customer.postalcode WITH pubs.
city_zipcodes.ZIP_CODE... That is, I have a table of zipcodes and cities
located in the "Pubs" database, while the "Customer.postalcode" table is
located in the "VCMS" database. During testing in my VBA application, I am
returning an error of: "Invalid Object Name city_zipcodes" -- which leads me
to believe that I am incorrectly performing this table join.
Any help or suggestions are greatly appreciated!
James
--***StoredProc Sample
CREATE PROCEDURE AO_GetServiceRequests
(@.Date1 datetime=null,
@.Date2 datetime=null)
AS Set Nocount On
--return specified ServiceRequest if Date and Status is supplied
if @.Date1 is not null
begin
SELECT
service_request.serial,
service_request.record_date,
service_request.customer,
location.latitude,
location.longitude,
location.zone,
customer.first_name,
customer.last_name,
customer.phone_day,
customer.phone_eve,
customer.e_mail,
customer.address,
customer.postalcode,
customer.primary_loc,
supervisor.shortkey AS supervisor,
technician.shortkey AS tech,
pubs.city_zipcodes.ZIP_CODE, --from the zipcode tables in the
pubs db
pubs.city_zipcodes.CITY --from the zipcode tables
in the pubs db
FROM
dbo.service_request
INNER JOIN dbo.customer ON dbo.service_request.customer = dbo.
customer.serial
INNER JOIN dbo.location ON dbo.service_request.location = dbo.
location.loc_no
INNER JOIN dbo.employee As technician ON dbo.service_request.
serv_by = technician.serial
INNER JOIN dbo.employee As supervisor ON dbo.service_request.
serv_supervisor = supervisor.serial
INNER JOIN city_zipcodes ON dbo.customer.postalcode = city_zipcodes.zipcode --this is the join I am trying to accomplish
where
(dbo.service_request.record_date >= CONVERT(DATETIME, @.Date1, 102)) AND (dbo.
service_request.record_date <= CONVERT(DATETIME, @.Date2, 102))
Set Nocount Off
return 0
end
GO
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200507/1Try these examples:
http://vyaskn.tripod.com/programming_faq.htm#q13
http://vyaskn.tripod.com/programming_faq.htm#q14
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"j c via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:514309300509D@.SQLMonster.com...
In my SProc, I am trying to perform a JOIN on 2 tables that are located in
seperate databases...
...Is this possible?
In the SProc below, the focus is on joining dbo.customer.postalcode WITH
pubs.
city_zipcodes.ZIP_CODE... That is, I have a table of zipcodes and cities
located in the "Pubs" database, while the "Customer.postalcode" table is
located in the "VCMS" database. During testing in my VBA application, I am
returning an error of: "Invalid Object Name city_zipcodes" -- which leads
me
to believe that I am incorrectly performing this table join.
Any help or suggestions are greatly appreciated!
James
--***StoredProc Sample
CREATE PROCEDURE AO_GetServiceRequests
(@.Date1 datetime=null,
@.Date2 datetime=null)
AS Set Nocount On
--return specified ServiceRequest if Date and Status is supplied
if @.Date1 is not null
begin
SELECT
service_request.serial,
service_request.record_date,
service_request.customer,
location.latitude,
location.longitude,
location.zone,
customer.first_name,
customer.last_name,
customer.phone_day,
customer.phone_eve,
customer.e_mail,
customer.address,
customer.postalcode,
customer.primary_loc,
supervisor.shortkey AS supervisor,
technician.shortkey AS tech,
pubs.city_zipcodes.ZIP_CODE, --from the zipcode tables in the
pubs db
pubs.city_zipcodes.CITY --from the zipcode tables
in the pubs db
FROM
dbo.service_request
INNER JOIN dbo.customer ON dbo.service_request.customer = dbo.
customer.serial
INNER JOIN dbo.location ON dbo.service_request.location = dbo.
location.loc_no
INNER JOIN dbo.employee As technician ON dbo.service_request.
serv_by = technician.serial
INNER JOIN dbo.employee As supervisor ON dbo.service_request.
serv_supervisor = supervisor.serial
INNER JOIN city_zipcodes ON dbo.customer.postalcode =city_zipcodes.zipcode --this is the join I am trying to accomplish
where
(dbo.service_request.record_date >= CONVERT(DATETIME, @.Date1, 102)) AND
(dbo.
service_request.record_date <= CONVERT(DATETIME, @.Date2, 102))
Set Nocount Off
return 0
end
GO
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200507/1|||> pubs.city_zipcodes.ZIP_CODE, --from the zipcode tables in
the
> pubs.city_zipcodes.CITY --from the zipcode
tables
The selected columns
> FROM
> INNER JOIN city_zipcodes ON dbo.customer.postalcode => city_zipcodes.zipcode --this is the join I am trying to accomplish
from the selected table.
You made two mistakes. First, you did not qualify the table correctly in
the join. Second, you did not qualify the appopriate columns correctly
within the select list. Your select list should qualify the columns using
the same table qualification used in the join(s) - these should always match
EXACTLY. Often, it is best to use an alias for the table (as Vyas has done
in his examples). This lets you change (or correct) the selected table
without having to change the associated columns in the remainder of the
statement.
> where
> (dbo.service_request.record_date >= CONVERT(DATETIME, @.Date1, 102)) AND
(dbo.
> service_request.record_date <= CONVERT(DATETIME, @.Date2, 102))
This logic should NOT be needed. The arguments are already datetime
variables - converting them to the same datatype does nothing beneficial.
If record_date is a datetime, then nothing is gained with the CONVERT
statement. If record_date is char or varchar, then your logic is incorrect
since you did not avoid the implicit conversion (which is what you were
apparently trying to do with convert).|||Thanks for the link. That cleared everything up!
j
Message posted via http://www.sqlmonster.com

Join Issue

my scenario is given below

createtable #product (prodID int, subproductid varchar(20))

createtable #subproduct (subproductid varchar(20),description varchar(40))

Insert #product select 1,'1001/2002'

Insert #product select 1,'3003/4004'

Insert #product select 1,'5005/6006'

insert subproduct select 1001 ,'aaa'

insert subproduct select 2002 ,'bbb'

insert subproduct select 3003 ,'ccc'

insert subproduct select 4004 ,'ddd'

insert subproduct select 5005 ,'eee'

insert subproduct select 6006 ,'fff'

this is how our two tables is related. i know its a bad design . but i can't help it.

my question is how can i join these two table ?

thanks in advance

Leena S

S Leena,

IF you know that the table design is bad, why can't you fix it? Do you need help in understanding why it is so bad?

Apparently, from the way the data is put together, and looking at this query, someone made some boneheaded decisions about how to store data in a database. You can be the 'hero' and correct the 'mistake'.

And then life, with queries such as this, will be so much easier...

|||

Hi,

One of the solutions would be a UDF that splits the text and join with that.

But I too suggest the above remark because the design is against the 'rules' of normalization imho ;-)

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||You could join using:

SELECT * --Change this
FROM #product JOIN #subproduct
ON '/' + #product.subproductid + '/' like '%/' + #subproduct.subproductid + '/%'

But as suggested already, you should consider changing your structure.

Rob|||

This is really bad design.

Anyhow if you are not authorized to change the design, the following approach may help you..

Code Snippet

create table #product(

prodID int,

subproductid varchar (20)

);

Insert #product select 1,'1001/2002'

Insert #product select 2,'3003/4004'

Insert #product select 3,'5005/6006'

create table #subproduct(

subproductid varchar(20),

description varchar(40)

);

Insert #subproduct select 1001 ,'aaa'

Insert #subproduct select 2002 ,'bbb'

Insert #subproduct select 3003 ,'ccc'

Insert #subproduct select 4004 ,'ddd'

Insert #subproduct select 5005 ,'eee'

Insert #subproduct select 6006 ,'fff'

--Generating Number Tables;

Select Identity(int,1,1) as Number Into #Numbers From #subproduct A Cross Join #subproduct B;

--Getting the results

Select ProdId,description From

(

Select

prodId

,Case When Number <= Len(subproductid) Then Substring('/' + subproductid + '/', Number+1, CharIndex('/',subproductid + '/', Number+1)-Number) End subproductid

from

#product P

Cross Join #Numbers N

Where

Number <= Len(subproductid) AndSUBSTRING('/' + subproductid + '/', number, 1) = '/'

) as Product

Join #subproduct sub on sub.subproductid = Product.subproductid

Join Issue

I have below 2 tables......

T1
COL1 COL2
1 2
5 6

T2
COL1 COL2
1 2
8 9

I need a query which will return me the data from T1 and the resultset should be......

COL1 COL2
5 6

Thanks,
Rahul JhaWhy should it be that? What is the significance of T2?|||select * from T1 where COL1=5|||http://w3schools.com/sql/sql_join.asp

Have a thorough read of the above link and give it a try.
Post your attempts back here regardless of if you are successful or not.

-George|||http://w3schools.com/sql/sql_join.asp

Have a thorough read of the above link and give it a try.
Post your attempts back here regardless of if you are successful or not.

-George

What exactly you wanted to proove from the above link is not know to me GEORGE. I like staraight talks......... hence would expect you to write the direct comment......... If you want me to understand what excatly the joins are, then you are the wrong person for this thread (Which I have mentioned even in my earlier threads)..... hence request you to keep out of it......

I was asked this question in an interview a long back. All of a sudden it came in my mind and gave a try. But didn't get through it......... Hence posted it to you guys......... :):) :)|||my question is same.. What is the significance of T2???
and if u know that what r the data in the table then

select * from t1 where col1='5'|||George, request you to read and understand the THREAD QUESTION. Don't just read the thread TITLE...........

Thanks,
Rahul Jha|||my question is same.. What is the significance of T2???

Don't know what is the significance of T2...... As said that I was asked this question a long back in an interview......

and if u know that what r the data in the table then

select * from t1 where col1='5'

Even I said the same answer...... but he wanted this through IF EXISTS and LEFt OUTER JOIN...... I didn't get any way to write the type of query that was been asked......

Thanks,
Rahul Jha|||Your question doesn't make sense
Therefore LoztInSpace and jezemine's answers are perfectly correct
What's the point in knowing the answer without knowing how you got there?
The link provided explains the most commonly used joins with very simple examples of how to use them.
Here's a clue... The join type starts with an "L" and without clarification of your questions I'm going to guess at you needing an "AND" clause as well.

Oh and if you were interviewing and got an SQL question - wouldn't it be nice to learn the answer yourself, so you could answer it in future interviews?

And you don't like straight talks, you like the answer on the plate - and you're not getting it on this one.|||And Rahul, I did read the entire thread and supplied a very useful answer (if I do say so myself). I'm trying to help you to help yourself - but clearly my advice is not appreciated so I am going to hold back on giving it to you from now on. I don't think you appreciate how many of your previous questions I have helped you on.|||actually my initial answer was wrong. try this:

select 5 as COL1, 6 as COL2

It's bulletproof because it will work even if the tables don't exist, and it works on any database.|||And Rahul, I did read the entire thread and supplied a very useful answer (if I do say so myself). I'm trying to help you to help yourself - but clearly my advice is not appreciated so I am going to hold back on giving it to you from now on. I don't think you appreciate how many of your previous questions I have helped you on.

why should he bother learning the intricacies of relational databases to do his job when you are always here to do his job for him. it is a lot more efficient to just ask you.

this is why, and you may have noticed, I write very little code for posters. I tend to nudge people in certain directions and give advice so that they will be more apt to figure things out on their own because that is the only way to learn how to program.