Hey guys... i cant figure this out for the life of me. I have a long T-sql query, and when i enter the string "Rental" into the Listingtype, it says invalid column name "Rental" ... im not looking for the value to be a column, im looking for it to match the value in the ListingType column... here's the query:
IF @Studio IS NOT NULL
SET @SQL = @SQL + ' AND r.StudioFlag = ' + CONVERT(VARCHAR(20), @Studio)
IF @Br1 IS NOT NULL
SET @SQL = @SQL + ' AND r.BRFlag1 = ' + CONVERT(VARCHAR(20), @Br1)
IF @Br2 IS NOT NULL
SET @SQL = @SQL + ' AND r.BRFlag2 = ' + CONVERT(VARCHAR(20), @Br2)
IF @Br3 IS NOT NULL
SET @SQL = @SQL + ' AND r.BRFlag3 = ' + CONVERT(VARCHAR(20), @Br3)
IF @Br4 IS NOT NULL
SET @SQL = @SQL + ' AND r.BRFlag4 = ' + CONVERT(VARCHAR(20), @Br4)
IF @OverBr4 IS NOT NULL
SET @SQL = @SQL + ' AND r.OverBRFlag4 = ' + CONVERT(VARCHAR(20), @OverBr4)
IF @Condo IS NOT NULL
SET @SQL = @SQL + ' AND r.CondoFlag = ' + CONVERT(VARCHAR(20), @Condo)
IF @ListingType IS NOT NULL
SET @SQL = @SQL + ' AND r.ListingType = ' + CONVERT(char, @ListingType)
IF @WindowAir IS NOT NULL
SET @SQL = @SQL + ' AND a.WindowAir = ' + CONVERT(VARCHAR(20), @WindowAir)
IF @CentralAC IS NOT NULL
SET @SQL = @SQL + ' AND a.CentralAir = ' + CONVERT(VARCHAR(20), @CentralAC)
IF @BalconyDeckPatio IS NOT NULL
SET @SQL = @SQL + ' AND a.BalconyDeckPatio = ' + CONVERT(VARCHAR(20), @BalconyDeckPatio)
IF @UseOfYard IS NOT NULL
SET @SQL = @SQL + ' AND a.UseOfYard = ' + CONVERT(VARCHAR(20), @UseOfYard)
IF @Dishwasher IS NOT NULL
SET @SQL = @SQL + ' AND a.Dishwasher = ' + CONVERT(VARCHAR(20), @Dishwasher)
IF @WasherDryer IS NOT NULL
SET @SQL = @SQL + ' AND a.WasherDryer = ' + CONVERT(VARCHAR(20), @WasherDryer)
IF @Fireplace IS NOT NULL
SET @SQL = @SQL + ' AND a.Fireplace = ' + CONVERT(VARCHAR(20), @Fireplace)
IF @EIK IS NOT NULL
SET @SQL = @SQL + ' AND a.EIK = ' + CONVERT(VARCHAR(20), @EIK)
IF @HardwoodFloors IS NOT NULL
SET @SQL = @SQL + ' AND a.HardwoodFloors = ' + CONVERT(VARCHAR(20), @HardwoodFloors)
IF @BroadBandNet IS NOT NULL
SET @SQL = @SQL + ' AND a.BroadbandNet = ' + CONVERT(VARCHAR(20), @BroadbandNet)
IF @TV IS NOT NULL
SET @SQL = @SQL + ' AND a.TV = ' + CONVERT(VARCHAR(20), @TV)
IF @Thermostat IS NOT NULL
SET @SQL = @SQL + ' AND a.Thermostat = ' + CONVERT(VARCHAR(20), @Thermostat)
IF @LandlordNotPresent IS NOT NULL
SET @SQL = @SQL + ' AND a.LandLordNotPresent = ' + CONVERT(VARCHAR(20), @LandLordNotPresent)
IF @Smoking IS NOT NULL
SET @SQL = @SQL + ' AND a.Smoking = ' + CONVERT(VARCHAR(20), @Smoking)
IF @NoPetsAllowed IS NOT NULL
SET @SQL = @SQL + ' AND a.NoPetsAllowed = ' + CONVERT(VARCHAR(20), @NoPetsAllowed)
IF @Cat IS NOT NULL
SET @SQL = @SQL + ' AND a.Cat = ' + CONVERT(VARCHAR(20), @Cat)
IF @MoreCats IS NOT NULL
SET @SQL = @SQL + ' AND a.MoreCats = ' + CONVERT(VARCHAR(20), @MoreCats)
IF @SmallDog IS NOT NULL
SET @SQL = @SQL + ' AND a.SmallDog = ' + CONVERT(VARCHAR(20), @SmallDog)
IF @LargeDogs IS NOT NULL
SET @SQL = @SQL + ' AND a.LargeDogs = ' + CONVERT(VARCHAR(20), @LargeDogs)
IF @Doorperson IS NOT NULL
SET @SQL = @SQL + ' AND a.Doorperson = ' + CONVERT(VARCHAR(20), @Doorperson)
IF @IngroundPool IS NOT NULL
SET @SQL = @SQL + ' AND a.IngroundPool = ' + CONVERT(VARCHAR(20), @IngroundPool)
IF @AboveGroundPool IS NOT NULL
SET @SQL = @SQL + ' AND a.AboveGroundPool = ' + CONVERT(VARCHAR(20), @AboveGroundPool)
IF @Elevator IS NOT NULL
SET @SQL = @SQL + ' AND a.Elevator = ' + CONVERT(VARCHAR(20), @Elevator)
IF @UseOfGarage IS NOT NULL
SET @SQL = @SQL + ' AND a.UseOfGarage = ' + CONVERT(VARCHAR(20), @UseOfGarage)
IF @LaundryFacilities IS NOT NULL
SET @SQL = @SQL + ' AND a.LaundryFacilities = ' + CONVERT(VARCHAR(20), @LaundryFacilities)
IF @HealthCenter IS NOT NULL
SET @SQL = @SQL + ' AND a.Health Center = ' + CONVERT(VARCHAR(20), @HealthCenter)
IF @StorageAreas IS NOT NULL
SET @SQL = @SQL + ' AND a.StorageAreas = ' + CONVERT(VARCHAR(20), @StorageAreas)
IF @WheelchairAccess IS NOT NULL
SET @SQL = @SQL + ' AND a.WheelchairAccess = ' + CONVERT(VARCHAR(20), @WheelchairAccess)
IF @BusinessCenters IS NOT NULL
SET @SQL = @SQL + ' AND a.BusinessCenters = ' + CONVERT(VARCHAR(20), @BusinessCenters)
IF @RentChargeMin IS NOT NULL AND @RentChargeMAX IS NOT NULL
SET @SQL = @SQL + ' AND a.RentCharge BETWEEN ' + CONVERT(VARCHAR(20), @RentChargeMin) + ' AND ' + CONVERT(VARCHAR(20), @RentChargeMax)
IF @RentChargeMin IS NOT NULL AND @RentChargeMAX IS NULL
SET @SQL = @SQL + ' AND a.RentCharge >= ' + CONVERT(VARCHAR(20), @RentChargeMin)
IF @RentChargeMAX IS NULL AND @RentChargeMAX IS NOT NULL
SET @SQL = @SQL + ' AND a.RentCharge <= ' + CONVERT(VARCHAR(20), @RentChargeMax)
Hi all. Just a quick question for you guys...I did a search on Select @@identity and I'm not sure I understand it. The program I'm migrating over (from sql server to oracle) has "Select @@identity" at the end of some insert statements. I'm just tryin to figure out what this does and what the Oracle equivlant would be? i have read posts(or in this case a blog) like this http://weblog.anthonyeden.com/archives/000054.html that states it gives you the value of the PK.
I've also read this http://www.kamath.com/tutorials/tut007_identity.asp stating select @@identity is handy to use as a "connection specific global variable." This all makes sense, however in the code I am examining select @@identity is not assigned to any variable. Basically, my question is if select @@ identity is not assigned to a variable in your code, what use does putting it at the end of a select statement serve? thank you
I have to implement some logic in a view and I don't know how to do it. Let me outline the logic:
- I'm working with a view where records represent participant activities. - Some participants can have numerous activities. - Each activity has a start date and an activity code. - Only records with activity code 23 or 33 appear in the view currently.
What I need to do is NOT include records where the participant has another activity with code 26 (from another table, the one the current view is derived from) which has the same start date as the activity 23/33 record in the view currently. Also, this should only be implemented if the start date is the first start date (minimum start date for that participant).
I've noticed that there are a lot of questions on this forum, but not as many thank you's. I'm far from a DBA (more of a simple, small town, one stop shop), but have learned tons of information from reading your posts. I certainly appreciate everyone's willingness and excitment to contribute to the SQL community. So here's to you...thank you!
What am i doing wrong with this im tired and for the life of me it wont work Cheers Phil
if (Select sum(Unitprice*qtysold) from #DataExport_tmp where recordtype = 'L'group by transref )<> select Totalcost from #DataExport_tmp where recordtype = 'T' BEGIN PRINT 'no good' end
i use windows vista and installed visual net 2005 and sqlserver 2005 but i have some proplem: my step: 1.open vs net 2005 2.make one project 3.make one button 4.get data->datagridview 5.choose add data source 6.choose new data---> this step then choose microsoft sql server 7.typing servername 8.choose attach a database file and choose browe.. 9.link to folder have database(.dbf) but i have worked them very much but not run for me i dont know why it is like that
"login faild"or you dont have permission to pen this filecontact the file ower or an administrator to obtain permissio
pls help me i very need for learning
my Y!M: phok28a@yahoo.com pls contact me and help me
this query works, i want to add a 4th column that is the value of the 3rd column subracted from the value of the 2nd column, how can i add this?? SELECT `tagid` AS w1, (SELECT count( `value` )FROM tagsWHERE `value` =1AND `tagid` = w1) AS w2, (SELECT count( `value` )FROM tagsWHERE `value` =0AND `tagid` = w1) AS w3FROM tagsWHERE `value` > -1GROUP BY `tagid`ORDER BY w2 DESC
I know thsi is a silly question but gonna ask it anyway :)
When you are using the 'insert' statement to insert records in to a database are all of the fields in the db table required for a successfull adding of a record.
Just that i have 16 fields in my db table and want to insert only 11 fields in to the new record and it is giving me an error, and i am sure my SQL is correct
I just need something verified.. This has to do with IP's and names. We have a sql failover cluster. So is that 4 names.. One for each local machine, one for the cluster itself and one for the sql portion? I guess my question is can the cluster group and sql have the same name and IP or do they need to be different.
Does any one know a quicker way to get data in from TableA to TableB. Both tables have the same structure(Acct# varchar(9); Type varchar(12); Date1 datetime; Date2 datetime; Date3 datetime; Date4 datetime; and Date5 datetime No Nulls allow in Acct & Type field.
TableA has 5.5 Million Rows and TableB has about 1 million rows , I wrote a Stored Proc using cursor to select from TableA insert into TableB if the Acct# and Type doesn't exists.
I didn't want to use bcp because I only wanted rows(Acct#'s) that exists in TableA that where not in TableB. My procedure is working but at a rate of 100 records per minute. Any suggestions?
I have a client that's insisting on deploying SQL Server 2000 as Windows Authntication mode only (not mixed mode). I've always done mixed mode in the past and I'm just looking for some input here. So, what's everyone think?
try to guess what the result is for this query before you run it:SELECT 123.654, 123d6, 123e4, '123'e4comments welcome, but please do not spoil it for anyone who hasn't tried it yet
For a query like below ... How do i have to select only the latest revisions, if i need to filter last current revisions of each document ... where the revision could be either alphabetical or even numerical ...
Presently I get all revisions with the below query ... Note: csd_revi is the field of CSD table for revisions.
I asked a question over the weekend and got a answer very quick. I'm new to stored procedures and have to do a insert into two tables. I need to insert into the first table (Journal_log). This table has a colunm ID that is Identity is set to yes. After inserting that record I need to retrieve the ID from the Journal_log that I just inserted and insert it into the second table (District_Journal) with data for a new record into the second. Can this be done in one stored procedure or do I have to have two?
Table Individuals IndividualID (PK) Initials First Last
Is there an efficient way to query for a list of tests with the PretestTech,PostTestTech,and Project manager initials? I guess I'm stuck because I want to Join the Test and Individual tables but there are three fields that need to be joined. So this is the only way I can think to make it work:
SELECT TestID ,TestInfo ,(SELECT Individual.Initials FROM Individual WHERE Individual.IndividualID = Test.PretestTech) AS 'PretestTech Initials' ,(SELECT Individual.Initials FROM Individual WHERE Individual.IndividualID = Test.PostTestTech) AS 'PostTestTech Initials' ,(SELECT Individual.Initials FROM Individual WHERE Individual.IndividualID = Test.ProjectManager) AS 'ProjectManager Initials' FROM Test
Is this efficient or should I rethink my Table design?
I want help with a couple of SQL queries.I have two Tables Table A and Table B.Both tables have the same two fields Name and Hobbies.One Name can appear beside multiple hobbies in each table.There are three queries I wish to run.1) Find all the Hobbie and Name combinations in Table B not in Table Aonly for Names that exist in Table A2) Find all Hobbies and Name combinations in Table A not in Table B3) Return all data in Table B that contains a Name that exists in TableARegards,Ciarán
I am trying to run a batchfile or a external process ( exe ) from within a Script Task and read the process status returned by the process. Is there a sample VB script code that does this that you guys can share ? What modules do I have to Import ?
Has anyone encountered this error message [Microsoft][ODBC Driver Managr] Driver does not support this function? This occurs when importing data. The company I work for upgraded to Server 2005 and now what once worked fine now has an error. Any suggestions?
Just a quick reality check. If you have a bunch of files that have a timestamp as part of the filename, the only way to find the newest file is to use a script, correct?
Is there anyway to predict what a customer is likely to buy based on their purchase history?
What I would like to do is something simliar to using a natural prediction join with multiple union selects, but with data supplied from my purchases table.
So I need something like this:
Code SnippetOpenquery([ds], 'SELECT DISTINCT [PurchasedProductID] FROM [Table] WHERE [CompanyName] = 'a')
To work like how this works:
Code Snippet(SELECT (SELECT '1234' AS [ProductID] UNION SELECT '12345' AS [ProductID]) AS [Table])
I am trying to create the following SP, but get an Error stating "Must Declare the scalar variable "@LoanApplicationID" eventhough I think I am declaring it. I am not sure what am I doing incorrect here, but a prompt solution would be appreciated. Thanks.
Hi. I am trying to setup php4 to talk to MSSQL 7, but am having no luck. Both are on a Win2k server. I get the error below. Please can someone tell me what to do, or even a step by step guide to setting up php to connect to a MSSQL7 database. I originally created the database in access 2000 and now want to use MSSQL to use it. This is the error i get:
Warning: MS SQL message: Could not locate entry in sysdatabases for database 'customers'. No entry found with that name. Make sure that the name is entered correctly. (severity 16) in C:Websiteewsiteestdb.php on line 13
Warning: MS SQL: Unable to select database: customers in C:Websiteewsiteestdb.php on line 13 Database unavailable
I was running a server with TempDB in RAM (please don't comment on why that's not a good idea). Well, everything was fine until one of our administrator install teh backup exec agent and restarted the server. Since then the server won't start giving an error the Tempdb coulld not be moved into ram. Well, I started the server using the -f parameter and reset the Tempdb in RAM setting to zero. Howeever, it still won't start and gives the same error about not being able to move Tempdb into RAM. Is my only option to rebuild the master database? This is just a backup server but I'd like to avoid the hassle of rebuilding master and reinitializing the databases.
I'm trying to work out how to extract the information that I need from a set of database tables and can't think of a way of doing it with SQL.
The database forms the basis for a diary/calendar system for part-time employees and has two tables:
- One is called 'Availability' and holds info on each available hour slot in the calendar. The table just has fields 'time' and 'date', where the time is an integer representing an hour and date is datetime. The calendar runs from 07.00 to 23.00 each day, so there could be 16 rows in 'Availability' for one day. If any part of a day is unavailable (i.e. the employee doesn't work then) there will be no corresponding rows in the table.
- The second table is 'Appointments', which holds details of appointments that the employee is booked for. The main fields are 'date', 'time' and 'duration' (integer for minutes). All appointments will cover a time span that is also covered by an available period, but they are not actually linked in any way.
I need an SQL query that will return all available time slots that start at least 60 minutes after any appointments have FINISHED and at least 120 minutes before any appointment STARTS.
Since there is no link between the 'Appointments' table and the 'Availability' table, I can't think of any way of doing this.
Hi,I would like to convert real data type to datetime type. Example:I have a real data type which is: 23,613456 (23 hours and 0,613456). Iwould like to have it in hh:mm:ss format. How to do this? Can I useconvert/cas function?Thanks for helpRgdsMario