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.
Try:
select S.siteId, S.siteName, sum(D.Impression) as Impression
from dbo.AE_Dimension as D
inner join dbo.AE_Channel as C
on D.channelId = C.channelId
inner join dbo.AE_Site as S
on C.siteId = S.siteId
where D.cpm = '0.5$'
group by S.siteId, S.siteName
Note that using table-aliases (e.g. "D" instead of "dbo.AE_Dimension") makes it more readable.
Have you tried actually running "SELECT * FROM get_user(1)" from an ADO command? It should work fine - although most people tend to use stored procedures for this type of thing.
modified on Friday, April 25, 2008 6:02 AM
If the databases are on the same server then there is no need to replicate the tables because you can reference tables using:
select * from <database>.<owner>.<table>
If the databases are on separate servers, but have reliable connectivity then consider using "linked servers" (look them up in BOL). Otherwise consider using "replication" (look it up in BOL). Hope that helps. Andy
The following SQL would return all of the data from all 3 tables:
SELECT ppar_engparameters.parmval, ppar_engparameters.parmname,
prcd.prcdname||' '||prcd.prcdversion, prcd.activeflag,
prcd.prodstatis, catg_pll.catnumber, catg_pll.category
FROM ppar_engparameters
INNER JOIN prcd
ON prcd.prcdname = ppar_engparameters.prcdname
AND prcd.activeflag = 'A'
LEFT OUTER JOIN catg_pll
ON catg_pll.partprcdname = prcd.prcdname
AND catg_pll.partprcdversion = prcd.prcdversion
WHERE ppar_engparameters.parmval= 'MyValue'
I have removed the "distinct" clause that you had in the original. This is often used to hide poorly-designed tables - you understand where-and-why you are getting duplicates before blindly using this. Proper indexes are required to make this run fast. I would expect the following indexes (or primary keys) from your original description:
--For the link from product-parameter to product.
create unique index prcd_idx1 on prcd (prcdname, prcdversion)
--For the link from product.
create index catg_pll_idx1 on catg_pll (partprcdname, partprcdversion)
--For the search on parameter values.
create index ppar_engparameters_idx1 on ppar_engparameters
(parmval, activeflag)
Other indexes may also be required for other activities. There should ideally be a primary key or unique-index on every table. Oracle's query optimiser uses statistical data from indexes to determine the best way of answering your queries. There is a command (that I cannot remember) for recalculating these statistics. Oracle is also able to display the query-plan that it uses - so you would be able to tell exactly what Oracle is doing under the covers. Search Google[^] for more information. Hope that helps. Regards Andy
How about:
select Q.ID, Q.item, Q.vendor, Q.Quote, V.phone, V.fax
from (
select ID, item, min(quote) AS MinQuote
from quote_tb
where ID = 11
group by ID, item
) AS MinQ
inner join quote_tb Q
on Q.ID = MinQ.ID
and Q.item = MinQ.item
and Q.quote = MinQ.quote
inner join vendor_tb AS V
on V.name = Q.vendor
order by Q.ID, Q.item
Note that if two different suppliers give the same quote for the same item then they will both be displayed. You could correct that using:
...
from (
select Q.ID, Q.item, min(vendor) AS MinVendor
from (
select ID, item, min(quote) AS MinQuote
from quote_tb
where ID = 11
group by ID, item
) AS MinQ
inner join quote_tb AS Q
on Q.ID = MinQ.ID
and Q.item = MinQ.item
and Q.quote = MinQ.quote
group by Q.ID, Q.item
) AS MinS
inner join quote_tb Q
on Q.ID = MinS.ID
and Q.item = MinS.item
and Q.vendor = MinS.MinVendor
...
It may help you to run each sub-query in turn to see how it is built-up. If you are using SQL-Server 2005 then you may be able to rewrite this more concisley using the windowing-functions. Regards Andy
Try:
SELECT DISTINCT IM.MENU_ID AS 'StructureId',
IM.MENUITEM_ID AS 'CategoryId', EA.Title AS 'Name'
INTO #temp1
FROM HN_IM_JOIN IM
INNER JOIN HNIM_ElementAttributes EA
ON EA.OwnerId = IM.MENUITEM_ID
INNER JOIN HNIM_Element EL
ON EL.ID = IM.MENUITEM_ID
WHERE EA.Lcid = 'en'
AND IM.MENU_ID = 'bd966aba'
ORDER BY EA.Title
Regards Andy
It would slot-in in the same way as your original code sample. However a better way would be to wrap it in a stored procedure:
CREATE PROCEDURE dbo.Insert_t
@Name VARCHAR(40),
@Age SMALLINT,
@Job VARCHAR(40)
AS BEGIN
SET NOCOUNT ON
UPDATE t SET num = num+1
WHERE name = @Name
AND age = @Age
AND job = @Job
IF (@@ROWCOUNT = 0) BEGIN
INSERT INTO t (id, name, age, job, num)
VALUES (NEWID(), @Name, @Age, @Job, 1)
END
RETURN 0
END
You can then create an ADO command object, with parameters, to execute it from your code. Note that you will need to add some error-trapping code. I'll let you figure that all out. Regards Andy
The following will remove the need for the "if exist" statement:
UPDATE t SET num=num+1
WHERE name='abc'
AND age=20
AND job='worker'
IF (@@ROWCOUNT = 0) BEGIN
INSERT INTO t(id,name,age,job,num)
VALUES(NEWID(),'abc',20,'worker',1)
END
Additionally you should look at your indexes. I would expect something like:
CREATE UNIQUE INDEX t_idx1 ON t (name, age, job)
I would recommend looking at the other SQL that use this table, then change the order of the columns in the above index to best-suit their requirements. Regards Andy
Your error message infers that your isolation level is not "committed read" (hence my warning about this in my original response). The MS article infers that you are trying to set the isolation-level in the "GetNext" SP, after the transaction has been started. If you are using ADO then the connection's "IsolationLevel" property should be set to "adXactReadCommitted" before the transaction starts. You need to be really careful about the isolation level and the scope of your transaction otherwise you will find that your separate processes will have to wait for each over to complete - if you do it really wrong then you could find that parallel processing actually takes longer than using just a single process. The technique that I specified should work even if you don't use the "READPAST" hist specified (so long as you don't start a transaction in your client-side code until AFTER you have called my SP. Hope that helps. Andy
I normally do something like this:
create procedure dbo.GetNextAvailable
as begin
declare @id int
set nocount on
--Start transaction so that locking works.
begin tran
--Get Id of next record to process. The "holdlock" hint tells SQL
--Server to place a lock on the row until the end of the transaction.
--The "readpast" hint tells SQL-Server to ignore a record if it is
--locked by one of your other two processes. Note that this only works
--for "read committed" isolation.
select top 1 @id = MyId from MyTable with (holdlock, readpast)
where status = 'READY'
order by MyId
--Mark the record as "in-progress" so that no-one else can pick-up
--the current record.
update MyTable set status = 'IN PROGRESS'
where MyId = @id
and status = 'READY'
--Complete the transaction.
commit trans
--Return the record to the front-end for processing. If no records
--returned then there are no records left to process.
select * from MyTable where MyId = @id
return(0)
end
You could potentially move the transaction outside of the stored procedure. I normally update the status to "SUCCESS" or "FAILURE" after I have completed processing of the record. If the record processing is substantial then I normally also maintain start and end times for each record. Regards Andy
Replace "JOIN" in your first query with "LEFT OUTER JOIN".
You could try:
create table #temp1 (
SumAmounts money
)
set @SQL = 'SELECT sum(amounts) FROM tbl_CostInfo .....'
exec(@SQL)
select @PaperCost = SumAmounts from #temp1
drop table #temp1
However, a better way of acheiving this would be to use a UDF to turn your @PaperCategories string into a list of values. The resulting SQL would look a bit like:
select @PaperCost = SUM(Amounts) from tbl_CostInfo
where CatId in (
select Element from dbo.udf_split(@PaperCategories))
You should be able to find the source code for a similar UDF using google. Regards Andy
A database creation script is supposed to look like:
CREATE DATABASE [testdb]
ON (NAME = N'testdb_Data', FILENAME = N'C:\testdb_Data.mdf',
SIZE = 2MB, MAXSIZE = 10MB, FILEGROWTH = 10%)
LOG ON (NAME = N'testdb_Log', FILENAME = N'C:\testdb_Log.ldf',
SIZE = 5MB, MAXSIZE = 5MB, FILEGROWTH = 10%)
If uncomment your "print" statement you will find that you are giving the log the same name as the database. Regards Andy
It probably means that there is already a "testdb" database on your server.
If you are using IIS then go to the "Configuration..." button on the "Virtual Directory" tab page. On the "Options" tab is a setting named "Enable Parent Paths".
select b.* from ( select ColA, Max(ColB) as MaxColB from Tabl1 group by ColA ) as a Tabl1 as b on b.ColA = a.ColA and b.ColB = a.ColB order by b.ColA
Did you use a clustered index for your "time" index on Table1? That would be considerably more efficient that using a non-clustered index.
Your update statement is using "where current of BicRank_DESC". This is only allowed if the cursor was declared with the "for update" clause. Regards Andy