Is It Possible To Re-reference A Column Alias From A Select Clause In Another Column Of The Same Select Clause?

Jul 20, 2005

Example, suppose you have these 2 tables

(NOTE: My example is totally different, but I'm simply trying to setup
the a simpler version, so excuse the bad design; not the point here)

CarsSold {
CarsSoldID int (primary key)
MonthID int
DealershipID int
NumberCarsSold int
}

Dealership {
DealershipID int, (primary key)
SalesTax decimal
}

so you may have many delearships selling cars the same month, and you
wanted 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 cs
join Dealership d on d.DealershipID = cs.DealershipID
group by cs.MonthID

My 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 cs
join Dealership d on d.DealershipID = cs.DealershipID
group by cs.MonthID

Notice the only difference is the 3rd column in the select. My
particular query is performing some crazy math and the only way I know
of how to get it to work is to copy and past the logic which is
getting out way out of hand...

Thanks,
Dave

View 5 Replies


ADVERTISEMENT

Expression Defined In SELECT Clause Overwrites Column Defined In FROM Clause

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

Select A As B And Reference B In The Where Clause

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

SQL Server 2008 :: Querying XML Data With Column Value In Same Select Clause

Aug 3, 2015

I'm working on a query in which I need to get few nodes values from the XML data by using the value from SQL column (MessageContentType) in this query. I'm able to get the nodes value when i hard code the value in the query but the problem is MessageContentType will vary from some records in the table, due to that I'm not getting the corresponding node values. I have tried few ways to get this value dynamically but I'm missing something.

Sample Table Data
MessageContentType | BodySegment
xx:ADT_A03_26_GLO_DEF | <ns0:ADT_A03_26_GLO_DEF xmlns:ns0="http://microsoft.com/HealthCare/HL7/2X">.....

Current Query - HardCode Script

SELECT
ID,MsgContentType
BODYSEGMENT,
BODYSEGMENT.value('declare namespace xx="http://microsoft.com/HealthCare/HL7/2X"; /xx:ADT_A03_26_GLO_DEF[1]/colxx[1]/colxx[1]','varchar(300)') AS TimeSpan
FROM
s

When i tried the below line of script, I'm getting this error "[color=#FF0000]The argument 1 of the XMLdata type method "value" must be a string literal.[/color]"

Concat MsgContentType Column
BODYSEGMENT.value('declare namespace xx="http://microsoft.com/HealthCare/HL7/2X"; /'+MsgContentType+'[1]/EVN_EventType[1]/EVN_2_RecordedDateTime[1]','varchar(300)') AS TimeSpan

To overcome that error i used sql column but I'm getting this error [color=#FF0000]XQuery [S.bodysegment.value()]: Syntax error near '[', expected a "node test"[/color].

BODYSEGMENT.value('declare namespace xx="http://microsoft.com/HealthCare/HL7/2X"; /[sql:column("MsgContentType")][1]/EVN_EventType[1]/EVN_2_RecordedDateTime[1]','varchar(300)') AS TimeSpan

I tried this line of script, i didn't get any error by timespan is coming as null, I do hope this script not pointing the correct node to traverse the sibling node.

BODYSEGMENT.value('declare namespace xx="http://microsoft.com/HealthCare/HL7/2X"; /*[local-name()=sql:column("MsgContentType")][1]/EVN_EventType[1]/EVN_2_RecordedDateTime[1]','varchar(300)') AS TimeSpan

View 9 Replies View Related

Using The ORDER BY Clause When The Ordered Column Is Not Needed In The SELECT Statement

May 15, 2008

Greetings,

I have a C# application that calls a stored procedure to query the database (MSSQL 2005). I only have one field/column returned from the query but I need that column ordered.

How do I use the ORDER BY clause without returning the index column which does the sorting? The first example is NOT what I want. I want something that works like the second example which only returns the 'Name' column.


ALTER PROCEDURE [dbo].[MyProcedure]



AS

BEGIN

SELECT DISTINCT A.Name, A.index

FROM
...
...
ORDER BY A.[Index], A.Name ASC

END



ALTER PROCEDURE [dbo].[MyProcedure]



AS

BEGIN

SELECT DISTINCT A.Name
FROM
...
...
ORDER BY A.[Index]

END

Thanks

View 14 Replies View Related

How To Reference Embedded Select In WHERE Clause

Oct 22, 2013

I need to check the value of a column being pulled from an embedded select in the WHERE clause of a script but can't figure out how to do it. The script looks like this:

SELECT
LIST_ID,
NAME,
ADDRESS
(SELECT ANSWER FROM VALID_ANSWER WHERE VALID_ANSWER.LIST_ID = VIEW_LIST.LIST_ID) AS ANSWER

FROM VIEW_LIST

WHERE ANSWER = 'No'

The syntax above works in Oracle but in SQL Server I receive the following error: "Invalid column name 'OHIP'."

View 6 Replies View Related

SQL Server 2012 :: Producing Running Total Column In Select Clause

Jul 27, 2014

I want to create the following scenario. I have a table that stores employees working on projects and their project hours by week, but now I also need a running total per week for each of those projects. For example take a look below:

EmployeeID, Project, Sunday, Monday, Tuesday,....Saturday, ProjectHours, TotalProjectHoursPerWeek(this is the column I am trying to derive), FiscalWeek

101, ProjectABC, 5,5,5,...5, 20, 40,25
102, ProjectXYZ 4,4,4,....4, 20 ,40,25
103,ProjectQWE, 2,2,2,...2, 8, 32,26
104, ProjectPOP, 6,6,6,...6, 24, 32,26

What I have tried so far:

Correlated Subquery:
SELECT EmployeeID,Project, Sunday, Monday,....Saturday, ProjectHours, SELECT(SUM(ProjectHours) FROM dbo.TableABC ap GROUP BY FiscalWeek),
FROM
dbo.TableABC a

I got this to work one time before, but now I am getting the following error:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

View 2 Replies View Related

SELECT Column Aliases: Refer To Alias In Another Column?

Apr 9, 2008

Using SQL Server 2000.  How can I refer to one alias in another column?E.g., (this a contrived example but you get the idea)SELECT time, distance, (distance / time) AS speed, (speed / time) AS acceleration FROM dataNote how the speed alias is used in the definition of acceleration alias but this doesn't seem to work.

View 11 Replies View Related

SELECT Column Aliases: Refer To Alias In Another Column?

Apr 10, 2008

Using SQL Server 2000. How can I refer to one alias in another column?

E.g., (this a contrived example but you get the idea)

SELECT time, distance, (distance / time) AS speed, (speed / time) AS acceleration FROM data

Note how the "speed" alias is used in the definition of "acceleration" alias but this doesn't work.

View 14 Replies View Related

Using Column Alias In SELECT (was Asking)

Nov 25, 2006

Hi,

I have a question.

select name, count, 1 Aa c1, 2 as c2, c1+c2 As total
from table1

From this example, the program will give out error message,
c1, and c2 are invalid columns.

In MS Access, it works. But, SQL query Analayer doesn't work this statement.
So, does query analayzer handle this case?

Thanks.

View 3 Replies View Related

Select Alias -- Invalid Column Name

Jun 26, 2007

Hi,I got 'Invalid Column Name NewCol1' when I query the following:Select col1, col2, (some calculation from the fields) as NewCol1,(some calculation from the fields) as NewCol2,NewCol1 = NewCol2 fromTable1 inner join Table2 inner join Table3....Where.....Basically, I want to find out if NewCol1 = NewCol2 after thecalculationAny advice?Thanks in advance. Your help would be greatly appreciated.Wanda

View 5 Replies View Related

Use The Reference Column Name In The Same SELECT Querry

Sep 17, 2007

Hi,
I am writing the following querry.





Code Snippet

Select dbo.fn_EnGiftId(116) AS 'EnGiftId', dbo.fn_DeGiftId(''+ EnGiftId +'') AS 'DeGiftId'
But the above querry giving me the following error When i am executing the querry:




Code Snippet

Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'EnGiftId'.
The scenario is I have created two functions 1st is for encryption of GiftId and 2nd is for Decryption of the Encrypted Giftid.
But in the same select querry i want to use the name of column which i have given to the encrypted data in the selct querry i want to use that same column name in the decryption function uin the same querry.

When i am executing the below querry it gives me the required output. So Why not above querry.




Code Snippet

Select dbo.fn_EnGiftId(116) AS 'EnGiftId', dbo.fn_DeGiftId(''+ dbo.fn_EnGiftId(116) +'') AS 'DeGiftId'
i d't want to write the encryption function again and again. Is theer any way to avoid this.?????

View 5 Replies View Related

SELECT Then DELETE Versus Extra Clause In SELECT

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

ERROR [42000] [Lotus][ODBC Lotus Notes]Table Reference Has To Be A Table Name Or An Outer Join Escape Clause In A FROM Clause

May 27, 2008

I am using web developer 2008, while connecting to I wanted to fetch data from Lotus notes database file, for this i used notesql connector, while connectiong to notes database i am fetting error


ERROR [42000] [Lotus][ODBC Lotus Notes]Table reference has to be a table name or an outer join escape clause in a FROM clause


I have already checked that database & table name are correct, please help me out
How i can fetch the lotus notes data in my asp.net pages.

View 1 Replies View Related

ERROR [42000] [Lotus][ODBC Lotus Notes]Table Reference Has To Be A Table Name Or An Outer Join Escape Clause In A FROM Clause

May 27, 2008

I am using web developer 2008, while connecting to I wanted to fetch data from Lotus notes database file, for this i used notesql connector, while connectiong to notes database i am fetting error


ERROR [42000] [Lotus][ODBC Lotus Notes]Table reference has to be a table name or an outer join escape clause in a FROM clause


I have already checked that database & table name are correct, please help me out
How i can fetch the lotus notes data in my asp.net pages.

View 1 Replies View Related

Select With IN Clause

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

Select To Clause

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

How SELECT In WHERE Clause Works?

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

Insert Into ....... Select Clause

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

Using Greater Than '&>' In SELECT Clause

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

IF Statement In SELECT WHERE Clause

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

Question About Where Clause In A Select

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

CURSOR Select Clause

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

Using Column Name In 'LIKE' Clause

Dec 28, 2006

Hi i want to join two tables basing on like condition of the column values of two tables.
My query is like this:

select
pc_assign_worklist.pxRefObjectInsName AS "pxRefObjectInsName",
pc_assign_worklist.pxUrgencyAssign AS "pxUrgencyAssign",
pc_assign_worklist.pyLabel AS "pyLabel",
pc_assign_worklist.pyAssignmentStatus AS "pyAssignmentStatus",
pc_assign_worklist.pxAssignedOperatorID AS "pxAssignedOperatorID" ,
pc_assign_worklist.pxCreateDateTime AS "pxCreateDateTime" ,
pc_assign_worklist.pxCreateOpName AS "pxCreateOpName",
pc_index_workparty.MemberIdentifier AS "MemberIdentifier",
pc_index_workparty.LastName AS "Last Name",
pc_index_workparty.FirstName AS "First Name",
pc_index_workparty.pxInsName AS "pxInsName"
from
dbo.pc_assign_worklist, dbo.pc_index_workparty
where
pxAssignedOperatorID ='dasxkx1'
AND pc_index_workparty.pzInsKey Like '%'+pc_assign_worklist.pxRefObjectInsName+'%' ORDER BY pxUrgencyAssign DESC

-----------

i want to compare the two columns of two tables using like or contains clause as column1 in table a has value like "hi i am" where as column2 in table2 has value "hi". I need help on how to accomplish this.

View 3 Replies View Related

'SELECT Clause Does Not Return Rowset From Sp'

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

UDefined Functions In Select/ Where Clause

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

Use A Variable Along With The FROM Clause In SELECT Statement

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

Grouping Select Statements With Where Clause

Aug 10, 2004

Hello

What I need to do is be able to group the results of my select statements in different columns. And end having the result work like this.


campaign Col1 Col2

<<Data>> <<Select counT(*) where field= value>> <<Select counT(*) where field= value>>

View 1 Replies View Related

Optimising Select Statements Which Has A ‘LIKE’ Where Clause.

Dec 14, 2004

Hi all

I have been doing some development work in a large VB6 application. I have updated the search capabilities of the application to allow the user to search on partial addresses as the existing search routine only allowed you to search on the whole line of the address.

Simple change to the stored procedure (this is just an example not the real stored proc):

From:
Select Top 3000 * from TL_ClientAddresses with(nolock) Where strPostCode = ‘W1 ABC’
To:
Select Top 3000 * from TL_ClientAddresses with(nolock) Where strPostCode LIKE ‘W1%’

Now this is when things went a bit crazy. I know the implications of using ‘with(nolock)’. But seeing the code is only using the ID field to get the required row, and the database is a live database with hundreds of users at any one time (some updating), I think a dirty read is ok in this routine, as I don’t want SQL to create a shared lock.

Anyway my problem is this. After the change, the search now created a Shared Lock which sometimes locks out some of the live users updating the system. The Select is also extremely SLOW. It took about 5 minutes to search just over a million records (locking the database during the search, and giving my manager good reason to shout abuse at me). So I checked the indexes. I had an index set on:

strAddressLine1, strAddressLine2, strAddressLine3, strAddressLine4, strPostCode.

So I created an index just for the strPostCode (non clustered).

This had no change to the ‘Like select’ what so ever. So I am now stuck.

1)Is there another way to search for part of a text field in SQL.
2)Does ‘Like’ comparison use the index in any way? If so how do I set this index up?
3)Can I stop a ‘Shared Lock’ being created when I do a ‘like select’?
4)Do you have any good comebacks I could tell the boss after his next outburst of abuse (please not so bad that he sacks me).

Any advice truly appreciated.

View 8 Replies View Related

How To Select Last Rows In Group By Clause

Nov 7, 2008

I have a table which stores datewise Transactions of different items. Fields and sample data is

RecID, ItemID, Date, Received, Issued, Stock
1, 5, 11-03-08, 10, 10
2, 5, 11-05-08, 3, 7*
3, 8, 11-15-08, 25, 25
4, 8, 11-16-08, 8, 33
5, 8, 11-18-08, 6, 27*

Now i want to select last row for each item (indicated by *). Is it possible in one single statement.

View 6 Replies View Related

Select Distinct Results From Where Clause?

Jul 14, 2014

Just wondering if it's possible to select distinct results from the where clause?

View 3 Replies View Related

Select .. NOT IN Clause Doesn't Return Anything?

Nov 3, 2005

Hi there,

It's a very strange thing!
I havea a table called invoices, and a table calle customer payments which has the invoiceID of the payment.

I have many invoices that haven't been paid (so they don't have a record on the customer payments). I know this, as i can for example do:
select * from invoices where invoiceID = 302247 (and i'll get one result)
select * from customer_payments where invoice = 302247 (and i'll get none results)

however, if i do the following:
select * from invoices where invoice_id not in
(select invoice_id from customer_payments)
I get nothing!!!???

It doesn't make any sense, as I should get at least 300 (including the 302247) - both invoiceids fields are int... so i just don't understand what's wrong?

thank you so much for any help!



Grazi

View 6 Replies View Related

Use Of User Defined In SELECT Clause

Jul 23, 2005

I'm having this query:SELECTss.subscription_id AS SubscriptionId,s.id AS ScopeId,s.[name] AS ScopeName,s.base AS ScopeBase,dbo.iqGetShapesByScopeAsString(s.id) AS ShapesAsStringFROMsubscription_scope ss,scope sWHEREss.subscription_id = @subscription_idANDss.scope_id = s.idORDER BYs.[name]The select only returns a single row but my database (SQL Server 2005CTP) seems to execute the "iqGetShapesByScopeAsString" function foreach row in the subscription_scope and scope tables. This is a bug,right? The function should be executed only once for each *returned*row in the SELECT, right? I believe that was the case in SQL 2k thoughI can't check it at the moment.// pt

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved