I have the same question. Did you figure our how to do so?
Thank you very much
Yev
EggHeadCafe.com - .NET Developer Portal of Choice
http://www.eggheadcafe.comalways bind to a view of a table, not directly to a table, so you can contro
l
the columns you want to bind.
MCH
"Yevheniy" wrote:
> I have the same question. Did you figure our how to do so?
> Thank you very much
> Yev
> EggHeadCafe.com - .NET Developer Portal of Choice
> http://www.eggheadcafe.com
>
Showing posts with label figure. Show all posts
Showing posts with label figure. Show all posts
Friday, March 23, 2012
How can I avoid this redundancy? [Sybase tSQL]
I'm kind of a newbie to Sybase tSQL, so I can't seem to figure this out.
Unfortunately, I have to use a bunch of nested queries to get data from a database I didn't create. My stored procedure is become rather huge because of the requirements my client is giving me which involves pulling bits of data from all sorts of random tables. Anyway...
Is there a way to simplify this?
...<snip>...
'varSomeVariable1' =
CASE WHEN CARD_FILE.company_nm = THEN
(SELECT BORROWER.borr_first_nm
FROM BORROWER
WHERE BORROWER.borrower_no = 1)
ELSE
(SELECT BORROWER.borr_first_nm
FROM BORROWER
WHERE BORROWER.borrower_no = 2)
END,
'varSomeVariable2' =
CASE WHEN CARD_FILE.company_nm = THEN
(SELECT BORROWER.borr_first_nm
FROM BORROWER
WHERE BORROWER.borrower_no = 2)
ELSE
(SELECT BORROWER.borr_first_nm
FROM BORROWER
WHERE BORROWER.borrower_no = 3)
END,
...<snip>...
Note that this is greatly simplified so as to not make it too confusing for everyone.
As you can see, the only difference between the nested SQL statements is the value of the 'borrower_no' in the WHERE clause... is there a better way to do this so I can avoid writing two complete SQL statements for each value I'm trying to get? I.e., can I have a conditional statment in a WHERE clause?See if the COALESCE keyword is in sybase and see if that will help you re-write the code. Using COALESCE along with LEFT JOINs have help me make query simpler.
Note without out the from and where clause, people can only guess on what could help. I mean that info is needed to know if the two tables are related to one another. If they are not related to one another I see no way to help you. And, I think that you are in trouble because a database poor design is harder to fix than stored proc bad design.
IN this case and most cases, knowing the Primary and Unique Keys would be great help to helping you.
Tim S|||Hi,
You can use decode in your where clause..
i.e
SELECT BORROWER.borr_first_nm
FROM BORROWER
WHERE
BORROWER.borrower_no = DECODE(varSomeVariable1,CARD_FILE.company_nm , 1,2)
Hope this helps you.|||Hi Shelva;
Isn't Decode() an Oracle function? I can't find it in the Sybase tSQL documentation :( I sure which I could use it 'cause I think that would solve my problem!|||DECODE is the same thing as a flattened-out case.
You can do the same thing with case, e.g. (I don't know if this solves the problem, but here is how to re-write the Oracle SQL):
SELECT BORROWER.borr_first_nm
FROM BORROWER
WHERE
BORROWER.borrower_no = CASE varSomeVariable1 WHEN CARD_FILE.company_nm THEN 1 ELSE 2 END|||Thanks MattR! That worked swell :cool: I didn't know you could use CASE in that way (i.e., in the WHERE clause.)sql
Unfortunately, I have to use a bunch of nested queries to get data from a database I didn't create. My stored procedure is become rather huge because of the requirements my client is giving me which involves pulling bits of data from all sorts of random tables. Anyway...
Is there a way to simplify this?
...<snip>...
'varSomeVariable1' =
CASE WHEN CARD_FILE.company_nm = THEN
(SELECT BORROWER.borr_first_nm
FROM BORROWER
WHERE BORROWER.borrower_no = 1)
ELSE
(SELECT BORROWER.borr_first_nm
FROM BORROWER
WHERE BORROWER.borrower_no = 2)
END,
'varSomeVariable2' =
CASE WHEN CARD_FILE.company_nm = THEN
(SELECT BORROWER.borr_first_nm
FROM BORROWER
WHERE BORROWER.borrower_no = 2)
ELSE
(SELECT BORROWER.borr_first_nm
FROM BORROWER
WHERE BORROWER.borrower_no = 3)
END,
...<snip>...
Note that this is greatly simplified so as to not make it too confusing for everyone.
As you can see, the only difference between the nested SQL statements is the value of the 'borrower_no' in the WHERE clause... is there a better way to do this so I can avoid writing two complete SQL statements for each value I'm trying to get? I.e., can I have a conditional statment in a WHERE clause?See if the COALESCE keyword is in sybase and see if that will help you re-write the code. Using COALESCE along with LEFT JOINs have help me make query simpler.
Note without out the from and where clause, people can only guess on what could help. I mean that info is needed to know if the two tables are related to one another. If they are not related to one another I see no way to help you. And, I think that you are in trouble because a database poor design is harder to fix than stored proc bad design.
IN this case and most cases, knowing the Primary and Unique Keys would be great help to helping you.
Tim S|||Hi,
You can use decode in your where clause..
i.e
SELECT BORROWER.borr_first_nm
FROM BORROWER
WHERE
BORROWER.borrower_no = DECODE(varSomeVariable1,CARD_FILE.company_nm , 1,2)
Hope this helps you.|||Hi Shelva;
Isn't Decode() an Oracle function? I can't find it in the Sybase tSQL documentation :( I sure which I could use it 'cause I think that would solve my problem!|||DECODE is the same thing as a flattened-out case.
You can do the same thing with case, e.g. (I don't know if this solves the problem, but here is how to re-write the Oracle SQL):
SELECT BORROWER.borr_first_nm
FROM BORROWER
WHERE
BORROWER.borrower_no = CASE varSomeVariable1 WHEN CARD_FILE.company_nm THEN 1 ELSE 2 END|||Thanks MattR! That worked swell :cool: I didn't know you could use CASE in that way (i.e., in the WHERE clause.)sql
Monday, March 19, 2012
how can a sql server consume a web service
I am trying to figure out how I can have SQL server consume a web
service.Hello Simone,
See [0] and [1].
[0]: http://msdn2.microsoft.com/en-us/library/ms365190.aspx
[1]: http://blogs.msdn.com/sqlclr/archiv.../25/Vineet.aspx
Thanks,
Kent Tegels
http://staff.develop.com/ktegels/
service.Hello Simone,
See [0] and [1].
[0]: http://msdn2.microsoft.com/en-us/library/ms365190.aspx
[1]: http://blogs.msdn.com/sqlclr/archiv.../25/Vineet.aspx
Thanks,
Kent Tegels
http://staff.develop.com/ktegels/
how can a sql server consume a web service
I am trying to figure out how I can have SQL server consume a web
service.
Thanks for your help.
SimoneHi Simone
SQL Server 2005 can provide you with a XML Web Service, this is documented
in Books Online see http://msdn2.microsoft.com/en-us/library/ms345123.aspx or
http://msdn2.microsoft.com/en-us/library/ms191274.aspx
If you want to store information from an RSS feed in your database you may
want to check out
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/exchsqlxml.asp.
With SQL 2005 the CLR could be used to do this and there is an example in
"Pro SQL Server 2005" by Thomas Rizzo et al.
ISBN 1-59059-477-0
John
"Simone" wrote:
> I am trying to figure out how I can have SQL server consume a web
> service.
> Thanks for your help.
> Simone
>|||Hello Thanks for your reply.
We need this for SQL server 2000 and I tried to search at the Microsoft
website but I couldn't find.
Can you help me finding information for this but for SQL server 2000?
Thanks
Simone
John Bell wrote:
> Hi Simone
> SQL Server 2005 can provide you with a XML Web Service, this is documented
> in Books Online see http://msdn2.microsoft.com/en-us/library/ms345123.aspx or
> http://msdn2.microsoft.com/en-us/library/ms191274.aspx
> If you want to store information from an RSS feed in your database you may
> want to check out
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/exchsqlxml.asp.
> With SQL 2005 the CLR could be used to do this and there is an example in
> "Pro SQL Server 2005" by Thomas Rizzo et al.
> ISBN 1-59059-477-0
> John
> "Simone" wrote:
> > I am trying to figure out how I can have SQL server consume a web
> > service.
> >
> > Thanks for your help.
> > Simone
> >
> >|||Hi Simone
The ability to provide a HTTP endpoint directly in SQL Server is only
available in SQL 2005, with SQL 2000 you would need to use something like
IIS. The XML to SQL example
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/exchsqlxml.asp will work for SQL 2000 if you download SQLXML SP2 or higher.
John
"Simone" wrote:
> Hello Thanks for your reply.
> We need this for SQL server 2000 and I tried to search at the Microsoft
> website but I couldn't find.
> Can you help me finding information for this but for SQL server 2000?
> Thanks
> Simone
> John Bell wrote:
> > Hi Simone
> >
> > SQL Server 2005 can provide you with a XML Web Service, this is documented
> > in Books Online see http://msdn2.microsoft.com/en-us/library/ms345123.aspx or
> > http://msdn2.microsoft.com/en-us/library/ms191274.aspx
> >
> > If you want to store information from an RSS feed in your database you may
> > want to check out
> > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/exchsqlxml.asp.
> > With SQL 2005 the CLR could be used to do this and there is an example in
> > "Pro SQL Server 2005" by Thomas Rizzo et al.
> > ISBN 1-59059-477-0
> >
> > John
> >
> > "Simone" wrote:
> >
> > > I am trying to figure out how I can have SQL server consume a web
> > > service.
> > >
> > > Thanks for your help.
> > > Simone
> > >
> > >
>
service.
Thanks for your help.
SimoneHi Simone
SQL Server 2005 can provide you with a XML Web Service, this is documented
in Books Online see http://msdn2.microsoft.com/en-us/library/ms345123.aspx or
http://msdn2.microsoft.com/en-us/library/ms191274.aspx
If you want to store information from an RSS feed in your database you may
want to check out
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/exchsqlxml.asp.
With SQL 2005 the CLR could be used to do this and there is an example in
"Pro SQL Server 2005" by Thomas Rizzo et al.
ISBN 1-59059-477-0
John
"Simone" wrote:
> I am trying to figure out how I can have SQL server consume a web
> service.
> Thanks for your help.
> Simone
>|||Hello Thanks for your reply.
We need this for SQL server 2000 and I tried to search at the Microsoft
website but I couldn't find.
Can you help me finding information for this but for SQL server 2000?
Thanks
Simone
John Bell wrote:
> Hi Simone
> SQL Server 2005 can provide you with a XML Web Service, this is documented
> in Books Online see http://msdn2.microsoft.com/en-us/library/ms345123.aspx or
> http://msdn2.microsoft.com/en-us/library/ms191274.aspx
> If you want to store information from an RSS feed in your database you may
> want to check out
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/exchsqlxml.asp.
> With SQL 2005 the CLR could be used to do this and there is an example in
> "Pro SQL Server 2005" by Thomas Rizzo et al.
> ISBN 1-59059-477-0
> John
> "Simone" wrote:
> > I am trying to figure out how I can have SQL server consume a web
> > service.
> >
> > Thanks for your help.
> > Simone
> >
> >|||Hi Simone
The ability to provide a HTTP endpoint directly in SQL Server is only
available in SQL 2005, with SQL 2000 you would need to use something like
IIS. The XML to SQL example
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/exchsqlxml.asp will work for SQL 2000 if you download SQLXML SP2 or higher.
John
"Simone" wrote:
> Hello Thanks for your reply.
> We need this for SQL server 2000 and I tried to search at the Microsoft
> website but I couldn't find.
> Can you help me finding information for this but for SQL server 2000?
> Thanks
> Simone
> John Bell wrote:
> > Hi Simone
> >
> > SQL Server 2005 can provide you with a XML Web Service, this is documented
> > in Books Online see http://msdn2.microsoft.com/en-us/library/ms345123.aspx or
> > http://msdn2.microsoft.com/en-us/library/ms191274.aspx
> >
> > If you want to store information from an RSS feed in your database you may
> > want to check out
> > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/exchsqlxml.asp.
> > With SQL 2005 the CLR could be used to do this and there is an example in
> > "Pro SQL Server 2005" by Thomas Rizzo et al.
> > ISBN 1-59059-477-0
> >
> > John
> >
> > "Simone" wrote:
> >
> > > I am trying to figure out how I can have SQL server consume a web
> > > service.
> > >
> > > Thanks for your help.
> > > Simone
> > >
> > >
>
how can a sql server consume a web service
I am trying to figure out how I can have SQL server consume a web
service.
Hello Simone,
See [0] and [1].
[0]: http://msdn2.microsoft.com/en-us/library/ms365190.aspx
[1]: http://blogs.msdn.com/sqlclr/archive...25/Vineet.aspx
Thanks,
Kent Tegels
http://staff.develop.com/ktegels/
service.
Hello Simone,
See [0] and [1].
[0]: http://msdn2.microsoft.com/en-us/library/ms365190.aspx
[1]: http://blogs.msdn.com/sqlclr/archive...25/Vineet.aspx
Thanks,
Kent Tegels
http://staff.develop.com/ktegels/
how can a sql server consume a web service
I am trying to figure out how I can have SQL server consume a web
service.
Thanks for your help.
Simone
Hi Simone
SQL Server 2005 can provide you with a XML Web Service, this is documented
in Books Online see http://msdn2.microsoft.com/en-us/library/ms345123.aspx or
http://msdn2.microsoft.com/en-us/library/ms191274.aspx
If you want to store information from an RSS feed in your database you may
want to check out
http://msdn.microsoft.com/library/de...xchsqlxml.asp.
With SQL 2005 the CLR could be used to do this and there is an example in
"Pro SQL Server 2005" by Thomas Rizzo et al.
ISBN 1-59059-477-0
John
"Simone" wrote:
> I am trying to figure out how I can have SQL server consume a web
> service.
> Thanks for your help.
> Simone
>
|||Hello Thanks for your reply.
We need this for SQL server 2000 and I tried to search at the Microsoft
website but I couldn't find.
Can you help me finding information for this but for SQL server 2000?
Thanks
Simone
John Bell wrote:[vbcol=seagreen]
> Hi Simone
> SQL Server 2005 can provide you with a XML Web Service, this is documented
> in Books Online see http://msdn2.microsoft.com/en-us/library/ms345123.aspx or
> http://msdn2.microsoft.com/en-us/library/ms191274.aspx
> If you want to store information from an RSS feed in your database you may
> want to check out
> http://msdn.microsoft.com/library/de...xchsqlxml.asp.
> With SQL 2005 the CLR could be used to do this and there is an example in
> "Pro SQL Server 2005" by Thomas Rizzo et al.
> ISBN 1-59059-477-0
> John
> "Simone" wrote:
|||Hi Simone
The ability to provide a HTTP endpoint directly in SQL Server is only
available in SQL 2005, with SQL 2000 you would need to use something like
IIS. The XML to SQL example
http://msdn.microsoft.com/library/de...exchsqlxml.asp will work for SQL 2000 if you download SQLXML SP2 or higher.
John
"Simone" wrote:
> Hello Thanks for your reply.
> We need this for SQL server 2000 and I tried to search at the Microsoft
> website but I couldn't find.
> Can you help me finding information for this but for SQL server 2000?
> Thanks
> Simone
> John Bell wrote:
>
service.
Thanks for your help.
Simone
Hi Simone
SQL Server 2005 can provide you with a XML Web Service, this is documented
in Books Online see http://msdn2.microsoft.com/en-us/library/ms345123.aspx or
http://msdn2.microsoft.com/en-us/library/ms191274.aspx
If you want to store information from an RSS feed in your database you may
want to check out
http://msdn.microsoft.com/library/de...xchsqlxml.asp.
With SQL 2005 the CLR could be used to do this and there is an example in
"Pro SQL Server 2005" by Thomas Rizzo et al.
ISBN 1-59059-477-0
John
"Simone" wrote:
> I am trying to figure out how I can have SQL server consume a web
> service.
> Thanks for your help.
> Simone
>
|||Hello Thanks for your reply.
We need this for SQL server 2000 and I tried to search at the Microsoft
website but I couldn't find.
Can you help me finding information for this but for SQL server 2000?
Thanks
Simone
John Bell wrote:[vbcol=seagreen]
> Hi Simone
> SQL Server 2005 can provide you with a XML Web Service, this is documented
> in Books Online see http://msdn2.microsoft.com/en-us/library/ms345123.aspx or
> http://msdn2.microsoft.com/en-us/library/ms191274.aspx
> If you want to store information from an RSS feed in your database you may
> want to check out
> http://msdn.microsoft.com/library/de...xchsqlxml.asp.
> With SQL 2005 the CLR could be used to do this and there is an example in
> "Pro SQL Server 2005" by Thomas Rizzo et al.
> ISBN 1-59059-477-0
> John
> "Simone" wrote:
|||Hi Simone
The ability to provide a HTTP endpoint directly in SQL Server is only
available in SQL 2005, with SQL 2000 you would need to use something like
IIS. The XML to SQL example
http://msdn.microsoft.com/library/de...exchsqlxml.asp will work for SQL 2000 if you download SQLXML SP2 or higher.
John
"Simone" wrote:
> Hello Thanks for your reply.
> We need this for SQL server 2000 and I tried to search at the Microsoft
> website but I couldn't find.
> Can you help me finding information for this but for SQL server 2000?
> Thanks
> Simone
> John Bell wrote:
>
how can a sql server consume a web service
I am trying to figure out how I can have SQL server consume a web
service.
Thanks for your help.
SimoneHi Simone
SQL Server 2005 can provide you with a XML Web Service, this is documented
in Books Online see http://msdn2.microsoft.com/en-us/library/ms345123.aspx o
r
http://msdn2.microsoft.com/en-us/library/ms191274.aspx
If you want to store information from an RSS feed in your database you may
want to check out
http://msdn.microsoft.com/library/d.../>
sqlxml.asp.
With SQL 2005 the CLR could be used to do this and there is an example in
"Pro SQL Server 2005" by Thomas Rizzo et al.
ISBN 1-59059-477-0
John
"Simone" wrote:
> I am trying to figure out how I can have SQL server consume a web
> service.
> Thanks for your help.
> Simone
>|||Hello Thanks for your reply.
We need this for SQL server 2000 and I tried to search at the Microsoft
website but I couldn't find.
Can you help me finding information for this but for SQL server 2000?
Thanks
Simone
John Bell wrote:[vbcol=seagreen]
> Hi Simone
> SQL Server 2005 can provide you with a XML Web Service, this is documented
> in Books Online see http://msdn2.microsoft.com/en-us/library/ms345123.aspx
or
> http://msdn2.microsoft.com/en-us/library/ms191274.aspx
> If you want to store information from an RSS feed in your database you may
> want to check out
> http://msdn.microsoft.com/library/d...
chsqlxml.asp.
> With SQL 2005 the CLR could be used to do this and there is an example in
> "Pro SQL Server 2005" by Thomas Rizzo et al.
> ISBN 1-59059-477-0
> John
> "Simone" wrote:
>|||Hi Simone
The ability to provide a HTTP endpoint directly in SQL Server is only
available in SQL 2005, with SQL 2000 you would need to use something like
IIS. The XML to SQL example
http://msdn.microsoft.com/library/d... />
sqlxml.asp will work for SQL 2000 if you download SQLXML SP2 or higher.
John
"Simone" wrote:
> Hello Thanks for your reply.
> We need this for SQL server 2000 and I tried to search at the Microsoft
> website but I couldn't find.
> Can you help me finding information for this but for SQL server 2000?
> Thanks
> Simone
> John Bell wrote:
>
service.
Thanks for your help.
SimoneHi Simone
SQL Server 2005 can provide you with a XML Web Service, this is documented
in Books Online see http://msdn2.microsoft.com/en-us/library/ms345123.aspx o
r
http://msdn2.microsoft.com/en-us/library/ms191274.aspx
If you want to store information from an RSS feed in your database you may
want to check out
http://msdn.microsoft.com/library/d.../>
sqlxml.asp.
With SQL 2005 the CLR could be used to do this and there is an example in
"Pro SQL Server 2005" by Thomas Rizzo et al.
ISBN 1-59059-477-0
John
"Simone" wrote:
> I am trying to figure out how I can have SQL server consume a web
> service.
> Thanks for your help.
> Simone
>|||Hello Thanks for your reply.
We need this for SQL server 2000 and I tried to search at the Microsoft
website but I couldn't find.
Can you help me finding information for this but for SQL server 2000?
Thanks
Simone
John Bell wrote:[vbcol=seagreen]
> Hi Simone
> SQL Server 2005 can provide you with a XML Web Service, this is documented
> in Books Online see http://msdn2.microsoft.com/en-us/library/ms345123.aspx
or
> http://msdn2.microsoft.com/en-us/library/ms191274.aspx
> If you want to store information from an RSS feed in your database you may
> want to check out
> http://msdn.microsoft.com/library/d...
chsqlxml.asp.
> With SQL 2005 the CLR could be used to do this and there is an example in
> "Pro SQL Server 2005" by Thomas Rizzo et al.
> ISBN 1-59059-477-0
> John
> "Simone" wrote:
>|||Hi Simone
The ability to provide a HTTP endpoint directly in SQL Server is only
available in SQL 2005, with SQL 2000 you would need to use something like
IIS. The XML to SQL example
http://msdn.microsoft.com/library/d... />
sqlxml.asp will work for SQL 2000 if you download SQLXML SP2 or higher.
John
"Simone" wrote:
> Hello Thanks for your reply.
> We need this for SQL server 2000 and I tried to search at the Microsoft
> website but I couldn't find.
> Can you help me finding information for this but for SQL server 2000?
> Thanks
> Simone
> John Bell wrote:
>
Subscribe to:
Posts (Atom)