This is probably the wrong category to post my question in. Apologies if it is.
I have got a Data Flow Task that has an OLE DB Source and a Flat file destination.
Is there a way to do the following:
SELECT id,'31809','C:LCImportDataImages' & id & '.jpg', '1' FROM table1
This obviously doesn't work but I want to insert the id from the table, followed by the value '31809', followed by 'C:LCImportDataImagesid.jpg', followed by '1'
So, the id in 'C:LCImportDataImagesid.jpg' is the same as the id from the table.
I have a sql select query that I'm pulling from a "Years" table to link to 3 columns in an Items table.ZCValuesYear table has two colums: YearID and YearYearID Year1 20042 20053 20064 20075 2008...I want to bind the "Year" value to the three colums in the ZCItem table: ItemUseFirstYearID ItemUseLastYearID ItemYearIDThe query below will pull all the "ID's" for each of the colums, but how would I make it pull the "Year" value (instead of record 4, it would pull 2007 instead)?<asp:SqlDataSource ID="sqlItemSelect" runat="server" ConnectionString="<%$ ConnectionStrings:MyConnString %>" SelectCommand="SELECT ZCPartVault.PartVaultID, ZCPartVault.PartVaultItemID, ZCValuesYear.Year, ZCItem.ItemName, ZCItem.ItemUseFirstYearID, ZCItem.ItemUseLastYearID FROM ZCPartVault FULL OUTER JOIN ZCItem ON ZCPartVault.PartVaultItemID = ZCItem.ItemID FULL OUTER JOIN ZCValuesYear ON ZCItem.ItemUseLastYearID = ZCValuesYear.YearID AND ZCItem.ItemUseFirstYearID = ZCValuesYear.YearID AND ZCItem.ItemYearID = ZCValuesYear.YearID" > </asp:SqlDataSource>
Hi, I have a question regarding how to insert one column values into a table by selecting from different table..Here is the syntax.. ------------ insert into propertytable values (select lastvalue+incrementby from agilesequences where name='SEQPROPERTYTABLE', 13926, 0, 4, 1, 451, 1, 8, 1)
the first column in the propertytable will be... select lastvalue+incrementby from agilesequences where name='SEQPROPERTYTABLE' How do I do that..Help PLZ..
I am mapping an entity from SQL 2005 to another entity in another system on SQL 2000. Since the destination system has its own ID generator, I want to keep the generated ID for each row of my table in a column of my table in SQL 2005. The new column is in the dataset now , but I don't know how to update my table to have that column values (The OleDbDestination just insert new items.)
I am SSRS user, We have a .net UI from where we want to pass multi select values, but these values are comma separated in the database. how can I write a sql query such that when I select multi values on my UI, the comma separated values are take care of.
We have three fields in a table: firstname, Surname & Organisation. Firstname & surname will always be filled but in most cases organisation is NULL.
Part of what these fields will be used for is a mailshot. If there is no organisation data then the mailshot will open with 'Dear Firstname Surname, ' but if the organisation is present they would like 'Dear Organisation'.
Is it possible to create a select state that checks the organisation field, and if a value is present return that value else return the firstname, surname combination? I have tried various things but I cannot get it to work.
I know the ideal situation would be to do this type of condition check at scripting level (PHP, ASP, Visual Basic) but my bosses would like to try it at SQL level.
If I have a Select statement like this in my C# code: Select * From foods Where foodgroup In (@foodgroup) And I want @foodgroup to have these values ... "meat", "dairy", fruit", what is the correct way to add the parameter? I tried meat, dairy, fruit 'meat', 'dairy', 'fruit' but neither worked. Is this possible?
I have to write a master detail query, in which the detail record shouldbe stored in a variable as Comma Seperated Values. Can anyone helpme......Sun*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
I have a stored procedure that allows users to select addresses based on partially supplied information from the user. My procedure seems to work fine in all but a few cases. If a user decides to select all the rows for a particular country the procedure below does not return any rows even if the rows exist. I tracked this down to the fact that for Non US countries I set both the StateCode and Country Code to nulls. . Below is a partial version of my code. Can anyone show me how I can do a "Like" Search even if some of the fields in the row contain null values? Thanks 1 CREATE PROCEDURE dbo.Addresses_Like( @SendTo VarChar(50) = Null 2 , @AddressLine1 VarChar(50) = Null 3 , @AddressLine2 VarChar(50) = Null 4 , @City VarChar(50) = Null 5 , @StateCode VarChar(2) = Null 6 , @ZipCode VarChar(10) = Null 7 , @CountryCode VarChar(2) = Null) 8 9 10 Declare @SearchSendTo VarChar(50) 11 Declare @SearchAddressLine1 VarChar(50) 12 Declare @SearchAddressLine2 VarChar(50) 13 Declare @SearchCity VarChar(50) 14 Declare @SearchStateCode VarChar(2) 15 Declare @SearchZipCode VarChar(10) 16 Declare @SearchCountryCode VarChar(2) 17 18 If (@SendTo Is Null) 19 Set @SearchSendTo = "" 20 Else 21 Set @SearchSendTo = @SendTo 22 23 If (@AddressLine1 Is Null) 24 Set @SearchAddressLine1 = "" 25 Else 26 Set @SearchAddressLine1 = @AddressLine1 27 28 If (@AddressLine2 Is Null) 29 Set @SearchAddressLine2 = "" 30 Else 31 Set @SearchAddressLine2 = @AddressLine2 32 33 If (@City Is Null) 34 Set @SearchCity = "" 35 Else 36 Set @SearchCity = @City 37 38 If (@StateCode Is Null) 39 Set @SearchStateCode = "" 40 Else 41 Set @SearchStateCode = @StateCode 42 43 If (@ZipCode Is Null) 44 Set @SearchZipCode = "" 45 Else 46 Set @SearchZipCode = @ZipCode 47 48 If (@CountryCode Is Null) 49 Set @SearchCountryCode = "" 50 Else 51 Set @SearchCountryCode = @CountryCode 52 53 54 Select AddressID 55 , SendTo 56 , AddressLine1 57 , AddressLine2 58 , City 59 , StateCode 60 , ZipCode 61 , CountryCode 62 , RowVersion 63 , LastChangedDateTime 64 , OperID 65 From Addresses 66 Where SendTo Like RTrim(LTrim(@SearchSendTo)) + "%" 67 And AddressLine1 Like RTrim(LTrim(@SearchAddressLine1)) + "%" 68 And AddressLine2 Like RTrim(LTrim(@SearchAddressLine2)) + "%" 69 And City Like RTrim(LTrim(@SearchCity)) + "%" 70 And StateCode Like RTrim(LTrim(@SearchStateCode)) + "%" 71 And ZipCode Like RTrim(LTrim(@SearchZipCode)) + "%" 72 And CountryCode Like RTrim(LTrim(@SearchCountryCode)) + "%" 73 Order By CountryCode, City, AddressLine1, AddressLine2, SendTo
I want to check if the tbaddress.address1 tbaddress_address1 if it is =null or empty then make it = tbaddress.address2 tbaddress_address2, at the same time I make the tbaddress.address2 tbaddress_address2, null. Otherwise leave it as it is.
Actually, I am working with the report and the tbaddress.address1 carrying the apt number. if there is no aprt # then the line print blanks. so that brings the empty line between the street address and the citystate line. That is what i am tryig to cover. There could be a way to do this from the crystal report but I am thinking of doing this way cause I can't get the solution to do it from the crystal report.
Select a.uid , a.name , mewp.data As Association from Asset a Join wshhistory wsh on a.uid = wsh.assetid Join worksheet w on wsh.wshid = w.uid left join
[code]...
When I return the data how do I eliminate the name's that are in bold but keep the association record as this is a relationship between joining tables..
Hi,I have a message system, where people insert their message to the database, and others pick up their messages by selecting messages that have their username as the reciever ID.I have just intoduced bulk message sending - sending to multiple users. This requires many usernames being entered into the recievername column, seperated by a comma.Is there a way to select messages if the column name contains their username, rather than is their username? Because there may be more than one name, I would like everyone that has their name in the column to be able to retrieve the message, even if they arent the only reciever..Please ask if more explanation is needed!Thanks,Jon
I need to do a SQL query based on the two biggest values of a field. PLS , what s the SQL syntax : select * from Table where PRICE ......Ex: if the values of the field PRICE are : 50, 40, 100, 30 and 150.The request should select the rows that have Price equal 100 or 120Thanks a lot for help
I am editing a pre-existing view.This view is already bringing data from 40+ tables so I am to modify itwithout screwing with anything else that is already in there.I need to (left) join it with a new table that lists deposits and thedates they are due. What I need is to print, for each record in theview, the due date for the next deposit due and the total of allpayments that they will have made by the next due date.So this is how things are. I join the table and it obviously bringsmultiple records for each record (one for each matching one in the newtable). I need, instead, to be able to make out what due date I shouldprint (the first one that is GETDATE()?) and the total of deposits upto that date.Now, payments can be either dollar amounts or percentages of anotheramount in the view. So if it's an amount I add it, if it's a % Icalculate the amount and add it.Example:for group X of clients...Deposit 1 due on oct 1: $20Deposit 2 due on oct 15: $30Deposit 3 due on nov 15: $40Deposit 4 due on nov 30: $50for group Y of clients...Deposit 1 due on Oct 30: $200Deposit 2 due on Nov 30: $300Deposit 3 due on Dec 30: $400So when if I execute the view today (Nov 7th) each client from group Xshould have:Next Due Date: nov 15. Total: $90 (deposit 1 + deposit 2 + deposit 3)Group Y should have:Next Due Date: Nov 30, total: $500 (Deposit 1 + deposit 2)And so on.
Is there any way or option to get the all columns of dataset added to table when we add a table in data region. It will take lot of time to add one by one and also there are chances to add one column ore than once.
Hi, i have something like this: SELECT a.boardname, a.description, a.threadcount, a.answercount, a.lastthreadid, b.username, b.subject, b.created FROM forum_board AS a, forum_topics AS b WHERE (a.forumid = @ID) AND (b.id = a.lastthreadid) The problem here is that lastthreadid does not always have a post linked to it, so sometimes its null. I want it to still return all the boards and then just returnnull values or "" in b.username, b.subject, b.createdThis returns nothing. How can i force it to select these values?
Hi. I have an sql query that i use to insert articles to a sql databse table and for that i use addWithValue to select witch textboxes etc goes where in the database. Now i need to retrive these values and place them back into some other textboxes (some of them multiline) ,and i wonder if there are any similar ways to retrive values like AddWithparameter so i can easily do textBox.text = //whatever goes here ?
I have a table with one row for each test a user has taken, with columns for userid, score, and test date. I have a query that gets the highest score and the date of the latest test for each user. Easy. But how can I also get the score achieved on the latest test for each agent?
Thanks, John
(By the way, I've been looking for a good SQL mailing list to ask this question and have been unsuccessful. If there's a better forum than this for this type of question, please let me know).
I have 2 identical tables one contains current settings, the other contains all historical settings.I could create a union view to display the current values from table A and all historical values from table B, butthat would also require a Variable to hold the tblid for both select statements.
Q. Can this be done with one joined or conditional select statement?
DECLARE @tblid int = 501 SELECT 1,2,3,4,'CurrentSetting' FROM TableA ta WHERE tblid = @tblid UNION SELECT 1,2,3,4,'PreviosSetting' FROM Tableb tb WHERE tblid = @tblid
I have 21 Boolean fields that I would like to calculate a percentage from. If the boolean value is true I would like to be able to return a percentage of Outcomes available from a possible of 21. So If 7 of my boolean fileds are true then i would like to be able to calculate that 33% of Outcomes are present.
Does anyone have anything that will get me started?
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Client]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[Client] GO
I am using SSRS 2008 R2 Report Builder 3.0 (10.50.4276.0) . I have simple set of data which has a persons Title and Name e.g. Mr Smith, Miss Jones, Doctor Foster
The report has a parameter where the user can select which records to show based on the matching titles (Mr, Miss, Doctor)
The Query for the report uses Title in (@Title) where @Title is the only parameter which can take multiple values. The report works correctly for any 1 value selected, but as soon as 2 or more values are ticked in the drop down, it fails.
I believe the parameter value is being passed into the query with a comma separating the values e.g. Mr,Miss which causes the IN statement to give an error, as the statement would be where Title IN ('Mr,Miss') which does not match any of the data values.
The parameter value passed needs to be 'Mr','Miss' for the IN statement to work. What statement do I have to put in the report query to get it to select any of the data rows where the title matches any 1 of the selected values?
Hello all, i have 2 sql tables, and they each contain a column with bigint values. What i want to do is add up the values from both table, and then display it as 1 number. Supposing i have the following table name: DownloadTable fileName | DownloadSize | file1 | 45 file2 | 50 file3 | 20 --------------------------------------------- second table table name: VideoTable fileName | VideoSize | file1 | 40 file2 | 60 file3 | 20 ---------------------------------------- Now, i want this to output 120, wich is the sum of the sum of the values of table 1 and 2 I've already tried the following: SELECT SUM(DownloadTable.DownloadSize) + SUM(VideoTable.VideoSize) FROM DownloadTable, VideoTable However, when i ran this, it gave me a huge number, that was far from accurate from what it was suppose to output. Can anyone help me? Thanks in advance Regards,
Hi guys, I need some help again please. On my page load, I need the following done... I have a label control that must display a certain numeric value. This value must be the sum of an array of values that must be retreived from a table ("Details") with the column name "DealValue" but only where the column "ConsultantID" = txtConsultant.text I understand the select statement, but I don't know how to "run" through the table to add all the values. Can someone please help me with this and how I would construct the MSSql select statement for this. Thanks again
I posed this problem a few days ago, but havent been able to generate the results i need. Suppose my resultset from an sql query gathering totalsales for a given day by a salesrep looks like this:Lastname totalsales orderID-----------------------doe 1403 510doe 500 680 doe 200 701using SUM(Accounts.totalsales) is not adding up the totalsales. What do I need to do to add up the totalsales, and then reassign it to a newfield?netsports
select (sum(ColA) + sum(ColB) + sum(ColC)) / 3 from tableA Where ....
I want to add the sum of the columns and then divide it by a constant. The problem I am having is there are a lot of nulls in the database that I am working on (i.e. one of the values in the ColB is Null, which makes the result of the entire expession null).
How do I get around this. I need to add the values of the columns?
I have a table with 6 columns. which we can call a, b, c, d, e, f. What I want to achieve is to put data in column d and e and then split this result in column f.
The data I want to put in column d and e is already exported and executed from a table called exp_data, which is from a period of november.
So this is what i have so far but is not working:
update split_table set d = select amount from exp_data where period = '1111' and exp_data.account = split_table.b and exp_data.company = split_table.a
The error I get is incorrect syntax near select. Fixed the issue by adding a parenthesis before the select until the end...
Hi! I have one table with thousands of records. Now, I have to add one more column to that table. And this new column will contain concatination of two columns in that same table. Is there any command to do this? My table has two columns as 'file', 'extn', now i want to add 'fullname' column to that table, and it will contain the concatinated value of 'file' and 'extn'. If the 'file' field has one record as "myfile", 'extn' has one record as ".doc", I want to insert "myfile.doc" in the new column 'fullname'. Like this my table has thousands of records. How can i do this? Please help me! Thanks in advance!
i insert the names of the collumns in the design of the sql server and in some of the names it add the brackets. for example [LabsRadiologyBiochemAmylase>125mMolPLitre]. i try to remove them but when i save it, it write them again. can someone explain me how to remove the brackets???
I have a line chart which works great except when the x axis displays duplicate months. I have tried format codes of MM-YYYY and MMM and have the "numeric or time-scale values" checkbox checked. The data displays fine but the X axis in the case of the MMM format code displays as:
Sep Oct Oct Nov Nov
leaving the user to wonder where exactly does October and November start...
I've googled around but don't see off-hand what I am doing wrong. I am display 3 simultaneous lines if that matters...
I am relatively new to SQL and as a project I have been asked to create the SQL for a simple database to record train details. I want to implement a check constraint which will prevent data from being inserted into a table if the weight of the train is more than the maximum towing weight of the locomotive. FOr instance, I need to add the unladen weight and maximum capacity of each wagon (located in the wagon type table) and compare it against the locomotive maximum pulling weight (the locomotive class table). I have the following SQL but it will not work:
check((select SUM(fwt.unladen_weight+fwt.maximum_payload) from hauls as h,freight_wagon as fw,freight_wagon_type as fwt,train as t where h.freight_wagon_serial_number = fw.freight_wagon_serial_number and fw.freight_wagon_type = fwt.freight_wagon_type and h.train_number = t.train_number) < (select lc.maximum_towing_weight from locomotive_class as lc,locomotive as l,train as t where lc.locomotive_class = l.locomotive_class and l.locomotive_serial_number = t.locomotive_serial_number))
The hauls table is where the constraint has been placed and is the intermediary table between train and freight wagon.
I may not have explained this very well; but in short, i need to compare the sum of two values in one table against a values located in another table...At present I keep getting a message telling me the sub query cannot return more than one row.
I am building a website in asp.net 1.1 with vb.net 2003 which will have the standings of the teams in our baseball league. Below is the database table I have created.
ID(int) home_team (nvarchar) away_team(nvarchar) win_teampf(nvarchar) lose_teampf(nvarchar) 1 Elmwood Murdock 7 22 Louisville Manley 4 33 Manley Elmwood 9 8 ID is the primary key. What I am attempting to do is add each instance of Elmwood from the win column to output the total number of wins from Elmwood and do the same for Elmwood in the losing team to output the total number of losses. The result will look something like this: Elmwood: 1 Win 1 Loss. .500 Thanks for your reply.
