Re: conditional select statment - Database Discussion Boards

enipla10-Mar-09 3:14

10-Mar-09 3:14

Yep it has focus. I open the query designer, add a table to it and right click in the designer.

The Execute SQL option is grayed out.

Is this how you operated it?

Thanks

Re: Server 08 Execute SQL

Colin Angus Mackay10-Mar-09 13:29

Colin Angus Mackay

10-Mar-09 13:29

enipla wrote:
query designer

Actually, I just discovered there is a thing called a Query Designer. I normally just write queries. I never knew about this crazy thing.

It is greyed out for me too.

Have you considered just writing the query SQL yourself? That isn't greyed out.

*Developer Day Scotland - Free community conference Delegate Registration Open

Re: Server 08 Execute SQL

enipla11-Mar-09 3:28

enipla

11-Mar-09 3:28

Yeah, I can write the query myself, but the designer is VERY helpful, and that's how I'm used to doing it.

It worked in previous versions.

This is nuts. MS help hasn't been able to find anything.

uniting 3 tables

suni_dotnet6-Mar-09 5:28

suni_dotnet

6-Mar-09 5:28

Hi All,
iam using if else for updating and inserting, i need to do this for 3 tables how can i unite them using unions or join..

IF EXISTS (SELECT col1,col2,col3,col4
FROM TableB)

UPDATE TableB
SET col2=TableA.col2,
col3=TableA.col3,
col4=TableA.col4
from TableA
where (TableB.col1 = TableA.col1
and TableA.Col5='no')

ELSE
INSERT INTO TableB (col1,col2,col3,col4)
select TableA.col1,TableA.col2,TableA.col3,TableA.col4

from TableA
where TableA.Col5='no';

and i need to repeat this code for TableC and TableD (along with TableA) for updating TableB. How can i unite all in one procedure.Hope iam clear if not i can...Please help...
may be the solution is simple but i dont know...
i tried but unable to ...
i cannot use 3 sp's, i need all in one procedure
Thanks in advance

Re: uniting 3 tables

Colin Angus Mackay7-Mar-09 12:05

Colin Angus Mackay

7-Mar-09 12:05

I don't fully see what you are trying to do. But if "unite all in one procedure" you mean that it all happens as one operation then you might want to look at transactions. If you wrap all of this up in a transaction it will appear as one procedure. Nothing else accessing the database will be able to see a half updated set of data. They'll either see the data as it was before the transaction, or they'll be locked out until the transaction is complete and when the lock is released they'll see it as the complete result.

*Developer Day Scotland - Free community conference
*Colin Angus Mackay's Blog
*Latest Scottish Developers Newsletter

Vogon Building and Loan advise that your planet is at risk if you do not keep up repayments on any mortgage secured upon it. Remember that the force of gravity can go up as well as down.

conditional select statment

Meax5-Mar-09 22:31

Meax

5-Mar-09 22:31

i have these data in the database table, Meetings

MeetingID Details Hour Minute
1 Meeting Details1 14 10
2 Meeting Details2 14 15

how can i select all the fields of MeetingID 2?

<br />
SELECT     MeetingID, Hour, Minute, Details<br />
FROM         Meetings<br />
WHERE     (Hour =<br />
                          (SELECT     MAX(Hour) <br />
                            FROM          Meetings)) AND (Minute =<br />
                          (SELECT     MAX(Minute) <br />
                            FROM          Meetings))<br />

but this code doesn't fetch any record

Re: conditional select statment

J4amieC5-Mar-09 22:42

J4amieC

5-Mar-09 22:42

Of course it doesn't - the row with the max hour might not be the same row as the row with the max minute.

If you stored the meeting time in a single field (of type datetime or smalldatetime) which would be the right solution, this would be easy.

SELECT TOP 1 * FROM Meeting ORDER BY MeetingDateTime DESC

As it is with your table you can just do

SELECT TOP 1 * FROM MEeting Order BY Hour DESC, Minute DESC

Re: conditional select statment

Meax5-Mar-09 23:14

Meax

5-Mar-09 23:14

thanks for helping out

Re: conditional select statment

Rupesh Kumar Swami5-Mar-09 22:58

Rupesh Kumar Swami

5-Mar-09 22:58

whether you search for MeetingID =2 or for which where Meeting time is maximum

I think you want to get the all detail of meeting for which meeting time is highest. use following query

 SELECT *<br />
FROM mettings<br />
WHERE (hour*60+minute) =(select max(hour*60+minute) from mettings)

hope this helps

Rupesh Kumar Swami
Software Developer,
Integrated Solution,
Bikaner (India)

My Company
Award: Best VB.NET article of June 2008: Create Column Charts Using OWC11

Re: conditional select statment

Meax5-Mar-09 23:16

Meax

5-Mar-09 23:16

thank you for helping out

Re: conditional select statment

J4amieC6-Mar-09 0:08

J4amieC

6-Mar-09 0:08

Rupesh Kumar Swami wrote:
SELECT *
FROM mettings
WHERE (hour*60+minute) =(select max(hour*60+minute) from mettings)

Why? Multiply the hour by 60 and add the minutes? If anything the other way round, surely?

Re: conditional select statment

Luc Pattyn6-Mar-09 0:42

Luc Pattyn

6-Mar-09 0:42

the math is fine, it is calculating minutes since midnight.
On a big table it could be slow...

Smile | :)

Luc Pattyn [Forum Guidelines] [My Articles]

- before you ask a question here, search CodeProject, then Google
- the quality and detail of your question reflects on the effectiveness of the help you are likely to get
- use the code block button (PRE tags) to preserve formatting when showing multi-line code snippets

Re: conditional select statment

Rupesh Kumar Swami6-Mar-09 1:07

Rupesh Kumar Swami

6-Mar-09 1:07

J4amieC wrote:
Why? Multiply the hour by 60 and add the minutes?

if there are multiple records, for which Metting time is equal to Highest Metting time, then it display all records.

However your suggestion is better.

Rupesh Kumar Swami
Software Developer,
Integrated Solution,
Bikaner (India)

My Company
Award: Best VB.NET article of June 2008: Create Column Charts Using OWC11

how to encrypt stored procedure.

Manprit.bagga5-Mar-09 18:10

Manprit.bagga

5-Mar-09 18:10

i provide my database to client and all stored procedured are open he can view ,so is there any way to encrypt the stored procudered
i have used with encryption clause it is good but it can be easily decrypted by a software which is freely available on net.

Re: how to encrypt stored procedure.

Eddy Vluggen6-Mar-09 0:03

Eddy Vluggen

6-Mar-09 0:03

Why did you give your database-password to the client? If he can't open the damn thing, he can't peek in it Smile | :)

I are troll Smile | :)

Re: how to encrypt stored procedure.

Manprit.bagga6-Mar-09 23:45

Manprit.bagga

6-Mar-09 23:45

actually we have to take the database back up and then we send it to client wherein client restore the database and make changes in web config of asp.net and uses the application

so is there any way where we can lock the database so that he can not peek in or encrypt the stored procedure

Re: how to encrypt stored procedure.

Adrian Pasik7-Mar-09 3:44

Adrian Pasik

7-Mar-09 3:44

You can do smart things. Just make some table with sources of your procedures encrypted in some heavy algorithm. Then decode it outside of database (or even inside through some .dll with C# CLR integration) and execute it programmatically. Or you can write stored procedure that will exec your encrypted stored procedure Smile | :)

Wish you luck!

Adrian Pasik

sql query and index

Shajeel5-Mar-09 17:36

Shajeel

5-Mar-09 17:36

Hi all,

I am using query something like that

select * from table1 where column1 = 'value1' and column2 = 'value2';

and I have created the index like this

create index index1
on table1(
column2
);

i have used only one column because column2 is usually unique and can only sometimes repeat

Will the query will use the index or do i have to create index with both columns? I am using Oracle database.

Regards
Shajeel

Client would like to search.... Everything

thrakazog5-Mar-09 14:00

thrakazog

5-Mar-09 14:00

The program I'm working on has a screen full of project data. The client wants to be able to run a "Google like" text search across all the text/lookup fields to return matching projects. Of course they want to be able to search on phrases also, matching all words, some words, etc.

The data is spread across around 10 tables. My first thought was to use Contains() in some capacity, but the db isn't full-text indexed. I'm not sure it would help if it was.

My remaining options seem to be:
1. Concatenate all the string info for each record into a field. Build up a custom select string with multiple Like searches, one for each word in the phrase to search. Run this with exec. Pretty sure the performance will suck on this one.

or

2. Concatenate all the string info for each record into a field. Select this data into a temp table and use a cursor to delete the non-matches for each word in the phrase to search. Pretty sure the performance will suck on this one also.

Are there any options I'm missing or has anyone got any better ideas?

Re: Client would like to search.... Everything

Eddy Vluggen6-Mar-09 0:01

Eddy Vluggen

6-Mar-09 0:01

thrakazog wrote:
1. Concatenate all the string info for each record into a field. Build up a custom select string with multiple Like searches, one for each word in the phrase to search. Run this with exec. Pretty sure the performance will suck on this one.

Well, it ranges from sucky to extremely sucky, depending on the implementation. I'm hoping to write an article about this topic in the weekend Smile | :)

A fast tip; why not create a shadow-table that holds the same columns, as varchar-types? It's a lot faster than converting everything on the fly, and you can let the server insert a varchar-record with a trigger from the original table.

Good luck Smile | :)

I are troll Smile | :)

Re: Client would like to search.... Everything [modified]

Eddy Vluggen7-Mar-09 3:00

Eddy Vluggen

7-Mar-09 3:00

There's a new article (not by me btw), containing a simple search-engine algorithm[^] that could be adapted to work with SQL CE Smile | :)

--edit--
This[^] is the article that you wanted Cool | :cool:

I are troll Smile | :)

modified on Saturday, March 7, 2009 3:22 PM

error?

suni_dotnet5-Mar-09 11:27

suni_dotnet

5-Mar-09 11:27

Hi All,
Iam Newbie...

IF EXISTS (SELECT RoomID,DateWhenFilled,DateWhenFree,TimeForHouseKeeping
FROM HouseKeeping)

UPDATE HouseKeeping
SET DateWhenFilled=PresidentialSuite.DateWhenFilled,
DateWhenFree=PresidentialSuite.DateWhenFree,
TimeForHouseKeeping=PresidentialSuite.TimeForHouseKeeping
where (HouseKeeping.RoomID = PresidentialSuite.RoomID
and PresidentialSuite.Available='no')

ELSE

INSERT INTO HouseKeeping (RoomID,DateWhenFilled,DateWhenFree,TimeForHouseKeeping)
select PresidentialSuite.RoomID,PresidentialSuite.DateWhenFilled,PresidentialSuite.DateWhenFree,
PresidentialSuite.TimeForHouseKeeping
from PresidentialSuite
where PresidentialSuite.Available='no';

Structure of both tables are not same.
with this Stored Procedure, Iam getting the error...
Msg 4104, Level 16, State 1, Line 4
The multi-part identifier "PresidentialSuite.RoomID" could not be bound.
Msg 4104, Level 16, State 1, Line 4
The multi-part identifier "PresidentialSuite.Available" could not be bound.

Iam unable to find, what the error is?
Please help

Re: error?

Blue_Boy5-Mar-09 13:04

Blue_Boy

5-Mar-09 13:04

Are you sure that columns RoomID and Avaliable exists in table PresidentialSuite?

For more info check this google result[^].

I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.

Re: error?

suni_dotnet5-Mar-09 13:24

suni_dotnet

5-Mar-09 13:24

thanks for ur reply..
yes, they exist.
and RoomID is primary key.
and if i modify the select statement (i just added braces and no other changes)
select (PresidentialSuite.RoomID,PresidentialSuite.DateWhenFilled,PresidentialSuite.DateWhenFree,PresidentialSuite.TimeForHouseKeeping)

then iam getting this error:
Msg 102, Level 15, State 1, Line 14
Incorrect syntax near ','.

Re: error?

Blue_Boy5-Mar-09 13:43

Blue_Boy

5-Mar-09 13:43

this T-SQL code

select (PresidentialSuite.RoomID,PresidentialSuite.DateWhenFilled,PresidentialSuite.DateWhenFree,PresidentialSuite.TimeForHouseKeeping)

is not completed,it must contain from caluse to specify from which table those columns comes.

I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.

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