Inner Select Column Field

Apr 18, 2008

Dear all,

I have problem in the following SQL. Please help. The problem in the inner select statement for the group by clause. Please help

select company.billtocompany,
(select quartername from quarter where a.orderdate between
convert(datetime, quarter.startdate) and convert(datetime, quarter.enddate)+1) quartername,

sum( a.qty) orderedqty

from a, company, countrycode

where a.billtocompany=company.compcode and
a.countrycode=countrycode.countrycode and
a.billtocompany='111111' and
a.orderdate between convert(datetime, '2008/01/01') and convert(datetime, '2008/10/01')

group by company.billtocompany, countrycode.countryname, quartername

View 1 Replies


ADVERTISEMENT

SELECT Statement - How To Not Get Column Field Names?

Jan 24, 2007

I do a SELECT * from table command in an ASP page to build a text fileout on our server, but the export is not to allow a field name rows ofrecords. The first thing I get is a row with all the field names. Whydo these come in if they are not part of the table records? How do Ieliminate this from being produced? Here's the ASP code....<html><head><title>Package Tracking Results - Client Feed</title></head><body><%' define variablesdim oConn ' ADO Connectiondim oRSc ' ADO Recordset - Courier tabledim cSQLstr ' SQL string - Courier tabledim oRSn ' ADO Recordset - NAN tabledim nSQLstr ' SQL string - NAN tabledim objFSO ' FSO Connectiondim objTextFile ' Text File' set and define FSO connection and text file object locationSet objFSO = CreateObject("Scripting.FileSystemObject")'Set objTextFile =objFSO.CreateTextFile(Server.MapPath("textfile.txt"))'Response.Write (Server.MapPath("textfile.txt") & "<br />")Set objTextFile = objFSO.OpenTextFile("C: extfile.txt",2)' write text to text file'objTextFile.WriteLine "This text is in the file ""textfile.txt""!"' SQL strings for Courier and NAN tablescSQLstr = "SELECT * FROM Courier"' set and open ADO connection & oRSc recordsetsset oConn=Server.CreateObject("ADODB.connection")oConn.Open "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" &"c:/Database/QaTracking/QaTracking.mdb" & ";"set oRSc=Server.CreateObject("ADODB.Recordset")oRSc.Open cSQLstr, oConnResponse.ContentType = "text/plain"Dim i, j, tmpIf Not oRSc.EOF ThenFor i = 1 To oRSc.Fields.CountobjTextFile.Write oRSc.Fields(i-1).NameIf i < oRSc.Fields.Count ThenobjTextFile.Write " "End IfNextobjTextFile.WriteLineWhile Not oRSc.EOFFor i = 1 To oRSc.Fields.CountIf oRSc.Fields(i-1) <"" Thentmp = oRSc.Fields(i-1)' If TypeName(tmp) = "String" Then' objTextFile.Write "" &_'Replace(oRSc.Fields(i-1),vbCrLf,"") & ""' ElseobjTextFile.Write oRSc.Fields(i-1)' End IfEnd IfIf i < oRSc.Fields.Count ThenobjTextFile.Write " "End IfNextobjTextFile.WriteLineoRSc.MoveNextWendEnd IfobjTextFile.CloseSet objTextFile = NothingSet objFSO = NothingoRSc.CloseSet oRSc = NothingoConn.CloseSet oConn = Nothing%></body></html>

View 1 Replies View Related

Conditionally Select A Field Depending On Another Field Value

Jul 13, 2004

I want in my query to select a different field in case another one is null. in mysql i'd do it like this:

select
a
,if(b is null, c, b)
,d
from
alphabet

how can this be done in sql server?
thanks

View 2 Replies View Related

Reporting Services :: Select Text Field Dataset Based On User Select Option?

Aug 4, 2015

I have a report that uses different datasets based on the year selected by a user.

I have a year_id parameter that sets a report variable named dataset_chosen. I have varified that these are working correctly together.

I have attempted populating table cell data to display from the chosen dataset. As yet to no avail.

How could I display data from the dataset a user selects via the year_id options?

View 4 Replies View Related

WHERE Field=(select Field From Tables)??????

Aug 26, 2005

I need some help.I am trying to write a query which does the followingSELECT * from table1 where field1=(SELECT distinct field1 FROM table1WHERE field2='2005' or field2='2010')I need all the values from table1 which match any value from field 1from the subquery.Any help is appreciated.thanks

View 4 Replies View Related

Transact SQL :: Find Unique Rows In Column That Are Associated With Another Field In Another Column?

May 1, 2015

I am having issues trying to write a query that would provide me the unique GUID numbers associated with a distinct PID if the unique GUID's > 1.  To summarize, I need a query that just shows which PID's have more than one unique GUID. A PID could have multiple GUID's that are the same, I'm looking for the PID's that have multiple GUID's that are different/unique. 

Table1

GUID PID
GUID1 PID1
GUID1 PID1
GUID1 PID1
GUID2 PID1
GUID3 PID2
GUID3 PID2
GUID3 PID2

The result of the query would only have PID1 because it has two unique GUID's. PID2 would not be listed has it has the same GUID3 in each row.

Result:

PID1 

View 2 Replies View Related

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 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

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 Number Inplace Of Column Name In SQL Select Statement

Jul 20, 2005

Hello All,Is there a way to run sql select statements with column numbers inplace of column names in SQLServer.Current SQL ==> select AddressId,Name,City from AddressIs this possible ==> select 1,2,5 from AddressThanks in Advance,-Sandeep

View 1 Replies View Related

Select Max Value Of One Field For Value Of Other Field(s)

Feb 29, 2008

I would like to query a table for a max value of one field for a distinct combination of two other fields. Let's call these fields RowID, ObjectID, and ObjectType. RowID is an auto-increment field, so for each distinct combination of ObjectID and ObjectType, there will be many values of RowID. To visualize an example:

RowID, ObjectID, ObjectType
1 , 1 , 1
2 , 1 , 2
3 , 1 , 3
4 , 1 , 1
5 , 1 , 2
6 , 1 , 3

Of these rows, I would only want 4, 5, and 6 (max values for distinct combination of ObjectID and ObjectType).

I hope I explained this clearly. I would imagine I'd need to use some form of nested query, but nothing I have tried so far has worked. I am using SQL 2005.

Thanks!

View 1 Replies View Related

Select Column Value Which Is Selected From A Column Of Another Table

Jan 2, 2004

i need help to get the value of a column which is selected from a column of another table....

View 3 Replies View Related

Transact SQL :: Select Row With Max (column Value) Group By Another Column

May 19, 2015

i dont't know how to select row with max column value group by another column. I have T-SQL

CREATE PROC GET_USER AS
BEGIN
SELECT T.USER_ID ,MAX(T.START_DATE) AS [Max First Start Date] ,
MAX(T.[Second Start Date]) AS [Max Second Start Date],
T.PC_GRADE,T.FULL_NAME,T.COST_CENTER,T.TYPE_PERSON_NAME,T.TRANSACTION_NAME,T.DEPARTMENT_NAME ,T.BU_NAME,T.BRANCH_NAME,T.POSITION_NAME
FROM (

[code]....

View 3 Replies View Related

Value To Select Of Field

Feb 27, 2008

Hi,
 I've got a question!
In a database I have a field interest-target with values like 8,15,115,3 and 18,13,15,6 and 51,6,7,118 etc.
Now I like to select from these fields the value 8. I tried the following:
SELECT * FROM TABLE WHERE FIELD LIKE '%8,%' OR FIELD LIKE '%,8,%' OR FIELD LIKE '%,8%'
I will the also get the value 18 wich I don't like :-(
Any solutions here?
Thanks!
 Roel

View 7 Replies View Related

How Do You Select A Field That Has ' In It

Feb 15, 2005

I want to select only the descriptions from a table that have a ' in them. Anyone know how to do this?

exp: Select * from SkuMaster where Description like '%'%'

I have tried all kinds of combinations with no luck.

View 2 Replies View Related

Select Where A Field Contains @

May 31, 2006

Is there a way in SQL Server to query for a character that is in a field? ... I have this old table before there was form validation, and I want to get the addresses out of the column that actually have an @.

I want to do something like this.

SELECT
email
FROM
xyz
WHERE
email contains '@'
AND
email contains '.'

any ideas? Will this work?

Thanks,
-L

View 4 Replies View Related

SQL Insert With Select And Specify Value For A Field

Oct 24, 2007

Hi,
Can someone tell me if it is possible to do an SQL insert with a select (to copy specific records) query and specify the value for a specific field to insert in the new records instead of using the value in the field in the select statement.
If so can you provide me with a simple example.
 Cheers
Mark :)

View 4 Replies View Related

Select With Or In A Specific Field

May 25, 2008

I've a select SELECT Field1, Field2, Field3 FROM Tablethe problem is that I can have filled Field1 OR Field2 ... I would like to create a new column in the result for see only the filled field  if Field1 is = null or  = ' ' I want to see Field2 if Field2 is = null or  = ' ' I want to see Field1

View 2 Replies View Related

Select Where Datetime Field Is Over 1hr Old

Jun 13, 2008

How would I go about selecting records where a datetime field is >= 1hr? 

View 2 Replies View Related

Problem With Select Max In The Field

Jan 8, 2004

For example i got 1 table T1 and has 1 field that names User_ID

User_ID
-----------
us00019
us00020
us00001
us00002
us00021
us00008
us00005
us00009
us00011
us00010
us00012
us00018

How can i retrive Max User_ID in that field .... i'm using SQL 7.0.. i couldnt do it,anyone got idea. Thankz

View 1 Replies View Related

Select Same Field Twice For Different Criteria

Mar 11, 2005

I need a little insight on how to select the same field from the same table, but for different criteria.

here are example tables...

Categories
CATSUBCATNAME
10MainTitle
11SubTitle #1
12SubTitle #2
20Section
21Section #1


DataTable
CATSUBCATINFO
11Detail Information for subtitle #1
12Detail information for subtitle #2

desired result would be:
MainTitle, SubTitle #1, Detail Information for subtitle #1
MainTitle, SubTitle #2, Detail Information for subtitle #2


Select c1.Name, c2.Name, d.info
from DataTable d, Categories c1, Categories c2
where c1.CAT = d.CAT
and c2.CAT = d.CAT
and c2.SUBCAT = d.SUBCAT

View 1 Replies View Related

Select From Two Different Field Columns.

May 22, 2006

OK, i'm sure this is an easy one, but can't seem to figure it out. Basically need to search against two database columns (fields) to see if either contain the work "test". Here is an example of the statement but of course dosn't work correctly:
SELECT * FROM tbl_db WHERE skills LIKE 'test' AND title LIKE 'test' 
Please note the AND is incorrect.
What i need to accomplish:
if one record contains 'test' in its skills field then SELECT it.
if one record contains 'test' in the title field the SELECT it.
I need to make sure that is 'test' is in both skills and title fields then only display once.
Thanks,
Jake
 
 

View 5 Replies View Related

Multi Field Select Using IN...

Oct 27, 2006

here is what i am wanting to do but is it possible to use multi fields with an IN statement?

SELECT *
FROM usmastf
WHERE usm_book, usm_acct IN
(SELECT usac_book, usac_acct
FROM uscommf
WHERE usac_code = 'O/W')

View 14 Replies View Related

SELECT Field &> 1 Time

Dec 15, 2006

hello !

for MS SQL 2000

how can I get something like

SELECT name from users WHERE Count(name) > 1

i want to return only the rows where name appears more than 1 time

thank you

View 12 Replies View Related

Select Only One Data In A Field

Jun 9, 2008

Hi there,
Need help..
I have a table name Changekitlist and have fields name CKNumber,ck_ID,ck_Name, etc.. Now, I plan to see each CKNumber one at a time so that I can put it in an array which I'll use in VB6 program.

However, If I use the command: "SELECT CKNumber from Changekitlist"
it displays all the CKNumber list that I have (CK001 to CK500) and I cannot store it in an array properly because it display all CKNumber at once and store all the 500 CKNumbers at once in the array.

What will I do so I can display one CK Number at a time, store in an array and loop so that I'll get the next CKNumber and store it again.. Need your advice please. Thanks!

View 12 Replies View Related

Select Max If Value Of Specific Field Is Y

May 15, 2014

I need to check if the latest record in a table for the current client, based on EFFECTIVE_DATE, has a value of 'Y' in the STATUS field. Sample records for client #19:

CLIENTEFFECTIVE_DATESTATUS
------ -------------- ------
19 08/13/2010Y
1901/10/2013N
1902/03/2014Y

If I run:
SELECT CLIENT, max(EFFECTIVE_DATE), STATUS
FROM FTABLE GROUP BY CLIENT, STATUS

I get the two latest records since STATUS is different:

CLIENTEFFECTIVE_DATESTATUS
------ -------------- ------
1901/10/2013N
1902/03/2014Y

I wrote the following and it works, but it may be causing 'Insufficient Disk Space' error from the database. Is there any way to write this query without temp table?

DECLARE @TEMPMAX

TABLE (CLIENT INT, EDATE DATE)
INSERT INTO @TEMPMAX (CLIENT, EDATE)
SELECT CLIENT_NO, max(EFFECTIVE_DATE)

[Code] ....

CLIENTEFFECTIVE_DATESTATUS
------ -------------- ------
1902/03/2014Y

View 3 Replies View Related

SELECT * Minus A Field

Jul 26, 2007

Hi,

I was wondering if anyone knows a command that will select all fields in a table except one?

Thanks!
Lee

View 6 Replies View Related

Select Where Length Of Field = 5

Sep 21, 2007

I'm sure it is possible to select all records where the length of a given field equals a certain value, but I don't know how to do it?

Can somebody tell me how?

Hans

View 4 Replies View Related

I Want Write Select Without Field Name

Mar 22, 2008

hello
my friends
i want write Select without write filed name
example
mytable(id,name1,family)
orginal selected is
select id,name1 from mytable
but i want write:
select field[0],field[1] from my table
that field[0] point to id and field[1] point to name1
thanx

M.O.H.I.N

View 3 Replies View Related

The Select Statement Is In A Field

Aug 31, 2005

Ok, I inherited this database and there is a field that stopres a selectstatement. Is there anyway possible to execute the value of the fieldwithin a select statement?For example:the table:Name "george"lookupForName "Select orders from Ordertable"So maybe something like select name, execute(lookupforname) as ordersSorry, I didn't design this, just inherited :)george

View 1 Replies View Related

Using A Select Statment For The Name Of The Field?

Jul 20, 2005

Do any of you know if it's possible to get the name of your field fromanother table?E.g.Select FYear as [Select description from YearTable where category=1]I need the name of the fields to be user defined and the only way Ican see this happening is through a table. But now...how do I get thefield name from the table?Any help would be appreciated!!!!Thank you,Susan

View 2 Replies View Related

How To Select The Row That Has The Maximum Value In One Field

Sep 21, 2006

This has me stumped.

I have a table UnitRateItems with the following columns

ItemID, Description

and  table UnitRates with the following columns

ItemID,Year,UnitRate

UnitRateItems and UnitRates have a primary-foreign key relationship on ItemID.

In UnitRates, there may be several rows with the same value for ItemID but a different value for Year.  What I want to do is pick the row with the largest value of Year for a given ItemID.  I want to do this for ALL the rows in the table (not just for a particular ItemID).

In other words, I want to return one row in UnitRates for each row in UnitRateItems. I want that one row to be the one with the highest Year for that ItemID.

It sounds simple, but I can't seem to figure out how to do it.  The MAX function only works on a single column.  I can get the MAX Year, but then I can't tell it "pick up the other values that go with the row with the MAX Year".

Any help would be greatly appreciated!

View 5 Replies View Related

Select Datetime Field With Vb.net

Apr 15, 2008

Hello to all.

I try to get record count on a sql 2005 table between two date.
My Sql string is

Select * from TableName where data between '2008-03-01' and '2008-03-31'

but vb returna conversion error
: the conversion fromchar data type to a datetime have created a datetime value not valid.



someone can help me ???

thank you in advance !!!!
Alberto

View 1 Replies View Related







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