i want to write a stored procedure where i pass column names a parameters and i want to get result based on that
For ex:-
if i pass the parameters as
col3 and col5 where id =1 then i should the result as
id col3 col4 col5
1 3 4 5
and if i pass input as col2and col6 where id =3, the result should be
id col2 col3 col4 col5 col6
3 4 8 2 6 9
Hi there, im still learning SQL so thanks in advance.I have a table with columns of customer's information, [customerID], [customerFirst], [customerLast], , [program] ... other columns ... There will be entries where there can be duplicate customerFirst and customerLast names. I would like to just return a single entry of the duplicate names and all associated row information. IE: [customerID], [customerFirst], [customerLast], [ email], [program] 01 Bill Smith bill.smith@hotmail.com ymca 02 Bill Smith bill.smith@hotmail.com Sports 03 jon doe jon.doe@hotmail.com AAA 04 jon doe jon.doe@hotmail.com Ebay 05 Paul Sprite paul.sprite@hotmail.com Rec Desired Returned result: 01 Bill Smith bill.smith@hotmail.com ymca 03 jon doe jon.doe@hotmail.com AAA 05 Paul Sprite paul.sprite@hotmail.com Rec So in my code i have this:dAdapter = new SqlDataAdapter("SELECT * FROM [Poc_" + suffix + "] WHERE (SELECT DISTINCT [CustomerLastName], [CustomerFirstName], [CustomerEmail] FROM [Poc_" + suffix + "])", cnStr); dAdapter.Fill(pocDS, "Data Set"); However this is throwing up an error when i build the app: An expression of non-boolean type specified in a context where a condition is expected, near ')'.
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: An expression of non-boolean type specified in a context where a condition is expected, near ')'.
Source Error:
Line 52: //dAdapter = new SqlDataAdapter("SELECT DISTINCT * FROM [Poc_" + suffix + "] ORDER BY [CustomerLastName]", cnStr); Line 53: dAdapter = new SqlDataAdapter("SELECT * FROM [Poc_" + suffix + "] WHERE (SELECT DISTINCT [CustomerLastName], [CustomerFirstName], [CustomerEmail] FROM [Poc_" + suffix + "])", cnStr); Line 54: dAdapter.Fill(pocDS, "Data Set");Line 55: Line 56: //Dataset for name comparison 1: Can someone explain to me why this error is happening?2: Can soemone confirm that my intentions are correct with my code?3: If I'm completely off, can someone steer me in the right direction?Thanks alot!-Terry
I'm trying to create a fairly simple custom transform component (because I've read that's the easiest one to create) which will take one column from a flat file source and based on the first row create the output columns. I'm actually trying to write a component that will solve the now well known problem with parsing CSV files in SSIS. I have a lot of source files and all have many columns so a component that can read in the first line from the CSV file and create the output columns automatically will save me lots of time when migrating the old DTS packages.
I have the basic component set up but I'm stuck when trying to override the OnInputPathAttached method because I don't know how to use the inputID to get the first line from the input (the buffer). Are there any good examples for creating output columns dynamically based on the input buffer? Should I just give up on on the transform and create a custom source component instead?
I have 3 different companies that share the same ticket_types(CRMS System). I need to display the Ticket Types and the 3 company's Ticket Count:
Ticket Type | Company A Count | Company B Count | Company C Count
I can get the information individually for each company, but if a company doesn't have a ticket in one of the ticket_types, then it isn't displayed in a row. So, I tried to write the following, which isn't pulling back any data.
DECLARE @startdate date = '20150306' DECLARE @enddate date = '20151031' DECLARE @AcctGrp varchar(20) = '111' ;WITH TType AS ( SELECT ctp.description as TicketType
[Code] .....
If I run each SELECT individually from above (excluding the last SELECT), it works and I get the following:
TicketType AR Request Credit Availability/Rush Cancel Order Credit Card Payment Expedite Order Freight Quote
[Code] ...
How to get the query results? Am I even close to getting it right?
I do appreciat your help, I want to run a store procedure which will show 6 months . I do not know how to write the procedure, here in the notion in my mind, I want to be able to pass an input parameter (month) to the procedure which will then run a query to show 6 months prior the input parameter month, how can I do that, thanks for your help
I am trying to write a stored procedure that will select information from a SQL table based on a specific time. For example I have a name field and a time field, I want to return just the names that were created between a specific time frame. ex between 3pm and 4pm. Any thoughts?
I have table 1 from which I select some values belonging to users.In table 2 I store tips a user might give: tipid, tiptitle, tiptext,tipcreatedateA user may give more than one tip.But now I want a query that selects the info of a SINGLE user and the LATEST tip he created, so resultset might look like:username lastname sex tiptitle tiptext tipcreatedateSo even though a user might have given more tips, only the latest will be retreived...and thus 1 row for a particular user is returned...How would I construct such a query? The problem is that I want to get exactly 1 row but more than 1 column from the table 2, so I think I cannot use the SQL Server "TOP" command...
I am building an SSIS package that loops through a table in SQL Server and dynamically builds a select statement that i would like to use as an ole db source. I have been having a difficult time with this as the select statement that i am generating is over 200,000 characters long so using an sql variable is out of the question.
I ended up placing the select statement into a table where each row of the table represents a piece of the select. I then use an execute_sql task that selects the entire rowset from this table into a variable object. I then use a for each loop to shred the variable and concatenate it into on big string variable called user:: sql_statement that is my select.
After setting up the loop and testing to see if the user:: sql_statement variable populates correctly i then added a data flow transfer with an ole db source and destination. I then go into the advanced editor for the source and set it to accept an sql statement from a variable and use my user:: sql_statement variable. I was forced to set validate external metadata option to false to avoid an error since there is no way to validate the columns until the for each loop runs during run time.
Now thats all fine and good but what is causing my problem is that during run time, when the package gets to the data flow task, the select statement doesn't seem to be populating the input columns of the data source. I have been searching to no avail on a way to tell the data source to update the input columns but every time it gets there, the package bombs out telling me the ole db source has no available output columns.
Specifically the error i get is : [DTS.Pipeline] Error: "output "OLE DB Source Output" (6616)" contains no output columns. An asynchronous output must contain output columns.
I want to select columns from different tables into a single table...Not sure if a temp table is suited for this and if so how I should implement it (NOTE: this query will be executed many times by different users at the same time, so I'd rather avoid temp tables!)I have:TABLE1idfirstnamedescriptioncreatedateTABLE2idcarnamespecificationsimportdateNow, I want a resultset that has the columns (columns from other tables from which the values should be retreived are behind the desired columns):id (TABLE1.id, TABLE2.id)title (TABLE1.firstname , TABLE2.carname)description (TABLE1.description , TABLE2.sepcifications)date (TABLE1.createdate , TABLE2.importdate)Thanks!
What are the options to create a table of contents based on the report items in a List Control? Document Mapping works for online viewing. A table of content would make the report easier to read when it's printed.
Hi, I know sys.tables and sys.columns gives me a list of tables andcolumns in a SQL 2005 database.How can I list Columns in a specific Table please?Thanks in advance,Ronny
I have 4 rows below in file tblTEST, and I want to be able to transfer the CODE from the MAIN location to the INT location (replacing all existing "A" codes), preceeded by an "I".
ID LOC CODE -- ----- ------ 11 MAIN B 11 INT A 22 MAIN C 22 INT A
I want the result to be:
ID LOC CODE -- ----- ------ 11 MAIN B 11 INT IB 22 MAIN C 22 INT IC
I am stumped as to how to do this - any help or advice would be appreciated.
The only thing I've come up with is:
UPDATE S SET s.code = B.code FROM tbltest B LEFT OUTER JOIN tbltest S ON B.id = S.id WHERE (S.loc = 'INT')
My requirement is below.enhancing the T- sql query as I was told not to use SSIS.
Insert data from Table A to Table B with conditions:
1. Truncate gender to one character if necessary.
2. Convert character fields to uppercase as necessary.
3. For systems that supply both residential and mailing addresses, use the residential address if available (both street_address and zip fields have value), mailing address otherwise.
In SSIS I took conditional split with 'ISNULL(res_street_address) == TRUE || ISNULL(res_zip) == TRUE '
default outputname :Consider Res Address; Outputname:Consider mail address.
and mapped as:
(Table A) mail_street_address---street address(Table B)
(Table A) mail_city----------------City(Table B)
(Table A) mail_Zip----------------Zip(Table B)
(Table A) mail_state-------------state(Table B)
(Table A) res_street_address--street address(Table B)
(Table A) res_city---------------City(Table B)
(Table A) res_Zip----------------Zip(Table B)
(Table A) res_state--------------state(Table B)
I want to do the same with T-sql code too:
I came up with below T-SQl but unable to pick(street,city,state,zip columns as I have take combination of street and zip from Table A not individual columns as I wrote in below query) based on above condition(3):
Insert into TABLE B SELECT Stats_ID ,UPPER(first_name) first_name ,UPPER(middle_name )middle_name ,UPPER(last_name) last_name ,UPPER(name_suffix) name_suffix
Auto_ID Account_ID Account_Name Account_Contact Priority 1 3453463 Tire Co Doug 1 2 4363763 Computers Inc Sam 1 3 7857433 Safety First Heather 1 4 2326743 Car Dept Clark 1 5 2342567 Sales Force Amy 1 6 4363763 Computers Inc Jamie 2 7 2326743 Car Dept Jenn 2
I'm trying to delete all duplicate Account_IDs, but only for the highest priority (in this case it would be the lowest number).
I know the following would delete duplicate Account_IDs:
DELETE FROM staging_account WHERE auto_id NOT IN (SELECT MAX(auto_id) FROM staging_account GROUP BY account_id)
The problem is this doesn't take into account the priority; in the above example I would want to keep auto_ids 2 and 4 because they have a higher priority (1) than auto_ids 6 and 7 (priority 2).
How can I take priority into account and still remove duplicates in this scenario?
I have 5 different tables with same structure. Each table has an account column and another column count. I am trying to generate a report based of the columns in each table. Account is same in all tables with same account numbers but count will be different.
I am facing a problem in writing the stored procedure for multiple search criteria.
I am trying to write the query in the Procedure as follows
Select * from Car where Price=@Price1 or Price=@price2 or Price=@price=3 and where Manufacture=@Manufacture1 or Manufacture=@Manufacture2 or Manufacture=@Manufacture3 and where Model=@Model1 or Model=@Model2 or Model=@Model3 and where City=@City1 or City=@City2 or City=@City3
I am Not sure of the query but am trying to get the list of cars that are to be filtered based on the user input.
I am using CROSS APPLY instead of UNPIVOT to unpivot > one column. I am wondering if I can dynamically replace column names based on different tables? The example code that I have working is based on the "Allergy" table. I have thirty more specialty tables to go. I'll show the working code first, then an example of another table's columns to show differences:
select [uplift specialty], [member po],[practice unit name], [final nomination status] ,[final uplift status], [final rank], [final uplift percentage] ,practiceID=row_number() over (partition by [practice unit name] order by Metricname) ,metricname,Metricvalue, metricpercentilerank
[code]....
Rheumatology Table:The columns that vary start with "GDR" and [GDR Percentile Rank] so I'm just showing those:
I have a table #vert where I have value column. This data needs to be updated into two channel columns in #hori table based on channel number in #vert table.
CREATE TABLE #Vert (FILTER VARCHAR(3), CHANNEL TINYINT, VALUE TINYINT) INSERT #Vert Values('ABC', 1, 22),('ABC', 2, 32),('BBC', 1, 12),('BBC', 2, 23),('CAB', 1, 33),('CAB', 2, 44) -- COMBINATION OF FILTER AND CHANNEL IS UNIQUE CREATE TABLE #Hori (FILTER VARCHAR(3), CHANNEL1 TINYINT, CHANNEL2 TINYINT) INSERT #Hori Values ('ABC', NULL, NULL),('BBC', NULL, NULL),('CAB', NULL, NULL) -- FILTER IS UNIQUE IN #HORI TABLE
One way to achieve this is to write two update statements. After update, the output you see is my desired output
UPDATE H SET CHANNEL1= VALUE FROM #Hori H JOIN #Vert V ON V.FILTER=H.FILTER WHERE V.CHANNEL=1 -- updates only channel1 UPDATE H SET CHANNEL2= VALUE FROM #Hori H JOIN #Vert V ON V.FILTER=H.FILTER WHERE V.CHANNEL=2 -- updates only channel2 SELECT * FROM #Hori -- this is desired output
my channels number grows in #vert table like 1,2,3,4...and so Channel3, Channel4....so on in #hori table. So I cannot keep writing too many update statements. One other way is to pivot #vert table and do single update into #hori table.
Hello, I think I have a pretty simple question, I have a list of record IDs that I need to search on. I'm trying to create a query like this:
Select record_ID FROM table WHERE record_id <> (1,3,4,6,8,9,11)
In a table with 12 records, I'd like it to return 2,5,6,10,12
Background: I have a list of .jpgs that correspond to record_ids. I want to take the list of .jpg names and compare that to the record_ids so I may delete any .jpgs that do to not have a corresponding record.
Okay, let's see if I can explain this one. I am summing multiple lines of data from a labor detail table, by status. Using this query
SELECT EM.Lastname, LD.WBS1, LD.WBS2, P.Longname, SUM(LD.Held) AS HELDLABOR, SUM(LD.TBWRittenOff) as TBWrittenOffLabor, SUM(LD.WrittenOff) AS WRITTENOFFLABOR FROM PR P INNER JOIN (SELECT WBS1, WBS2, SUM(CASE WHEN BillStatus = 'h' THEN Billext ELSE 0 END) AS Held, SUM(CASE WHEN BillStatus = 'w' THEN Billext ELSE 0 END) AS TBWrittenOff, SUM(CASE WHEN BillStatus = 'x' THEN Billext ELSE 0 END) AS WrittenOff FROM LD WHERE BillSTatus IN ('x','h', 'w') GROUP BY WBS1, WBS2) LD ON p.WBS1 = ld.wbs1 AND P.WBS2 = LD.WBS2 INNER JOIN EM ON p.ProjMgr = EM.Employee WHERE p.Status IN ('a', 'i') AND P.ChargeType = 'r' GROUP BY EM.Lastname, LD.WBS1, LD.WBS2, P.Longname ORDER BY EM.Lastname, LD.WBS1
I get these results...
LastnameWBS1WBS2LongnameHELDLABORTBWrittenOffLaborWRITTENOFFLABOR Boulet0001039.000100S.r. 41 & Del Prado Shopping Center/miscellaneous civil engineering18408.6309923.47 Boulet0001039.000102S.r. 41 & Del Prado Shopping Center/rezoning process008790 Boulet0001039.000106S. R. 41 & Del Prado Shopping center / const plan rev for environ planting2200.6800 Boulet0001039.000107S.r. 41 & Del Prado Shopping Center/cpd rezoning9335.4600
Okay, so now, of coarse, I want to change everything. I only want to return rows if there is a value > 0 in either Held Labor or TBWrittenOffLabor. Otherwise, no row return.
Here's what I tried, but it didn't work out because it still returns rows, it just zero's out the values for written off labor.
SELECT EM.Lastname, LD.WBS1, LD.WBS2, P.Longname, SUM(LD.Held) AS HELDLABOR, SUM(LD.TBWRittenOff) as TBWrittenOffLabor, SUM(CASE WHEN LD.HELD > '0' THEN LD.WrittenOff ELSE '0' END) AS WRITTENOFFLABOR FROM PR P INNER JOIN (SELECT WBS1, WBS2, SUM(CASE WHEN BillStatus = 'h' THEN Billext ELSE 0 END) AS Held, SUM(CASE WHEN BillStatus = 'w' THEN Billext ELSE 0 END) AS TBWrittenOff, SUM(CASE WHEN BillStatus = 'x' THEN Billext ELSE 0 END) AS WrittenOff FROM LD WHERE BillSTatus IN ('x','h', 'w') GROUP BY WBS1, WBS2) LD ON p.WBS1 = ld.wbs1 AND P.WBS2 = LD.WBS2 INNER JOIN EM ON p.ProjMgr = EM.Employee WHERE p.Status IN ('a', 'i') AND P.ChargeType = 'r' GROUP BY EM.Lastname, LD.WBS1, LD.WBS2, P.Longname ORDER BY EM.Lastname, LD.WBS1
I have a search page that allows users to type/select values contined within the entry they're looking for. My SELECT statement returns columns in a table that get compared to the user input. So if someone selected Status (Open) then all of the 'Open' Request entries should populate the search page. How do I phrase the SELECT statement to compare values if the user gives them, but ignore the fields where no data was input when it's searching? So a search where no values were entered would return every Request Entry instead of an error; no entry. Thanks!
Let's say I have a table with 3 fields: an ID field (primary key, set as an id field, etc.), a Name field (nvarchar50), and an Age field (int). I have a form that has three elements: DropDownList1: This drop down list contains 3 choices- "=", ">", and "<". Age: This text box is where someone would enter a number. Button1: This is the form's submit button. I want someone to be able to search the database for entries where the Age is either equal to ("="), greater than (">"), or less than ("<") whatever number they enter into TextBox1. The code-behind is shown below. The part I'm confused about is that if I load this page, the query works the -first- time. Then, if I try to change the parameters in the form and submit it, I get the following error: "The variable name '@Age' has already been declared. Variable names must be unique within a query batch or stored procedure." Any help would be appreciated. Here is what I'm using in my code behind: protected void Button1_Click(object sender, EventArgs e) { System.Text.StringBuilder sb = new System.Text.StringBuilder(); sb.Append("SELECT * FROM People WHERE Age "); switch (DropDownList1.SelectedValue) { case "=": sb.Append("= "); break; case ">": sb.Append("> "); break; case "<": sb.Append("< "); break; } sb.Append("@Age"); SqlDataSource1.SelectCommand = sb.ToString(); SqlDataSource1.SelectParameters.Add("Age", TypeCode.Int32, Age.Text); }
I am building a pretty simple intranet application where we need to be able to track changes to tables. The tracking feature do not need to be very advanced, we just need to see who changed something and what it was. Therefore I decided just to use a trigger based solution, but need some input/advice since my SQL skills is somewhat lacking.
Consider the following (mock-up) schema:
-- My content table CREATE TABLE [Content]( [ContentGuid] [uniqueidentifier] NOT NULL PRIMARY KEY DEFAULT (newid()), [Data] [nvarchar](4000) NOT NULL, [ChangedBy] [nchar](10) NOT NULL, [MaybeNull] [int] NULL )
-- My history table CREATE TABLE [History]( [ChangedTable] [nvarchar](50) NOT NULL, [ReferenceGuid] [uniqueidentifier] NOT NULL, [ChangedBy] [nchar](10) NOT NULL, [ChangedOn] [datetime] NOT NULL DEFAULT (getutcdate()), [IsDelete] [bit] NOT NULL DEFAULT ((0)), [Changes] [xml] NOT NULL ) ON [PRIMARY]
-- My insert/update trigger CREATE TRIGGER [RecordChangeOnInsertUpdate] ON [Content] AFTER INSERT,UPDATE AS BEGIN SET NOCOUNT ON;
DECLARE @Changes xml SET @Changes = (select * from [inserted] for xml raw ('Content'), elements xsinil)
INSERT INTO [History] ([ChangedTable] ,[ReferenceGuid] ,[ChangedBy] ,[ChangedOn] ,[IsDelete] ,[Changes]) SELECT 'Content', ContentGuid, ChangedBy, getutcdate(), 0, @Changes FROM [inserted] END
-- My delete trigger CREATE TRIGGER [RecordChangeOnDelete] ON [Content] AFTER DELETE AS BEGIN SET NOCOUNT ON;
DECLARE @Changes xml SET @Changes = (select * from [deleted] for xml raw ('Content'), elements xsinil)
INSERT INTO [History] ([ChangedTable] ,[ReferenceGuid] ,[ChangedBy] ,[ChangedOn] ,[IsDelete] ,[Changes]) SELECT 'Content', ContentGuid, ChangedBy, getutcdate(), 1, @Changes FROM [deleted] END
I have decided to use the "one history table for all table changes" method. The changes for a row is stored in a xml column which obviously limits the total size of columns in a table being tracked, but that is not a problem in my application. In general I like this set up, since I will be able to change the schema continuously without having to change the triggers, and since the application will probably evolve a lot over the coming months this is pretty important to me.
EDIT: I should add that all the tables I will be tracking have a uniqueidentifier column. This makes it possible to related table rows in the different tables being tracked with their history.
A few concerns with the above SQL:
- Can the inserted/deleted table change between "SET @Changes = (select * ..." and the "INSERT INTO ..." statement, such that the data is not valid? If so, how to work around that?
- If I were to (hypothetically) perform a "UPDATE [Content] SET [Data] = 'something'", not only is my update trigger called once for each row updated, but the XML added to the inserted row in the history table ([History].[Changes]) represent all the rows updated in the batch update. How do I get around this?
I need to sort based on the values sent from the input xml column resultdata
This is an value which i have in my database, sent from front end.
Currently the display is based on database storage level of master data. i will not be able to change the master data ordering. based on selection, i have to sort since i have an logic which stored the data into resultdata column
Current Result IdSalesMonthSeasonDeptDataProductInfoProdDataProductInfoDescription 1Jan Winter RO,HOHeadOffice,RegionalOfficeRD,AI,SA Bus,Ship,PlaneSales for the month of Jan 2May Summer SO,HOHeadOffice,SalesOfficeAI,RD,TR,SA Bus,Ship,Train,PlaneMay sales information 3Sep Rain HO,RO,SOHeadOffice,SalesOffice,RegionalOfficeSA,TR,RD Bus,Ship,TrainSales for the month of Sep in the rain season
Expected Result IdSalesMonthSeasonDeptDataProductInfoProdDataProductInfoDescription 1Jan Winter RO,HORegionalOffice, HeadOfficeRD,AI,SA Bus,Plane,ShipSales for the month of Jan 2May Summer SO,HOSalesOffice, HeadOfficeAI,RD,TR,SA Plane,Bus,Train,ShipMay sales information 3Sep Rain HO,RO,SOHeadOffice,RegionalOffice,SalesOfficeSA,TR,RD Ship,Train,BusSales for the month of Sep in the rain season
I have query which retrieves multiple column vary from 5 to 15 based on input parameter passed.I am using table to map all this column.If column is not retrieved in the dataset(I am not talking abt Null data but column is completely missing) then I want to hide it in my report.
