Database Discussion Boards

dansoft19-Apr-06 4:33

19-Apr-06 4:33

The following sql join query is displaying "[Microsoft][ODBC SQL Server Driver]Syntax error or access violation" error

SELECT failure.locid,device.count,failure.count<br />
FROM<br />
{<br />
    SELECT locality.locid,count(*) AS count<br />
    FROM allotment,locality<br />
    WHERE allotment.locid=locality.locid<br />
    group by locality.locid<br />
} AS device<br />
FULL OUTER JOIN<br />
{<br />
    SELECT locid,count(deviceid) AS count<br />
    FROM failures<br />
group by locid<br />
} AS failure ON device.locid = failure.locid

device is used to find number of device in a particular locality
failure is to find the number of failures in the particular location
Both device and failure queries are working fine separately.

All localities are stored in locality table. the alloted devices are in allotment table, failures are in failures table

Please correct the error in the query. Thanks.

Re: full outer JOIN error

Colin Angus Mackay19-Apr-06 5:28

Colin Angus Mackay

19-Apr-06 5:28

dansoft wrote:
{

I've never seen that type of bracket/brace used in SQL. Use the normal brackets ()

"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog

Re: full outer JOIN error

dansoft19-Apr-06 6:00

dansoft

19-Apr-06 6:00

Colin Angus Mackay wrote:
Use the normal brackets ()

Sir, where i have to use the normal brackets () ?

Re: full outer JOIN error

dansoft19-Apr-06 6:02

dansoft

19-Apr-06 6:02

Oh Sorry, i'll change the bracket.

Thanks

Re: full outer JOIN error

Colin Angus Mackay19-Apr-06 6:02

Colin Angus Mackay

19-Apr-06 6:02

dansoft wrote:
Sir, where i have to use the normal brackets () ?

Where do you think?

I highlighted the fact you were using braces {} and that it was wrong. Replace the {} with ()

Charts in RDL File

DotNetDominator19-Apr-06 4:20

DotNetDominator

19-Apr-06 4:20

Hi,
Does Anyone know how to change the size of LEGEND Box in any chart of RDL File? There are many details in LEGEND area of my chart which makes legend area much congested.

Thanks in Advance

decimal problem

dansoft19-Apr-06 2:51

dansoft

19-Apr-06 2:51

Hi,

why the select query is not returning the decimal value?

consider the following query:

select 1/2

it is returning 0
it must return 0.5 Please help.

Thanks

Re: decimal problem

Colin Angus Mackay19-Apr-06 2:59

Colin Angus Mackay

19-Apr-06 2:59

Because it is doing an integer division and truncating everything past the decimal point.

use SELECT 1.0/2.0

Re: decimal problem

dansoft19-Apr-06 3:03

dansoft

19-Apr-06 3:03

I need to use the colname. for eg: select colname1/colname2 from table

the colname1 has the value 1 & colname2 has the value 2

thanks

Re: decimal problem

Colin Angus Mackay19-Apr-06 3:07

Colin Angus Mackay

19-Apr-06 3:07

Then cast the column values to floats

SELECT CAST(colname1 AS FLOAT) / CAST(colname2 AS FLOAT) FROM MyTable

Re: decimal problem

dansoft19-Apr-06 3:12

dansoft

19-Apr-06 3:12

Colin Angus Mackay wrote:
SELECT CAST(colname1 AS FLOAT) / CAST(colname2 AS FLOAT) FROM MyTable

Thanks

It is working

How to delete the Duplicate rows

Balavardhan19-Apr-06 1:47

Balavardhan

19-Apr-06 1:47

Hi,
I want to delete Duplicate rows from the table

Thanking you.....

Balu

Re: How to delete the Duplicate rows

Eric Dahlvang19-Apr-06 5:07

Eric Dahlvang

19-Apr-06 5:07

What is the structure of your table? (i.e. field names and data types)

----------
There go my people. I must find out where they are going so I can lead them.
- Alexander Ledru-Rollin

Re: How to delete the Duplicate rows

ldaoust19-Apr-06 10:04

ldaoust

19-Apr-06 10:04

http://support.microsoft.com/default.aspx?scid=kb;en-us;139444

http://www.4guysfromrolla.com/webtech/sqlguru/q051200-2.shtml

Google your question.

Louis.
* google is your friend *

SQL scripts

kirthikirthi19-Apr-06 1:20

kirthikirthi

19-Apr-06 1:20

Hi
I have an Database in SQLServer in that i have no. of tables with primary key's and forign key's

I want to see the Script of the databse in

SQLServer Enterprice manager

Is it posible to see the whole (1 database)database scripts

Like "CASE Studio 2"

asdfasdfasdfasdfasdfasdfasdfasdfasdfasdf

Help me write Query

Balavardhan18-Apr-06 21:16

Balavardhan

18-Apr-06 21:16

hi,
I have a table like

saleno Itemname Qty
1 M.board 10
2 Mouse 20
3 Keyboard 20
4 H.disk 30
5 Monitor 40

after the Update Query i want the result(just add qty with above Qty value in base table)

saleno Itemname Qty
1 M.board 10
2 Mouse 30
3 Keyboard 40
4 H.disk 50
5 Monitor 70

Thank you fro your reply

Balu

Re: Help me write Query

Colin Angus Mackay18-Apr-06 22:38

Colin Angus Mackay

18-Apr-06 22:38

Sorry, I don't understand what you want.

To update a row in a table you can use
UPDATE MyTable SET Qty = 70 WHERE saleno = 5

Re: Help me write Query

Darpan Gogia18-Apr-06 23:13

Darpan Gogia

18-Apr-06 23:13

Hi Balu,

Here's your reply.

If you have a proper sequence with no missing links use the following:

Select a.saleno,b.saleno, coalesce(a.qty,0)+coalesce(b.qty,0) as Qty
from tbl_Sale a left join tbl_sale b
ON a.saleno=b.saleno+1

Else use the following:-

Select a.saleno,coalesce(a.qty,0)+COALESCE((Select b.Qty from tbl_Sale b where b.saleno=
(Select max(c.saleno) from tbl_sale c where c.saleno

Re: Help me write Query

Colin Angus Mackay18-Apr-06 23:22

Colin Angus Mackay

18-Apr-06 23:22

Oh! I see what he was getting at now. I really do wish people would say what they want rather than have everyone guess what they want through some cryptic IQ test what-is-the-next-number-in-the-sequence style question.

Re: Help me write Query

Balavardhan19-Apr-06 0:31

Balavardhan

19-Apr-06 0:31

thank you for reply

How to LOCK a table in MS SQL SERVER

Krishnatv18-Apr-06 18:59

Krishnatv

18-Apr-06 18:59

how to lock an individual table in sql server database ?

krishna

How to get Oracle service names

Xiaoming Qian18-Apr-06 17:05

Xiaoming Qian

18-Apr-06 17:05

Is there any way to get the existing Oracle service names(Stored in TNSNAMES.ORA) using C#.

Re: How to get Oracle service names

phimix18-Apr-06 18:48

phimix

18-Apr-06 18:48

What is TNSNAMES.ORA? A table, a file or something else?

phi
phimix.com OMG | :OMG:

Re: How to get Oracle service names

Xiaoming Qian18-Apr-06 22:02

Xiaoming Qian

18-Apr-06 22:02

TNSNAMES.ORA is An Oracle Network Configuration File.The following is a sample file:

<br />
# TNSNAMES.ORA Network Configuration File: D:\oracle\ora81\network\admin\tnsnames.ora<br />
# Generated by Oracle configuration tools.<br />
<br />
CLCX =<br />
 (DESCRIPTION =<br />
   (ADDRESS_LIST =<br />
     (ADDRESS = (PROTOCOL = TCP)(HOST = 200.22.10.6)(PORT = 1521))<br />
   )<br />
   (CONNECT_DATA =<br />
     (SID = ORCL) (SERVER = DEDICATED)<br />
   )<br />
 )<br />

Re: How to get Oracle service names

phimix18-Apr-06 23:28

phimix

18-Apr-06 23:28

Then you should at least be able to use the System.IO functions like ReadLine(). I don't know if there are any methods specifically for working with Oracle files, but it's quite easy to implement some file reading with System.IO.

phi
phimix.com OMG | :OMG:

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