Consolidation Query: written

Showing posts with label written. Show all posts

Sunday, March 25, 2012

Conversion of query from Oracle to SQL Server

Hi Friends,
Need ur help desperately. I am stuck with one of the queries which i had written in Oracle and need the same in SQL Server.Please have a look at the following query :

select * from r_tin_1099_info where instr(translate( nm_ctrl_cd , '~!@.#$%^&*()_+}{":?><`-=]['''';/., ', '*******************************' ),'*') > 0;

Basically my purpose is to replace the values in column NM_CTRL_CD having wild card characters with '*' and then select this rows to display.

However i am not able to run the same query in SQL Server since TRANSLATE is not a built in func. I have tried a lot to replace it but could only one func : REPLACE . But the same will not replace any one of the above wild characters but will replace the entire pattern.Please note that it should be able replace even if one of the wild card characters are present in the string and not necessarily the entire pattern shown above.

please reply ASAP since i am working and need this query to fix a defect.

Thanks in advance.Hi all,
Can somebody please reply to my query mentioned above !!!|||I'd suggest using:SELECT *
FROM r_tin_1099_info
WHERE nm_ctrl_cd LIKE '%[]~!@.#$%^&*()_+}{":?><`-=[]%'
-PatP|||Hey thanks a ton... I will try this out and let you know about the results !!!|||Hi,
The query u have sent does not return any result even though the data is there in the table.Can you please help us out with the query !!

Thanks|||Sorry, I was trying to avoid using escape characters and that got me into trouble. A better solution is:SELECT *
FROM r_tin_1099_info
WHERE nm_ctrl_cd LIKE '%[~!@.#$%^&*()_+}{":?><`x-=x]['';/., ]%' ESCAPE 'x'-PatP|||hey Pat... thanks a lot.. this is working fine... just another question... do u knw any equivalent func for TRANSLATE(in DB2)... bcoz i have a query in DB2 which needs to be translated in SQL Server and since TRANSLATE is not a built in func.. i am not able to execute the same query. the query is as follows:

update r_tin_1099_info set nm_ctrl_cd = substr(replace(translate( coalesce(last_nm,tin_nm_1), '', '~!@.#$%^&*()_+}{":?><`-=]['''';/., ' ),' ',''),1,4) where locate('*',translate( nm_ctrl_cd , '*******************************', '~!@.#$%^&*()_+}{":?><`-=]['''';/., ' )) > 0

I know i mite be asking to much from you.. but it will gr88 if u can guide me with this query as well !!

Thanks.|||Because the second argument to the Translate() call is an empty string, the Translate function will do nothing, it is meaningless so it can be discarded. That leaves you with:UPDATE r_tin_1099_info
SET nm_ctrl_cd = substr(replace(coalesce(last_nm, tin_nm_1), ' ', ''), 1, 4)
WHERE nm_ctrl_cd LIKE '%[~!@.#$%^&*()_+}{":?><`x-=x]['';/., ]%' ESCAPE 'x'-PatP|||hey pat... i tried ur query... its executing without any errors but doesnt seem to update the records... the values having wild characters are not updated and still contain the wild characters... Can you please help me out with this

thanks.|||If you can tell me what you want, I can probably help. The code that you posted in your last question ought to have exactly the same effect as the code that I posted in response to it, but that doesn't appear to be what you actually want.

As I've given you several examples to work from, you ought to be able to get pretty close to what you want on your own. If not, please post:

1) whatever DML you have working
2) A DDL script to build your schema
3) At least a few sample rows of data (BCP native format would be preferred)
4) An example of the output that you'd like from your query

I'll help you, but I can't read your mind and I won't actually do your job for you.

-PatP

Conversion of non Ansi standard queries to ANSI Standard queries

Hi,
In our company we are trying to support SQL Server 2005 in 90 mode. Our
application consists around 120 Stored procedure written with non ansi
standard format joins (*=), is there any tools to convert them or any other
quicky method to do it.
Thanks in advance.
Cheers
RajeshIn article <D05276DF-6E55-49D4-A35E-03ECC8B953A4@.microsoft.com>, =?Utf-
8?B?UmFqZXNoIFY=?= <Rajesh V@.discussions.microsoft.com> says...
> Hi,
> In our company we are trying to support SQL Server 2005 in 90 mode. Our
> application consists around 120 Stored procedure written with non ansi
> standard format joins (*=), is there any tools to convert them or any other
> quicky method to do it.
> Thanks in advance.
> Cheers
> Rajesh
>
Not sure about the available tools other than search and replace via any
good text editor, but another question is the ambiguity of old syntax
outer joins which may produce different results when converted to ANSI
standard syntax.
--
Graham (Pete) Berry
PeteBerry@.Caltech.edu|||On Wed, 26 Sep 2007 01:44:01 -0700, Rajesh V <Rajesh
V@.discussions.microsoft.com> wrote:
>Hi,
> In our company we are trying to support SQL Server 2005 in 90 mode. Our
>application consists around 120 Stored procedure written with non ansi
>standard format joins (*=), is there any tools to convert them or any other
>quicky method to do it.
Hi Rajesh,
No automated tools that I know of. In similar cases in the past, I have
found that if you assign one person to the task, he or she will build up
routine quickly, so that once (s)he is past the learing curve, the
process of replacing the non-standard code becomes pretty fast.
Don't forget to reward the poor guy/gal with a day off or a bonus after
completing such an unrewarding and mind-numbing task!!
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

Conversion of code from oracle to sql server

hi all
I've already written the following code which works fine in oracle.
can somebody help me out for sql server as we have migrated to sql
server 2000.
select EMP_ID,EMP_CODE,EMP_NAME,EMP_DESIG,EMP_DEPARTMENT from EMPLOYEE
WHERE RECORD_DELETED='0' START WITH EMP_CODE='" & objUser.Id & "'
connect by prior EMP_CODE = EMP_REP_AUTH
thanxHello,
You can write the recursive queries from SQL 2005 onwards.
http://www.sqlservercentral.com/columnists/sSampath/recursivequeriesinsqlserver2005.asp
http://www.sqlservercentral.com/columnists/fBROUARD/recursivequeriesinsql1999andsqlserver2005.asp
Thanks
Hari
<jefftim@.gmail.com> wrote in message
news:1170159273.454099.318510@.p10g2000cwp.googlegroups.com...
> hi all
> I've already written the following code which works fine in oracle.
> can somebody help me out for sql server as we have migrated to sql
> server 2000.
> select EMP_ID,EMP_CODE,EMP_NAME,EMP_DESIG,EMP_DEPARTMENT from EMPLOYEE
> WHERE RECORD_DELETED='0' START WITH EMP_CODE='" & objUser.Id & "'
> connect by prior EMP_CODE = EMP_REP_AUTH
>
> thanx
>

Conversion of code from oracle to sql server

hi all
I've already written the following code which works fine in oracle.
can somebody help me out for sql server as we have migrated to sql
server 2000.
select EMP_ID,EMP_CODE,EMP_NAME,EMP_DESIG,EMP_DEPARTMENT from EMPLOYEE
WHERE RECORD_DELETED='0' START WITH EMP_CODE='" & objUser.Id & "'
connect by prior EMP_CODE = EMP_REP_AUTH
thanx
Hello,
You can write the recursive queries from SQL 2005 onwards.
http://www.sqlservercentral.com/columnists/sSampath/recursivequeriesinsqlserver2005.asp
http://www.sqlservercentral.com/columnists/fBROUARD/recursivequeriesinsql1999andsqlserver2005.asp
Thanks
Hari
<jefftim@.gmail.com> wrote in message
news:1170159273.454099.318510@.p10g2000cwp.googlegr oups.com...
> hi all
> I've already written the following code which works fine in oracle.
> can somebody help me out for sql server as we have migrated to sql
> server 2000.
> select EMP_ID,EMP_CODE,EMP_NAME,EMP_DESIG,EMP_DEPARTMENT from EMPLOYEE
> WHERE RECORD_DELETED='0' START WITH EMP_CODE='" & objUser.Id & "'
> connect by prior EMP_CODE = EMP_REP_AUTH
>
> thanx
>

Conversion of code from oracle to sql server

hi all
I've already written the following code which works fine in oracle.
can somebody help me out for sql server as we have migrated to sql
server 2000.
select EMP_ID,EMP_CODE,EMP_NAME,EMP_DESIG,EMP_D
EPARTMENT from EMPLOYEE
WHERE RECORD_DELETED='0' START WITH EMP_CODE='" & objUser.Id & "'
connect by prior EMP_CODE = EMP_REP_AUTH
thanxHello,
You can write the recursive queries from SQL 2005 onwards.
http://www.sqlservercentral.com/col...00
5.asp
http://www.sqlservercentral.com/col...lserver2005.asp
Thanks
Hari
<jefftim@.gmail.com> wrote in message
news:1170159273.454099.318510@.p10g2000cwp.googlegroups.com...
> hi all
> I've already written the following code which works fine in oracle.
> can somebody help me out for sql server as we have migrated to sql
> server 2000.
> select EMP_ID,EMP_CODE,EMP_NAME,EMP_DESIG,EMP_D
EPARTMENT from EMPLOYEE
> WHERE RECORD_DELETED='0' START WITH EMP_CODE='" & objUser.Id & "'
> connect by prior EMP_CODE = EMP_REP_AUTH
>
> thanx
>sql sql

Conversion of Access application to SQL Server

Hi there,

I have written an application which uses MS Access for it's database engine.
Due to the large size which the database has become I have decided that it
would be sensible to use SQL Server with the application instead.

I am an extreme SQL Server newbie so I am not really sure what I'm doing
yet! I have successfully downloaded and installed the MS SQLDE 2000 and
service pack 3.

What do I need to do next? Ideally I would like to convert the existing
Access database to MS SQL Server format. Also I would like to know if it is
possible to create an SQL Server database from scratch using a gui
environment similar to Access and if so which software (preferably free) do
I need to achieve this?

Many thanks,
Clive.The easiest way to start is to create the tables in SQL and then point
the MS Access app to these tables. You will need to use the same table
design so it will be easy to follow. You can keep your existing
queries, forms, reports etc... so you keep the functionality of Access
with the back end of SQL which is far better IMO.

For the front end, you can't do this in SQL. You need something else
and seeing as you know Access, it's the best place for you to do this
and you don't need to re-do anything. All you need to do is make sure
you link to the SQL tables using ODBC and keep the naming convention
(for the links at least) the same. Everything else will either work,
or be as near as damn it.

I'd also recommend looking at www.mvps.org/access as this should have
plenty of helpful tips for you. Not sure what SQL stuff is there, but
it may help with a few other bits and bobs.

HTH

Ryan

I'd also recommend looking at www.mvps.org/access as this should have
plenty of helpful tips for you. Not sure what SQL stuff is there, but
it may help with a few other bits and bobs.

HTH

Ryan

"Clive Minnican" <clive@.mail.com> wrote in message news:<BeLUc.1603$CT4.510@.newsfe3-gui.ntli.net>...
> Hi there,
> I have written an application which uses MS Access for it's database engine.
> Due to the large size which the database has become I have decided that it
> would be sensible to use SQL Server with the application instead.
> I am an extreme SQL Server newbie so I am not really sure what I'm doing
> yet! I have successfully downloaded and installed the MS SQLDE 2000 and
> service pack 3.
> What do I need to do next? Ideally I would like to convert the existing
> Access database to MS SQL Server format. Also I would like to know if it is
> possible to create an SQL Server database from scratch using a gui
> environment similar to Access and if so which software (preferably free) do
> I need to achieve this?
> Many thanks,
> Clive.|||"Clive Minnican" <clive@.mail.com> wrote in message news:<BeLUc.1603$CT4.510@.newsfe3-gui.ntli.net>...
> Hi there,
> I have written an application which uses MS Access for it's database engine.
> Due to the large size which the database has become I have decided that it
> would be sensible to use SQL Server with the application instead.
> I am an extreme SQL Server newbie so I am not really sure what I'm doing
> yet! I have successfully downloaded and installed the MS SQLDE 2000 and
> service pack 3.
> What do I need to do next? Ideally I would like to convert the existing
> Access database to MS SQL Server format. Also I would like to know if it is
> possible to create an SQL Server database from scratch using a gui
> environment similar to Access and if so which software (preferably free) do
> I need to achieve this?
> Many thanks,
> Clive.

I believe that Access has an upsizing wizard which attempts to
automatically upgrade Access applications to MSSQL, although like all
platform migration tools it probably has a number of limitations. In
any case, you may get a better response to this in an Access newgroup.

http://www.aspfaq.com/show.asp?id=2182

As for GUIs for MSDE, see here:

http://www.aspfaq.com/show.asp?id=2442

Simon

Thursday, March 22, 2012

conversion from Access query to mssql query

Hello Guys

I am chaging the connectivity of MSaccess2K to sqlserver
the code is written in vb editor of access
i have established the connection string
but the following query is generating error of
invalid object name

strSQL = SELECT DISTINCT [Sites Union Controls].Description,
[Sites Union Controls].[Rous Reportable Site], [Sites Union Controls].[Type], Samples.SequenceNumber FROM Jobs INNER JOIN ([Sites Union Controls] INNER JOIN Samples ON [Sites Union Controls].SiteSerial = Samples.SiteSerial) ON Jobs.JobSerial = Samples.JobSerial
WHERE ((Jobs.JobSerial) = " & intJobSerial & ") ORDER BY Samples.SequenceNumber

I am getting an error invalid object name sites union controls

can't we do union of two tables as above in mssql
Please Help

Thanks In AdvanceI'm sure some one will correct me if Im wrong but I dont think this query will ever work in SQL Server, it does look like something that might run in access though.

Ive had problems like this before, Access loves adding in brackets () that just get in the way and confuse things in SQL Server. It also seams to list all the tables then join them in the FROM statement which is not s SQL thing either.

I think your also going to have a problem with the WHERE clause, namely the part " & intJobSerial & " reefers to a variable in Access. Even if you have created the variable in SQL Server the syntax is still wrong

Your query doesnt look like a union query, but a standard select query gone a bit wrong in the FROM part. Your query should look something like

DECLARE @.intJobSerial VARCHAR(50) -- this creates the variable as a 50 character text field, don't need this bit if youve done it already

SET @.intJobSerial = 'XXX' -- this sets the variable to XXX, don't need this bit if youve done it already

SELECT DISTINCT [Sites Union Controls].[Description],[Sites Union Controls].[Rous Reportable Site], [Sites Union Controls].Type, Samples.SequenceNumber

FROM [Sites Union Controls]
INNER JOIN Samples
ON [Sites Union Controls].SiteSerial = Samples.SiteSerial
INNER JOIN Jobs
ON Jobs.JobSerial = Samples.JobSerial

WHERE Jobs.JobSerial = @.intJobSerial

ORDER BY Samples.SequenceNumber

The invalid object sites union controls in actually the table used the query, Im guessing its because the FROM Part was all messed up at it was the first thing it came across after it went wrong.

Hope this helps|||Hello,

Trying by used the next name Sites_Union_Controls because I'm not sure that you can have a name with blanc characters

Good luck

Sylvie

Quote:

Originally Posted by aakash

Hello Guys

I am getting an error invalid object name sites union controls

can't we do union of two tables as above in mssql
Please Help

Thanks In Advance

|||Hey, a couple of points...
SQL uses + for concatenation so you need to change your where statement to
((Jobs.JobSerial) = " + @.intJobSerial + ")

JohnK is right, if intJobSerial is a local variable then it needs to be declared and it must begin with an @..

There is nothing wrong with the FROM statement, it's a little unusual to delay the two ON clauses at the end but this gives a different result set because nulls in the 3rd table, in your query SAMPLES, can be handled differently this way. It's more effective if your mixing Left Outer and Inner Joins, however.

The biggest thing I see is that the permissions on the SQL table [Sites Union Controls] may be different than what you expect. You should probably be using a full reference to it as [database].[owner].[table] to at least eliminate the possibility that your error is a security setup problem.

Tom|||Sites Union Controls, was query in old ms access project which was connected to ms access database i am changing connectivity to ms sql 2000 ,none of the above solutions seem to be working ,
please help

Sunday, March 25, 2012

Conversion of query from Oracle to SQL Server

Conversion of non Ansi standard queries to ANSI Standard queries

Conversion of code from oracle to sql server

Conversion of code from oracle to sql server

Conversion of code from oracle to sql server

Conversion of Access application to SQL Server

Thursday, March 22, 2012

conversion from Access query to mssql query

Consolidation Query

Blog Archive

About Me