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 Complete Forum Thread with Replies
Sponsored Links:
Related Messages:
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 !
View Related
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 !
View Related
SELECT Statement - How To Not Get Column Field Names?
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 Replies !
View Related
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 !
View Related
Select Table Names
here a quick rundown of what i want to do: database database1 database hold all my good information. database 2 is the copy for breaking. Im too a point that i need to get my 2nd DB to by like the first. I want to truncate all the tables in database2, and then export, or select from insert into. I just need the data, not triggers or anything like that. So: How can i find the name of my tables (not views) in that database For each (select table_name from system where database="database") copy database.table_name database1.table_name; i hope that makes sense. I just need to restore my devel DB back to the original so i can mess it up again, Thanks
View Replies !
View Related
How To Write A Script To Create A Table (With Unfixed Field Names)?
Hello All. You may find below script stupid to you. I am trying to create a table with week number that is not fixed, i.e. March 2006 may have 4 weeks and April 5 weeks based on our company calendar. And Field Names could be Grp, SubGrp, Week_10, Week_11, Week_12, Week_13 depending on the week being stored in TempWeekFile. We could start the month having only Week 10 and following week with Week 11 added into TempWeekFile. My below script shows error. I have following records in my TempWeekFile Week_No WeekCnt 10 1 11 2 12 3 13 4 How to write a script that do the above? Has anyone done this using a better method? Please advise. Thank you. --------------------------------------------------------- if exists (select * from information_schema.tables where table_name='WeeklySalesToThird_Month') drop table WeeklySalesToThird_Month Declare @WeekCount int Declare @Cnt int Declare @WeekNo varchar(2) Select @WeekCount = count(*) from TempWeekFile Select @WeekCount Select @Cnt=1 CREATE TABLE [dbo].[WeeklySalesToThird_Work_Month] ( [Grp] [varchar] (30) NULL , [SubGrp] [varchar] (30) NULL , while @Cnt<=@WeekCount Begin select @WeekNo = Week_No from TempWeekFile where WeekCnt=@Cnt [Week_@WeekNo] [Money] NULL @Cnt=@Cnt+1 End ) ON [PRIMARY] GO --------------------------------------------------------
View Replies !
View Related
Select The Insert Value Into Field In Another Table!!!!!!
i have tbl_location which includes userid, building, room. i combine the building and room into one feild called mailstop SELECT Userid, Building +'/'+Room AS mailstop FROM tbl_Location i then want to take this recordset and insert it into a field called mailstop in my employee table. but they must based upon the userid of the location table and the userid of the employee table. so the userid of the location table must match the userid of the employee table and insert that mailstop value into the mailstop feild in employee table. i want to get this right the first time. any help would be greatly appreciated.
View Replies !
View Related
Referencing A Field In Another Table Within A SELECT
I have a SELECT line in an UPDATE query that looks like this: UPDATE Terms SET Field1 = LEFT(Emp_SSN,9), Field2 = Health, Field3 = (SELECT Emp_PayCode FROM Rates WHERE Health = ID) FROM Inserted,Rates WHERE Terms.Field1 = Emp_SSN On the Field3 update, how do I evaluate Emp_PayCode. Emp_PayCode is a field in another table (Employees). The value of that field is Instructional24 and if I hard code that value into the SELECT line, it works fine and returns the value ($0.00).
View Replies !
View Related
Looking For Field Name From Table On Select Criteria
Hari writes "Sub:- looking for Field name from table on select criteria dear friends I have an table [assosories] which have 100 fields. initial 10 fields have some date, varchar, int types rest 90 are bool type. for a single row I need to know the field name which have true vaules in rest 90 fields I need to insert this field name into another table as a row. Thanks HARI"
View Replies !
View Related
Help With A Query- Select A Top Field And Join It With Another Table
hi, i need help with a query:SELECT Headshot, UserName, HeadshotId FROM tblProfile INNER JOIN Headshots ON Headshots.ProfileId=tblProfile.ProfileId WHERE (UserName= @UserName) this query will select what I want from the database, but the problem is that I have multiple HeadshotIds for each profile, and I only want to select the TOP/highest HeadshotId and get one row foreach headshotId. Is there a way to do that in 1 SQL query? I know how to do it with multiple queries, but im using SqlDataSource and it only permits one. Thanks!
View Replies !
View Related
Need Help W/ SELECT From One Table, One Field, Multiple Unique Records
I'm new to MS SQL and VB. I have a table with one field JOB_NAME containing 20 records. Out of that field I want to retrieve 6 of the 20 records into a pulldown menu. They are all unique text names like so: Anna Smith John Doe etc. I did not see IDs listed for any of the names in the table when I looked. There is no common denominator to the names that can be filtered in the SELECT statement, and the 6 that I want will need to be pulled out individually. Is there a way to do this with a SELECT statement? I have not found much information about how to extract unique records out of a single field. Here's the statement I'm using which pulls all of them: strSQL = "SELECT DISTINCT JOB_NAME AS Names FROM [WORKER_NAMES] WHERE JOB_NAME<>' ' ORDER BY JOB_NAME ASC" This gives me the total list but I only want to bring back 6 of the 20 for the pulldown. Is there a way to modify this statement to pull only the records that I want? Thanks for any help you can give. AJ
View Replies !
View Related
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 !
View Related
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 !
View Related
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 !
View Related
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 !
View Related
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 !
View Related
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 !
View Related
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 !
View Related
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 !
View Related
Scrambled Field Names In Views
While creating a view in SQl Server 2005 Management Studio Or Interation Services on a SQL Server 2000 database using "*" something very strange happens. Has anyone every seen this happen before? The new view is created something like the following: CREATE VIEW [dbo].[new_view] AS SELECT * FROM Table1 GO However, when right-clicking on the new view, and choosing "Design", the SELECT scrambles the field names with aliases of the other field names. When the view is run, the result set is incorrect. It may look something like the following: SELECT Field1, Field2 AS Field3, Field3 AS Field4, Field4 AS Field5, . . . From Table1 GO
View Replies !
View Related
Trying To Display Alias Field Names
Hello All, I have the following code: USE MLS select sc.name,f.field#,fdesc,flong from sysobjects so join syscolumns sc on so.id = sc.id join fld f on f.field# = replace(sc.name,'_','') where so.name = 'dbo.tbl_MLS_Leads_Trans' I am trying to get the description which is flong and I get the following error message: Msg 208, Level 16, State 1, Line 2 Invalid object name 'fld'. What am I doing wrong? TIA Kurt
View Replies !
View Related
Finding Unique Field Names With Different Values
I am in a situation where I need to find out unique field names with different values in a table having 200+ columns. Let's say I have two rows with 200+ columns ( I exported these rows from Lotus Notes to SQL Server) I am not sure what columns makes unique of these rows. It's going to be tedious by checking each column values. Is there anyway I can write a squl query on these two rows which outputs column names which are having unique values. I would appreciate If anybody gives me hint about achieving desired result
View Replies !
View Related
A Strange Problem With SQL Query Fro Getting Field Names
Hello All, I have been trying to get this code work, but I could not. Every thing seems going well. However, The result of running the sql query is strange. It shows the field names twice. Eg:) if you have a table called "newtable" that has two fields[Custnumber, Custname], you will get somthing like this [Custnumber, Custname Custnumber, Custname]. I have tried many times, but I couldn't fix it. Sub Page_Load(sender As Object, e As EventArgs) handles Mybase.Load if not page.Ispostback then try Sqlconnection = New Sqlconnection (connectionString) querystring = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNs WHERE TABLE_NAME = 'Newtable'" SqlCommand = New SqlCommand(queryString, Sqlconnection) SqlConnection.Open dataReader = SqlCommand.ExecuteReader(CommandBehavior.CloseConnection) while dataReader.Read() Tablefields_txt.text += dataReader.Getstring(0) & ", " End while catch ex as Exception msgbox("An error has occured: " + ex.Message,0, "Error Message") finally SqlConnection.Close() End try End if Any help , please
View Replies !
View Related
Best Approach To Sending Field Names Dynamically
Hi,I have a C# web app that searches my database table using thefollowing search parametersSearch string, criteria (< =) and the field you want to perform yoursearch on. My understanding is that stored procedure is the way to go.What's the best way of doing this using stored procedures. Can Idefine a placeholder for the field name?Ex.SELECT field1, field2... FROM Table WHERE field1='value1' where field1and value1 are both sent from code.If it's not possible then what is the best way to approach thisproblem? I see so many searches like that on the internet. I can onlydo them with inline SQL and not stored procedure.Thank youMaz.
View Replies !
View Related
Query Returning Duplicate Field Names
I have a .NET program that can connect to either an Access 97 database or anSQL Server 7 database. In the database I have two tables which have a fieldcalled ID. When I run a query like "SELECT A.*, B.* FROM A, B", the queryreturns those fields as "A.ID" and "B.ID" when connected to Access 97, butas "ID" and "ID" in SQL Server 7. Is there anyway to get SQL Server toprepend the table name to the field name in a case like this and not returnduplicate field names like that without having to specify aliases for thefields?- Don
View Replies !
View Related
Can You Pass Field Names In Stored Procs?
CREATE PROCEDURE [dbo].[removeContact] @RemoveType VARCHAR (50), @ListID INT, @TargetField VARCHAR (50), @TargetValue VARCHAR (150), @RowsAffected INT OUTPUT AS UPDATE [tblContacts] SET @RemoveType = 'yes' WHERE [list_id]=@ListID AND @TargetField = @TargetValue SELECT @RowsAffected = @@ROWCOUNT GO declare @P1 int set @P1=0 exec removeContact '[unsubscribe]', 6, '[email]', 'email@domain.com', @P1 output select @P1 I've tried this with and without the square brackets. Either way, nothing gets updated. Am I barking up the wrong tree?
View Replies !
View Related
Changing Field Names Is SQL Server 2000
Why is it that when you change a field name is SQL Server it sometimes completely messes things up. I renamed a field in one of my tables from Emp# to EmpNumber. I had a view based on this table and naturally I knew I would have to change a view I had based on the table. I opened the view and changed the field from Emp# to EmpNumber but when I tried to open the view I got an error “Invalid column Name EMP#”. I have not been able to fix this. I have dropped and recreated the view, refreshed all the objects using enterprise manager, refreshed all the objects using Query Analyzer, shut down and restarted my computer, taken my database offline and put it back on line. The field name EMP# is not in any tables in my database and not referenced any views or procs . I am just starting on this database so I could verify this very easily because I only have a few views and procs. Has anyone had this problem or more importantly does anyone know why this is happening or how to fix it?
View Replies !
View Related
.dbf File Import (duplicate Field Names)
I am importing a file creating by an application which exports the file into .dbf format. Very unfortunately, this .dbf file can have fields with IDENTICAL column_names. Utilizing ActiveX, I create an ado connection to the .dbf file using a visual foxpro drver. However, and not unexpectantly, I can not do the 'select *' from the file if there are duplicate names. Can anyone make recommendations here that might help? Oh, this is SQL200 in case that impacts what you might advise!!!!
View Replies !
View Related
Dynamic/variable Field Names In Expressions
I have a report containing 3 datasets. One of these sets is a record set of 2 columns (Name,Priority). I use this dataset as a list so I can create a page for each Name is the list. On each page, I would like to access the other 2 datasets and depending on the Name in the list (Fields!Name.Value), I need to access different Fields. This can easily be done by making a long =IFF list but I would rather make this dynamic, cause the IFF statement becomes a long mess. One of the Names of the list is Joe so if the list name = Joe, his columns need to be read. =Max(Fields!ABSDIFF_JOE.Value, "APPLICATIONS") Or =round(sqrt(pow(StDevP(Fields!DIFF_TJOE.Value, "APPLICATIONS"),2) + pow(avg(Fields!DIFF_TJOE.Value, "APPLICATIONS"),2)),2) When the next list/page is created, the next name needs to be used to get the data from the correct field/column =Max(Fields!ABSDIFF_MAX.Value, "APPLICATIONS") or =round(sqrt(pow(StDevP(Fields!DIFF_TMAX.Value, "APPLICATIONS"),2) + pow(avg(Fields!DIFF_TMAX.Value, "APPLICATIONS"),2)),2) Can I somehow create this as such: =Max(Fields!ABSDIFF_ & Fields!Name.Value &.Value, "APPLICATIONS") or =round(sqrt(pow(StDevP(Fields!DIFF_T& Fields!Name.Value &.Value, "APPLICATIONS"),2) + pow(avg(Fields!DIFF_T& Fields!Name.Value &.Value, "APPLICATIONS"),2)),2) As you can see, I replaced the name (Joe or Max) with "Fields!Name.Value". Unfortunately this doesn't work. Any advice would be appreciated. Thanks Rob Ps. The record set used as input for this report is rather difficult to change.
View Replies !
View Related
DTS Of Data Between Two Tables, Same Field Names But Differnt Datatypes
I need to migrate data from one sql database to another. The second DBis a newer version of the "old" database with mostly the same tablesand fieldnames. In order support some reporting queries in the "new"version I needed to change the datatype of a few fields from varchar toint(the data stored was integers already as they were lookup tables).DTS works great except in the cases of about 10 fields which I changedthe datatypes on from varchar to int.DTS seems to drop the data if the fieldname and datatype are not anexact match. Is there any way to use DTS and have it copy data from afield call subsid type varchar to a field call subsid type int?
View Replies !
View Related
Splitting Out Names From One Field Delimted By Comma And A Space
have a field in a table that has combined lastname,firstname and middle name like combs,albert mike woods-athere,jane alice The last and first name are separated by a comma, and the middle name by a space I need in tsql to split them to last name,first name and middle name I used below select substring (longname, 1, patindex( '%,%' , longname) -1 ) 'firstname', substring (longname, patindex( '%,%', longname) + 1, len(longname)) 'lastname', substring (longname, patindex( '% %', longname) + 1, len(longname)) 'middlename' FROM Demographic_staging I get the names all split, but I get the middle name with the first name. How can I limit the first name to recoginze the comma, but stop at the space
View Replies !
View Related
Get Percentage With Variation Of Field Values (country Names)
Any help here would be greatly appreciated... Unfortunately, data wasn't filtered prior to getting inserted into this table. Now I am stuck with cleaning it up. I have thought about writing a query to update all the values, but there are just too many variations, including spelling mistakes, so I've ruled that out as a possible solution. I have a table which has a Country field but the values per record vary. For example US, U.S., USA, United States, UK, United Kingdom, Canada, Can, etc. I'm trying to find the percent of records per country. Sample table data: mytable Id Name Country 1 John US 2 James UK 3 Jane United States 4 Mary Canada 5 Jack U.S. 6 Tony United Kingdom 7 Jeff US 8 Tom Canada 9 Beth UK 10 Mark USA I would like to show US: 50% --> (includes any variation of US ncluding US, U.S., USA, United States) UK: 30% CAN: 20% I've made several attempts myself with no luck. Thanks in advance.
View Replies !
View Related
Field Names Missing In MDX Data Set When Using NON EMPTY Clause
So I have an MDX query in an SSRS data set. Here is my MDX query: Code SnippetSELECT { [Measures].[Gross Sales Amount USD], [Measures].[Net Sales Amount USD] } ON COLUMNS, { ([Promotion].[Media Property].[Promo Code Description].ALLMEMBERS) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOMEMBER(@BeginDateConvert, CONSTRAINED) : STRTOMEMBER(@EndDateConvert, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@PromotionMediaProperty, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( { [Promotion].[Campaigns].[Campaign].&[Paid Partner] } ) ON COLUMNS FROM ( SELECT ( { [Products].[Product Line].[Line].&[Merchandise] } ) ON COLUMNS FROM ( SELECT ( { [BusinessUnit].[Business Unit].[Product Business Unit].&[40] } ) ON COLUMNS FROM [Net Sales]))))) WHERE ( [BusinessUnit].[Business Unit].[Product Business Unit].&[40], [Products].[Product Line].[Line].&[Merchandise], [Promotion].[Campaigns].[Campaign].&[Paid Partner] ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS This query returns 4 fields. Media Property, Promo Code Description, Gross Sales, and Net Sales. For the given query the measures are empty or null. I do not want any data to show up when the measures are null so I put in NON EMPTY clauses before the COLUMNS and before the ROWS. So now my query looks like this: (I only added the NON EMPTY clauses) Code Snippet SELECT NON EMPTY { [Measures].[Gross Sales Amount USD], [Measures].[Net Sales Amount USD] } ON COLUMNS, NON EMPTY{ ([Promotion].[Media Property].[Promo Code Description].ALLMEMBERS) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOMEMBER(@BeginDateConvert, CONSTRAINED) : STRTOMEMBER(@EndDateConvert, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@PromotionMediaProperty, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( { [Promotion].[Campaigns].[Campaign].&[Paid Partner] } ) ON COLUMNS FROM ( SELECT ( { [Products].[Product Line].[Line].&[Merchandise] } ) ON COLUMNS FROM ( SELECT ( { [BusinessUnit].[Business Unit].[Product Business Unit].&[40] } ) ON COLUMNS FROM [Net Sales]))))) WHERE ( [BusinessUnit].[Business Unit].[Product Business Unit].&[40], [Products].[Product Line].[Line].&[Merchandise], [Promotion].[Campaigns].[Campaign].&[Paid Partner] ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS Adding the NON EMPTY returns nothing... not even field names. This is a problem because, I have a table in the report that looks at this data set and when there are no fields, the report can't run. How can I still have NON EMPTY functionality and still show the fields? Is this a problem in SSRS?
View Replies !
View Related
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 !
View Related
TSQL - Copy Table As New Table And Get The Sum Of Specific Field Group By Other Field
Hi guys, I need to get a column with the sum of the field "SUF" from table "JurnalTransMoves_1" when that field ("SUF") is ordered by the field "REFERENCE" from table "Stock", and Show the value only once. The desired result should by something like: Stock.REFERENCE JurnalTransMoves.SUF SUM(JurnalTransMoves.SUF) Group By Stock.REFERENCE 5752 10 60 5752 20 5752 30 5753 400 3000 5753 500 5753 600 5753 700 5753 800 5754 7 15 5754 8 Is there any chance to do that? Thanks in advance, Aldo. Code Snippet SELECT Accounts.FULLNAME AS 'ACCOUNTS.FULLNAME', Accounts.ACCOUNTKEY AS 'ACCOUNTS.ACCOUNTKEY', Accounts.FILTER AS 'ACCOUNTS.FILTER', Accounts.SORTGROUP AS 'ACCOUNTS.SORTGROUP', AccSortNames.SORTCODENAME AS 'AccSortNames.SORTCODENAME', Accounts.CreditTermsCode AS 'Accounts.CreditTermsCode', CreditTerms.DETAILS AS 'CreditTerms.DETAILS' CreditTerms.CURRENF AS 'CreditTerms.CURRENF' CreditTerms.MONTH AS 'CreditTerms.MONTH', CreditTerms.DAYS AS 'CreditTerms.DAYS', CreditTerms.SHAREPRC AS 'CreditTerms.SHAREPRC', CreditTerms.TEMF AS 'CreditTerms.TEMF', CASE WHEN CAST(Accounts.VatExampt AS int) = 0 THEN 'x' WHEN CAST(Accounts.VatExampt AS int) = 1 THEN 'y' ELSE 'Undefined' END AS 'VAT', Stock.DOCUMENTID AS 'Stock.DOCUMENTID', DocumentsDef.DOCNAME As 'DocumentsDef.DOCNAME', CASE WHEN CAST(Stock.DOCUMENTID as int) = 1 THEN Stock.DOCNUMBER WHEN CAST(Stock.DOCUMENTID as int) = 3 THEN Stock.DOCNUMBER WHEN CAST(Stock.DOCUMENTID as int) = 35 THEN Stock.DOCNUMBER WHEN CAST(Stock.DOCUMENTID as int) = 120 THEN Stock.DOCNUMBER WHEN CAST(Stock.DOCUMENTID as int) = 31 THEN Stock.REFERENCE WHEN CAST(Stock.DOCUMENTID as int) = 44 THEN Stock.REFERENCE WHEN CAST(Stock.DOCUMENTID as int) = 34 THEN Stock.REFERENCE WHEN CAST(Stock.DOCUMENTID as int) = 43 THEN Stock.REFERENCE WHEN CAST(Stock.DOCUMENTID as int) = 40 THEN Stock.REFERENCE ELSE '' END AS 'Invoice No', Stock.VALUEDATE AS 'Stock.VALUEDATE', JurnalTrans.DESCRIPTION AS 'JurnalTrans.DESCRIPTION', JurnalTrans.REF2 AS 'JurnalTrans.REF2', JurnalTransMoves.SUF AS 'JurnalTransMoves.SUF', JurnalTransMoves_1.SUF AS 'JurnalTransMoves_1.SUF', JurnalTransMoves.TRANSID AS 'JURNALTRANSMOVES.TRANSID' FROM JURNALTRANSMOVES AS JurnalTransMoves_1 INNER JOIN JURNALTRANSMOVES AS JurnalTransMoves INNER JOIN (SELECT DISTINCT JURNALTRANSID, RECEIPTSTOCKID, FULLMATCH, TABLFNUM, CKCODE, RSORT, RUSEFID FROM RECEIPTJURNALMATCH) AS ReceiptJurnalMatch_1 ON ReceiptJurnalMatch_1.JURNALTRANSID = JurnalTransMoves.ID INNER JOIN ACCOUNTS AS Accounts ON JurnalTransMoves.ACCOUNTKEY = Accounts.ACCOUNTKEY INNER JOIN JURNALTRANS AS JurnalTrans ON JurnalTransMoves.TRANSID = JurnalTrans.TRANSID INNER JOIN STOCK AS Stock ON JurnalTrans.STOCKID = Stock.ID ON JurnalTransMoves_1.TRANSID = JurnalTrans.TRANSID AND JurnalTransMoves_1.ACCOUNTKEY = Accounts.ACCOUNTKEY LEFT OUTER JOIN ITEMS AS Items INNER JOIN STOCKMOVES ON Items.ITEMKEY = STOCKMOVES.ITEMKEY INNER JOIN ITEMSORTNAMES AS ItemSortNames ON Items.SORTGROUP = ItemSortNames.ITEMSORTCODE ON Stock.ID = STOCKMOVES.STOCKID LEFT OUTER JOIN ACCSORTNAMES AS AccSortNames ON Accounts.SORTGROUP = AccSortNames.ACCSORTCODE LEFT OUTER JOIN CREDITTERMS AS CreditTerms ON Accounts.CREDITTERMSCODE = CreditTerms.CREDITTERMSCODE LEFT OUTER JOIN DOCUMENTSDEF AS DocumentsDef ON Stock.DOCUMENTID = DocumentsDef.DOCUMENTID WHERE Accounts.SORTGROUP Between '3001' And '3020' AND Accounts.ACCOUNTKEY IN ('123456') ORDER BY Accounts.ACCOUNTKEY
View Replies !
View Related
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 !
View Related
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 !
View Related
Msg 1013 Select Not Working -- Correlation Names?
I am trying to run a select query and getting the following error: Server: Msg 1013, Level 15, State 1, Line 12 Tables or functions 'PatientVisit' and 'PatientVisit' have the same exposed names. Use correlation names to distinguish them. Not sure what I am doing wrong! Here is the query. Can anyone point me int the right direction? SELECT Batch.Entry AS [Date Of Entry], PatientProfile.[Last] + ', ' + PatientProfile.[First] AS Name, MedLists.Description AS [Adjustment Type], PatientVisit.TicketNumber, PatientVisitProcs.Code AS [Procedure Code], TransactionDistributions.Amount AS [Adjustment Amount], PatientVisitProcs.DateOfServiceFrom, ISNULL(CONVERT(varchar(255), Transactions.Note), ' ') AS Notes, DoctorFacility.DotID, DoctorFacility.ListName as DrName, PatientVisit.DoctorID as DrID FROM PaymentMethod INNER JOIN VisitTransactions ON PaymentMethod.PaymentMethodId = VisitTransactions.PaymentMethodId INNER JOIN Transactions ON VisitTransactions.VisitTransactionsId = Transactions.VisitTransactionsId INNER JOIN TransactionDistributions ON Transactions.TransactionsId = TransactionDistributions.TransactionsId INNER JOIN Batch ON PaymentMethod.BatchId = Batch.BatchId INNER JOIN PatientVisit ON VisitTransactions.PatientVisitid = PatientVisit.PatientVisitId Inner Join PatientVisit ON DoctorFacility.DoctorFacilityID = PatientVisit.DoctorID INNER JOIN PatientProfile ON PatientVisit.PatientProfileId = PatientProfile.PatientProfileId INNER JOIN MedLists ON Transactions.ActionTypeMId = MedLists.MedListsId INNER JOIN PatientVisitProcs ON TransactionDistributions.PatientVisitProcsId = PatientVisitProcs.PatientVisitProcsId Any Idea on how to fix it up? Thanks
View Replies !
View Related
Table Names In Stored Procedures As String Variables And Temporary Table Question
How do I use table names stored in variables in stored procedures? Code Snippetif (select count(*) from @tablename) = 0 or (select count(*) from @tablename) = 1000000 I receive the error 'must declare table variable '@tablename'' I've looked into table variables and they are not what I would require to accomplish what is needed. After browsing through the forums I believe I need to use dynamic sql particuarly involving sp_executesql. However, I am pretty new at sql and do not really understand how to use this and receive an output parameter from it(msdn kind of confuses me too). I am tryin got receive an integer count of the records from a certain table which can change to anything depending on what the user requires. Code Snippet if exists(Select * from sysobjects where name = @temptablename) drop table @temptablename It does not like the 'drop table @temptablename' part here. This probably wouldn't be an issue if I could get temporary tables to work, however when I use temporary tables i get invalid object '#temptable'. Heres what the stored procedure does. I duplicate a table that is going to be modified by using 'select into temptable' I add the records required using 'Insert into temptable(Columns) Select(Columns)f rom TableA' then I truncate the original table that is being modified and insert the temporary table into the original. Heres the actual SQL query that produces the temporary table error. Code Snippet Select * into #temptableabcd from TableA Insert into #temptableabcd(ColumnA, ColumnB,Field_01, Field_02) SELECT ColumnA, ColumnB, Sum(ABC_01) as 'Field_01', Sum(ABC_02) as 'Field_02', FROM TableB where ColumnB = 003860 Group By ColumnA, ColumnB TRUNCATE TABLE TableA Insert into TableA(ColumnA, ColumnB,Field_01, Field_02) Select ColumnA, ColumnB, Sum(Field_01) as 'Field_01', Sum('Field_02) as 'Field_02', From #temptableabcd Group by ColumnA, ColumnB The above coding produces Msg 208, Level 16, State 0, Line 1 Invalid object name '#temptableabcd'. Why does this seem to work when I use an actual table? With an actual table the SQL runs smoothly, however that creates the table names as a variable problem from above. Is there certain limitation with temporary tables in stored procedures? How would I get the temporary table to work in this case if possible? Thanks for the help.
View Replies !
View Related
SSIS Loading DWH Staging Area When Table Names Is Selected From Table List
Hello, Maybe anyone have done that before? I have table where i store SOURCE_TABLE_NAME and DESTINATION_TABLE_NAME, there is about 120+ tables. i need make SSIS package which selects SOURCE_TABLE_NAME from source ole db, and loads it to DESTINATION_TABLE_NAME in destination ole db. I made such SSIS package. set ole db source data access mode to table or view name variable. set ole db destination data access mode to table or view name variable. set to variables defoult values (names of existing tables) but when i loop table names is changed, it reports error, that can map columns, becouse in new tables is different columns. how to solve that problem?
View Replies !
View Related
|
TRACKER
