Database Discussion Boards

Re: Where .. IN ...

Eddy Vluggen28-Mar-12 7:29

Eddy Vluggen

28-Mar-12 7:29

I'd advice to not use the "in" operator with a user-defined varchar, but to name the values individually.

SQL

SELECT *
  FROM tableName
 WHERE ColumnId = 1 OR ColumnId = 2

You can simply concatenate "OR ColumnId = @value" to the query and set the parameters.

Bastard Programmer from Hell Suspicious | :suss:

SQL query with multiples values(same column)

OsiKosi27-Mar-12 15:47

OsiKosi

27-Mar-12 15:47

I have this tables:
illness (cod_i,illness)
symptoms (cod_s,symptom)
and
illness_symptoms (cod_i,Cod_s)

How can i do to find out a illness that has for example 2/3 symptoms
-> cod_i | cod_s
i_1 s_1
i_1 s_2
i_1 s_3
that is select the illness that has the s_1 and s_2 symptoms. Please Help me

Re: SQL query with multiples values(same column)

Luc Pattyn27-Mar-12 16:18

Luc Pattyn

27-Mar-12 16:18

You need to select from the many-to-many table joined to itself, as in (not tested):

1. assuming s_1 and s_2 are known:

SQL

SELECT * FROM illness_symptoms AS is1 
INNER JOIN illness_symptoms AS is2 ON is1.cod_i=is2.cod_i
WHERE is1.cod_s=s_1 AND is2.cod_s=s_2

2.
Assuming symptom1 and symptom2 are known:

SQL

SELECT * FROM illness_symptoms AS is1 
INNER JOIN illness_symptoms AS is2 ON is1.cod_i=is2.cod_i
INNER JOIN symptoms AS sy1 ON is1.cod_s=sy1.cod_s
INNER JOIN symptoms AS sy2 ON is2.cod_s=sy2.cod_s
WHERE sy1.symptom=symptom1 AND sy2.symptom=symptom2

In both cases, then probably apply one more JOIN to obtain the details of the illnesses found.

Smile | :)

Luc Pattyn [My Articles] Nil Volentibus Arduum

Re: SQL query with multiples values(same column)

OsiKosi27-Mar-12 16:34

OsiKosi

27-Mar-12 16:34

THANKS A LOT MAN
i just tried the firs option with 2 and four symptoms and it really works. I doing a program to predict based on four choosen symptoms(combobox) if there are a illness, is like the idea of helping some kind of doctor in the task of diagnose illness. REALLY THANKS first time i ask in this very informative website Java | [Coffee]

Powerful Germany...

Re: SQL query with multiples values(same column)

Luc Pattyn27-Mar-12 17:37

Luc Pattyn

27-Mar-12 17:37

You're welcome.

Smile | :)

PS: you can always vote on a message to express to what extent it has helped you.

Luc Pattyn [My Articles] Nil Volentibus Arduum

Re: SQL query with multiples values(same column)

Vipin_Arora27-Mar-12 21:06

Vipin_Arora

27-Mar-12 21:06

my Vote +5 for good example and nice explaination...

Happy Coding... Smile | :)

What is the best SQL Server disaster recovery solution?

obarahmeh26-Mar-12 23:59

obarahmeh

26-Mar-12 23:59

Dear All,

We are planning to have a complete solution for SQL Server disaster situation.
We will have two servers, one is locally and the other is in another country where we have VPN connections between the two offices.

I planned to build a transactional replication with fail over options to redirect our applications to the subscriber server.
My question is: Is this option (Transactional Replication) the most useful and best solution for mu purpose?
And if it is the best solution, how can our applications redirect to the subscriber server if the primary server is down???
Is there any thing to use that may check the primary server availability and redirect the applications to the subscriber server automatically???

Your help is very very very appreciated

Best Regards

Kind Regards
OBarahmeh

Re: What is the best SQL Server disaster recovery solution?

Corporal Agarn27-Mar-12 2:34

Corporal Agarn

27-Mar-12 2:34

obarahmeh wrote:
Is this option (Transactional Replication) the most useful and best solution for mu purpose?

Actually there are several ways of doing this, but if your way works for you then it is the best way. Sorry I do not remember how to fail-over. Currently we use log shipping but not the High Availability of it as we manually switch (not my idea).

Good luck, and do not forget to practice the fail-over every so often.

Oracle, inserting date and time

Chris_B8426-Mar-12 22:31

Chris_B84

26-Mar-12 22:31

Hi, I'm fairly new to SQL and am using Oracle SQL Developer. I have a table with a field of data type DATE. (It is the 3rd field along). I need to insert a date and time into the field. I have tried the following:

INSERT INTO rentals VALUES (1, 7782, 02/02/2010 09:45:00,'PQ07JHG');

INSERT INTO rentals VALUES (1, 7782, TO_DATE(02/02/2010 09:45:00, 'dd-mm-yyyy' 'hh24:mm:ss'), 'PQ07JHG');

INSERT INTO rentals VALUES (1, 7782, TO_DATE(02/02/2010 09:45:00), 'PQ07JHG');

I keep on getting the same error: Bind variable "45" is NOT DECLARED.

Thanks in advance for any help Smile | :)

Re: Oracle, inserting date and time

Mycroft Holmes26-Mar-12 22:42

Mycroft Holmes

26-Mar-12 22:42

What tool are you using to insert the data (application you are building or a management tool) it looks like a management tool so try:

INSERT INTO rentals VALUES (1, 7782, TO_DATE('02/02/2010 09:45:00'), 'PQ07JHG');

I do believe todate takes a string literal as the first parameter.

Never underestimate the power of human stupidity
RAH

Re: Oracle, inserting date and time

Chris_B8426-Mar-12 22:54

Chris_B84

26-Mar-12 22:54

The software I'm using is Oracle SQL Developer.

I tried the above and got the error: "not a valid month".

I also tried this (the only different from the original post is that I put '' around them)

INSERT INTO rentals VALUES (1, 7782, to_date('02/02/2010 09:45:00', 'dd/mm/yyyy hh24:mm:ss'), 'PQ07JHG');

And with this effort, I got the error:

"format code appears twice"

Re: Oracle, inserting date and time

Chris_B8426-Mar-12 23:07

Chris_B84

26-Mar-12 23:07

Aha I found the problem and it works.

I put mm for minutes - it should be mi. Smile | :)

MySQL tables crashed

khurram_shahzad25-Mar-12 2:33

khurram_shahzad

25-Mar-12 2:33

Hi everyone,
I'm a software developer and just started my professional career life, question here i want to ask is ... that few days ago i was at client site to resolve some issue in application deployed by a company that uses MySQL for database, what i was found that there are four MySQL tables were crashed due to low harddisk space issue (I've searched on net that this is a usual problem with MySQL) that then i repaired by using myisamchk command. Error shown by MySQL when these were accessing is [table name] marked as crashed and should be repaired.

ok so this was the background scenario, now what i want to know/ask here is ... as described in above details that these 4 four tables were not accessible becuase they are crashed but I took a backup of this database and restore it to MySQL at another machine then i can access these table easily with no issue/error by MySQL, SO WHY THIS HAPPENS THAT AT ONE SIDE THESE ARE AS CRASHED AND ON ANOTHER THE BACKUP OF SAME DATABASE THAT WAS TAKEN WHEN THESE TABLES ARE CRASHED ARE THEN WORKING FINE.

I really will appreciate any kind of help on this topic Im just asking this for knowlegde and second thing is my boss also asked me the same question Poke tongue | ;-P

Re: MySQL tables crashed

jschell26-Mar-12 10:15

jschell

26-Mar-12 10:15

khurram_shahzad wrote:
..were crashed due to low harddisk space issue...SO WHY THIS HAPPENS

If I had to guess - because of low hard disk space.

By the way...really, really bad idea to let a database server run out of hard drive space. Doesn't matter what type of database either.

Re: MySQL tables crashed

Shameel27-Mar-12 3:46

Shameel

27-Mar-12 3:46

The message might be a bit misleading. Crashed does not mean your tables are goofed up, it only means that the database engine has set an internal flag on those tables that indicate that the tables can no longer take new rows because of lack of space.

Once you move your database to a new server, your database engine is smart enough to recalculate the new space and removes the flag.

Re: MySQL tables crashed

khurram_shahzad27-Mar-12 10:02

khurram_shahzad

27-Mar-12 10:02

Yes thanks .. I'm also thinking the same.

Merget Replication Issue

Ahmed Barradah23-Mar-12 22:05

Ahmed Barradah

23-Mar-12 22:05

Hi,
I am trying to make a merge replication between 2 remote machines: The remote server is running Windows 2008 Server with SQL database Server 2008 , the other local machine is running on a Windows7 and running also 2008 SQL Server.

I have configured my distribution database on the server and I have made a shared folder and gave it all the needed authority on the same Server as well.Then I have configured the Publisher on the same server along with the distribution and according to my reading that's the common used configuration.

Then I have configured my Subscriber on the local machine (Windows 7) with the same version of database.

However, once I start monitoring the replication from the subscriber I get this error "The schema script for tablename.csh could not be propagated to the subscriber". When I look into the publication side I see that there is an error about accessing the shared folder that I have created on the server, basically it cannot read it but I am sure that I have added all the needed authority for read/write to that folder, and I put this folder under the Administrators account and also I can see it under Computer management under the shared folder.

Sorry for my long question but I have worked hard on this and I would appreciate any help from the community.

Where did I go wrong with this? Is it the way I have shared this folder? Or the security agent.

I have followed the tutorial on this link http://www.sqlservercentral.com/stairway/72401/[^] which is really good but still I am getting the same error.

Your help is highly appreciated!!

Thank you!

Help with Calculations

Vimalsoft(Pty) Ltd21-Mar-12 21:44

Vimalsoft(Pty) Ltd

21-Mar-12 21:44

I need your help . i have a spreadsheet In the Spread Sheet in the First line there is a field “Unit Type Total PQ”.

That field is calculated from =F2*D2 and the value of F2 = 0.05 and D2 = 12 so Excel reports that the Field “Unit Type Total PQ” results into 0.56.

If I take a CALCULATOR and multiply 0.05 * 12 I get 0.6

And if I go to SQL I get with this

SQL

SELECT  CONVERT(DECIMAL(10,2),0.05) * 12

I get 0.60

Why is Excell giving me Incorrect values or how can i fix my SQL

Vuyiswa Maseko,

Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code.

C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.com
vuyiswa[at]dotnetfunda.com
http://www.Dotnetfunda.com

Re: Help with Calculations

Blue_Boy21-Mar-12 22:42

Blue_Boy

21-Mar-12 22:42

Hi Vuyiswa Maseko, Excel by default remove zero number from decimal value, if you want to display decimal value with 2 numbers with separator then right click on cell and Format Cells.. option then select Number category, there you can see option decimal places, where you can set 2 or another decimal places.

I Love T-SQL
"VB.NET is developed with C#.NET"
If my post helps you kindly save my time by voting my post.

Re: Help with Calculations

PIEBALDconsult22-Mar-12 3:16

PIEBALDconsult

22-Mar-12 3:16

Maybe your 0.05 is really 0.0466666666 ?

Re: Help with Calculations

Vimalsoft(Pty) Ltd22-Mar-12 6:13

Vimalsoft(Pty) Ltd

22-Mar-12 6:13

yes that is correct

asp.net

smart5521-Mar-12 19:39

smart55

21-Mar-12 19:39

Hi, I'm vickyraj.. I'm new to asp.net I'm creating studentrecord using database in asp.net.
I need prev and next button code i have tried to sort that one but i didn't get......

please... can anyone help me....to sort out here...
I'would be thankful to you.... Wink | ;)

vickyraj

Re: asp.net

Mycroft Holmes21-Mar-12 20:19

Mycroft Holmes

21-Mar-12 20:19

1. Read the guidelines on how to ask a question.
2. Pick the right forum - you really should ask this in the ASP forum.

Never underestimate the power of human stupidity
RAH

Re: asp.net

solutions@ashish21-Mar-12 22:47

solutions@ashish

21-Mar-12 22:47

You can use asp.net data-grid option control to populate your data into browser in the right side property explorer limit property set to as you want
In this case your data populate into pagination

Kumar Ashish

SQL queries for multiple columns depends on same column of another table

AhmedMasum19-Mar-12 8:21

AhmedMasum

19-Mar-12 8:21

See the following scenario:

Tables for the question:

SQL

Table : T1
c10  c12
---  ----
e1   A
e2   B
e3   C

Table: T2

c1  c2  c3  c4  c5   C6
--- -- --- ---  ---  ---
1   P  e1  e2   null  X
2   Q  e3  null null  Y
3   R  e2  e1   null  X
4   S  e3  e1   e2    Z


The c3, c4 and c5 columns of T2 table depends on c12 column of T1 table.

I want the following output:


c1  c2  c3  c4  c5
--- -- --- ---  ---
1   P   A   B   null
2   Q   C  null null
3   R   B   A   null
4   S   C   A   B

Can you help me to write an SQL to retrieve the above output from the table T1 and T2?

The SQL may be like:

SQL

Select c1, c2, t1.c12 as c3....... from t1, t2 where join condition

But I am not getting the actual sql in my mind.
So please help me regarding this.

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