I know that the answer to my problem is something that lies right infront my eyes but I got totally stuck. I cant understand why, when I deploy a database application I've made, when the application is run for the second time, the database doesn't have the data I have entered the first time... Is it something I have to tell my application?
It must be something trivial but I just can't put my finger on it.
i'm using query analyzer,
simply trying to use smallmoney as
In my table I have declared as:
payrate smallmoney,
Inserting values into table like this:
'258'
Error recieved is:
Disallowed implicit conversion from data type varchar to data type smallmoney,
What am I doing wrong? Also, ideally i'd like the 258 to be expressed as £258 (english pounds) if anyone can help with that also.
Thanks in advance
Hi all of you,
I've got a .dtproj project along with 10 dtsx packages. Up to here everything is fine, every package have its own source files, sql destinations and so on..
Issue comes when I try create a flat file source connection for the eleven one.
I get this message:
The component has detected potential metadata corruption during validation.
Error at Data Flow Task [Flat File Source [1]]: Failed to find any non-special error column in output "Flat File Source Error Output" (3).
It may not be possible to recover to a valid state using a component-specific editor. Do you want to use the Advanced Editor dialog box for editing this component?
Otherwise if I'm gonna to open a new .dtproj project separately no problem at all, I mean, appears the suitable window, "Connection Manager", "Column", "Error output" on the left and so on..
It's very strange. The rest of the packages, the same situation. Fortunately, all of them are stored on the server.
I don't get the point at all. Could you please tell me what the hell is happening?
Thanks in advance and regards,
Waiting my my 431 study package turning up, any offers on any addional books etc that would help me pass this exam?
Cheers up front.
Hi folks,
This is what I want to do,but is it possible.
I think I have got the hang of queries and stuff like that but here is what I want to do.
I am using the 2008 express freebie from microsoft with Sql server 8 freebie.
Here is waht I want to do. Place a load of buttons on a screen each one with a list in it.
so for example box one will specify all the days of the week,the others all the colors of the rainbow and the others all the month in the years.
So the the boxes will feed through to a tiny little sql server database with three tables...days..colours...months.
I want somebody to be able top come along and then click on a particular day of the week...let's say thursday...then click on a colur.lets say pink...then click on a month,lets say september..
Then a table will appear with that info in it..i.e. thursday,yellow september.
I know I could write a little prog in VB or C++ to do this but I am trying to relate it to a database.
I think the bottom line here is that I am trying to get the user to create the query and get the result of that query out onto a page.
As you can see I am just below novice level and I think the question I am really asking is that is it possible that the person using my program is actually making up a query(based on my criteria)and can get a result,rather than me have top store a query(or stored procedure) that just responds to a click on the box.
If this lot makes any sense what so ever,then could someone kind soul point me it the right direction(or any direction) :)
Thanks
Jimm222
I am looking thru SQL Books, browsing the forum, but still unclear of the best method to perform the following calculation. I am really struggling with this and any helpful hints would be greatly appreciated.
Situation
Table 1 = customer information. Contains customer #, name, address, etc.
Table 2 = default price list. Contains item #, item desc, price
Table 3 = Allowed discounts based on customer types. Hospital gets $1 off, Doctors get 25 cents off, individuals get no discount.
Further discounts are given for multiple purchases. The 1st item is 100%, 2nd item is 75% and 3rd and subsequent items get 50% off purchase that day.
Here is a basic query result set of customer number, price, item ordered, item description and customer type.
Cust Price ItemNo Item Type
1 $5.50 23432 Moisturizer Hospital
2 $5.55 23453 Moisturizer 2 Indvidual
2 $29.00 23243 Lotion Pkg Indvidual
3 $29.00 23243 Lotion Pkg Doctor
3 $21.25 23232 Gloves Doctor
3 $3.00 23235 Drape Doctor
3 $2.00 27821 Lube Doctor
Calculation
Customer 1 ordered 1 item @ 5.50, but because they are a hospital they get a $1.00 discount. They only ordered 1 item so they pay 100% of the discounted price.
(5.50 - 1.00) * 100% = 4.50
Customer 2 ordered 2 items @ 5.55 and 29.00 respectively. They get no discount because they are an individual client, but do qualify for the multiple item discount of 25% for the second item.
29.00 + 5.55 * 75% = $33.16
Customer 3 ordered 4 items @ 29.00, 21.25, 2.00 and 3.00 respectively. They get a 25 cent discount off each item for being a doctor. They also get a multiple item discount of 25% off the second item and and 50% off items 3 and 4.
The math ideally will look like this
(29.00 - .25)*100% + (21.25 - .25)*75% + (3.00-.25)*50% + (2.00 - .25) * 50% = $47
Problem
For customer 2 if someone entered the lower valued item first the math would look like this
5.55 + 29.00 * 75% = $27.30 (loss of revenue)
Possible Solution
The prices need to be sorted in descending order to maximize revenue prior to discounts being taken for a given day.
Should I use a "rank by clause" statement?
Then I need to multiply the first item by 100%, the 2nd by 75% and the remaining items by 50% to get a grand total of money due. Any pointers would be greatly appreciated.
I am not at all sure how to accomplish this.
Hi there...I am having a whole bunch of trouble trying to design a certain query. Ihave two tables (see a representation of them here:http://www.plankmeister.org.uk/tables.html ) one called menu_data and onecalled page_data.menu_data contains a list of defined menus (menu_group) which may be sortedaccording to the menu_group_display_order column.page_data contains a list of page definitions. Each page belongs to amenu_group. All the pages in a given group may be sorted according to thepage_display_order column. (for instance, a query to order all pages with amenu_group equal to 1 in ascending order would produce:CatsDogsFishSquirrels)What I am trying to do is to design a query that will tell me thepage_user_name of all the pages that sit at the 'top' of their menu group,but ordered by the menu_group_display_order column.So basically, it will order the data in menu_data by themenu_group_display_order colum, then comparing menu_data.menu_group topage_data.menu_group, will return the 'first' row after being ordered bypage_display_order, so that in the case of the data given, the return wouldbe:HorsesCatsBirdsRabbitsI've tried UNION, GROUP BY, TOP 1, INNER JOIN, OUTER JOIN, SLIGHTLY TO THELEFT JOIN, ONLY IF ITS A WEDNESDAY JOIN... etc etc... But I just can't getit to work! It either returns 40 rows, or one. I've spent a total of about10 hours trying to design this query, and am losing what precious littlehair I have left at an alarming rate.Hope someone can provide some insight!Plankmeister.
View 3 Replies View RelatedThis is a shameless plug. My new book, SQL PROGRAMMING STYLE is outnow.Joe Celko's SQL Programming Styleby Joe CelkoSave 20% and get Free Shipping if you order by Friday May 6.Place your order now or learn more below_______________________________________If you're looking for a book with a collection of tricks & tips andrules for formatting and writing portable, readable, maintainable SQLcode, you'll find what you need here!Are you an SQL programmer that, like many, came to SQL after learningand writing procedural or object-oriented code? Or have switched jobsto where a different brand of SQL is being used, or maybe even beentold to learn SQL yourself?If even one answer is yes, then you need this book. A "Manual of Style"for the SQL programmer, this book is a collection of heuristics andrules, tips, and tricks that will help you improve SQL programmingstyle and proficiency, and for formatting and writing portable,readable, maintainable SQL code. Based on many years of experienceconsulting in SQL shops, and gathering questions and resolving hisstudents' SQL style issues, Joe Celko can help you become an evenbetter SQL programmer. More specifically, this book will give youexpert advice from a noted SQL guru and award-winning columnist.Table of Contents_______________________________________FeaturesHelps you write Standard SQL without an accent or a dialect that isused in another programming language or a specific flavor of SQL, codethat can be maintained and used by other people.Enables you to give your group a coding standard for internal use, toenable programmers to use a consistent style.Gives you the mental tools to approach a new problem with SQL as yourtool, rather than another programming language-one that someone elsemight not know!Place your order here:http://books.elsevier.com/us//mk/us...r=&community=mkVolume discounts available, contact Join Bytes!
View 1 Replies View RelatedHello everyone. I need some assistance here. I have a web form to get information about user details. The web form has two parts. the first part is user personal information where there is few textboxes, dropdownlist , radiobuttons , checkbox etc. this is where user inputs his personal information. the second part contains the office information where there is few textboxes, dropdownlist , radiobuttons , checkbox etc too. but the second part is automatically populated by data from the database(lets say table1). this happens as soon as user selects an option from dropdownlist in second part(here an option for dropdownlist is name of the office) . there is also a save and cancel button at the end of the form.now what i want here is, whenever i click a save button in my webform,i want the data of first part of my webform (ie user personal information) to get stored in new table(say table2).but i dont want the data of secont part of my webform(ie office information) to get stored in table2.(coz its already stored in table1.thats from whereit got populated earlier).But i want a reference of second part of my web form(ie office info) in table2.(only the reference id of related data from table1.not the whole data)and finally i want to display data from both the tables in one single details view.(both personal and office information)i hope u people are gettin it?i know i have to use foriegn keys.but i dont know how to use in this case.(sql express)and can u plz explain how to implement this in cs file with example including source code.(c#, vwd2005 express, sql express). thanks. jack.
View 5 Replies View RelatedI downloaded MS SQL Server 2005 Express Edition. The MS site said that it was "easy to install, use and manage." However I'm totally confused. I'm not a total neophyte but I can't even seem to launch the program.
I have an MS SQL database powering my website. I view the database on my home computer using MS Access 2000 with an ODBC connection. MS Access 2000 however, does not let me make changes to the structure of the database. I can't add, delete or modify fields in my current tables. I can't add or delete tables. Is MS SQL Server 2005 Express Edition the proper software to perform these functions? If it is, is there a guide to helping me launch the program, connect to my MS SQL database and perform these functions? If this is not the right software, what is?
I would greatly appreciate any guidance anyone can offer.
Thanks!
okay so i have query which sure does run fine if i drop it into a SQL server's console. however i try and put this into ACCESS so the accounting department can use it and it returns no values.
not sure if i should drop in the vb forum or what but im about to build a page in c# and have them get it that way.
Any one have any suggestions
Code:
select Doc.Dat, Doc.DocNo, Doc.IdPers, Pos.ItemNo, Pos.[Description], Pos.Quantity, Pos.SinglePrice, Pos.Discount from Pos, Doc where Doc.Dat like '%2005%' AND IdPers = (select IdPers from Pers where match = 'Aec One Stop Group, Inc.') AND Pos.IdDoc in ( select idDoc from Doc where Type = 8 ) ORDER by Dat, DocNo DESC
I've been parachuted into a place with a few dozen servers, several thousand external users (and scheduled to grow). Right now the whole place - IT and non-IT alike - l logs in with SA.....
(where's the 'Puking' icon??)
They've got Windows logins, but that's about as tight as it gets. Honestly I've never seen anywhere as insecure....and not sure where to start.
I have two table A and B:
A 2000000 Rows 569288KB 8KB index
B 3000000 Rows 853712KB 8KB index
But when do "SELECT COUNT(*) FROM A/B", table B is significantly slower than A:
A 0 secend
B 8 seconds
Does anyone know why? So I can boost the performance to search table B.
Thanks in advance.
Ok I have created a 2005 sql advanced database with text indexing. I have create the database like so
created a new database with text indexing enabled and the following table
create table support (problemId VARCHAR(50) NOT NULL PRIMARY KEY, problemTitle varchar(50) NOT NULL, problemBody text NOT NULL, linkOne varchar(50), linkTwo varchar(50), linkThree varchar(50), linkFour varchar(50), ftid int NOT NULL)
next
create fulltext catalog remoteSupportCatalog
create unique index ui_remotesupport ON support (ftid)
then
create fulltext index on support(problemBody)key index PK__support__7C8480AE on remoteSupportCatalog
--------
I then populated some rows and issues a quesry
Select * from support where freetext(problemBody, 'test database')
it works pulls back all the data I expected it to pull back
In my asp page I created a database connection with the folling select command
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:rsdb2ConnectionString2 %>"
SelectCommand="SELECT * FROM support WHERE FREETEXT(problemBody, @srchBox)">
created the search parameter<SelectParameters>
<asp:ControlParameter ControlID="srchBox" PropertyName="Text" Type="String" Name="srchBox" /> //this is a text box that is searchable with a button
</SelectParameters>
and it doesnt give me back an error or data it does nothing. What am I missing?????
I hate making complecated queries....but this doesn't seem too hard and I cannot figure out where my error is. The message is "Incorrect syntax near the keyword 'FROM'."
The query is:
SELECT tblUsers.lUserID GROUP BY tblUsers.lUserID HAVING Count(tblDLHistory.lDownloadID) = 0 FROM tblUsers LEFT JOIN tblDLHistory ON tblUsers.lUserID = tblDLHistory.lUserID WHERE tblDLHistory.lVersionID = 5
The tables are
tblUsers
_______
lUserID
tblDLHistory
__________
lDownloadID
lUserID
lVersionID
What on earth am I doing wrong?
I need to search the database and pull up all customers who have a 'device' and their email address. I have watered down my select statement, but the following is the basics of it. I just cannot figure out how to also append the email. I have tried many different attempts and have come up dry.
Select a.company
From dbo.contact1 as a,
(
Select DISTINCT accountno
From dbo.contsupp
Where contact LIKE 'Product Inventory' AND contsupref LIKE '%device%'
) as b
Where a.accountno = b.accountno
Below are some sample databases to get an idea of some possibilities. There will not always be an entry in dbo.contsupp for an email address. There will not always be an entry in dbo.contsupp for a device.
------------------------
|dbo.contact1 |
|------------------------|
|accountno | company |
|------------------------|
|5123 | Alpha |
|4158 | Beta |
|2121 | Gamma |
|5555 | Omega |
------------------------
--------------------------------------------
|dbo.contsupp |
|--------------------------------------------|
|accountno | contact | contsupref |
|--------------------------------------------|
|5123 |Product Inventory|device 01 |
|2121 |product note |the note |
|2121 |Product Inventory|device 02 |
|5123 |Email Address |a@b.com |
|4158 |ladkafndkaldkfna |device stopped|
--------------------------------------------
**note: i am using MSSQL 2000
Hello,
I have a small table to manage orders in my company. When a new order is entered, the script makes use of the last row of the table to find out the last order, increments it and creates the new order number. The problem is, a few days ago the last row got stuck. New insertions to the table all got the same order number and are placed above the last row. Anybody has any idea what's going on?
Hi
I am completely stuck trying to make a query
Heres is a shorten downed example of what I am trying to achieve. I have four tables. The first being OrderHeader, the second OrderLines, the third StockCategory. The OrderHeader table contains basic order details, which in turn is linked to the OrderLines table which show if the order in the OrderHeader has a single or multiple order lines. The StockCategory table shows what stock group the item in each order line is associated to.
Here is an example of the tables in a shorten downed version (in both data and fields):
OrderHeader:
Sales Order Ref, Order Date,
1, 01/05/2008
2, 01/05/2008
3, 02/05/2008
4, 02/05/2008
OrderLines:
Sales Order Ref, Part Number
1, 222
1, 234
1, 333
2, 222
2, 555
2, 444
3, 333
3, 111
4, 222
StockCategory
Stock Category, Part Number,
A, 222
B, 234
C, 333
D, 444
E, 111
Stock Group:
Stock Group, Stock Category, Priority
Berr, A, 1
Berr, B, 1
OGD, C, 2
OGD, D, 2
DFID , E, 3
The thing I am trying to do is assign each of the orders to a Stock Group which I can do. The thing that has baffled me is if an two of the order lines in one order are assigned to different stock groups. If this occurs I want to assign the order to the order to the stock group with the highest priority (1 being highest, 3 being lowest) for example if one order line in the order was assigned to Berr (priority 1) and the other to DFID (priority 3) the order would be assigned to Berr.
I am using the following query:
DECLARE @Period DATETIME
DECLARE @BeginDate DATETIME
DECLARE @EndDate DATETIME
SET @Period =’2008-05-01’
SELECT @BeginDate = [BeginDate] FROM DatePeriods AS dp WHERE dp.ID = @Period
SELECT @EndDate = [EndDate] FROM DatePeriods AS dp WHERE dp.ID = @Period
SELECT
COUNT(oh.[Sales Order Reference])
FROM dbo.OrderHeaders AS oh
LEFT JOIN dbo.OrderLines AS ol ON oh.[Sales Order Reference] = ol.[Sales Order Reference]
LEFT JOIN dbo.StockCategories AS sc ON ol.[Part Number] = sc.[Part Number]
WHERE oh.[Order Date] BETWEEN @BeginDate AND @EndDate
AND sc.[Stock Group] IN (SELECT sg.[Stock Group] FROM dbo.StockGroup AS sg WHERE sg.[ID] = 'Berr')
AND ol.[Sales Order Reference] NOT IN (SELECT col.[Sales Order Reference]
FROM dbo.CancelledOrderLines AS col
WHERE col.[Part Number] = ol.[Part Number])
GROUP BY oh.[Sales Order Reference]
Is this possible to do?
Thanking you in advance!!!
Hi,
Been spinning on this for whole weekend I can't seem to get what you I want. I have the following xml result from my query. As you notice one of the child elements has the tag identifier VJobs, how can I make it so it says 'task' instead?
<task id="2" name="Saw 1" color="#99ccff" expand="true" />
<task id="3" name="Saw 2" color="#99ccff" expand="true" />
<VJobs id="3" name="Layout#" color="#99ccff">
<customproperty taskproperty-id="tpc0" value="Unknown" />
<customproperty taskproperty-id="tpc1" value="17.938 " />
<customproperty taskproperty-id="tpc2" value="Unknown" />
<customproperty taskproperty-id="tpc3" value="0" />
<customproperty taskproperty-id="tpc4" value="Operator Unknown" />
</VJobs>
</task>
Here is the query, which details jobs to be done on different equipments
SELECT EquipmentID + 1 as id,
EquipmentDescr as [name],
'#99ccff' AS color,
'true' AS [expand],
(SELECT JobID + 2AS id,
'Layout#' AS [name],
'#99ccff' AS color,
(SELECT [taskproperty-id] AS [taskproperty-id],
[value] AS [value]
FROM dbo.JobDetails customproperty
WHERE customproperty.JobID = VJobs.JobID
FOR XML AUTO, TYPE)
FROM VJobs
WHERE VJobs.EquipmentID = task.EquipmentID
FOR XML AUTO, TYPE)
FROM VEquipments task
ORDER BY EquipmentDescr
FOR XML AUTO, TYPE
Hello, Well actually I am beginner to ASP.Net, I am working on Microsoft Virtual PC with VS 2005 and SQL Server 2005 installed. Now when Create web application in Studio all works fine with databases but when i try to write some code for accessing SQL Databases I keep getting this error PLZ heLP.Following is my code<% @ Import Namespace="System.Data" %><% @ Import Namespace="System.Data.SqlClient" %><html><script language="C#" runat="server" Debug="false" >SqlConnection sqlcon;protected void Page_Load(Object Src, EventArgs e){ sqlcon=new SqlConnection("Data Source=VS02005;Initial Catalog=Gaurav;Integrated Security=True"); SqlDataAdapter sqlcom = new SqlDataAdapter("select * from Employee", sqlcon); DataSet ds = new DataSet(); sqlcom.Fill(ds,"Employee"); DataGrid1.DataSource = ds.Tables["Employee"].DefaultView; DataGrid1.DataBind();}</script><form runtat="server"> <asp:datagrid id="DataGrid1" runat="server" /></form></html>This is the error i am gettingServer Error in '/' Application.--------------------------------------------------------------------------------SELECT permission denied on object 'Employee', database 'Gaurav', schema 'dbo'. 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: SELECT permission denied on object 'Employee', database 'Gaurav', schema 'dbo'. I am stuck dont know what to do, I hve checked all permissions for user gaurav, I also gave administrator rights to gaurav but nothing is working PLEASE help me.
View 4 Replies View RelatedSorry for all the code below. I am realizing that my DB design is bad but I already have 7 pages built around it that work fine...until now, so I would really like to not change the DB if possible. My table has 22 columns: iID which is the identity colum. Then there is iAsmtID which is the assessment ID. Lastly there are 20 colums- q1 through q20, each of which will have a 1, 2, or 3, depending on the radio buttons the user clicked. Nows my problem. I have to find a percent for the assessment. It works like this. 3s are NA so we are not worried about them now. I need to find the number of ones and the number of twos for each assessment ID. Then add those together and divide by the number of ones. How can I find the number of the ones and twos. I have below but its not working. Says there is incorrect syntax at the ',' which is a different color below. Any and all help appreciated.'Open connectionset conn=Server.CreateObject("ADODB.Connection")conn.open My_Connset rs = Server.CreateObject("ADODB.Recordset")str = "SELECT SUM((CASE WHEN q1=1 THEN 1 ELSE 0 END)+(CASE WHEN q2=1 THEN 1 ELSE 0 END)+(CASE WHEN q3=1 THEN 1 ELSE 0 END)+(CASE WHEN q4=1 THEN 1 ELSE 0 END)+(CASE WHEN q5=1 THEN 1 ELSE 0 END)+(CASE WHEN q6=1 THEN 1 ELSE 0 END)+(CASE WHEN q7=1 THEN 1 ELSE 0 END)+(CASE WHEN q8=1 THEN 1 ELSE 0 END)+(CASE WHEN q9=1 THEN 1 ELSE 0 END)+(CASE WHEN q10=1 THEN 1 ELSE 0 END)+(CASE WHEN q11=1 THEN 1 ELSE 0 END)+(CASE WHEN q12=1 THEN 1 ELSE 0 END)+(CASE WHEN q13=1 THEN 1 ELSE 0 END)+(CASE WHEN q14=1 THEN 1 ELSE 0 END)+(CASE WHEN q15=1 THEN 1 ELSE 0 END)+(CASE WHEN q16=1 THEN 1 ELSE 0 END)+(CASE WHEN q17=1 THEN 1 ELSE 0 END)+(CASE WHEN q18=1 THEN 1 ELSE 0 END)+(CASE WHEN q19=1 THEN 1 ELSE 0 END)+(CASE WHEN q20=1 THEN 1 ELSE 0 END) AS [color:#FF0000]CountOfOnes,SUM[/color]((CASE WHEN q1=2 THEN 1 ELSE 0 END)+(CASE WHEN q2=2 THEN 1 ELSE 0 END)+(CASE WHEN q3=2 THEN 1 ELSE 0 END)+(CASE WHEN q4=2 THEN 1 ELSE 0 END)+(CASE WHEN q5=2 THEN 1 ELSE 0 END)+(CASE WHEN q6=2 THEN 1 ELSE 0 END)+(CASE WHEN q7=2 THEN 1 ELSE 0 END)+(CASE WHEN q8=2 THEN 1 ELSE 0 END)+(CASE WHEN q9=2 THEN 1 ELSE 0 END)+(CASE WHEN q10=2 THEN 1 ELSE 0 END)+(CASE WHEN q11=2 THEN 1 ELSE 0 END)+(CASE WHEN q12=2 THEN 1 ELSE 0 END)+(CASE WHEN q13=2 THEN 1 ELSE 0 END)+(CASE WHEN q14=2 THEN 1 ELSE 0 END)+(CASE WHEN q15=2 THEN 1 ELSE 0 END)+(CASE WHEN q16=2 THEN 1 ELSE 0 END)+(CASE WHEN q17=2 THEN 1 ELSE 0 END)+(CASE WHEN q18=2 THEN 1 ELSE 0 END)+(CASE WHEN q19=2 THEN 1 ELSE 0 END)+(CASE WHEN q20=2 THEN 1 ELSE 0 END) AS CountOfTwos FROM ITCC_Test WHERE iAsmtID="&iAsmtIDresponse.Write(str)rs.open str, connif rs.eof = true then ' response.Write("<h2>No count done</h3>") response.End()else'Declare variables CountOfOnes = rs("CountOfOnes") CountOfTwos = rs("CountOfTwos")end ifrs.closeset rs = nothingconn.close'set conn = nothing
View 2 Replies View RelatedHi All
I have a job that extracts data from 10 different tables to 10 diffrenet tables of MS-Access (.mdb file).
The job ran okay since last one month but yesterday it ran and did not stop at all .Status is shown as "Executing" ..
I tried to stop it but it is not stopping . I tried to kill associated process (spid) but it allows me to kill spid but it shows there running.
Any idea why it is happening ?
If any body ahs any solution you can call me at 1 860 520 7454.
Sujit
I have a table with 4 relevant fields (blank lines added for clarity).
State, City, Name, Primary_Contact
IL, Springfield, Bill, n
IL, Springfield, Frank, n
IL, Springfield, Larry, n
IL, Bloomington, Steve, n
IL, Bloomington, Chris, y
IL, Chicago, Betty, n
IL, Chicago, Linda, n
IL, Chicago, Sue, n
I need a query to return the state and cities that don't have a
Primary_Contact='y'
So the results would be:
IL, Springfield
IL, Chicago
That's it. Any help is greatly appreciated.
I need the results from the following query to be with the results of the second query. Any ideas?
Code:
SELECT
PR.WBS1, PR.WBS2, PR.WBS3, PR.Fee, PR.ConsultFee, PR.ReimbAllow, PR.LongName, PR.Name, CL.Name AS CLIENTNAME,
CLAddress.Address2 AS CLIENTADDRESS2, CLAddress.Address3 AS CLIENTADDRESS3, CLAddress.Address4 AS CLIENTADDRESS4,
CFGMain.FirmName, CFGMain.Address1, CFGMain.Address2, CFGMain.Address3, CFGMain.Address4,
Contacts.FirstName + ' ' + Contacts.LastName AS CONTACT, LB.AmtBud, LB.BillBud
FROM PR LEFT OUTER JOIN
Contacts ON PR.ContactID = Contacts.ContactID LEFT OUTER JOIN
CL ON CL.ClientID = PR.ClientID LEFT OUTER JOIN
CLAddress ON CL.ClientID = CLAddress.ClientID LEFT OUTER JOIN
LB ON LB.WBS1 = PR.WBS1 AND PR.WBS2 = LB.WBS2 AND LB.WBS3 = PR.WBS3
CROSS JOIN
CFGMain
Where pr.wbs1 = '001-298' and pr.wbs3 != 'zzz'
and
Code:
SELECT *
FROM LD
WHERE (BilledPeriod = '200408') AND (WBS1 = '001-298')
Thanks.
Hi,
I have a sql problem I'm trying to solve. I'm selecting from a table based on a foreign key, and the select returns 2 rows. The table has a column called type, and each row for the foreign key has a different type value. Hopefully the example below can help to explain:
Case 1:
PK | FK | Type | Text
--------------------------
1 | 226 | 0 | some text goes here
2 | 226 | 1 | NULL
Case 2:
PK | FK | Type | Text
--------------------------
3 | 334 | 0 | some text goes here
4 | 334 | 1 | actual text I want to select is in this cell
I'm trying to create a select statement to grab the text for the foreign key I'm looking up. In case 2, I want the text where type=1 but in case 1 I want the text where type=0.
I had started writing it as
select text from table where fk=334 and ( (type=4 and text is not null) or type=0 )
but this returns both rows. What I what is something that I think is more akin to
case a || case b
expression in programming - if case a evaluates as true, use that, otherwise evaluate case b and use if true, otherwise return false.
I hope you can understand what I'm trying to get and any suggestions would be much appreciated.
Thanks in advance,
Peter
I'ma newbie so please bare with me.
I have two tables, they are tomcat and eagle. With column's named Id, speed, ceiling, range, and data in all the fields.
This is my query:
SELECT id
FROM tomcat INNER JOIN eagle
ON tomcat.id = eagle.id;
my error message:
Msg 209, Level 16, Stae 1, Line 1
Ambiguous column name 'id'
I'm using the book "Head First SQL" as a ref, without any luck. I'm using SQL 2005 express.
Any thoughts, I'm sure its a simple issue.
Thanks
US Navy - We are fueled, armed, and go for launch.
Everything works except for the select portion, I cant put my finger
on what is wrong with this.. I included the trigger and error..
If one can show what I did wrong that would be great..Thanx..
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[arcit] ON [dbo].[active] AFTER UPDATE
as
begin
begin tran
SET IDENTITY_INSERT Archive ON
INSERT INTO Archive()
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'archive' order by ordinal_position
fromInserted i
inner join deleted d
on d.grid = i.grid
inner join [Active] a
on a.grid = i.grid
WHERE i.arc = 1
and isNull(d.arc,0) != 1
DELETE FROM a
fromInserted i
inner join deleted d
on d.grid = i.grid
inner join [Active] a
on a.grid = i.grid
WHERE i.arc = 1
and isNull(d.arc,0) != 1
commit tran
end
Error Message
Msg 156, Level 15, State 1, Procedure arcit, Line 10
Incorrect syntax near the keyword 'from'.
I am attempting to write a function to Check the price of every disk and return the number of titles ont he disk which has the highest price. I do not even think that I am in the ballpark:
CREATE FUNCTION highest_price ( )
BEGIN
SELECT count(*) AS
FROM Disk
WHERE price = (SELECT max(price) FROM Disk);
END
/
MusicalWork (idwork, title)
Piece (idpiece, duration, iddisk, idwork)
Disk (iddisk, brand, type, issuingdate, price)
Execute (idpiece, idinterpreter)
Interpreter (idinterpreter, name, address)
Thank you in advance!
Hi I have the following tables:
document_area: doc_area_id(int) and doc_area_name(string).
document_area_access: doc_area_id(int) and username(string).
I am trying to do a select statement in an sqldatasource in .net that will select all the document_area.doc_area_name's where the current users username is in the document_area_access using the doc_area_id to link the tables.
Any suggestions?
Cheers, Mark
I'm trying to load in the code to a tutorial that requires SQL Server. Step number one on the install instructions says:
Set Up The Database
You may choose to set up your own Northwind database or restore the included Northwind.bak. Regardless of which approach you take you will need to grant your local [MachineName]ASPNET account access to Northwind and then execute GenerateStoredProcedures.sql against it to create the stored procedures. If you receive a message "There is no such user or group 'aspnet'." you may need to search and replace "ASPNET" with "[your machine name]ASPNET"
I'm using SQL Server Express and the SQL Server Management Studio Express, and well...I'm already stuck. I'm trying to create a new user named ASPNET from the management Studio. I don't see any UI that mentions creating user accounts, and the closest thing is LOGIN accounts. So I created a ASPNET login account, and the disconnected from SQL Express and tried to login as ASPNET. When I do this, I get an error that reads:
Login failed for user 'aspnet'. The user is not associated with a trusted SQL server connection. ERROR: 18452
suprisingly, every SQL Express tutorial that I could find never seems to mention anything about creating a user account. Can someone clue me in to what I am doing wrong? Or point me to a SQL Express tutorial that explains user accounts or logins so I can move on to step #2.
Thanks, -=Me=-
most my fields that I am interested in getting percentage values for have to be counted first.... I am having problems converting the counted values to percentages. I don't think I am doing it right...
I would greatly appreciate an example of a very simple matrix table with a counted value that displays percentages. I have found examples online but are part of a much more complex table and I can't seem to get thier formating to work on mine.
Kind regards,
Rich
i want to write a database trigger to increment the salary by 10% for technicians who have done three tests on a particular date.
there are two employee types.(1)technicians (2)traffic controllers.
employee category is defined in "Type" attribute of Employee table. the increment should happen only to technicians.thank you in advance.
Employee (EmployeeID,Name,Salary,Tpye)
Test(TestNo,TestName,maxpoint)
TestEvent(TestNo,TestEventNo,TestDate)
TestEventEmployee(TestEventNo,EmployeeID)
TestEvent
TestEventNo
TestNo
Date
TE001
T001
7/7/2007 12:00:00 AM
TE002
T004
7/7/2007 12:00:00 AM
TE003
T003
7/7/2007 12:00:00 AM
TE004
T003
7/8/2007 12:00:00 AM
TE005
T002
7/9/2007 12:00:00 AM
TE006
T001
7/9/2007 12:00:00 AM
NULL
NULL
NULL
TestEventEmployee
TE001
EMP001
TE002
EMP001
TE003
EMP001
TE003
EMP005
TE002
EMP002
thus only EMP001 should get a increment..thanks in advance
Hi
I have got a query being executed on a view having half a million records each on 3 tables used in the view.
It takes 11 mins to execute it. However if I cancel the query in between at any time it shows me the first
381805 records always. I doubt if the query gets stuck somewhere because if I select top 381805 from table
the query is as fast as 23 seconds. I am using sql 2000 server.
Thanks