Is It Possible To Use A Where Clause In Select Statments When You Use Intersection?
Apr 26, 2008
Lets say that Dealers have ZipCodes, and that a Dealer can have more than one zipCode, and we want the list of dealers that have both 90210 and 90211 zip codes. BUT we don't want any dealers that have only one of the two ZipCodes in question
What I want to do is something like this
Select DealerID from DealerZips where Zip = '90210'
intersection
Select DealerID from DealerZips where Zip = '90211'
but I get this error msg:
Line 2: Incorrect syntax near 'intersection'
The following sql is silly, but it does run without error
Select DealerID from DealerZips
intersection
Select DealerID from DealerZips
So I am pretty sure my problem is with the Where clauses.
help!
View 3 Replies
ADVERTISEMENT
Jul 20, 2005
Example, suppose you have these 2 tables(NOTE: My example is totally different, but I'm simply trying to setupthe a simpler version, so excuse the bad design; not the point here)CarsSold {CarsSoldID int (primary key)MonthID intDealershipID intNumberCarsSold int}Dealership {DealershipID int, (primary key)SalesTax decimal}so you may have many delearships selling cars the same month, and youwanted a report to sum up totals of all dealerships per month.select cs.MonthID,sum(cs.NumberCarsSold) as 'TotalCarsSoldInMonth',sum(cs.NumberCarsSold) * d.SalesTax as 'TotalRevenue'from CarsSold csjoin Dealership d on d.DealershipID = cs.DealershipIDgroup by cs.MonthIDMy question is, is there a way to achieve something like this:select cs.MonthID,sum(cs.NumberCarsSold) as 'TotalCarsSoldInMonth',TotalCarsSoldInMonth * d.SalesTax as 'TotalRevenue'from CarsSold csjoin Dealership d on d.DealershipID = cs.DealershipIDgroup by cs.MonthIDNotice the only difference is the 3rd column in the select. Myparticular query is performing some crazy math and the only way I knowof how to get it to work is to copy and past the logic which isgetting out way out of hand...Thanks,Dave
View 5 Replies
View Related
May 14, 2008
2 examples:
1) Rows ordered using textual id rather than numeric id
Code Snippet
select
cast(v.id as nvarchar(2)) id
from
(
select 1 id
union select 2 id
union select 11 id
) v
order by
v.id
Result set is ordered as: 1, 11, 2
I expect: 1,2,11
if renamed or removed alias for "cast(v.id as nvarchar(2))" expression then all works fine.
2) SQL server reject query below with next message
Server: Msg 169, Level 15, State 3, Line 16
A column has been specified more than once in the order by list. Columns in the order by list must be unique.
Code Snippet
select
cast(v.id as nvarchar(2)) id
from
(
select 1 id
union select 2 id
union select 11 id
) v
cross join (
select 1 id
union select 2 id
union select 11 id
) u
order by
v.id
,u.id
Again, if renamed or removed alias for "cast(v.id as nvarchar(2))" expression then all works fine.
It reproducible on
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
and
Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Feb 9 2007 22:47:07 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
In both cases database collation is SQL_Latin1_General_CP1251_CS_AS
If I check quieries above on database with SQL_Latin1_General_CP1_CI_AS collation then it works fine again.
Could someone clarify - is it bug or expected behaviour?
View 12 Replies
View Related
Nov 29, 2007
Far below (in section "original 3 steps"), you see the following:1. a temp table is created2. some data is inserted into this table3. some of the inserted data is removed based on a join with the sametable that the original select was made fromIn my opinion, there is no way that the join could produce more rowsthan were originally retrieved from viewD. Hence, we could get rid ofthe DELETE step by simply changing the query to be:INSERT INTO #details ( rec_id, orig_corr, bene_corr )SELECT rec_id, 0, 0FROM viewDWHERE SOURCE_SYS NOT IN ( 'G', 'K' )AND MONTH( VALUE_DATE_A8 ) = MONTH( @date )AND YEAR( VALUE_DATE_A8 ) = YEAR( @date )AND INMESS NOT LIKE '2__' ---- the added line===== original 3 steps (mentioned above) =====CREATE TABLE #details (rec_id UNIQUEIDENTIFIER PRIMARY KEY NOT NULL,orig VARCHAR(35) NULL,bene VARCHAR(35) NULL,orig_corr TINYINT NULL,bene_corr TINYINT NULL)INSERT INTO #details ( rec_id, orig_corr, bene_corr )SELECT rec_id, 0, 0FROM viewDWHERE SOURCE_SYS NOT IN ( 'G', 'K' )AND MONTH( VALUE_DATE_A8 ) = MONTH( @date )AND YEAR( VALUE_DATE_A8 ) = YEAR( @date )DELETE dFROM #details dJOIN viewD v ON ( d.rec_id = v.rec_id )WHERE INMESS LIKE '2__'
View 1 Replies
View Related
May 24, 2007
I am new to reporter server and I am much more familar with Crystal. I am trying to write a formula/expression that is easy in crystal but can't seem to write it in Report Server.
It is a basic If Then statement that returns a value if 2 conditions are true and another value if they are false.
So
If State = 'TX'
and
City = 'Dallas'
then 1
else
0
I have been able to use the IIF expression but I can only get it to work with one variable. So, I can do
=IIF(State = 'TX, 1,0)
but can't add the other criteria.
Any help is appreciated
Jeff
View 4 Replies
View Related
Aug 9, 2006
Parwej writes "i have one table, fields are
cs_call_id , fromid , toid , body
----------------------------------
101 1 2 fg
101 3 2 df
101 4 2 fd
101 4 1 dfd
101 3 1 w3e
i would like to find
conversation between
2,3,4
means find the row or 2,3,4 (fromid or toid)"
View 2 Replies
View Related
Feb 10, 2008
I'm in a Database class and am finding it very difficult to find any outside help. I'm sure this will appear to be very basic to those of you who work in the Database field, but your help will be greatly appreciated.
INVENTORY( SKU, Description, QUANTITYOnHand, QuantityOnOrder, Warehouse)
WAREHOUSE( Warehouse, Manager, SquareFeet)
From the two tables above we're suppose to:
1.Write an SQL statement to show the Warehouse and average QunatityOnHand of all items stored in a warehouse managed by 'Smith'. Use a subquery.
2.Write an SQL statement to show the Warehouse and average QunatityOnHand of all items stored in a warehouse managed by 'Smith'. Use a join.
This is what I came up with. Please give me some feedback:
SELECT Avg(QuantityOnHand)
FROM INVENTORY
WHERE WAREHOUSE IN
(SELECT WAREHOUSE
FROM MANAGER
WHERE Manager = ‘Smith’);
2.44)SELECTAvg(QuantityOnHand)
FROMINVENTORY, WAREHOUSE
WHEREManager = ‘Smith’
View 3 Replies
View Related
Jun 3, 2008
Hello all,
I'm new to SQL and my Teach Yourself in Blah Blah Blah book doesn't index anything helpful to my task. I have a single
TABLE ratings (rid INTEGER PRIMARY KEY, mvid INTEGER, uid INTEGER, rating INTEGER)
with about 100,000,000 rows. I would ultimately like to be able to select rows that are the intersection of two mvid on uid or vice versa -- that is, all rows whose uid is present in the set of rows where mvid=a AND in the set where mvid=b (or vice versa). Something like:
ridmviduidrating
----------------------
1123763
2123314
3123442
4211652
5211764
6211445
7535933
8535762
SELECT * FROM ratings INTO @temp_table WHERE mvid=123
SELECT * FROM ratings WHERE uid IN @temp_table AND mvid=211returns
5211764
6211445
FIRST, I don't know how to accomplish this intersection at all and the only idea I've had is querying the first clause of the intersection and storing it in a temporary/variable table then selecting from that and the original table for the second clause (not that I know how to do this), but I'm afraid this will be very inefficient for the >O(n^2) queries I must perform, so SECOND, should I build derived intersection tables from the results so as to have ~O(1) when repeating the queries later, or will SQL be doing sufficient behind-the-scenes magic? Is there an efficient SQL statement that could generate table(s) of the set of intersections if necessary? Thanks
View 20 Replies
View Related
Jul 23, 2005
I'm trying to build dynamic sql from a string passed by a callingapplication. Let's assume for this discussion that the user can pass astring of letters with these logical operators ("and", "or", and "andnot") seperating them. Each letter can be rebuilt into a sub querythat will search for people in a table by their middle initial. Forexample,X and Y(select SSN from tblPerson where MiddleInitial = 'X') UNION(select SSN from tblPerson where MiddleInitial = 'Y')This seems pretty easy with the and operator (UNION) but how can I do"or" and "and not"? I remember from a SQL class I had 10 years agothat there was an INTERSECTION operator but it appears that T-SQLdoesn't support it. The closest option is EXIST and NOT EXIST butthese can not be simply inserted between two sub queries (I think),they require the user of a where clause. It would obviously work inthe example above but in the more complecated example below it wouldn'tbe an easy replacement of the operators and sub queries.X and (Y or J) and not LSo, the bottom line is that I have no problem replacing the letterswith the appropriate sub query but I'm looking for a way to replace thelogical operator with SQL syntax that will mimic the logical operator.I hope this makes sense. : )Will Wirtz
View 7 Replies
View Related
Mar 3, 2005
What I have is a small DTS package that truncates a table then loads it from a text file. I want to enhance it by sending an e-mail with record counts to our client.
The load is pretty straight forward
delete from marketing..solicit_consumer
from marketing..solicit_consumer sc
join dsi_use..dnc_tmp dt
on sc.consumer_no = dt.consumer_no
and sc.solicit_cd = dt.solicit_cd
go
insert marketing..solicit_consumer
select * from dsi_use..dnc_tmp
go
After I have an Active X script to format an e-mail but I need the counts from the SQL statement. I have tried to use the following with no luck.
Option Explicit
Function Main()
Dim oPkg, oDataPump, sSQLStatement
' Build new SQL Statement
sSQLStatement = "SELECT count (*) FROM dsi_use..dnc_tmp " & _
DTSGlobalVariables("DNC_Count").Value & "'"
' Get reference to the DataPump Task
Set oPkg = DTSGlobalVariables.Parent
Set oDataPump = oPkg.Tasks("DTSTask_DTSDataPumpTask_1").CustomTask
' Assign SQL Statement to Source of DataPump
oDataPump.SourceSQLStatement = sSQLStatement
if oDataPump.SourceSQLStatement <> 0 then
FrmtEmail ()
Main = DTSTaskExecResult_Success
else
Main = DTSTaskExecResult_Failure
end if
' Clean Up
Set oDataPump = Nothing
Set oPkg = Nothing
End Function
The actualy format of the e-mail I think will work if ony I can get the main function to work.
Right now it gives me a VB script runtime error.
Type Mismatch:'[string: "SELECT count(*) FR"]'
error on line 19
Line 19 is somewhere within these lines
' Assign SQL Statement to Source of DataPump
oDataPump.SourceSQLStatement = sSQLStatement
if oDataPump.SourceSQLStatement <> 0 then
FrmtEmail ()
help...
View 1 Replies
View Related
Mar 18, 2004
i'm passing 4 paramaters to a stored proc. based on the values of the paramaters i add conditions to my select. can som one please reviwe the proc below and tell me if my syntax is wrong or if there is another way of doing this.
Thank You,
Thomas
CREATE PROCEDURE [Multi_Picking_Slip_FillListview1]
@str_Division nvarchar(50), @str_Season nvarchar(50), @str_Cust nvarchar(50), @str_ShipTo nvarchar(50) AS
SELECT * from tblDistribution WHERE PikingNo = 'NO'
If @str_Division <> ''
AND Division =@str_Division
If @str_Season <> ''
AND Season = @str_Season
If @str_Cust <> ''
AND cusNumber = @str_Cust
If @str_ShipTo <> ''
AND shpStoreNo = @str_ShipTo
GO
View 7 Replies
View Related
Oct 30, 2007
I am not sure how to do this. I need to run 3 sql statements against a table with a variable created in one of them.
Here is the first statement
Select ID from table1 where value = 1
Need to store that value in a variable
Update table1
Set value = 0
Where ID = variable
Update table1
Set value = 1
Where ID = variable + 1
ID is a incremental identity field, so it is numeric. Basically I need to change the value of one record to 0, and make the next records value = one.
Any help is appreciated.
View 1 Replies
View Related
Nov 2, 2007
Hope someone can help;
I have a table with a list of payment information i have three other tables that store different types of commission rates that were active at a particular time.
Payments table – holds all payments received by customers
DirectRate table – holds the Direct rate active between start and end dates
ComRate table – holds the Commission rate active between start and end dates
FieldRate table – holds the Field rate active between start and end dates
Basically I am trying to get the total value of commission on all payments for all the different rates. To give you an example one payment can be of type Direct which would have to have the correct payment rate applied from the DirectRate table for the correct date range, this also applies for payments that are of type ComRate & FieldRate.
So I have the following SQL
SELECT CASE
WHEN dp.ReceivedByID = 1
THEN
dp.Amount * ((Select tF.Rate From dbo.mTrackerFeeChange tF where tF.ClientID=d.ClientID and tF.ContractID=d.ContractID AND ((dp.PaymentOn >= tF.StartDate AND dp.PaymentOn <= tF.EndDate) or (dp.PaymentOn >= tF.StartDate AND tF.EndDate IS NULL)))/100)
WHEN dp.ReceivedByID = 2
THEN
dp.Amount * ((Select tD.Rate From dbo.mTrackerDirectChange tD where tD.ClientID=d.ClientID and tD.ContractID=d.ContractID AND ((dp.PaymentOn >= tD.StartDate AND dp.PaymentOn <= tD.EndDate) or (dp.PaymentOn >= tD.StartDate AND tD.EndDate IS NULL)))/100)
ELSE
dp.Amount * (((Select tF.Rate From dbo.mTrackerFeeChange tF where tF.ClientID=d.ClientID and tF.ContractID=d.ContractID AND ((dp.PaymentOn >= tF.StartDate AND dp.PaymentOn <= tF.EndDate) or (dp.PaymentOn >= tF.StartDate AND tF.EndDate IS NULL))) + (Select tFe.Rate From dbo.mTrackerFieldChange tFe where tFe.ClientID=d.ClientID and tFe.ContractID=d.ContractID AND((dp.PaymentOn >= tFe.StartDate AND dp.PaymentOn <= tFe.EndDate) or (dp.PaymentOn >= tFe.StartDate AND tFe.EndDate IS NULL))))/100)
END
From
dbo.DebtPayment dp,
dbo.ImportBatchItem bi,
dbo.Debt d
where
d.DebtID=dp.DebtID
AND dp.DebtID=bi.ItemID
AND bi.ImportBatchID=2
I am using dp.ReceivedByID to assertain the payment type then depending upon that using the case statement to multiply the amount by the correct rate for the correct date range in the correct table. This sql works fine but it gives me a list of commision values, one for each payment. My problem is when I try to do a sum on this case statement I get an error
“Cannot perform an aggregate function on an expression containing an aggregate or a subquery�
any help most appriciated
p
View 7 Replies
View Related
Nov 12, 2007
How do i add multiple case statements
CREATE procedure rpt_blankregistrationquestions
@cmb1 as varchar(100),
@cmb2 as varchar(100) WITH ENCRYPTION
AS
BEGIN
SELECT DISTINCT
Child.surname + ', ' + Child.forename AS ChildName,
permissionRequired.description,
healthitems.description,
dietaryneeds.description,
CASE WHEN permissionRequired.active = 1 THEN 'YES'
WHEN permissionRequired.active = 0 THEN 'NO'
END AS Child_Permission
CASE WHEN healthitems.description.active = 1 THEN 'YES'
WHEN healthitems.description.active = 0 THEN 'NO'
END AS Health_Permission
CASE WHEN dietaryneeds.description.active = 1 THEN 'YES'
WHEN dietaryneeds.description.active = 0 THEN 'NO'
END AS Dietaryneeds_Permission
FROM healthItems CROSS JOIN
DietaryNeeds CROSS JOIN
permissionRequired CROSS JOIN
Child
ORDER BY ChildName
END
View 1 Replies
View Related
Nov 8, 2002
Hi,
I have a deadlock situation with index intersection.
Connection1:
Updates a table T1 which has indexes I1 and I2
Connection2:
Selects from T1 (joining many other tables), SQL Server uses index intersection of I1 and I2.
The deadlock error log shows,
Connection1 has exclusive lock on I1 and waits on exclusive lock request on I2
Connection2 has a shared lock on I2 and waits on shared lock request on I1
Connection2 is selected as the deadlock victim.
The transaction isolation level is explicitly set to READ COMMITTED in ADO connection object.
I don't understand why SQL server needs to hold on the shared lock on I1 while waiting for a shared lock on I2.
Is there any index hint to prohibit SQL server from using index intersection other than explictly using INDEX=I1 or I2.
Thanks
View 3 Replies
View Related
Nov 12, 2004
Hi all !
I have a table with no keys (temp table) which looks like this :
col1|col2|col3
001|A|.087
001|B|.032
001|C|.345
002|A|.324
002|B|.724
003|A|.088
003|C|.899
001|A|.087
001|A|.234
001|B|.032
As you see, there is some duplicate entries in it. I would like to get a list of all the rows that have the same col1 and col2 BUT different col3 value. The result should return col1=001 col2=A but NOT col1=001 col2=B. I tried a lot of queries with EXISTS, HAVING, etc... but nothing seems to work.
Anyone have an idea how I can do it ?
View 7 Replies
View Related
Aug 14, 2006
Hi Can anybody explain me what is the difference between inner join and intersection?
I prepare a query but it shows the same results then why we need two functions like this to perform same operation
Thanx-Nagu
View 4 Replies
View Related
Sep 24, 2007
Hi,
I have a stored proc and using transactions as foolows
(not coplete Sp)
Begin Transaction TransName
Select @vsSql = Create a temp table (dynamically)
Exec( @vsSql )
Select @vsSql = dynamic insert statement
Exec( @vsSql )
and executing couple of dynamic statements using Exec
And @ the end of SP
if @@error <>0
rollback transaction TransName
else
commit transaction TransName
and when i execute the stored proc i am getting the following error
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count = 1.
And also my sql server management studio hogs up
Can any one please help me on this
~Mohan
View 9 Replies
View Related
Apr 17, 2014
Using SQL Server 2008 R2
I'm trying to create a report and chart for a a manufacturing resource's activity for a given period (typically 30-90 days)
Jobs are created for the length of the run (e.g. 4 days). If the weekend is not worked and the above jobs starts on a Friday, the resource's activity needs to show 1 day running, 2 days down, 3 days running without the production scheduler having to make it two jobs. (A job can have multiple interruptions due to downtime). I have the jobs' schedules in one table and the downtimes in another (so think of the downtime as a calendar table--non working hours). Unusually, the end time is supplied with the downtime factored in.
So I need the query to create 3 datetime ranges for this job: Fri running, Sat,Sun down, Mon,Tues,Wed Running. Been going round in circles on this for a while. i'm sure there's an elegant way to do it: I just can't find it. I've found several similar post, but can't apply any to my case (or at least can;t get them to work)
Below is some sample date and expected results. I hope the explanation and example data is clear.
-- Create tables to work with / Source and Destination
CREATE TABLE #Jobs
(
ResourceID int
,JobNo VARCHAR(10)
,startdate SMALLDATETIME
,enddate SMALLDATETIME
[Code] ....
Below is some sample data
|--------------------------J1------------------------------------| running
|----D1-----| |-------D2-------| down
|--J1--|----D1-----|-------J1------|-------D2-------|-----J1-----| result
|-----------------J1-----------------------| running
|----D1-------| down
|-----------------J1-----------------------| |----D1-------| result
View 4 Replies
View Related
Jul 24, 2015
SSAS 2008 R2
Is it possible to filter out a measure only at the intersection of Two dimension members? I have a date dimension, Â a Hospital dimension and a wait time measure.
For Example, is it possible to filter out Wait time for Bayside Hospital for the Month of June 2015?
I want Wait time to continue to be displayed for all other months and roll up into the totals without the filtered value.
View 4 Replies
View Related
Aug 3, 2006
Hi SQL Experts,
i have a strange problem
i have a variable which stores some values(ID) with single quote (so that i can use directlt inside the IN Clause of SELECT
Declare @DMSIDs AS VARCHAR(1000) -- variable declare,
Select @DMSIDs = '''DMS00046847'',''DMS00048305''' -- for test putting 2 correct values with escape characters
Select * from issue where id in (@DMSIDs) -- valid statment, but does not return any data
Select * from issue where id in ('DMS00046847','DMS00048305') -- same above constant value this returns data, but putting the values in varaible then trying fails.
The reason is i have a master table called issue and have another table [delta] where a particular column will store all the ID's of the issue table comma separated with single quote and i wanted to use something like below in my actual application
Select * from issue where ID in (Select distinct delta_ID from Delta_branch where date = getdate())
but since the above example with variable is not returning any data i wonder if such is possible in any other ways.
thank you for reading and helping me.
View 3 Replies
View Related
Aug 3, 2007
I'm having trouble using the To clause in my select statement. The following errs out with
Incorrect syntax near the keyword 'to'.
use pubs
select
*
from
view_Rates to 'c: est.csv'
I also tried this and got same error:
use pubs
select
*
to 'c: est.csv'
from
view_Rates
View 6 Replies
View Related
May 30, 2007
I'd like to create a report with the folloiwng format:
DATE1 DATE2 DATE3 DATE4 DATE5 [fixed 5 dates across the top, from today to T+5]
THING1 x x x x
THING2 x x x x
THING3 x x x x
THING4 x x x x
my raw data looks like this:
THING1, DATE1, TEXT VALUE 1
THING1, DATE2, TEXT VALUE 2
&c&c.
Now: there may be 0, 1 or several (by which I mean 2-5 max) text values to display at each intersection. If there are zero I'd like it to be blank, if there are one or several, i'd like to display them in a little list within the cell.
Is this possible?
View 1 Replies
View Related
Jun 8, 2008
Please help
I'm trying to do a select command but doesn't return any record
I have two tables one is "lists" another one is "list_records"
in the liss table I have 4 records and in the list_records I have only one record which is tell who is already visited the site so I created a query to get people who is NOT visited the site
Here is my query (I got 0 record return)
SELECT *FROM lists
list_reccords
WHERE NOT EXISTS (SELECT *
FROM lists,
list_records WHERE list_records.is_visited = 1
AND lists.list_id = list_records.list_id)
View 4 Replies
View Related
Mar 26, 2004
Hi,
I have a question about Insert into .....
Select clause in a SP. I need to insert some rows into a temperary table in a specific order. For example,
insert into #TempTable
{
.......
........
}
Select * from products order by @SortBy @SortDirection.
//
First of all, the order by clause does not take
variables. I have tried to use
declare @query varchar (1000)
set @query = 'Select * from products order by " + @SortBy + ' ' + @SortDirection
exec (@query)
//
However, I get an error message because I should supply a Select statement.
How can I solve this problem??
Thanks for your help!
View 3 Replies
View Related
Jan 10, 2006
Here is the first part of a query for MySQL that I am trying to get working on MSSQL:
Code:
SELECT n.*,
round((n.rgt-n.lft-1)/2,0) AS childs,
count(*)+(n.lft>1) AS level,
((min(p.rgt)-n.rgt-(n.lft>1))/2) > 0 AS lower,
(( (n.lft-max(p.lft)>1) )) AS upper
FROM table n
...
But, I get this error message:
Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near '>'.
Is there a way to convert this? Thanks
View 2 Replies
View Related
Jun 12, 2008
Hi Everyone,
I have the following stored procedure, I would like to use
IF statement or something of the sort in the where clause i.e.
The last line in the SP is: AND (category.categoryID = @categoryID),
I only want to check this, if @categoryID is not = 12.
So can I do something like this:
IF @categoryID <> 12
AND (category.categoryID = @categoryID)
STORED PROCEDURE:
CREATE PROCEDURE sp_get_total_risk_patients
@categoryID int
AS
SELECT COUNT(DISTINCT patient.patientID) AS total_patients
FROM patient
INNER JOIN patient_record ON patient.patientID = patient_record.patientID
INNER JOIN sub_category ON sub_category.sub_categoryID = patient.sub_categoryID
INNER JOIN category ON category.categoryID = sub_category.categoryID
WHERE risk = 6
AND (completed_date = '' OR completed_date IS NULL)
AND (category.categoryID = @categoryID)
View 4 Replies
View Related
Aug 23, 2007
Hello,
When you rename a field/calculation in the select part of the sql statement, how do you reference it in the where clause?
For example:
Select A, B, (C + D) as X
From test
WHERE X > 1
Many thanks!
View 3 Replies
View Related
Feb 28, 2008
Hi,
I have a simple select statement that joins a master and a detail tables using a single field. Looks somthing like this:
Master:
Field1 (Unique key)
more fields...
DateField (Index field)
Detail:
Field1 (unique key)
Field2 (unique key)
more fields....
The master has 100 thousand records and the detail has 100 million records.
If I had a statment "Select.....From Master, Detail" what would be the best way to write the where clause?
Would one of the following where clauses run faster than the other based on the number of records in the tables?
Where1:
where Master.DateField = value and Master.Field1 = Detail.Field1
Where2:
where Master.Field1 = Detail.Field1 and Master.DateField = value
View 3 Replies
View Related
Oct 11, 2006
I need to dynamically construct the field order of a cursor based on fixed labels from another table, but when I put that resulting query I receive the error:
Server: Msg 16924, Level 16, State 1, Line 78
Cursorfetch: The number of variables declared in the INTO list must match that of selected columns.
I have 6 fields defined in the cursor select, and 6 parameters in the fetch. The results of running the @sql portion returns valid data. Should this be possible to define a parameter containing the select clause of the cursor?
select colnum, coldesc, colname into #ae_defs from ae_adefs
select @Sql = (select colname from #ae_defs where coldesc = 'PATIENT NAME') +
', ' +
(select colname from #ae_defs where coldesc = 'PATIENT NUMBER') +
', ' +
(select colname from #ae_defs where coldesc = 'ACCOUNT NUMBER') +
', ' +
(select colname from #ae_defs where coldesc = 'VISIT DATE') +
', ' +
(select colname from #ae_defs where coldesc = 'VISIT TYPE') +
', DocID from ae_dtl1'
DECLARE myCursor CURSOR FOR
Select @SQL
OPEN myCursor
print @@Cursor_rows
FETCH NEXT FROM myCursor into @var1, @var2, @var3, @var4, @var5, @DocID
View 2 Replies
View Related
May 3, 2004
I have sp20, simplified, as:
ALTER PROCEDURE dbo.sp20 (@CustomerID int, @aDate as datetime) AS
SELECT Customers.* INTO #EndResult1 FROM Customers WHERE Customers.CustomerID >= @CustomerID
SELECT Orders.* INTO #EndResult2 FROM Orders Where Orders.[TakenDate] >= @aDate
SELECT #EndResult1.*, #EndResult2.*
FROM #EndResult1 INNER JOIN #EndResult2 ON #EndResult1.CustomerID = #EndResult2.CustomerID
This works fine in EM.
When I try to execute it from MS Access ADP Project I get
'Stored Procedure excuted succesfully, but did not return any records'
Although, in EM it returns the right number of records.
Thank you in advance - Rehman
View 2 Replies
View Related
Sep 11, 2000
Hi,
Is there any way of emulating Oracle's capability of passing output of user-defined functions in the select statement or better still in the Where clause in SQL server 7.0? If not then could we hope for it in SQl server 2000?
Regards,
Vikas..
View 1 Replies
View Related
Dec 28, 2004
I have a table 'table_list' which contains two columns, table_name and a record_count. This table stores a list of tables and their corresponding record counts.
What I am trying to do is, to be able to write a select statement, that can read each table name in the 'table_name' column, execute a select count(*) for the same, and update its record_count with the result of select count(*).
This is the code in my procedure..
DECLARE @tab_list CURSOR
set @tab_list = CURSOR FOR select * from table_list
OPEN @tab_list
DECLARE @tab_name varchar(256)
DECLARE @rec_cnt int
FETCH NEXT FROM @tab_list INTO @tab_name, @rec_cnt
select count(*) from @tab_name
This select is looping around along with FETCH till all the table names are exhausted and their counts are updated from the cursor back into the table.
Problem is that, I am not able to use select count(*) from @tab_name, and its not accepting a variable there.
Please help me to construct the select statement that is similiar to
x=<table name>
select * from x
where x is a variable and the table name gets substituted.
what is the syntax for it ?
View 7 Replies
View Related
