I am new to SQL server 2005. I run a large query daily against a teradata warehouse and it populates an access database. I am now attempting to run my query in a SQL 2005 database. I get an error on this case statement:
CASE
WHENCAST ( ( B.zip_cd ( format '99999' ) ) AS char ( 5 ) ) = '*****' THEN SUBSTR ( CAST ( ( B.zip_cd ( format '999999999' ) ) AS char ( 9 ) ), 1, 5 )
ELSECAST ( ( B.zip_cd ( format '99999' ) ) AS char ( 5 ) )
ENDAS ZIP_CD
The error is: Msg 102, Level 15, State 1, Line 229
Incorrect syntax near '99999'.
I have a view where I'm using a series of conditions within a CASE statement to determine a numeric shipment status for a given row. In addition, I need to bring back the corresponding status text for that shipment status code.
Previously, I had been duplicating the CASE logic for both columns, like so:
Code Block...beginning of SQL view... shipment_status = CASE [logic for condition 1] THEN 1 WHEN [logic for condition 2] THEN 2 WHEN [logic for condition 3] THEN 3 WHEN [logic for condition 4] THEN 4 ELSE 0 END, shipment_status_text = CASE [logic for condition 1] THEN 'Condition 1 text' WHEN [logic for condition 2] THEN 'Condition 2 text' WHEN [logic for condition 3] THEN 'Condition 3 text' WHEN [logic for condition 4] THEN 'Condition 4 text' ELSE 'Error' END, ...remainder of SQL view...
This works, but the logic for each of the case conditions is rather long. I'd like to move away from this for easier code management, plus I imagine that this isn't the best performance-wise.
This is what I'd like to do:
Code Block ...beginning of SQL view... shipment_status = CASE [logic for condition 1] THEN 1 WHEN [logic for condition 2] THEN 2 WHEN [logic for condition 3] THEN 3 WHEN [logic for condition 4] THEN 4 ELSE 0 END,
shipment_status_text =
CASE shipment_status
WHEN 1 THEN 'Condition 1 text'
WHEN 2 THEN 'Condition 2 text'
WHEN 3 THEN 'Condition 3 text'
WHEN 4 THEN 'Condition 4 text'
ELSE 'Error'
END, ...remainder of SQL view...
This runs as a query, however all of the rows now should "Error" as the value for shipment_status_text.
Is what I'm trying to do even currently possible in T-SQL? If not, do you have any other suggestions for how I can accomplish the same result?
I need some help on how to structure a sql statement. I am creating a membership directory and I need the stored procedure to output the Last Name, First Name (and if married) Spouse First Name. Like this Flinstone, Fred & Wilma All members are in one directory linked by two fields. [Family ID] all the family members have the same family id and then there is a Family position id that shows if they are the Husband, Wife or Kids. I have no problem with this part select (LastName + ',' + FirstName) as Name, [Phone 1] as Phone, [Unit Name] as WD, [Street 1] as Street, SUBSTRING(City,1,3) as City, SUBSTRING(Postal,1,5) as Zipfrom Membership Where [HH Order]=1 Order By LastName ASC Could someone help me on how to display the " & Spouse FirstName " as part of the name field only if there is a spouse [HH Order]=2 for the current [Family ID]????
I need to get multiple values for each row in a database, then do a calculation and insert the calculation and the accountnumber related to the calculation the data, into a different column. I get an error trying it this way...there is no real identifier, it is jsut something that needs to get done per row...any ideas on how I can accomplish this? Declare @NetCommission decimal Declare @AccountNumber varchar(50) Set @NetCommission = (select (CommissionRebate * Quantity) from Account A Join Trades T on A.AccountNumber = T.AccountNumber) Set @AccountNumber = (select A.AccountNumber from cmsAccount A Join Trades T on A.AccountNumber = T.AccountNumber)
i have a complex sql statement and i think that my structure looks good but apparently not because i keep getting the same error, i was wondering if anyone knew how to correct this problem.
SELECT A.*, B.Name, C.SIName, D.IID,
(Select [LastName] , [FirstName] FROM E INNER JOIN F ON E.SID = F.SID , A WHERE F.Emp='Service' AND E.Lead=1 AND E.ID=[A].[D]) AS Service,
(Select [LastName] , [FirstName] FROM E INNER JOIN F ON E.SID = F.SID ,A WHERE F.Emp='Industry' AND E.Lead=1 AND E.ID=[A].[D]) AS Industry
FROM A , B, C WHERE (1=1) AND B.SID = C.SID AND A.ID = B.ID
i always get this error: Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
I have this stored procedure that returns a rowid, distance. It has a latitude, longitude, and range as inputs, it takes the latitude and longitude and computes a distance with every lat/long in a table PL_CustomerGeocode. Once that distance is computed it compares that distance with the range, and then returns the rowid, distance if the distance is <= range. I have the SELECT statement down, but now i just need to enter this information into a seperate table PL_Distance with (rowid, distance) as columns. The sql statement is as follows, and i cant figure out where the rowid part is an the distance part is: DECLARE @DegreesToRadians float SET @DegreesToRadians = Pi()/180 SELECT rowid, Cast(distance As numeric(9,3)) AS distance FROM (SELECT rowid, CASE WHEN @srcLat = geocodeLat And @srcLong = geocodeLong THEN 0.0 WHEN ABS(Arc) > 1 THEN 0.0 ELSE 3963.1 * 2 * asin(Power(Arc, 0.5)) END AS distance FROM (SELECT Power(sin(DLat/2),2) + cos(@srcLat*@DegreesToRadians)*cos(geocodeLat*@DegreesToRadians)*Power(sin(DLong/2),2) AS Arc, rowid,geocodeLat,geocodeLong FROM (SELECT @srcLong*@DegreesToRadians-geocodeLong*@DegreesToRadians AS DLong, @srcLat*@DegreesToRadians-geocodeLat*@DegreesToRadians AS DLat, rowid, geocodeLat, geocodeLong FROM dbo.PL_CustomerGeoCode) AS x) AS y) AS z WHERE distance <= @range
Hi All, My sql is a little rusty, i ve been trying to do few things but still no luck. I m trying to query some data in one column based on certain . Here is my puzzle: I have 7 tables: categories, characteristics, configs, rm_cat, rm_chars, rm_conf and rooms. And here are the details on these tables: - categories: {cat_id, cat_name} - characteristics: {char_id, char} - configs: {conf_id, conf} - rm_cat: {room_id, cat_id} - rm_chars: { room_id, char_id} - rm_conf: {room_id, conf_id} - room: {room_id. room_name}
I m trying to select a "room_name" based on a certain cat_id, char_id and conf_id and i don't know how to do this.
I have what is turning out to be a very complex T-SQL query to build.
I'm porting an App from Access to SQL Server... one of the Access queries used a function made in VBA to return a value.
For the life of me, I can't figure out how to make this work using only SQL Statements.
I'm not even sure how to even ask this. So here I go.. I've really simplified the SQL statement to help out with this. There are initially two tables.
Container and TraceRecord
Container is a table of Cargo Containers (the truck trailers you see on the highways) TraceRecord is a table of location records as the containers move from city to city on the railroad.
The Containers move on fixed routes (ie.. Long Beach to Chicago, Long Beach to New York, etc...).. in the Container table there is a field called Route which records which Route the container is moving on and is related to a table of routes which I'll get to later.
[This is the old Access query.. notice the IIF statement and the function call to "IsOnTime"] SELECT c.ContainerID, c.IngateDate, t.Location, t.Status, t.EventDate, t.EventTime IIf(IsNull(c.IngateDate) Or IsNull(t.Location),"No Ingate Rail Record Captured",IsOnTime(t.Location,t.Status,t.Rail,c.Ro ute,c.IngateDate,t.EventDate, t.EventTime)) AS RailSch, t.Rail FROM c Container LEFT JOIN t TraceRecord ON c.ContainerID = t.UnitNumber
For each route there is a scheduled travel plan.
Example.. when a container is taken to the BNSF railroad in Long Beach that is called an "Ingate" and is considered Day 0 (zero). As the container moves on the railroad from Long Beach to Chicago, it will pass through other cities, and the TraceRecord table will record where the container is and what time and day.
What I need to do is determine, based on the latest TraceRecord record, by how many hours is the Container "on time".
There is a routing table which lists the predefined travel path for each route.. listing the number of days and hours a container should be a certain place since the day the container was taken into the railroad at the origin "Ingate".
What the IsOnTime function did was take the arguments and do some math with the routing tables and find out how many hours a unit is or is not on time.
Here's a copy of the function from Access 97 using DAO. I don't know if any of this is going to make sense to anyone.. but I'm stuck and don't know what else to do.
Function IsOnTime(strLocation As String, strStatus As String, strRail As String, intRoute As Integer, _ dateIngateDate As Date, dateCurrentDate As Date, dateCurrentTime As Date) As Variant On Error GoTo errorh: Dim rs As Recordset 'Rail Schedule Recordset Dim db As Database 'Current Database Dim sqlFind As String 'Search String to find city transit time Dim dateScheduleDateTime As Date Dim dateDifference As Integer 'Hours difference between trace and schedule Dim varvar As Variant
If dateIngateDate = Null Then IsOnTime = -9999 Else Set db = CurrentDb() Set rs = db.OpenRecordset("tTransitTimeTable", dbOpenSnapshot)
There are 3 tables, VendorLists, Vendors, and Referrals.
VendorLists is a linking table. It has VendorListID, VendorID, and ListID fields.
Vendors is linked to VendorLists through the VendorID field (one to many)
Referrals is linked to VendorLists through VendorListID (one to many)
I'm given a value for ListID and have to pull records from both the Vendors and Referrals table (a referral is a description of a vendor, one to many).
I am able to do this with the following SQL select statement:
SELECT Referrals.Description, Vendors.Company FROM Referrals CROSS JOIN Vendors WHERE Referrals.VendorListID IN (SELECT VendorListID FROM VendorLists WHERE (ListID = lid)) AND (Vendors.VendorID IN (SELECT VendorID FROM VendorLists WHERE ListID = lid)) ORDER BY Vendors.VendorID
This pulls all the appropriate records and values that i need and orders them by the identifier for the vendor. However, I want to randomly order the vendors but still group them together by company, so, if the VendorID is 1 for "joe's crab shack" and 2 for "billy's ice cream shop", the above will always list joe's crab shack first and all it's referrals. i want to be able to randomly order the vendors, but still keep the referrals of those vendors grouped together so that when i iterate over them, they're grouped.
Does anyone have any idea how to do this? I'm stumped!!
SELECT * FROM relations INNER JOIN paths ON relations.path = paths.path_id WHERE (paths.links = '161') AND (relations.node1 = 162) OR (paths.links = '161') AND (relations.node2 = 162) OR (paths.links = '162') AND (relations.node1 = 161) OR (paths.links = '162') AND (relations.node2 = 161) OR (paths.links LIKE '162%') AND (relations.node1 = 161) OR (paths.links LIKE '%162') AND (relations.node2 = 161) OR (paths.links LIKE '161%') AND (relations.node1 = 162) OR (paths.links LIKE '%161') AND (relations.node2 = 162) OR (paths.links LIKE '%161;162%') OR (paths.links LIKE '%162;161%') ORDER BY relations.node1
Don't pay attention to the 161 and 162 things, is just test data, now my problem is that I want to transform that into a DELETE statement, but I can't find the right way to do it, so far I managed to do something like:
PHP Code:
DELETE relations FROM relations INNER JOIN paths ON relations.path = paths.path_id WHERE (paths.links = '161') AND (relations.node1 = 162) OR (paths.links = '161') AND (relations.node2 = 162) OR (paths.links = '162') AND (relations.node1 = 161) OR (paths.links = '162') AND (relations.node2 = 161) OR (paths.links LIKE '162%') AND (relations.node1 = 161) OR (paths.links LIKE '%162') AND (relations.node2 = 161) OR (paths.links LIKE '161%') AND (relations.node1 = 162) OR (paths.links LIKE '%161') AND (relations.node2 = 162) OR (paths.links LIKE '%161;162%') OR (paths.links LIKE '%162;161%')
But that would delete only from the relations table and not from the paths table. I need to delete from both tables.
Here is my SQL string:"SELECT to_ordnum, to_orddate," _& "(SELECT SUM((DDPROD.pr_stanmat * DDPROD.pr_prfact) *(DOBOM2.b2_quant * DDORD.or_quant)) FROM DDPROD INNER JOIN DOBOM2 ONDDPROD.pr_prodnum = DOBOM2.b2_prodnum INNER JOIN DDORD ONDOBOM2.b2_orid = DDORD.or_id INNER JOIN DDTORD ON DDORD.OR_TOID =DDTORD.TO_ID WHERE DOBOM2.b2_ordnum = ''order number here from resultof outer select) AS Total" _& "FROM DDTORD WHERE to_trak2id IN (39, 40, 41) AND to_ordtype = 's'AND to_status = 'c' GROUP BY to_ordnum, to_orddate ORDER BY to_ordnumDESC"The outter Select statement returns various amounts of order numbersrepresented by 'to_ordnum' in the outer Select clause which has tomeet the critera in the outer WHERE clause. I would like to placethese numbers selected into the inner WHERE clause for the innerselect statement where DOMBOM2.b2_ordnum = ?the order selected byouter select statement.I have tried placing to_ordnum into that location but the SQL2000server does not process it.Any suggestions, ideas?Thank you,Brett
i was tasked to created an UPDATE statement for 6 tables , i would like to update 4 columns within the 6 tables , they all contains the same column names. the table gets its information from the source table, however the data that is transferd to the 6 tables are sometimes incorrect , i need to write a UPDATE statement that will automatically correct the data. the Update statement should also contact a where clause
the columns are [No] , [Salesperson Code], [Country Code] and [Country Name]
i was thinking of doing
Update [tablename] SET [No] = CASE WHEN [No] ='AF01' THEN 'Country Code' = 'ZA7' AND 'Country Name' = 'South Africa' ELSE 'Null' END
Hello friends, I want to use select statement in a CASE inside procedure. can I do it? of yes then how can i do it ?
following part of the procedure clears my requirement.
SELECT E.EmployeeID, CASE E.EmployeeType WHEN 1 THEN select * from Tbl1 WHEN 2 THEN select * from Tbl2 WHEN 3 THEN select * from Tbl3 END FROM EMPLOYEE E
can any one help me in this? please give me a sample query.
I have a pretty complex SQL statement that looks like this: SELECT aspnet_Employers.active, aspnet_Employers.accountexecutiveusername, aspnet_Employers.created, aspnet_Employers.Title AS Contact, SUM(aspnet_Employers.EmployeeCount) AS [# Emps], COUNT(aspnet_Signups.account) AS [# Email Addresses], COUNT(aspnet_ContactMe.username) AS [# Contact Me], COUNT(aspnet_AppsSubmitted.account) AS [# Apply Now]FROM aspnet_Employers LEFT OUTER JOIN aspnet_AppsSubmitted ON aspnet_Employers.UserName = aspnet_AppsSubmitted.account LEFT OUTER JOIN aspnet_ContactMe ON aspnet_Employers.UserName = aspnet_ContactMe.username LEFT OUTER JOIN aspnet_Signups ON aspnet_Employers.UserName = aspnet_Signups.accountGROUP BY aspnet_Employers.accountexecutiveusername, aspnet_Employers.created, aspnet_Employers.Title, aspnet_Employers.active It does work the way i want it, but the problem is, on my Gridview when i change the Employers accounts "Active" status either way, it changes the username field from the username of the account, to "null". Why does it do this? What would i change to prevent this from happening?
I may not be seeing the forest through the trees here but here goes. I've got a table of computer configurations with columns for cpu, ram, m/b, hdd, etc. The values in those columns are related to the id field in another table named parts. The parts table has columns, id, partnumber, description, and cost. What I want to do is be able to pull a record from the computer configurations table and instead of getting the integers in the cpu, ram, etc. fields I want to put the corresponding description field from the parts table. For example:I want this...id Name CPU RAM MB HDD ... 1 Fast Machine Fast CPU Big RAM Greate MB Huge HDDNOT this....id Name CPU RAM MB HDD ... 1 Fast Machine 1 3 2 7 Below is a screenshot of my actual table relationships.Thanks in advance
can somebody explain to me why the below update fails to update one row and updates the entire table?
Code:
UPDATE addlist SET add_s = 1 WHERE EXISTS (SELECT a.add_s, a.email_address, e.public_name FROM add a, edit e WHERE a.email_address = e.email_address and a.add_email = 'mags23@rice.edu' and a.add_s = 0 and e.public_name = 'professor');
hi.I am having probelms with an update statement. every timei run it, "every" row updates, not just the one(s) intended.so, here is what i have. i have tried this with both AND and ORand neither seem to work.i dont know why this is elluding me, but i'd appreciate help with thesolution.thanks.UPDATE addSET add_s = 1WHERE add.add_status = 0 and add.add_email = 'mags23@rice.edu'or add_s in(SELECT a.add_sFROM add a, edit eWHERE a.email_address = e.email_addressand e.public_name = 'professor')
This is difficult to explain in words, but the following code outlines what I am trying to do:
with myTableWithRowNum as ( select 'row' = row_number() over (order by insertdate desc), myValue from ( select table1Id As myValue from myTable1 union select table2Id As myValue from myTable2 ) )
select * from myTableWithRowNum Can anyone think of a work around so that I can use the Row_Number function where the data is coming from a union?
Hi All, I've looked through the forum hoping I'm not the only one with this issue but alas, I have found nothing so I'm hoping someone out there will give me some assistance. My problem is the case statement in my Insert Statement. My overall goal is to insert records from one table to another. But I need to be able to assign a specific value to the incoming data and thought the case statement would be the best way of doing it. I must be doing something wrong but I can't seem to see it.
Here is my code: Insert into myTblA (TblA_ID, mycasefield = case when mycasefield = 1 then 99861 when mycasefield = 2 then 99862 when mycasefield = 3 then 99863 when mycasefield = 4 then 99864 when mycasefield = 5 then 99865 when mycasefield = 6 then 99866 when mycasefield = 7 then 99867 when mycasefield = 8 then 99868 when mycasefield = 9 then 99855 when mycasefield = 10 then 99839 end, alt_min, alt_max, longitude, latitude ( Select MTB.LocationID MTB.model_ID MTB.elevation, --alt min null, --alt max MTB.longitude, --longitude MTB.latitude --latitude from MyTblB MTB );
The error I'm getting is: Incorrect syntax near '='.
I have tried various versions of the case statement based on examples I have found but nothing works. I would greatly appreciate any assistance with this one. I've been smacking my head against the wall for awhile trying to find a solution.
material ========= material_id project_type project_id qty 1 AB Corporate 1 3 2 Other Project 2 7
i have taken AB Corporate for AB_Corporate_project ,Other Project for Other_project
sample query i write :--
select m.material_id ,m.project_type,m.project_id,m.qty,ab.ab_crp_id, ab.custname ,op.other_proj_id,op.other_custname,op. po case if m.project_type = 'AB Corporate' then select * from AB_Corporate_project where ab.ab_crp_id = m.project_id else if m.project_type = 'Other Project' then select * from Other_project where op.other_proj_id=m.project_id end from material m,AB_Corporate_project ab,Other_project op
but this query not work,also it gives errors
i want sql query to show data as follows
material_id project_type project_id custname other_custname qty 1 AB Corporate 1 abc -- 3 2 Other Project 2 -- dsd 7
so plz help me how can i write sql query for to show the output plz send a sql query
I am trying to use a case statement in one of my stored proc but I am stuck a little bit.Here is a example, something like:declare @id int set @id =1case @id When 1 then select * from contactsend case but this keeps on giving me error: Incorrect syntax near the keyword 'case'. Any help is appreciated!
Hi I have some question regarding the sql case statment.Can i use the case statement with the where clause.Example: SELECT FirstName, IDFROM myTablewhere case when ID= '123' then id = '123' and id='124' endorder by idBut the above code does not work.
Hi all, I was wondering if there is any way in an sql statement to check whether the data your trying to get out of the DB is of a particular type, ie. Int, char etc. I was thinking about a case statement such as <code> CASE WHEN (MyNum <> INT) then 0 end AS MyNum </code>
This has to be included in the sql statement cause I need this field to get other data. Any thoughts on how to achieve this would be greatly appreciated.
If I’m in the wrong thread section please advise of best one to get help in.
Hi !!!i hope one of the sql specialists answer me about the best and most effeceint way to acheive what i am looking for Scenario:-------------i have a 3 tables related to each other Addresses, Groups and GroupAddressthe relation is for both addresses and groups is one to many in the GroupAddress.the behaviour in the application : user can add addresses to his address list and from the address list a user can add an address to many groups like if you have Group name "Freinds" and you add me in it and you have Football team group and you add me to it like that !!!not i have another function called "copy group"in the GroupAddress i have this data as example GroupID AddressID1 41 61 21 441 72 82 62 93 133 73 10and the group ID called "Freinds"i want to copy the group so i can have another group that has the same addresses by one click rather than collectiong them again one by one ...by the way the new copy will have a new group name ( as this is thebusiness logic so user can not have dupicate group name )so what is the best SQL statement that i need to copy the group ???i hope that clear enough!
I am trying determine if I can do something like the code below. I have done a left join on a table. In the select statement there are three possible values. Yes, No, or NULL. I could like to use a Case statement to determine if there is Null. If so, then output N/A in place of the Null. So then my possible valus are Yes, No, and N/A.
Any clues?
Thanks, John
SELECT TOP 100 OfferDressRoomYN.yesno as OfferDressRoom = CASE WHEN offerDressRoomYN.yesno IS NULL THEN 'N/A' END, FROM dataquestionnaire dq LEFT OUTER JOIN yesno OfferDressRoomYN ON dq.c3_1 = OfferDressRoomYN.yesnoid
In my query below i have the results ,The thing to observe in the result set it for the name "Acevedo" , "Abeyta" its not doing a group by and populating the results in the following column.Rather its addind a new row and adding it as 1 in the next row. I have to populate the counts in one row for common names.Shall i use a if condition within a case block.If yes how?any other work arounds would be appriciated. Please help Thanks
select isnull(replace(Ltrim(Rtrim(P.Lastname)),',',''),'' ) Lastname , case ProductID WHEN 22 then count(S.Product) Else 0 END AS Builders , case ProductID WHEN 23 then count(S.Product) Else 0 END AS Associates , case ProductID WHEN 24 then count(S.Product) Else 0 END AS Affiliates FROM vwpersons p with (nolock) join vwSubscriptions S with (nolock) on S.RecipientID = P.ID where P.Lastname in (select Ltrim(Rtrim(H.name)) from externaldata.dbo.Hispanicnames H) group by P.Lastname, S.ProductID having count(P.LastName)>=1 order by 1
I am trying to get avg score by site, by call type. Columns are Site(varchar), Calltype(varchar), totalscore(float). Calltypes are A, B, C, D. Sites are 1, 2, 3, 4. I can do a straight average statement and only get one calltype. I want to do a CASE statement to get all average scores for all calltypes.
Select Site, avg(totalscore) as [Avg Score] FROM DB WHERE calltype = 'A' GROUP BY Site
Results
Site Avg Score (for A) 1 85 2 75.5 3 85.33
SELECT Site, AVG(CASE WHEN TotalScore > 0 AND CallType = 'A' THEN Totalscore ELSE 0 END) AS [Avg Score For A] FROM DB GROUP BY Site
Results
Site Avg Score For A 1 i get 8.5 2 i get 37.75 3 i get 36.57 Why am I getting a difference? Any help is greatly appreciated - thank you
Hi Ive got a simple query where I want to calculate an average of one number divided by the other ie: avg(x / y)
Im trying to use a case statement to return 0 in the event that y is 0, to avoid a division by zero error. My query is still returning a division by zero error anyway can anybody help?
SELECT CCode, CASE WHEN BS_TOTAL_ASSETS = 0 THEN 0 ELSE AVG(BSCLTradeCreditors / BS_TOTAL_ASSETS) END AS myaverage FROM [Company/Year] GROUP BY CCode, BS_TOTAL_ASSETS
