Re: Help in Query - Oracle - Database Discussion Boards

Re: Synchronizing different locations in sql server 2005

Jerry Hammond15-Aug-09 3:47

Jerry Hammond

15-Aug-09 3:47

softdev_sup wrote:
well man i am doing this for almost more then 7 years but still in search for better solution

"My interest is in the future because I'm going to spend the rest of my life there." - Charles F. Kettering

Re: Synchronizing different locations in sql server 2005

Ashfield16-Aug-09 21:00

Ashfield

16-Aug-09 21:00

softdev_sup wrote:
well man i am doing this for almost more then 7 years but still in search for better solution

Then that maybe suggests that replication is the best solution - after all, thats what it was designed to do, keep databases syncronised.

Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP

Help in Query - Oracle

aaraaayen14-Aug-09 8:36

aaraaayen

14-Aug-09 8:36

Hi Friends,

I need help in the following query.

SELECT A.BILL, A.BILLER, A.VERSION_NO,
A.CODE, A.NAME,A.SEGMENT_CODE,
A.COMMENT,B.BILL AS BILL, B.BILLER AS DW_BILLER,
B.MCN AS DW_MCN, B.SO AS DW_SO, B.BG AS DW_BG,
B.CODE AS DW_CODE, B.NAME AS DW_NAME,B.SEGMENT_CODE AS DW_SEGMENT_CODE
FROM WEB_REF A, WEB_CUST B
WHERE A.BILL=B.BILL AND A.BILLER=B.BILLER
ORDER BY A.BILL

The above query is working fine. and o/p like below:

A3 3A 3
A3 3A 2
A3 3A 1

But now requirement is that, the table A consists VERSION_NO.
For any combination that found for A.BILL=B.BILL AND A.BILLER=B.BILLER
may have different VERSION_NO.

Now I want to fetch only the row which is having max(version_no) for the A.BILL=B.BILL AND A.BILLER=B.BILLER the combination.

I needs the o/p shd be
A3 3A 3

Likewise for all the rows. Pls help me.

Thanks in Advance.

Regards,

Re: Help in Query - Oracle

Mycroft Holmes14-Aug-09 13:41

Mycroft Holmes

14-Aug-09 13:41

I don't know Oracle but in SQL Server I would use Top 1 and order by version_no desc. There will be something similar in Oracle.

Never underestimate the power of human stupidity
RAH

Re: Help in Query - Oracle

N a v a n e e t h14-Aug-09 17:45

N a v a n e e t h

14-Aug-09 17:45

Mycroft Holmes wrote:
There will be something similar in Oracle.

Yes. Oracle uses rownum. Something like,

Select * from table where rownum = 1 order by version_no desc

Navaneeth

How to use google | Ask smart questions

Re: Help in Query - Oracle

aaraaayen16-Aug-09 23:54

aaraaayen

16-Aug-09 23:54

Hi Navaneeth,

Thank you so much.

I understood the rownum=1 will work when i do order by version_no desc.

But in this case, order by is dynamic..that is user-given input. So whatever column user wish to come as order by he ll select that.

Can u help me how in that case i have to use this query.

Thanks lot really!!!

Regards,

Re: Help in Query - Oracle

aaraaayen17-Aug-09 0:00

aaraaayen

17-Aug-09 0:00

Navaneeth,

Thanks.

If I user rownum=1 order by version_no desc, this will give the very first row, but i want the rows with version_no = max(version_no) in every code,unit_id combination. If I have 3 rows with this combination, among the 3 rows i wanted to display the row which is having max(version_no).

Kindly help me. Thanks.

Regards,
Sageetha

Re: Help in Query - Oracle

Johan Hakkesteegt17-Aug-09 2:23

Johan Hakkesteegt

17-Aug-09 2:23

And just using MAX doesn't work?

SELECT A.BILL, A.BILLER, MAX(A.VERSION_NO) AS VERSION_NO,
A.CODE, A.NAME,A.SEGMENT_CODE,
A.COMMENT,B.BILL AS BILL, B.BILLER AS DW_BILLER,
B.MCN AS DW_MCN, B.SO AS DW_SO, B.BG AS DW_BG,
B.CODE AS DW_CODE, B.NAME AS DW_NAME,B.SEGMENT_CODE AS DW_SEGMENT_CODE
FROM WEB_REF A, WEB_CUST B
WHERE A.BILL=B.BILL AND A.BILLER=B.BILLER
ORDER BY A.BILL

My advice is free, and you may get what you paid for.

SQLServerCE.dll

sohaib_a13-Aug-09 4:46

sohaib_a

13-Aug-09 4:46

I have device running Windows CE 5.0.

I am developing an application for it in C# using a SQL server mobile database.

In order to install the database on the device,I first have to add SQLserverCE.dll.But I don't know why the folder or dll is not available. I tried to install Microsoft SQL Server MObile Developer Tools.but still nothing.

I also reinstalled SQl Server 2005...

considering all the columns in a field

jishbalan13-Aug-09 0:36

jishbalan

13-Aug-09 0:36

Hello friends,

I having two table, one table havin only one fields.

eg : Table A
release
rel1
rel2
rel3

I want to select datas from other table.
table B

release case ID
rel1 a 1
rel1 b 1
rel2 a 2
rel3 c 3

I want to select the cases and ID from table B where release is all the datas present in TAble A. I can build the query to get case and ID. Actually i need the query for another purpose.

I used below query
SELECT cases, ID FROM TAble B WHERE release IN(Select release from TAble B)

this query is very slow for my purpose
The query should link both the table

Please help me to opimise the query

Regards
Jishith

Re: considering all the columns in a field

Ashfield13-Aug-09 1:28

Ashfield

13-Aug-09 1:28

Try this

SELECT cases, ID 
FROM TAbleB b
Join TableA a on a.release = b.release

and check your indices

Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP

PROBLEM IN STORED PROCEDURE SUBSTRING :((

dotnetcsharpdev13-Aug-09 0:16

dotnetcsharpdev

13-Aug-09 0:16

i have a simple stored procedure   and i want to split postcode in it which could be of length either 7 or 6 and tha second part will always be of length 3. am pasting the sql here. and also the error message. Plz help me resolve this error

<code>

SELECT          CONVERT(varchar, DecryptByKey(Postcode)) as Postcode,
          CONVERT(varchar, DecryptByKey(Postcode)) as Postcode1,
          CONVERT(varchar, DecryptByKey(Postcode)) as Postcode2,

                    if Postcode.length = 7
                         begin
                              set Postcode1= substring(Postcode,5,7)
                              set Postcode1= substring(Postcode,1,4)
                         end
                    else
                         begin
                              set Postcode1 = substring(Postcode,4,6)
                              set Postcode1 = substring(Postcode,1,3)

                         end

     FROM            xyz
     WHERE (abc = @def)

     END

</code>

<ERROR>
error:
Msg 102, Level 15, State 1, Procedure XYZ,   LINE 7
Incorrect syntax near '='.
Msg 102, Level 15, State 1, Procedure XYZ, Line LINE 12)
Incorrect syntax near '='.
</ERROR>

Re: PROBLEM IN STORED PROCEDURE SUBSTRING :((

Ashfield13-Aug-09 1:25

Ashfield

13-Aug-09 1:25

What database are you using? The syntax for if Postcode.length = 7 doesn't look like it will work - certainly not in sql server. In sql server it would be

SELECT          CONVERT(varchar, DecryptByKey(Postcode)) as Postcode,
          CONVERT(varchar, DecryptByKey(Postcode)) as Postcode1,
          CONVERT(varchar, DecryptByKey(Postcode)) as Postcode2,               
                                     
POSTCODE1 = case when len(postcode) = 7 then substring(Postcode,5,7) else substring(Postcode,4,6) end,
postcode2 = case when len(postcode) = 7 then substring(Postcode,1,4) else substring(Postcode,1,3)
FROM            xyz
     WHERE (abc = @def)

hope this helps

Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP

Re: PROBLEM IN STORED PROCEDURE SUBSTRING :)

dotnetcsharpdev13-Aug-09 1:55

dotnetcsharpdev

13-Aug-09 1:55

thanx Ashfield for ur reply I figured it out earlier itself and have implemented it almost the same way.

copying my code incase it helps anyone else.

<pre>
LEN(CONVERT(varchar, DecryptByKey(Postcode))) as Postcodeln,
     case when LEN(CONVERT(varchar, DecryptByKey(Postcode))) = '7' then
          substring(CONVERT(varchar, DecryptByKey(Postcode)),1,4)
     else
          substring(CONVERT(varchar, DecryptByKey(Postcode)),1,3)
     end Postcode1,
     case when LEN(CONVERT(varchar, DecryptByKey(Postcode))) = '6' then
          substring(CONVERT(varchar, DecryptByKey(Postcode)),4,6)
     else
          substring(CONVERT(varchar, DecryptByKey(Postcode)),5,7)
     end Postcode2,

</pre>

Many Thanks

Re: PROBLEM IN STORED PROCEDURE SUBSTRING :)

Ashfield13-Aug-09 4:29

Ashfield

13-Aug-09 4:29

No problem, glad to have helped

Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP

Turnkey Websites And Php Scripts (3016)

maliksp12-Aug-09 22:46

maliksp

12-Aug-09 22:46

Brand New & Updated for 2009 you can sell these turnkey websites and Php scripts. Individually, or as a complete package! YOU will keep 100% of every sale! This Package gives you all that and a whole lot more. Visit us for more detail. http://www.clicknearn.net/3016-64.html

Re: Turnkey Websites And Php Scripts (3016)

Ashfield13-Aug-09 1:26

Ashfield

13-Aug-09 1:26

Stop spamming and pay for your adverts for what is probably a crap product anyway

Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP

Get Time From the Datetime of SQL 2005?

Pawan Kiran12-Aug-09 20:26

Pawan Kiran

12-Aug-09 20:26

Hi

I have a value in Datetime Datatype as '8/8/2009 7:45:00 AM'
i want to get only
7:45 AM

How to get this...........

Re: Get Time From the Datetime of SQL 2005?

Ashfield12-Aug-09 21:05

Ashfield

12-Aug-09 21:05

A bit messy, but

select ltrim(right(convert(varchar,getdate(),0),7))

Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP

SQL Server 2008 R2 Community Preview Now Available To All

brucedkyle12-Aug-09 9:07

brucedkyle

12-Aug-09 9:07

The first Community Technology Preview (CTP) of Microsoft SQL Server 2008 R2 is now available to you from TechNet.

There's new features that help you manage applications and multiple servers. Report Builder 3.0 includes support for geospatial visualization for you to include mapping, routing, custom shapes.

Many other features. See SQL Server 2008 R2 CTP Open to All[^] for download and feature information.

Learn more about Microsoft emerging techologies[^] on the Microsoft blog for independent software vendors (ISVs) in the United States.

Re: SQL Server 2008 R2 Community Preview Now Available To All

Mycroft Holmes12-Aug-09 16:55

Mycroft Holmes

12-Aug-09 16:55

brucedkyle wrote:
Report Builder 3.0

But I still can't embed a SQL 2008 report in a VS 2008 winforms app b/c the report viewer does not support the new Tablix control - AAArrrgghhh. The only way to deploy a SQL 2008 report is via reporting sevices, what a PITA.

Never underestimate the power of human stupidity
RAH

Re: SQL Server 2008 R2 Community Preview Now Available To All

Jerry Hammond12-Aug-09 19:14

Jerry Hammond

12-Aug-09 19:14

brucedkyle wrote:
There's new features that help you manage applications and multiple servers. Report Builder 3.0 includes support for geospatial visualization for you to include mapping, routing, custom shapes.

[homer simpson voice]Oohhh...shiney objects in my reports...[/homer simpson voice]

"My interest is in the future because I'm going to spend the rest of my life there." - Charles F. Kettering

SQL 2005 Problem : Insert exec statement cannot be nested

mihirhp12-Aug-09 0:49

mihirhp

12-Aug-09 0:49

Hi,
I m having three store procedures
1. Test_1
2. Test_2
3. Test_3

in Test_1 i get output of one date
in Test_2 i user Test_1 to get output of one week
in Test_3 i want output of 7 weeks so for that when i use Test_2 in Test_3 i got error
"Insert exec statement cannot be nested"

please help me out of this.

Thank you.

Re: SQL 2005 Problem : Insert exec statement cannot be nested

Jerry Hammond12-Aug-09 3:58

Jerry Hammond

12-Aug-09 3:58

I looked in my crystal ball, stirred the tea leaves, and tried the medicine man lodge and I was still unable to see your query. Do you think your could be so kind as to post it here so we might be able to fathom better your problem. I can not express how happy that would make all of us.

"My interest is in the future because I'm going to spend the rest of my life there." - Charles F. Kettering

Re: SQL 2005 Problem : Insert exec statement cannot be nested

Mycroft Holmes12-Aug-09 16:58

Mycroft Holmes

12-Aug-09 16:58

Jerry Hammond wrote:
Do you think your could be so kind as to post it here

Apparently not - I wonder if it is embarassment or he considers his code too proprietry to post, or maybe he worked it out himself.

Never underestimate the power of human stupidity
RAH

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.