|
i dont know the EXACT reason why, but i have been told(by my step mom who's been a data architect at a large insurance company's data warehouse for the past 20 years, and many other people) that SELECT * FROM has significant and unnecessary server overhead. And when your working with db's that have terras and terras of data you can and will bring the system down.
The using of SELECT * FROM worn have a noticeable effect if your working on a much smaller scale db but its considered bad practice.
SQL just has these little things that bring performance down, another example would be using the prefix "sp_" in front of a stored procedure's name... the reason for that is that sp stands for system procedure and NOT stored procedure, so SQL goes and looks through the system procedures first before moving on to stored procedures.
Cheers 
Harvey Saayman - South Africa
Junior Developer
.Net, C#, SQL
you.suck = (you.passion != Programming)
|
|
|
|
|
HarveySaayman wrote: know the EXACT reason why
Yah, I have the same recollection and only use it under special circumstances.
HHmmm, maybe I should talk to your step mum  . I think the sp_ issues is well documented and well known but the Select * issues is a little more obscure.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
There is nothing difficult with this!
If you need all columns of a table - use *, if you need only a few columns, specify them by their name! And of course you stress the DB Server more by querying all columns of a table instead of selecting only a few of them.
|
|
|
|
|
| Hi
on Data base one column has data like this As a representative of Preston Insulation, I
when i show this on my report those styles has to apply for the data .
the data base column can be varchar or xml any data taype.
Thanks in advance
|
|
|
|
|
ch.ramesh wrote: those styles has to apply for the data .
How are you storng these styles? If you look at the code to store the style yu should be able to reverse it back out to apply in your report?
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Hi Bob
thanks for your reply.
we are fecthing the Data from DB as
'As a representative of Preston Insulation, I would like to congratulate you on your recent construction permit and introduce myself, my Company and the products that we offer. Please take a moment to review the following information, as having the right partners in the building process will save time, effort and dollars. '
like this we are getting data.from the client we are getting that data .
we can change this column to any data type .
we have to show the report with the styles in .
Thanks
|
|
|
|
|
Sorry, I don't know what you are talking about.
ch.ramesh wrote: As a representative of Preston Insulation, I would like to congratulate you on your recent construction permit and introduce myself, my Company and the products that we offer. Please take a moment to review the following information, as having the right partners in the building process will save time, effort and dollars.
has no styles to apply.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
First off, I really don't know much about vbScript, so hopefully what I have thrown together won't look too awful.
I am trying to come up with a way to label parcels in ArcGIS, and I am about ready to throw my computer out the window. I have to use vbScript to create the labels by connecting to a database pulling the data from multiple tables, assembling a text string and assigning it to a polygon. Unfortunately the vbScript engine in ArcGIS doesn't support joins, so I came up with a way to create one query, get the data I need from one table and stuff it into an array (if necessary) for the second query. Unfortunately, I am getting bombarded with errors when I try to do the second query, and I have scoured search engines for a solution with no luck at all. Everything I found tells me how to do one query, but doesn't address the problem of running multiple queries. Any help that I can be given would be greatly appreciated. Everything works fine until I try to do the second query. I'm not sure what the procedure is to do another query, or if it's even possible.
Here is what I have written so far, including the portions that don't work (they are commented out).
Thanks a lot in advance!
Function FindLabel ([FEATURE_ID])
Dim strPrclQry, strLseQry, strinfo, LeaseNum, aLeaseList, LabelCount, FinalLabel, sLeaseList
'form query string
strPrclQry = "SELECT LEASE_NAME FROM TRACTS WHERE TRACTS.FEATURE_ID = '"&[FEATURE_ID]&"' ORDER BY LEASE_NAME ASC"
Dim ADOConn
set ADOConn = CreateObject("ADODB.Connection")
Dim rsPrcl
set rsPrcl = CreateObject("ADODB.Recordset")
ADOConn.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=.\DART_PROJECTS.mdb"
ADOConn.CursorLocation = 3
rsPrcl.Open strPrclQry, ADOConn, 3, 1, 1
'if no record is found, return empty string
Select Case rsPrcl.RecordCount
Case -1, 0
strInfo = " "
Case 1
'reading only the first record
LeaseNum = rsPrcl.Fields("LEASE_NAME").Value
strInfo = LeaseNum + strInfo
aLeaseList = strInfo
Case Else
for I = 1 to rsPrcl.RecordCount
if I <> 1 then
strInfo = " " + strInfo
End If
LeaseNum = rsPrcl.Fields("LEASE_NAME").Value
strInfo = LeaseNum + strInfo
rsPrcl.Movenext
Next
aLeaseList = Split(strInfo)
End Select
'closing connections
rsPrcl.Close
Set rsPrcl = Nothing
'put arrays into labels
If IsArray(aLeaseList) <> 0 then
LabelCount = 0
For Each sLeaseList In aLeaseList
if LabelCount <> 0 then
FinalLabel = vbNewLine + FinalLabel
End If
LabelCount = 1
FinalLabel = sLeaseList + FinalLabel
Next
Erase aLeaseList
'If there is only one lease in the parcel
Else
'form query string
' strLseQry = "SELECT L_NAME, F_NAME1, F_NAME2, ADD_INFO FROM LEASES WHERE LEASE_ID = '"&aLeaseList&"'"
' Dim rsLse
' set rsLse = CreateObject("ADODB.Recordset")
' rsLse.Open strLseQuery, ADOConn, 3, 1, 1
'building text string if there is a name
' If rsLse.Fields("L_NAME").Value <> "" then
' FinalLabel = rsLse.Fields("L_NAME").Value
' if IsNull(rsLse.Fields("F_NAME1") = "False" then
' strLabelInfo = rsLse.Fields("L_NAME")&", "&LEFT(rsLseFields("F_NAME1"), 1)
' End If
' if IsNull(rsLse.Fields("F_NAME2") = "False" then
' strLabelInfo = strLabelInfo + "/" & LEFT(rsLse.Fields("F_NAME2"), 1)
' End If
' if IsNull(rsLse.Fields("ADD_INFO") = "False" then
' strLabelInfo = strLabelInfo & " " & rsLse.Fields("ADD_INFO")
' End If
' End If
FinalLabel = aLeaseList
End If
'closing connections
'rsLse.Close
'Set rsLse = Nothing
ADOConn.Close
Set ADOConn = Nothing
FindLabel = FinalLabel
End Function
|
|
|
|
|
Try releasing your ADO.Recordset - you are using it twice in your code without a release - or create two record sets.
|
|
|
|
|
I would say that the following line looks iffy:
strLseQry = "SELECT L_NAME, F_NAME1, F_NAME2, ADD_INFO FROM LEASES WHERE LEASE_ID = '"&aLeaseList&"'"
If LeaseNum values are numbers then your aLeaseList should be comma-separated, then use:
strLseQry = "SELECT L_NAME, F_NAME1, F_NAME2, ADD_INFO FROM LEASES WHERE LEASE_ID IN ("&aLeaseList&")"
If that doesn't work then post the value of your strLseQry variable to this thread.
|
|
|
|
|
Thanks for the quick responses, I've made some real progress and have gotten the queries to work!
|
|
|
|
|
Don´t know exactely if this will help, but you do not necessarily must use "JOIN" to get data out of several tables. You can define the connection between two or more tables in the "Where" clause. In your case it would look something like this:
SELECT TRACTS.LEASE_NAME, LEASES.L_NAME, LEASES.F_NAME1, LEASES.F_NAME2 FROM TRACTS, LEASES
WHERE TRACTS.LEASE_NAME=LEASES.L_NAME AND TRACTS.FEATURE_ID = '"&[FEATURE_ID]&"'
ORDER BY LEASE_NAME ASC"
You will receive 1 recordset with all needed data.
There is one little blemish within that kind of SQL. To connect several tables by a "JOIN" statement works quicker than only using the "Where" clause because "JOIN" is an optimized procedure. But in Your case that doesn´t matter!
|
|
|
|
|
hi. I'm trying to delete a row by specifying the actual row number. When I call all columns from one table it does work but when I already multiselect them I get an error: Msg 4405, Level 16, State 1, Line 1
View or function 'Records ORDERED BY rowID' is not updatable because the modification affects multiple base tables.
This one works:
WITH [Records ORDERED BY rowID] AS
(SELECT row_number() OVER (ORDER BY Records.[Plate Number] ASC) AS rowID,*)
DELETE FROM [Records ORDERED BY rowID] WHERE rowID=1
This one gets the error:
WITH [Records ORDERED BY rowID] AS
(SELECT row_number() OVER (ORDER BY Records.[Plate Number] ASC) AS rowID,
Records.[Officer ID],
DriverInfo.[Plate Number],
DriverInfo.[License Number],
DriverInfo.[Conduction Number],
DriverInfo.[Vehicle Category],
DriverInfo.[Vehicle Type],
DriverInfo.[Vehicle Brand],
DriverInfo.[Last Name],
DriverInfo.[First Name],
DriverInfo.[Middle Name],
DriverInfo.[Birth Date],
DriverInfo.[Registered Address],
DriverInfo.[Address' City Code],
Records.[Violation Commited],
Violations.[Violation Code],
Records.[Street Name],
Records.[City of Apprehension],
Cities.[City Code],
Violations.[Fine Amount],
Records.[Date/Time Apprehended]
FROM Violations,DriverInfo,Records,Cities
WHERE Records.[Plate Number] like DriverInfo.[Plate Number]
and Records.[Violation Commited] like Violations.Violations
and Records.[City of Apprehension] like Cities.City)
DELETE FROM [Records ORDERED BY rowID] WHERE rowID=1
|
|
|
|
|
Because you are pulling information from multiple tables, it can not figure out which row number you are using
Try changing to this for example:
(SELECT Records.[Plate Number], row_number() OVER (ORDER BY Records.[Plate Number] ASC) AS rowID,
|
|
|
|
|
I see. How can I use this when calling multiple tables then? I need to invoke multiple tables because I only want to delete the ones that satisfy that multiselect query. If I just use one table. The other rows that are not supposed to be included in my deletion will be deleted. Like when I want to delete row 1 in the condition of the multiselect I created, and when I only specify one table, the row 1 that it will be reading is the row 1 of that table not the multiselected table. Got any suggestions? 
|
|
|
|
|
Is it this what you are looking for?
WITH ORDERED_RECORDS AS(
SELECT
row_number() OVER (ORDER BY Records.[Plate Number] ASC) AS rowID
,Records.[Officer ID]
,DriverInfo.[Plate Number]
,DriverInfo.[License Number]
,DriverInfo.[Conduction Number]
,DriverInfo.[Vehicle Category]
,DriverInfo.[Vehicle Type]
,DriverInfo.[Vehicle Brand]
,DriverInfo.[Last Name]
,DriverInfo.[First Name]
,DriverInfo.[Middle Name]
,DriverInfo.[Birth Date]
,DriverInfo.[Registered Address]
,DriverInfo.[Address' City Code]
,Records.[Violation Commited]
,Violations.[Violation Code]
,Records.[Street Name]
,Records.[City of Apprehension]
,Cities.[City Code]
,Violations.[Fine Amount]
,Records.[Date/Time Apprehended]
FROM Violations
,DriverInfo
,Records
,Cities
WHERE Records.[Plate Number] like DriverInfo.[Plate Number]
and Records.[Violation Commited] like Violations.Violations
and Records.[City of Apprehension] like Cities.City
)
DELETE FROM Records
WHERE Records.[Plate Number]
IN (
SELECT [Plate Number]
FROM ORDERED_RECORDS
WHERE rowID=1
)
|
|
|
|
|
Oh gosh. Thanks! I GOT IT WORKING FINALLY!!!! THANK YOU SOOO SOOO MUCH!!!!!! 
modified on Thursday, July 17, 2008 12:38 PM
|
|
|
|
|
Hi all,
I want to fill a grid (no hirarchical) where to combine customer data and the last selling item of each customer in a row (getting only the last sellings depending on sells_date).
My idea was
SELECT C.id,C.customerdata,S.id,S.sellsdata FROM dbo.customer AS C LEFT OUTER JOIN (SELECT TOP 1 id,sellsdata FROM dbo.sells ORDER BY sells_date DESC) AS S ON C.id=S.id
Whith this string I get only one selling item and not one item for each.
How does the correct SQL-String look likes ?
tnx in advance
Frank
|
|
|
|
|
SELECT C.id,C.customerdata,S.id,Selldata = (Select TOP 1 S.sellsdata FROM dbo.sells S Where C.id=S.id ORDER BY sells_date DESC) FROM dbo.customer AS C
Don't know why you want to return C.id and S.id since they should be the same anyhow.
|
|
|
|
|
Thank you for your answer,
you are right S.id is useless.
I played with your solution but on this way I only can get one item from the sells table ( = sellsdata). What's to do when I nead all the items from sells table row ?
tnx
Frank
|
|
|
|
|
fracalifa wrote: What's to do when I nead all the items from sells table row ?
Then you can go back to using a join to do so.
|
|
|
|
|
This was my first suggestion, but then I'm already at the initial position of the problem !
more ideas ?
|
|
|
|
|
I'm confused.
Originally from what I understand, your question was, how do you get the data such that you get all the rows of the first table, combined with 1 row of sell table for each row of the first table.
Now you say you want all of the rows from sell? What exactly do you want? Give me examples please.
|
|
|
|
|
Your first understanding is right.
I need for all customers their last sold product. But the last product information consits of 4 items. So the solution grid should look like that
ID-----Name----Address------> LastSoldItem----ProductNr.----ProductPrice----LastSellingDate
1111--Miller--1.st Avn----------Ship-----------00112233--------1000,00-----------05/09/2008
1123--Riven---Hallstreet--------Car------------00055564--------2000,00-----------07/08/2008
etc.
Every customer has sold many products and I want to see only the last sold product consisting of 4 items.
The tables are related by the customer-ID.
With your suggested solution I only get one item of the last sold product. (and not all the 4 I need)
I hope this clears your confusion....
tnx
frank
|
|
|
|
|
fracalifa wrote: Every customer has sold many products and I want to see only the last sold product consisting of 4 items.
fracalifa wrote: With your suggested solution I only get one item of the last sold product. (and not all the 4 I need)
You mean 4 columns from the sells table?
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
