Re: cross query question in access 2007 (sum & avg) - Database Discussion Boards

Re: Need Query for this

sujithkumarsl9-Jun-09 20:08

sujithkumarsl

9-Jun-09 20:08

you are great

My small attempt...

Re: Need Query for this

sujithkumarsl9-Jun-09 19:43

sujithkumarsl

9-Jun-09 19:43

hi all......

i got the solution... i think we can use the same technique which use for duplicate checking

SELECT COL1, COL2, count(*)
FROM table
GROUP BY COL1, COL2

i think this is fine

My small attempt...

Re: Need Query for this

_Damian S_9-Jun-09 19:47

_Damian S_

9-Jun-09 19:47

Wow, looks remarkably like the answer I gave you 6 mins ago... Wink | ;-)

Knowledge is knowing that the tomato is a fruit. Wisdom is not putting it in fruit salad!!

Booger Mobile - Camp Quality esCarpade 2010

Re: Need Query for this

sujithkumarsl9-Jun-09 20:08

sujithkumarsl

9-Jun-09 20:08

i seen your comment after i post my... anyway thanks... Big Grin | :-D

My small attempt...

cross query question in access 2007 (sum & avg)

E_Gold9-Jun-09 19:31

E_Gold

9-Jun-09 19:31

Hi is it possible with cross query to do this ? (in Access 2007)
I have this table:

50 | A1
60 | A1
70 | B1
80 | B1
90 | C1

I need to get this result:

A1 B1 C1
sum | avg sum | avg sum | avg
110 | 55 130 | 75 90 | 90

Re: cross query question in access 2007 (sum & avg)

E_Gold9-Jun-09 19:31

E_Gold

9-Jun-09 19:31

50 | A1

60 | A1

70 | B1

80 | B1

90 | C1

I need to get this result:

A1 ------------------ B1 --------------- C1

sum | avg ----- sum | avg ----- sum | avg

55 | 110 --------- 75 | 130 ------ 90 | 90

Re: cross query question in access 2007 (sum & avg)

_Damian S_9-Jun-09 19:46

_Damian S_

9-Jun-09 19:46

No, it's not. What are you actually trying to achieve?

Knowledge is knowing that the tomato is a fruit. Wisdom is not putting it in fruit salad!!

Booger Mobile - Camp Quality esCarpade 2010

Re: cross query question in access 2007 (sum & avg)

E_Gold10-Jun-09 0:14

E_Gold

10-Jun-09 0:14

I try this:

TRANSFORM Sum(Worki.Value) AS XXsum
SELECT Worki.Name AS Name, Worki.Tdate AS Tdate, Worki.ID AS ID
FROM Worki
GROUP BY Worki.Name, Worki.Tdate, Worki.ID
PIVOT Worki.Trit;

but I how can I get the average ?

password protect sql views

Anoop Brijmohun9-Jun-09 4:57

Anoop Brijmohun

9-Jun-09 4:57

hi guys,
Any idea on how i can protect my sql 2005 view on a client site, in which others cannot see what the view does?
been trying to look around on google, but have not found anything.

thanks
Anoop

Re: password protect sql views

Mycroft Holmes9-Jun-09 14:45

Mycroft Holmes

9-Jun-09 14:45

Give the view a schema prefix eg ABSystems.ViewName and use SQL security to restrict access to the view. This will not stop a quality DBA but will slow down the average user.

A DBA owns the database you are working on and as such will get access to your code somehow. I can think of 2 ways to get around this setup.

Never underestimate the power of human stupidity
RAH

connection parameters in configuration file

Fadi Yoosuf9-Jun-09 4:29

Fadi Yoosuf

9-Jun-09 4:29

hi,

I have a program which does some operations with database.(Single tier architecture)

I will sometimes have to move the application to other systems having the database.

In order to make the database connection parameters configurable, i am storing the params such as username, password etc. in an xml file.

Is it the correct way to handle the situation?

If so will it be safe to store the database password in an xml file? how can i solve the issue?

If the application is meant for ordinary users(who may not have the database files), how can we approach the problem? Cconfiguration file will not be easy for him to edit no?

Thank you
Fadi

Re: connection parameters in configuration file

Eddy Vluggen9-Jun-09 7:30

Eddy Vluggen

9-Jun-09 7:30

Fadi Yoosuf wrote:
Is it the correct way to handle the situation?

There is no "correct way". It is a good idea to store it in a configuration-file, since it makes it easier to change.

Fadi Yoosuf wrote:
If so will it be safe to store the database password in an xml file?

Yes and no. It might be safe on a NTFS-partition, depending on the settings. Passwords are rarely written into a file as plain text, so you could encrypt it if you wanted more security.

Fadi Yoosuf wrote:
how can i solve the issue?

Switch to "Windows Authentication Mode", and you won't need to save a password. This would require just a small change in your connection-string. Use "integrated security" instead of the username/password combination.

I are troll Smile | :)

Re: connection parameters in configuration file

Mycroft Holmes9-Jun-09 14:47

Mycroft Holmes

9-Jun-09 14:47

This is a standard approach and can be handled by using encrypted passwords, encrypting the config file or putting the password into the registry (not recommended these daya).

Never underestimate the power of human stupidity
RAH

Re: connection parameters in configuration file

Fadi Yoosuf10-Jun-09 8:12

Fadi Yoosuf

10-Jun-09 8:12

Thank you very much for the info.

But I wish to know one more thing.

How huge applications like encyclopedias stores information?

If they store in database, how is it possible to install them, without having the database server in the system?

Re: connection parameters in configuration file

Mycroft Holmes10-Jun-09 12:59

Mycroft Holmes

10-Jun-09 12:59

Fadi Yoosuf wrote:
How huge applications like encyclopedias stores information?

These apps distribute a database with the app and are considered single user applications. A DB like SQL Express, Access or there are a few other small, single user databases out there.

Never underestimate the power of human stupidity
RAH

Confusion on Full Text Searching

Brendan Vogt9-Jun-09 3:33

Brendan Vogt

9-Jun-09 3:33

Hi,

I am using SQL Server 2005. I need to implement a full text search capability. I decided to use CONTAINS, and not sure if it is the correct statement to use. If the user types in word1 word2 in the search textbox, how would I send this to the CONTAINS statement? Something like word1 OR word2? What happens if he tries to search via phrase?

Please can someone help?

Regards
Brendan

Re: Confusion on Full Text Searching

Eddy Vluggen9-Jun-09 7:23

Eddy Vluggen

9-Jun-09 7:23

You can use the CONTAINS-statement to search for pieces of text. It's not very usefull if you want to search for multiple parts; you'd have to check each text-column in each table for each word that the user types. Short example, we search our db for the text "Hello world".

Now, what should happen if table A holds this exact string, and table B has a text-field that starts with the word "World" and ends with the words "Hello, hello!"?

Worse, what happens when the user searches for the text "This is the world?" Would it find both records, or would it show those records that have the most occurences of the word "the"? These noise-words should be filtered to ensure relevant results.

All in all, FTS[^] isn't just a query you implement (although that can be starting-point for building your own), and most people that are using SQL Server resort to the text-indexing service of MSSQL[^].

You could also consider Lucene[^], there are some articles here[^] on CP on integrating it into your application.

Good luck Smile | :)

I are troll Smile | :)

Perfomance Question on the Following Query

Vimalsoft(Pty) Ltd8-Jun-09 22:39

Vimalsoft(Pty) Ltd

8-Jun-09 22:39

Good Morning All

i have the Following Query

ALTER PROCEDURE [dbo].[sp_Staff_Select_NEW] 
(
@lETTER CHAR(2)
)
AS
SELECT DISTINCT ID, DESCR, NOTE  
FROM TBL_STAFF
WHERE LEFT(ltrim(rtrim(Descr)), 1)  = @lETTER
ORDER BY Descr
--SELECT DISTINCT ID, DESCR, NOTE  
--FROM TBL_STAFF
--WHERE SUBSTRING(Descr,1,1)= @lETTER
--ORDER BY Descr

As you can see there is a Commented part. Which one is more Faster between

SELECT DISTINCT ID, DESCR, NOTE  
FROM TBL_STAFF
WHERE LEFT(ltrim(rtrim(Descr)), 1)  = @lETTER
ORDER BY Descr

and

SELECT DISTINCT ID, DESCR, NOTE  
FROM TBL_STAFF
WHERE SUBSTRING(Descr,1,1)= @lETTER
ORDER BY Descr

Thank you

Vuyiswa Maseko,

Few companies that installed computers to reduce the employment of clerks have realized their expectations.... They now need more and more expensive clerks even though they call them "Developers" or "Programmers."

C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.somee.com
http://www.vuyiswamaseko.tiyaneProperties.co.za
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/

Re: Perfomance Question on the Following Query

J4amieC8-Jun-09 23:00

J4amieC

8-Jun-09 23:00

Hold on while I consult my crystal ball.

If you want to check the performance try putting a million records in a table with roughly equal number of initial letter and profile the two queries.

As an asside, your two queries may give different results as the one using the LEFT function ltrim and rtrim's whereas the SUBSTRING one does not.

Re: Perfomance Question on the Following Query

Vimalsoft(Pty) Ltd8-Jun-09 23:11

Vimalsoft(Pty) Ltd

8-Jun-09 23:11

lol

The Thing is that am using in my Filtering menu, and a user wait for 5 seconds before he sees a Menu with the Record based on the Alphabet.

Thanks For your Comment.

Can you cache the data ?

David Mujica9-Jun-09 2:48

David Mujica

9-Jun-09 2:48

If you are looking for the fastest way to retrieve data, maybe you should look at populating some type of collection once at the start of the applicaiton so that subsequent lookups do not make database calls.

Generally indexes won't work in this situation because you are using a function on the field, "Descr".

Also, why would you be storing leading spaces in the "Descr" field ? Perform a LTrim(Descr) on the data before storing it.

Consider this before implementing a cache:
1) How many rows are in the tbl_staff table ? Too many rows may cause excessive memory usage by your application, but if we are talking even a few thousand, you should be OK.

2) How often are rows inserted into the tbl_staff table ? How will you synchronize the collection with the new data in the database ? (This can be done if you have built a data abstraction layer ...)

Re: Can you cache the data ?

Vimalsoft(Pty) Ltd9-Jun-09 20:57

Vimalsoft(Pty) Ltd

9-Jun-09 20:57

Good Morning Again

i have changed my statement and it look like this

/*This Function will get the Staff names based on the First Character 
* */
ALTER PROCEDURE [dbo].[sp_Staff_Select_NEW]
(
@lETTER VARCHAR(1)
)
AS
SELECT ID, DESCR, NOTE  
FROM TBL_STAFF
WHERE SUBSTRING(Descr,1,1)= @lETTER
GROUP BY Descr,NOTE,ID

This is what i changed.

1)I used a group by instead of a distinct

2)Above the i used char(2) while i was only accepting one character, its a small thing but one bit counts

3)i have added a index on the Field Descr

And it wo9rks like wonders the way it is fast.

Thank you for your Help and Comment

Vuyiswa Maseko

Check the Execution Plan

David Mujica10-Jun-09 3:28

David Mujica

10-Jun-09 3:28

Good to hear things are working for you. You may want to use the option called "Display Estimated Execution Plan" in Studio Manager to see exactly how this store procedure is executing.

1) In the query window, do something like EXEC sp_Staff_Select "M"
2) From the Query menu, select the "Display Estimated Execution Plan"
check the results, you want to make sure that it is not performing any "scans", but rather it uses "seeks". A scan means that it is reading the table or index top to bottom ... lots of I/O.

My reason for suggesting this is that I don't believe your index on "Descr" is being used. Suspicious | :suss:

Check it out. If nothing else, it will show you how you can use the execution plan to help you optimize your queries. Thumbs Up | :thumbsup:

Re: Check the Execution Plan

Vimalsoft(Pty) Ltd10-Jun-09 3:33

Vimalsoft(Pty) Ltd

10-Jun-09 3:33

Thank you David i will try it Smile | :)

DB2 Update Query Problem

DBLearner8-Jun-09 6:38

DBLearner

8-Jun-09 6:38

Consider two tables T1 and T2.

T1 has two columns F1 and F2.
T2 has two columns F3 and F4.

We need to set F1 = F3 where F2 = F4.

A query for this made for ORACLE works, but does not work under DB2. What will be the DB2 specific implementation of this query? (and the standard SQL92 implementation?.

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