SELECT Statement - How To Not Get Column Field Names?
I do a SELECT * from table command in an ASP page to build a text file
out on our server, but the export is not to allow a field name rows of
records. The first thing I get is a row with all the field names. Why
do these come in if they are not part of the table records? How do I
eliminate this from being produced? Here's the ASP code....
<html>
<head>
<title>Package Tracking Results - Client Feed</title>
</head>
<body>
<%
' define variables
dim oConn ' ADO Connection
dim oRSc ' ADO Recordset - Courier table
dim cSQLstr ' SQL string - Courier table
dim oRSn ' ADO Recordset - NAN table
dim nSQLstr ' SQL string - NAN table
dim objFSO ' FSO Connection
dim objTextFile ' Text File
' set and define FSO connection and text file object location
Set 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 tables
cSQLstr = "SELECT * FROM Courier"
' set and open ADO connection & oRSc recordsets
set 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, oConn
Response.ContentType = "text/plain"
Dim i, j, tmp
If Not oRSc.EOF Then
For i = 1 To oRSc.Fields.Count
objTextFile.Write oRSc.Fields(i-1).Name
If i < oRSc.Fields.Count Then
objTextFile.Write " "
End If
Next
objTextFile.WriteLine
While Not oRSc.EOF
For i = 1 To oRSc.Fields.Count
If oRSc.Fields(i-1) <"" Then
tmp = oRSc.Fields(i-1)
' If TypeName(tmp) = "String" Then
' objTextFile.Write "" &_
'Replace(oRSc.Fields(i-1),vbCrLf,"") & ""
' Else
objTextFile.Write oRSc.Fields(i-1)
' End If
End If
If i < oRSc.Fields.Count Then
objTextFile.Write " "
End If
Next
objTextFile.WriteLine
oRSc.MoveNext
Wend
End If
objTextFile.Close
Set objTextFile = Nothing
Set objFSO = Nothing
oRSc.Close
Set oRSc = Nothing
oConn.Close
Set oConn = Nothing
%>
</body>
</html>
View Complete Forum Thread with Replies
Related Forum Messages:
Dynamic Column Names In Select Statement
I have a quick question on SQL Server. Lets say I have table Order which has column names OrderId, CustomerName, OrderDate and NumberofItems. To select the OrderID values from the table I say Select OrderId from Order. But in the select if I want the column name to be variable how do I do it. I tried the following code through a stored procedure. declare @order_id nvarchar(10) select @order_id = 'OrderID' SELECT @order_id from Order. The code above gave me the string "OrderID" as many times as there were rows in the table but I could never get the actuall values in the OrderId column. Can you please send me some ideas or code where I can get values from the column names and at the same time change the column name dynamically.
View Replies !
Field Names From SQL Statement
Is there anyway to determine what the resulting Field Names are going to be from a SQL Statement? For example: SELECT TABLE1.FIELD1, TABLE1.FIELD2, TABLE1.FIELD3, TABLE2.FIELD1 AS ANOTHERNAME FROM TABLE1 INNER JOIN TABLE2 ON TABLE1.PK = TABLE2.FK resulting field names: FIELD1 FIELD2 FIELD3 ANOTHERNAME Seems easy enough splitting all values before "FROM" by comma and doing some manipulation to remove table names and anything before the word "AS". However, it gets more difficult when you have complex CASE statements embedded in you query that may also contain commas. Just a shot in the dark because I don't know if anyone has already done something like this before. Thank you in advance, Jeff
View Replies !
SELECT Table Field Names
I need a statement or sp that will display, for a given user database, an individual table's fieldnames, datatype, and length. Any help is appreciated. Randy
View Replies !
How To Select Field Names Of Table?
Can someone write a query that select all the fields of tables in database that have type 'image'? Something like this: Select TableName, FieldName FROM TableWhereTheyKeepThoseThings WHERE TableWhereTheyKeepThoseThings.FieldType='Image' ... olny it should work :)
View Replies !
Cannot Copy Field Names In Column Headings From View Results
When I am using a SQL Query I have an ability to control whether or not I am able to Include the Column Headers when copying or saving results. The control exists in the Options > Query Results > Results to Grid > Include column headings etc. My question is how to get this same ability when attempting to copy the results of a VIEW vs. a Query. The idea is that when I setup a view it€™s a drag/drop type of query building (query building for dummies if you will). Once I have a view and click the Execute icon it will return all the records selected by the View. However, when I click the upper left/top box to select all rows and column and then try to copy/paste the records into Excel all the data copies just fine but the field name/column headers are not there. How can I get the header fieldname date to copy/paste from View result set that I'm able to copy from a Query result set? Thank you, Mike
View Replies !
Using Variables To Select Column Names
Hi I've tried declaring and setting variables in my sql statement and then trying to use them instead of defining a column directly - sorry quite hard to explain, i'll do a simple example eg DECLARE @column DECLARE @value SET @column = 'col1' SET @value = 'bloggs' Select * FROM table1 WHERE @column = @value It keeps returning no results even though i've tried Select * FROM table1 WHERE col1 = 'bloggs' -- which returns results I realise its the column which is not being selected, but there must be a way by using a variable? thanks
View Replies !
TSQL - Use ORDER BY Statement Without Insertin The Field Name Into The SELECT Statement
Hi guys, I have the query below (running okay): Code Block SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02' FROM myTables WHERE Conditions are true ORDER BY Field01 The results are just as I need: Field01 Field02 ------------- ---------------------- 192473 8461760 192474 22810 Because other reasons. I need to modify that query to: Code Block SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02' INTO AuxiliaryTable FROM myTables WHERE Conditions are true ORDER BY Field01 SELECT DISTINCT [Field02] FROM AuxTable The the results are: Field02 ---------------------- 22810 8461760 And what I need is (without showing any other field): Field02 ---------------------- 8461760 22810 Is there any good suggestion? Thanks in advance for any help, Aldo.
View Replies !
The Select Statement Is In A Field
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 Replies !
Select Statement With A New Identity Field
Hello, Is it possible to generate a identityfield dynamically upon select, like this: SELECT tempID AS identity(1,1), username FROM table1 ORDER BY username ASC I want the output to be: 1 - Name12 - Name23 - Name3 The reason for this, is that i want to change the sort order in many diffrent ways, but i need to get the IDs from 1-?? even when the sort order changes. Like: SELECT tempID AS identity(1,1), username FROM table1 ORDER BY username DESC I want the output to be: 1 - Name32 - Name23 - Name1 Patrick
View Replies !
Query To Sum The Same Field Twice In The Select Statement
Hello friends , I have table (MoneyTrans) with following structure [Id] [bigint] NOT NULL, [TransDate] [smalldatetime] NOT NULL, [TransName] [varchar](30) NOT NULL, -- CAN have values 'Deposit' / 'WithDraw' [Amount] [money] NOT NULL I need to write a query to generate following output Trans Date, total deposits, total withdrawls, closing balance i.e. Trans Date, sum(amount) for TransName='Deposit' and Date=TransDate , sum(amount) for TransName=Withdraw and Date=TransDate , Closing balance (Sum of deposit - sum of withdraw for date < = TransDate ) I am working on this for past two days with out getting a right solution. Any help is appreciated Sara
View Replies !
Select Statement Eliminate Field Name
Hi I have tabelA, Which has 10 columns, I need to select 10 column values only no field names. Is there any way I can select only table values not field names. I don't want to see field name in my query result set. Please let me know. I appreciate your help. Thanks Regards -Leong
View Replies !
How Do I Call A Select Statement Properly When The Field Is A Yes/no?
Hello, i am pretty new to asp. I am trying to do a select statement for sending an email to everyone who is not an admin. the code is below, i know it must be fairly simple, yet i do not know how to do it. With the code below, I select everyone. I want to know how to do it properly, similar to the second which does not work. Dim cmd As New OleDbCommand("SELECT Username, Pass, Gender, FirstName, LastName, Email, NickName FROM tblUsers", conn) DOES NOT WORK: Dim cmd As New OleDbCommand("SELECT Username, Pass, Gender, FirstName, LastName, Email, NickName FROM tblUsers WHERE Admin = 'N'", conn) Thanks in advance.
View Replies !
Select Statement With Run Time Field Selection
I have this SELECT statement. SELECT [issueID], [name] FROM [MyIssue] What I wanted to do is in addition to the above statement, I want to add two run time fields like this: 99 [issueID],'All Issues' [name] So let's say the above select statements generates this list: Summer 2007 Issue Winter 2007 Issue The two addition fields will make the result list like this: 01 Summer 2007 Issue 02 Winter 2007 Issue 99 All Issues How do I accomplish this? Any help is much appreciated.
View Replies !
SQL Update Statement Set Field Value To Column Default
Is there a way to set a field value to the column default in an update statement? Eg. UPDATE Table2 SET field1 = DefaultValue where DefaultValue is the field1 column default in the table definition. The reason I need to do this is when I delete a record from Table1, I need to set the foreign key in Table2 to the default (I don't want to delete the record in Table2, just want to set the key to a default key). I could hard-code the default value in the stored procedure but I think that's just not clean. If I create a new instance of the DB and the default value changes, I'd need to change the stored procedure(s). Just not clean... To avoid a drawn-out discussion, there are reasons why I can't setup a relationship between the two tables and use "ON DELETE SET DEFAULT". Any info greatly appreciated.
View Replies !
Inner Select Column Field
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 Replies !
Convert A Time Field In The Select Statement Of The Query
Hi, I have a field called "Starting DateTime" and I want to convert into my local time. I can convert it in the report with the expression "=System.TimeZone.CurrentTimeZone.ToLocalTime(Fields!Starting_DateTime.Value)", but that is too late. I want to convert it in the Select statement of the query. Can anyone help me please? Thx
View Replies !
Column With Select Statement
Hi, i have a doubt, can a column have the value of a select? I mean, i'm making a photo gallery and on the categories table i need to know how many photos i have, so i need to count in the table photos the ones associated with the id of the category, the problem is that i'm listing categories with a datalist, is there a way so that a column on the categories table have the result of the count? Thanks in advance, if you don't understood my question feel free to ask me again and i'll try to explain it better, i really need this.
View Replies !
Need To Set A Field In A Select Statement Equal To Yes Or No If Record Exists In Separate Table
Hey gang, I've got a query and I'm really not sure how to get what I need. I've got a unix datasource that I've setup a linked server for on my SQL database so I'm using Select * From OpenQuery(DataSource, 'Query') I am able to select all of the records from the first two tables that I need. The problem I'm having is the last step. I need a field in the select statement that is going to be a simple yes or no based off of if a customer number is present in a different table. The table that I need to look into can have up to 99 instances of the customer number. It's a "Note" table that stores a string, the customer number and the sequence number of the note. Obviously I don't want to do a straight join and query because I don't want to get 99 duplicates records in the query I'm already pulling. Here's my current Query this works fine: Select *From OpenQuery(UnixData, 'Select CPAREC.CustomerNo, CPBASC_All.CustorCompName, CPAREC.DateAdded, CPAREC.Terms, CPAREC.CreditLimit, CPAREC.PowerNum From CPAREC Inner Join CPBASC_All on CPAREC.CustomerNo = CPBASC_All.CustomerNo Where DateAdded >= #12/01/07# and DateAdded <= #12/31/07#') What I need to add is one more column to the results of this query that will let me know if the Customer number is found in a "Notes" table. This table has 3 fields CustomerNo, SequenceNo, Note. I don't want to join and select on customer number as the customer number maybe repeated as much as 99 times in the Notes table. I just need to know if a single instance of the customer number was found in that table so I can set a column in my select statement as NotesExist (Yes or No) Any advice would be greatly appreciated.
View Replies !
Possible To Parse A Column In A Select Statement?
I have a column called SEGMENTED_BLOCK sample data:X,X,XXX,XX,XX,TYZC123456,X,X,TOYZ654321,1234,777777I need to do something that has the effect ofSELECT(stuff before first comma) as FIRST_ITEM,(stuff after first comma, but before second) as NEXT_ITEM,(stuff after second comma but before third(if any)) as THIRD_ITEMFROM SEGMENT_XREFWHERE LOOKUP_ITEM = 12345ORDER BY FIRST_ITEMFIRST_ITEM is pretty easy, but it gets uglier fast.My attempts are horrendously ugly nested checkindex and substring statements.Is there an easier way?
View Replies !
Return SP In A SELECT Statement Column
I have a stored procedure which contains a complex scripting that is not an option to rewrite as a single SELECT statement. I want the following output: CatID | CatTitle | CatTree 001 | News | exec sp_DisplayTree(@CatID) My code I tried doesn't work: SELECT C.CatID As CatID, C.CatTitle As CatTitle, CatTree = (exec sp_DisplayTree C.CatID) FROM Cats As C WITH (nolock) I cannot find a solution to my solution, please help...
View Replies !
Select Statement Using Column Numbers
I have a number of lookup tables in my db. Each table typically has an ID column and a 'lookup values' column. I want to create a scalar function wherein, by supplying the table name and the ID, I want to retrieve the lookup value and vice-versa. I donot want to create seperate function for each table, which is easy. One function has to return values given the table name and the value of the ID or lookup value. The problem being faced is how to construct the select statement. An Execute statement created out of column names, retrieved with Col_Name(), is not useful as Execute statement is not permitted inside a a function.
View Replies !
In Code Behind, What Is Proper Select Statement Syntax To Retrieve The @BName Field From A Table?
In Code Behind, What is proper select statement syntax to retrieve the @BName field from a table?Using Visual Studio 2003SQL Server DB I created the following parameter:Dim strName As String Dim parameterBName As SqlParameter = New SqlParameter("@BName", SqlDbType.VarChar, 50) parameterBName.Value = strName myCommand.Parameters.Add(parameterBName) I tried the following but get error:Dim strSql As String = "select @BName from Borrower where BName= DOROTHY V FOWLER " error is:Line 1: Incorrect syntax near 'V'. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near 'V'. Source Error: Line 59: Line 60: Line 61: myCommand.ExecuteNonQuery() 'Execute the query
View Replies !
Adding Column Values Together In SQL SELECT Statement
I have an SQL Select statement that I need to add a column to called SalePrice, the SalePrice column needs to be calculated by adding together the values of 12 columns, then multiplying that value by the value in a another column to calculate margin. My issue is that I can only get it to add 7 column values together, if I add any more columns to the equation it just returns and null result. My DB is SQL 2005 Express SP2. My select statement is below: SELECT dbo.MFG_DATA_Machines.ID, dbo.MFG_DATA_Machines.MachineName, dbo.MFG_DATA_Parts_CPU.PartDescription AS CPU, dbo.MFG_DATA_Parts_CPU.PartCost AS CPUCost, dbo.MFG_DATA_Parts_Motherboard.PartDescription AS Motherboard, dbo.MFG_DATA_Parts_Motherboard.PartCost AS MotherboardCost, dbo.MFG_DATA_Parts_RAM.PartDescription AS RAM, dbo.MFG_DATA_Parts_RAM.PartCost AS RAMCost, dbo.MFG_DATA_Parts_HDD.PartDescription AS HDD, dbo.MFG_DATA_Parts_HDD.PartCost AS HDDCost, dbo.MFG_DATA_Parts_OpticalDrive.PartDescription AS OpticalDrive, dbo.MFG_DATA_Parts_OpticalDrive.PartCost AS OpticalDriveCost, dbo.MFG_DATA_Parts_Video.PartDescription AS Video, dbo.MFG_DATA_Parts_Video.PartCost AS VideoCost, dbo.MFG_DATA_Parts_OS.PartDescription AS OS, dbo.MFG_DATA_Parts_OS.PartCost AS OSCost, dbo.MFG_DATA_Parts_Modem.PartDescription AS Modem, dbo.MFG_DATA_Parts_Modem.PartCost AS ModemCost, dbo.MFG_DATA_Parts_FloppyDrive.PartDescription AS FloppyDrive, dbo.MFG_DATA_Parts_FloppyDrive.PartCost AS FloppyDriveCost, dbo.MFG_DATA_Parts_CardReader.PartDescription AS CardReader, dbo.MFG_DATA_Parts_CardReader.PartCost AS CardReaderCost, dbo.MFG_DATA_Parts_PowerSupply.PartDescription AS PowerSupply, dbo.MFG_DATA_Parts_PowerSupply.PartCost AS PowerSupplyCost, dbo.MFG_DATA_Parts_CaseType.PartDescription AS CaseType, dbo.MFG_DATA_Parts_CaseType.PartCost AS CaseTypeCost, dbo.MFG_DATA_Machines.Notes, dbo.MFG_DATA_Machines.MarginPercent, dbo.MFG_DATA_Machines.PriceOverride, (dbo.MFG_DATA_Parts_CPU.PartCost + dbo.MFG_DATA_Parts_Motherboard.PartCost + dbo.MFG_DATA_Parts_RAM.PartCost + dbo.MFG_DATA_Parts_HDD.PartCost + dbo.MFG_DATA_Parts_OpticalDrive.PartCost + dbo.MFG_DATA_Parts_Video.PartCost + dbo.MFG_DATA_Parts_OS.PartCost + dbo.MFG_DATA_Parts_Modem.PartCost + dbo.MFG_DATA_Parts_FloppyDrive.PartCost + dbo.MFG_DATA_Parts_CardReader.PartCost + dbo.MFG_DATA_Parts_PowerSupply.PartCost + dbo.MFG_DATA_Parts_CaseType.PartCost) * ((dbo.MFG_DATA_Machines.MarginPercent + 100) / 100) AS SalePrice FROM dbo.MFG_DATA_Machines LEFT OUTER JOIN dbo.MFG_DATA_Parts_CaseType ON dbo.MFG_DATA_Machines.CaseType = dbo.MFG_DATA_Parts_CaseType.ID LEFT OUTER JOIN dbo.MFG_DATA_Parts_Motherboard ON dbo.MFG_DATA_Machines.Motherboard = dbo.MFG_DATA_Parts_Motherboard.ID LEFT OUTER JOIN dbo.MFG_DATA_Parts_Video ON dbo.MFG_DATA_Machines.Video = dbo.MFG_DATA_Parts_Video.ID LEFT OUTER JOIN dbo.MFG_DATA_Parts_RAM ON dbo.MFG_DATA_Machines.RAM = dbo.MFG_DATA_Parts_RAM.ID LEFT OUTER JOIN dbo.MFG_DATA_Parts_PowerSupply ON dbo.MFG_DATA_Machines.PowerSupply = dbo.MFG_DATA_Parts_PowerSupply.ID LEFT OUTER JOIN dbo.MFG_DATA_Parts_OS ON dbo.MFG_DATA_Machines.OS = dbo.MFG_DATA_Parts_OS.ID LEFT OUTER JOIN dbo.MFG_DATA_Parts_OpticalDrive ON dbo.MFG_DATA_Machines.OpticalDrive = dbo.MFG_DATA_Parts_OpticalDrive.ID LEFT OUTER JOIN dbo.MFG_DATA_Parts_Modem ON dbo.MFG_DATA_Machines.Modem = dbo.MFG_DATA_Parts_Modem.ID LEFT OUTER JOIN dbo.MFG_DATA_Parts_HDD ON dbo.MFG_DATA_Machines.HardDisk = dbo.MFG_DATA_Parts_HDD.ID LEFT OUTER JOIN dbo.MFG_DATA_Parts_FloppyDrive ON dbo.MFG_DATA_Machines.FloppyDrive = dbo.MFG_DATA_Parts_FloppyDrive.ID LEFT OUTER JOIN dbo.MFG_DATA_Parts_CPU ON dbo.MFG_DATA_Machines.CPU = dbo.MFG_DATA_Parts_CPU.ID LEFT OUTER JOIN dbo.MFG_DATA_Parts_CardReader ON dbo.MFG_DATA_Machines.CardReader = dbo.MFG_DATA_Parts_CardReader.ID
View Replies !
How Do I Get The Actual Name Of A Column Or Table In A Sql Select Statement?
Hello fellow .net developers, In a website I'm working on I need to be able to put all of the user tables in a database in a dropdownlist. Another dropdownlist then will autopopulate itself with the names of all the columns from the table selected in the first dropdownlist. So, what I need to know is: is there a sql statement that can return this type of information? Example: Table Names in Database: Customers, Suppliers Columns in Customers Table: Name, Phone, Email, Address I click on the word "Customers" in the first dropdownlist. I then see the words "Name", "Phone", "Email", "Address" in the second dropdownlist. I'm sure you all know this (but I'll say it anyways): I could hardcode this stuff in my code behind file, but that would be really annoying and if the table structure changes I would have to revise my code on the webpage. So any ideas on how to do this the right way would be really cool. Thanks in advance, Robert
View Replies !
Concatenating Column Values In SELECT Statement
I'm puzzled as to how to express what I want in a stored procedure. Assume two columns, Surname and GivenName. The surname might be missing. When I originally wrote this app in Access, I used the following expression: SELECT Iif( IsNull(Surname), GivenName, Surname + ", " + GivenName ) AS Agent FROM Agents; I've looked at the syntax for CASE but I can't figure out exactly how to say what I intend, particularly the AS Agent column aliasing. Any help greatly appreciated. Please cc me privately so I receive your assistance at once! TIA, Arthur
View Replies !
How To Eliminate Column Header From Select Statement
Hi Everybody: I want select data from SQL server and save to a file without column header. Anyone has any suggestions? I try PLAIN option in select statement which is described in msdn library as: "PLAIN: Prevents column headings from appearing in the query output that is displayed. You can use PLAIN whether or not a TO clause is present. If an INTO clause is included, PLAIN is ignored. " However, I tried in both SQL 2000 and SQL 7, none of them supports it. Any ideas? Thanks in advance. Joan
View Replies !
Table Names And Field Names
I'm trying to do an update query that looks like this: UPDATE PAEMPLOYEE SET PAEMPLOYEE.LOCAT_CODE = EMPLOYEE.PROCESS_LEVEL FROM PAEMPLOYEE A JOIN EMPLOYEE B ON A.EMPLOYEE = B.EMPLOYEE It's erroring out on the Employee prefix B.EMPLOYEE saying: ..."does not match with a table name or alias name used in the query" Is it wrong or will it cause problems to have a field name the same as the table name?
View Replies !
Hiding Or Removing Column Output From Select Statement
I'm executing the following... select COL1, min(COL2) from TABLE group by COL1 the table has many duplicate entries, where COL2 is the primary key and unique, but its the duplicate COL1 entries that have to be removed. I was hoping a simple "delete from table where COL1 not in (select COL1, min(COL2) from TABLE group by COL1)" would do the trick, but obviously in returning two columns from the subselect this won't work. Can I hide the COL2 output from the query that will be put in the subselect? this is a one-off thing, so i'm not overly concerned about overhead or elegance. just need to make it so. tia a
View Replies !
Using The ORDER BY Clause When The Ordered Column Is Not Needed In The SELECT Statement
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 Replies !
Select Statement That Will Output Related Rows With Different Column Data Per Row?
Is there a way to build a select statement that will output related rows with different column data per row? I want to return something like: rowtype| ID | value A | 123 | alpha B | 123 | beta C | 123 | delta A | 124 | some val B | 124 | some val 2 C | 124 | some val 3 etc... where for each ID, I have 3 rows that are associated with it and with different corresponding values. I'm thinking that I will have to build a temp table/cursor that will get all the ID data and then loop through it to insert each rowtype data into another temp table. i.e. each ID iteration will do something like: insert into #someTempTable (rowtype, ID, value) values ('A', 123, 'alpha') insert into #someTempTable (rowtype, ID, value) values ('B', 123, 'beta') insert into #someTempTable (rowtype, ID, value) values ('C', 123, 'delta') etc.. After my loop, I will just do a select * from #someTempTable Is there a better, more elegant way instead of using two temp tables? I am using MSSQL 2005
View Replies !
Can A Column Be Derived Using Substring But The Parameters Are A Result Of A Select Statement?
I have a table which has a field called Org. This field can be segmented from one to five segments based on a user defined delimiter and user defined segment length. Another table contains one row of data with the user defined delimiter and the start and length of each segment. e.g. Table 1 Org aaa:aaa:aa aaa:aaa:ab aaa:aab:aa Table 2 delim Seg1Start Seg1Len Seg2Start Seg2Len Seg3Start Seg3Len : 1 3 5 3 9 2 My objective is to use SSIS and derive three columns from the one column in Table 1 based on the positions defined in Table 2. Table 2 is a single row table. I thought perhaps I could use the substring function and nest the select statement in place of the parameters in the derived column data flow. I don't seem to be able to get this to work. Any ideas? Can this be done in SSIS? I'd really appreciate any insight that anyone might have. Regards, Bill
View Replies !
Add Column With Fixed Number Of Values (text) To The Select Statement
Hello, I have such a problem. Need to add additional column to my query. The column should consist of set of fixed number (same as number of query rows) values (text). At start thought it's simple but now Im lost. Is there any chance to do it. Apreciate any help. I need to tell that I have only access to select on this database so no use of operation on tables.
View Replies !
Random Selection From Table Variable In Subquery As A Column In Select Statement
Consider the below code: I am trying to find a way so that my select statement (which will actually be used to insert records) can randomly place values in the Source and Type columns that it selects from a list which in this case is records in a table variable. I dont really want to perform the insert inside a loop since the production version will work with millions of records. Anyone have any suggestions of how to change the subqueries that constitute these columns so that they are randomized? SET NOCOUNT ON Declare @RandomRecordCount as int, @Counter as int Select @RandomRecordCount = 1000 Declare @Type table (Name nvarchar(200) NOT NULL) Declare @Source table (Name nvarchar(200) NOT NULL) Declare @Users table (Name nvarchar(200) NOT NULL) Declare @NumericBase table (Number int not null) Set @Counter = 0 while @Counter < @RandomRecordCount begin Insert into @NumericBase(Number)Values(@Counter) set @Counter = @Counter + 1 end Insert into @Type(Name) Select 'Type: Buick' UNION ALL Select 'Type: Cadillac' UNION ALL Select 'Type: Chevrolet' UNION ALL Select 'Type: GMC' Insert into @Source(Name) Select 'Source: Japan' UNION ALL Select 'Source: China' UNION ALL Select 'Source: Spain' UNION ALL Select 'Source: India' UNION ALL Select 'Source: USA' Insert into @Users(Name) Select 'keith' UNION ALL Select 'kevin' UNION ALL Select 'chris' UNION ALL Select 'chad' UNION ALL Select 'brian' select 1 ProviderId, -- static value '' Identifier, '' ClassificationCode, (select TOP 1 Name from @Source order by newid()) Source, (select TOP 1 Name from @Type order by newid()) Type from @NumericBase SET NOCOUNT OFF
View Replies !
Table Column Names = Dataset Column Values?!
I need to create the following table in reporting services PRODUCT April March Feb 2008 2007 2008 2007 2008 2007 chair 8 9 7 4 4 4 table 3 4 5 6 4 6 My problem is the month names are a column in the dataset, but I dont know how to get it to fill as column headers??? Thanks in advance!!!
View Replies !
Select Statement Within Select Statement Makes My Query Slow....
Hello... im having a problem with my query optimization.... I have a query that looks like this: SELECT * FROM table1 WHERE location_id IN (SELECT location_id from location_table WHERE account_id = 998) it produces my desired data but it takes 3 minutes to run the query... is there any way to make this faster?... thank you so much...
View Replies !
Return Field Names
Howdy all,I'm wishing to write a stored proc to return only the field names from a table. What I've tried gets the field names but also returns all of the data in each row. I only want the field names. Is this possible?Thanks!JP
View Replies !
SQL For Field Names From A Table
Hi,We have a database with some tables with (what I woulddenote as) 'referred field names'.Like this:DataTable1 with fields F1, F2, F3DataTable2 with fields F3, F4, F5DataTable3 with fields F1, F5, F2We also have a table with field namesFieldNameTable with fields FIELD, NAMEcontaining data like:FIELD NAME----------------F1 FieldName1F2 FieldName2F3 FieldName3F4 FieldName4F5 FieldName5Now, we need a way to query the data of these tables, butthe result of the query should show the 'referred field names'from the FieldNameTable.For example, querying DataTable3 should produce the outputFieldName1 FieldName5 FieldName2------------------------------------------... ... ...... ... ...Any idea how (and whether) this can be done with an SQL query?Thanks in advance for tips & tricks.Dirk Vdm
View Replies !
Changing Field Names
This is a followup to my last post. If a field name is changed in the database, what is the easiest way to determine what stored procedures and triggers that reference that field are now broken?
View Replies !
Show Field Names/Schema
I can't seem to find a sample code, either here or on the net - - so I'll go ahead and ask... What I'm looking for is a sample of how to query a database, so that I can populate a listbox with the table names from a database - - and then, populate another listobx with the field names from the database, in order to build a user-driven sql statement builder.... either of the above (at least the field name part) - either a code sample, or a link, will be greatly appreciated) - - Thanks
View Replies !
Crosstab Qry With Dynamic Field Names
Hi,I am trying to create a stored procedures (SQL 7.0), to provide dataina crosstab format.(I'm using Crystal Reports 8.5, but the Crosstab capabilities areterrible, so I have to do as much as possible on the SQL side)I have a table [Occurrences] with the following fields:Year (int)Month (int)Occurs (int)Claims (int)I need a query to give me the following format:Acct_Month 2001 2002 2003Occurs Claims Occurs Claims Occurs ClaimsJanuary 120 180 132 196 110 140February 154 210 165 202 144 178March etc.......Catch! I need the Year field name to be the contents of the fieldYear in the Table (2001, 2002, 2003...). Not the usual Year_1, Year_2approach.I got the month name ok...Acct_Month = DATENAME(month, Convert(Varchar(2), Month) + '/01/'+Convert(Char(4),Year))Is it possible to do this easely, without the use of cursors?Any help would be much appreciated.Luis Pinto
View Replies !
Finding Out Field Names By Lineage ID
Hi, is there any "robust" way to find out the name of a field in the pipeline by it's Lineage ID programatically? There is a sample code out there (on one of the blogs) but it seams not to be reliable... The usecase is easy... What is the field name in an error output of that column that causes the error? We don't want to have hardcoded LineageIDs in the error handling so I think it's the best idea to go with field names... However we only get that LineageID... Thanks,
View Replies !
|
TRACKER
