Am new to sql, and I wold appreciate help with optimising the folloing example. The result of the example should be to list a result with details of the Column names:
OPBal| Receipt| IssTrns| Transfer| ClBal
SELECT dbo.inventory.location, dbo.inventory.itemnum,
(select sum(dbo.matrectrans.linecost) where dbo.matrectrans.issuetype LIKE 'RECEIPT' ) As Receipt,
( select sum(dbo.matrectrans.linecost)where dbo.matrectrans.issuetype LIKE 'TRANSFER' ) As Transfer,
( select(dbo.IST_ITEMDETAIL.curbal*dbo.IST_ITEMDETAIL.avgcost)where dbo.IST_ITEMDETAIL.logdate='2006-07-20' ) As OpBal,
( select (IST_ITEMDETAIL.curbal*IST_ITEMDETAIL.avgcost)where IST_ITEMDETAIL.logdate='2006-08-21' ) As ClBal,
( sum(matusetrans.linecost) ) As IssTrnf
FROM dbo.matrectrans, dbo.matusetrans, dbo.IST_ITEMDETAIL , ( dbo.inventory inner JOIN dbo.item
ON dbo.inventory.itemnum = dbo.item.itemnum AND dbo.inventory.orgid = dbo.item.orgid )
WHERE dbo.inventory.location = dbo.matusetrans.storeloc
AND dbo.inventory.itemnum = dbo.matrectrans.itemnum AND dbo.inventory.siteid = dbo.matrectrans.siteid
OR dbo.inventory.location = dbo.matrectrans.tostoreloc AND dbo.inventory.itemnum = dbo.matusetrans.itemnum
AND dbo.inventory.siteid = dbo.matusetrans.siteid OR dbo.inventory.location = dbo.matrectrans.fromstoreloc
OR
dbo.inventory.location = dbo.ist_itemdetail.location AND dbo.inventory.itemnum = dbo.ist_itemdetail.itemnum
GROUP BY dbo.inventory.location, dbo.inventory.itemnum,dbo.matrectrans.issuetype,(dbo.IST_ITEMDETAIL.curbal*dbo.IST_ITEMDETAIL.avgcost),
dbo.IST_ITEMDETAIL.logdate,dbo.IST_ITEMDETAIL.curbal,dbo.IST_ITEMDETAIL.avgcost
hi all, i have speed issue on displaying 4k line of records using temp table.. before this it works fine and fast.. but maybe when i starts joining group by it loads slower.
SELECT DISTINCT customlotno, itemid, ItemName, Ownership, TotalCTNInPlt, TotalCarton, sum(CartonPcs) AS CartonPcs, StorageID, StorageStatus ,OriginUOM, PickQtyUOM, WhsID, WhsName, LocID, Zone, Expirydate, recvDate INTO #ByItemID FROM ( SELECT * FROM tblItemdetail )AS L1 GROUP BY customlotno, itemid, ItemName, ownership, TotalCTNInPlt, TotalCarton, StorageID, StorageStatus ,OriginUOM, PickQtyUOM, WhsID, WhsName, LocID, Zone, Expirydate, recvDate
SELECT * FROM #ByItemID ORDER BY CustomLotNo
DROP TABLE #ByItemID
---------------------------- or maybe just use something like nested SELECT like this, but cannot work:-
select customlotno, itemid, locid(
select * from tblitemdetail where customlotno='IN28606000'
OK heres the situation, I have a Categories table and a Products table, each Category can have one or many Products, but a product can only belong to one Category hence one-to-many relationship.
Now I want to do a SELECT query that outputs all of the Categories onto an ASP page, but also displays how many Products are in each category eg.
CatID | Name | Description | No. Products
0001 | Cars | Blah blah blah | 5
etc etc
At the moment I'm doing nesting in my application logic so that for each category that is displayed, another query is run that returns the number of products for that particular category. It works ok!
However, is there a way to write a SQL Statement that returns all the Categories AND number products from just the one SELECT statement, rather than with the method I'm using outlined above? The reason I'm asking is that I want to be able to order by the number of products for each category and my method doesn't allow me to do this.
I've only been doing sql 2005 for a couple of months with minimal training so there's a lot I don't know. What I'm trying to do is load a nested table (industry & customer totals) based on a value from the table it's nested in. This is the relationship of the data. I have at the highest group, an industry code, then a customer, then the part and then the fiscal year. What I'm trying to accomplish is to get a group total (footer) for the (1) industry code and (2) the customer code. This footer would contain the fiscal years (ascending) and their monthly totals. I would like to take the industry code from table one and pass it to the select statement in the dataset that feeds the nested table. I've read this is not possible to load a dataset field into a parm but I've seen where some people know how to work around this. If you reply, please explain in simple terms. Thanks!
industry Customer Year OCT NOV DEC 001 - Signposts M12345 Part 1 2006 5 6 2 2007 0 3 1
Hey guys i have a stock table and a stock type table and what i would like to do is say for every different piece of stock find out how many are available The two tables are like thisstockIDconsumableIDstockAvailableconsumableIDconsumableName So i want to,Select every consumableName in my table and then group all the stock by the consumable ID with some form of total where stockavailable = 1I should then end up with a table like thisEpson T001 - Available 6Epson T002 - Available 0Epson T003 - Available 4If anyone can help me i would be very appreciative. If you want excact table names etc then i can put that here but for now i thought i would ask how you would do it and then give it a go myself.ThanksMatt
What's worng, please help? SELECT TTarea,personel,Date FROM person_table WHERE TTarea = (SELECT TTarea FROM TTarea_table WHERE Center='CENTER_office') I have many TTarea and I want to send back from inner SELECT statement but give an error that inner select statement don't return many result.I want to return many result and I display many TTarea in the CENTER_office
SELECT Tbl_Region.REGION, [NEW_HMO_CONTRACTS].[# of New Members] AS [HMO NEW CONTRACTS], [NEW_HMO_MEMBERS].[# of New Members] AS [HMO NEW MEMBERS], [TERMED_HMO_CONTRACTS].[# of Termed Contracts] AS [HMO TERMED CONTRACTS], [TERMED_HMO_MEMBERS].[# of Termed Members] AS [HMO TERMED MEMBERS] FROM (((Tbl_Region LEFT JOIN [SELECT qry_New_Members_HMO_All_Regions_1.Reg, Count(qry_New_Members_HMO_All_Regions_1.CONTRACT_N UM) AS [# of New Members] FROM (SELECT tbl_hmo.Reg, tbl_hmo.CONTRACT_NUM FROM tbl_hmo LEFT JOIN tbl_hmo_History ON tbl_hmo.CONTRACT_NUM = tbl_hmo_History.CONTRACT_NUM WHERE (((tbl_hmo_History.CONTRACT_NUM) Is Null)) GROUP BY tbl_hmo.reg, tbl_hmo.CONTRACT_NUM
) AS qry_New_Members_HMO_All_Regions_1
GROUP BY qry_New_Members_HMO_All_Regions_1.reg ) AS NEW_HMO_CONTRACTS ON Tbl_Region.REGION = [NEW_HMO_CONTRACTS].reg) LEFT JOIN (SELECT qry_New_Members_HMO_All_Regions_1.reg, Count(qry_New_Members_HMO_All_Regions_1.MEMBER_NUM ) AS [# of New Members] FROM (SELECT tbl_hmo.reg, tbl_hmo.MEMBER_NUM FROM tbl_hmo LEFT JOIN tbl_hmo_History ON tbl_hmo.MEMBER_NUM = tbl_hmo_History.MEMBER_NUM WHERE (((tbl_hmo_History.MEMBER_NUM) Is Null)) GROUP BY tbl_hmo.Aff_Area, tbl_hmo.MEMBER_NUM
) AS qry_New_Members_HMO_All_Regions_1 GROUP BY qry_New_Members_HMO_All_Regions_1.reg) AS 4_NEW_HMO_MEMBERS ON Tbl_Region.REGION = [4_NEW_HMO_MEMBERS].reg) LEFT JOIN (SELECT qry_Termed_Contracts_HMO_All_Regions_1.reg, Count(qry_Termed_Contracts_HMO_All_Regions_1.CONTR ACT_NUM) AS [# of Termed Contracts] FROM (SELECT tbl_hmo_History.reg, tbl_hmo_History.CONTRACT_NUM FROM tbl_hmo RIGHT JOIN tbl_hmo_History ON tbl_hmo.CONTRACT_NUM = tbl_hmo_History.CONTRACT_NUM WHERE (((tbl_hmo.CONTRACT_NUM) Is Null)) GROUP BY tbl_hmo_History.reg, tbl_hmo_History.CONTRACT_NUM ) AS qry_Termed_Contracts_HMO_All_Regions_1 GROUP BY qry_Termed_Contracts_HMO_All_Regions_1.reg) AS TERMED_HMO_CONTRACTS ON Tbl_Region.REGION = [TERMED_HMO_CONTRACTS].reg) LEFT JOIN (SELECT qry_Termed_Members_HMO_All_Regions_1.reg, Count(qry_Termed_Members_HMO_All_Regions_1.MEMBER_ NUM) AS [# of Termed Members] FROM (SELECT tbl_hmo_History.reg, tbl_hmo_History.MEMBER_NUM FROM tbl_hmo RIGHT JOIN tbl_hmo_History ON tbl_hmo.MEMBER_NUM = tbl_hmo_History.MEMBER_NUM WHERE (((tbl_hmo.MEMBER_NUM) Is Null)) GROUP BY tbl_hmo_History.reg, tbl_hmo_History.MEMBER_NUM ) AS qry_Termed_Members_HMO_All_Regions_1 GROUP BY qry_Termed_Members_HMO_All_Regions_1.reg) AS TERMED_HMO_MEMBERS ON Tbl_Region.REGION = [TERMED_HMO_MEMBERS].reg;
error: Server: Msg 156, Level 15, State 1, Line 3 Incorrect syntax near the keyword 'FROM'. Server: Msg 156, Level 15, State 1, Line 8 Incorrect syntax near the keyword 'AS'. Server: Msg 156, Level 15, State 1, Line 18 Incorrect syntax near the keyword 'AS'. Server: Msg 156, Level 15, State 1, Line 24 Incorrect syntax near the keyword 'AS'. Server: Msg 156, Level 15, State 1, Line 31 Incorrect syntax near the keyword 'AS'.
I am trying to get some functionality from nested IF's witin a SQL Select Statement. I do not want to create a stored procedure as I have another program that must use select statements that I will be using once I have this query written. Below is my code.
SELECT Sales.OrderID, Buyers.Userid, Buyers.Email, Sales.ShippingCo, IF (Addresses.Company IS NULL OR Addresses.Company = '') BEGIN IF ( RTRIM(LTRIM(Addresses.FirstName)) IS NULL RTRIM(LTRIM(Adresses.LastName)) AS CompanyOrName, ELSE RTRIM(LTRIM(Addresses.FirstName)) & ' ' & RTRIM(LTRIM(Adresses.LastName)) AS CompanyOrName, END ELSE Addresses.Company AS CompanyOrName, END
All I am trying to do is join the first and last names as the company in my table if the company doesn't exist, and then only display the last name if the first name is null.
I keep getting the error "Msg 156, Level 15, State 1, Line 6 Incorrect syntax near the keyword 'IF'."
I know I am close to geting this to work, but for some reason googling for tutorials on writing IF statements isn't helping out.
Thanks ahead of time for any help. It will be greatly appreciated.
Hi all,I have the following databasedesign :www.marinescu.dk/databasedesign.pdfwhich i have a nested SELECT on but i need some more information which idon't know how to retrieve. I have the following SELECT :SELECT DISTINCT Resource.ResourceID, Localized.ResourceValue,Localized.Font, Resource.ResourceName, Resource.Comment, Type.TypeName FROMLocalized, Resource,Type WHERE Localized.ResourceID = Resource.ResourceIDAND Resource.TypeID = Type.TypeID ORDER BY Resource.ResourceIDFor some Resources there are Rules. I will like to have a new column namedRulesText in my query where there will be shown the RuleText if there is anyfor that particular Resource.Could anybody help me here ????Best RegardsMihai Marinescu
insert into SEL values(194,'6/1/2006 12:00:00 AM',730,11) insert into SEL values(194,'6/1/2006 12:00:00 AM',930,4) insert into SEL values(194,'6/1/2006 12:00:00 AM',1830,10) insert into SEL values(194,'6/1/2006 12:00:00 AM',1930,20) insert into SEL values(194,'6/1/2006 12:00:00 AM',2130,14) insert into SEL values(194,'6/1/2006 12:00:00 AM',2230,0) insert into SEL values(195,'6/1/2006 12:00:00 AM',730,22) insert into SEL values(195,'6/1/2006 12:00:00 AM',930,43) insert into SEL values(195,'6/1/2006 12:00:00 AM',1830,0) insert into SEL values(195,'6/1/2006 12:00:00 AM',1930,54) insert into SEL values(195,'6/1/2006 12:00:00 AM',2130,21) insert into SEL values(195,'6/1/2006 12:00:00 AM',2230,6)
CREATE TABLE [dbo].[station_info]( [STATE] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [STATION_NAME] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [MAIN_ID] [int] NOT NULL ) ON [PRIMARY]
insert into station_info values('SEL','PUCHONG',196) insert into station_info values('JHR','BulohKasap',5)
Above script showing table as follow SEL MAIN_ID | DATE_TAKEN | TIME | DAILY_RAINFALL -------------------------------------------------------- 194 | 6/1/2006 12:00:00 AM | 730 | 11 194 | 6/1/2006 12:00:00 AM | 930 | 4 .......... .......... 202 | 6/1/2006 12:00:00 AM | 450 | 23 .......... .......... *This table storing DAILY_RAINFALL everyday from time to time for each MAIN_ID.
station_info STATE | STATION_NAME | MAIN_ID --------------------------------------- SEL | Puchong | 194 JHR | BulohKasap | 5 ......... ......... *This table storing MAIN_ID description. Main_ID is a primary key.
1. 1 day having many TIME. So, we only take which time having MAX(DAILY_RAINFALL) per day per MAIN_ID to do the SUM(DAILY_RAINFALL) for that month.
So far, i've this query SELECT CAST(CAST(m.Month AS varchar(2)) + '/1/' + CAST(m.Year AS varchar(4)) AS datetime) AS DATE_TAKEN,m.MAIN_ID,m.DAILY_RAINFALL FROM ( SELECT MONTH(t.Date_Taken) AS Month, YEAR(t.Date_Taken) AS Year, t.Main_ID, SUM(t.Daily_Rainfall) As Daily_Rainfall FROM ( SELECT t1.MAIN_ID,t1.DATE_TAKEN,t1.TIME, t1.DAILY_RAINFALL FROM (SELECT ROW_NUMBER() OVER(PARTITION BY DATE_TAKEN,MAIN_ID ORDER BY DAILY_RAINFALL DESC) AS RowNo,* FROM dbo.SEL)t1 INNER JOIN dbo.STATION_INFO t2 ON t2.MAIN_ID=t1.MAIN_ID AND t1.RowNo=1 AND t1.DATE_TAKEN>=CONVERT(VARCHAR(10),DATEADD(m,-12,GETDATE()),101) AND t1.DATE_TAKEN<=CONVERT(VARCHAR(10), GETDATE(), 101) WHERE t2.STATE='SEL')t GROUP BY MONTH(t.Date_Taken),YEAR(t.Date_Taken),t.Main_ID)m ORDER BY Main_ID,Date_Taken *Assume GETDATE()=6/10/2007
I'm stuck when joint table below. The purpose of joint is to pickup [cumrf1year] column (if DATE_TAKEN>=CONVERT(VARCHAR(10),DATEADD(m,-12,GETDATE()),101) AND t1.DATE_TAKEN<=CONVERT(VARCHAR(10), GETDATE(), 101)) and pickup [month_year] value depend on DATE_TAKEN (if DATE_TAKEN=6/1/2006, [month_year]=6/30/2006, if DATE_TAKEN=7/1/2006, [month_year]=7/31/2006). MAIN_ID is foreign key to relate [longterm_rf_temp] table below.
insert into longterm_rf_temp values(194,'6/30/2006',207.94,550.7,850.7,1150.7,1450.7) insert into longterm_rf_temp values(194,'7/31/2006',200.64,590.4,858.7,1260.7,1550.7) insert into longterm_rf_temp values(194,'8/30/2006',222.64,390.4,958.7,1460.7,1750.7) insert into longterm_rf_temp values(195,'6/30/2006',217.94,550.7,840.7,1150.7,1324.7) insert into longterm_rf_temp values(195,'7/31/2006',202.64,590.4,858.7,1260.7,1659.7) insert into longterm_rf_temp values(195,'8/30/2006',222.64,490.4,958.7,1460.7,1733.7)
HI All, I have what is most likely a simple MS SQL query problem (2005).I need to add two computed columns to a result set, both of those columns are required to provide the count of a query that contains a parmamter. (The id of a row in the result set) 3 Tables (Showing only the keys)t_Sessions-> SessionID (Unique) t_SessionActivity-> SessionID (*)-> ErrorID (Unique) t_SessionErrors-> ErrorID (1) I need to return something like (Be warned the following is garbage, hopefully you can decifer my ramblings and suggest how this can actualy be done) SELECT SessionID, (SELECT COUNT(1) AS "Activities" FROM t_SessionActivity WHERE t_SessionActivity.SessionID = t_Sessions.SessionID), (SELECT COUNT(1) AS "Errors" FROM dbo.t_Sessions INNER JOIN dbo.t_SessionActivity ON dbo.t_Sessions.SessionID = dbo.t_SessionActivity.SessionID INNER JOIN dbo.t_SessionErrors ON dbo.t_SessionActivity.ErrorID = dbo.t_SessionErrors.ErrorID WHERE t_SessionActivity.SessionID = t_Sessions.SessionID)FROM t_Sessions Any help greatfully received. Thanks
I need help nesting select statements. Here's what I'm trying to do:
select (select e.emp_name_lf as employee, e.emp_id from employee e, install_payroll_detail ipd where e.emp_id = ipd.emplno) e.emp_name_lf as username from employee e, install_payroll_master ipm where e.emp_id = ipm.entered_by
I just want one row with both the employee and username, however I cannot get the syntax. Any help is greatly appreciated.
I have been struggling over the following problem for a few days... i was wondering if anyone could shed some light...!
I have the following query:
SELECT Field1, Field2 FROM Table1 INNER JOIN Table2 ON Table1.ID = Table2.ID WHERE Table2.Field1 = ( SELECT TOP 1 Field1 FROM Table2 WHERE Field3='X' ORDER BY Date1) AND Table2.Field2 = ( SELECT TOP 1 Field2 FROM Table2 WHERE Field3='X' ORDER BY Date1)
Is there a better way to do this. I was thinking of something very similar to the below query (Which doesnt work):
SELECT * FROM Table1 A INNER JOIN Table2 B ON (A.ID=B.ID) INNER JOIN ( SELECT TOP 1 * FROM Table2 WHERE Field3='X' ORDER BY Date1 ) C ON (A.ID=C.ID) WHERE B.Field1 = C.Field1 B.Field2 = C.Field1
1)table of customers: CustomerName, CustomerId, CustomerAddress 2)table of orders: OrderId, CustomerId, OrderAmount
I would like to have a query that returns everything from the customer table and add one column that has the amount of orders the customer has made, this is what I have so far:
CREATE PROCEDURE dbo.GetAllCutomerInfo AS
DECLARE @OrderCount int
SELECT CustomerName, CustomerId, CustomerAddress, (SELECT COUNT(OrderId) FROM Cust_Orders WHERE CustomerId= CustomerId) FROM Customers ORDER BY CustomerName
Can you add a variable:
CREATE PROCEDURE dbo.GetAllCutomerInfo AS
DECLARE @CustID int
DECLARE @OrderCount int
SELECT CustomerName, @CustID=CustomerId, CustomerAddress, (SELECT COUNT(OrderId) FROM Cust_Orders WHERE CustomerId= @CustID) FROM Customers ORDER BY CustomerName
I have two tables tbl1 and tbl2, which I do a full outer join between tbl1 and tbl2 on recordId field. The recordId field is not a key in either of the tables.
If there is one row each for a recordId 123 in both tables, the select query would return one combined row.
If tble1 had two rows for recordId 123, and tbl2 had one row for the same, it would return to rows repeating the data in tbl2.
If tbl2 had two rows and bl1 had one row, it would return two rows in output repeating the data in tbl1.
Is the above correct? Would the result be different if it was an inner join instead of full outer join?
Is it ever possible that one of the two records with recordId 123 will be dropped from the result?
I need to divide amount in joint account. So if joint account has 2 account holders & amount is 35622.15 then one person should have 17811.08 and other person should have 17811.07
If I used below query it just give me 17811.08 for both account holders so when we sum it it's one penny extra.
In a SQL db we have we get the following error when just doing a simple select query against the view. Msg 217, Level 16, State 1...Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).
No changes have been made to triggers or stored procedures recently and all was good prior to that.I understand that if my triggers loop this error will occur. But the select query does not fire any triggers functions or any other items. and the select worked with no issues last week.
I have nested a Stored Procedure within a stored procedure. The nested stored procedure ends in a select statement. What I'd like to do is either capture the results of the select statement (it will be 1 row with 3 columns, I only need the contents of first column), or suppress the select statement from displaying in the final results of the Stored Procedure it is nested in.
hey there, i'm trying to move one record from one table to the next,so i'm doing this by doing an insert into table, then delete from theprevious table. Only thin g is on the insert i want to include aparameter. Can't work how to do this. Do you think i need to doanother update query and insert the parameter that way, after theinsert is done?ALTER PROCEDURE dbo.ReturnLoan(@strBarcode varchar(100),@strLibrary varchar(100),@dateReturned datetime)ASINSERT INTO Loan_History(Barcode,UserID,Date_Borrowed,Library)Select Barcode, UserID, Date_Borrowed, Library FROM Loans WHEREBarcode = @strBarcodeAND Library = @strLibrary;DELETE FROM LoansWHERE Barcode = @strBarcodeAND Library = @strLibrary ;RETURN
So I have another query I can't seem to function the way I was hoping. I've learned a lot in this past month or so but I've hit another challenge. Anyways what I'm trying to do is when a user/student wants to add a new major I want to show a list of majors that are NOT already in his/her profile. So basically if I had a full list of majors: Accounting Computer Science Mathematics
and the user already had Mathematics in his/her profile I'd like it to display only:
Accounting Mathematics
Below is the layout of the tables, my attempt at the query, and then below that some example data.
Code Snippet
USE [C:COLLEGE ACADEMIC TRACKERCOLLEGE ACADEMIC TRACKERCOLLEGE.MDF] GO /****** Object: Table [dbo].[Majors] Script Date: 04/17/2008 22:38:06 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[Majors]( [MajorID] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, CONSTRAINT [PK_Majors] PRIMARY KEY CLUSTERED ( [MajorID] ASC )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY]
GO SET ANSI_PADDING OFF
USE [C:COLLEGE ACADEMIC TRACKERCOLLEGE ACADEMIC TRACKERCOLLEGE.MDF] GO /****** Object: Table [dbo].[MajorDisciplines] Script Date: 04/17/2008 22:38:16 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[MajorDisciplines]( [MajorDisciplineID] [int] IDENTITY(0,1) NOT NULL, [DegreeID] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [MajorID] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [DisciplineName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Description] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Criteria] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL, CONSTRAINT [PK_MajorDiscipline] PRIMARY KEY CLUSTERED ( [MajorDisciplineID] ASC )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO SET ANSI_PADDING OFF GO ALTER TABLE [dbo].[MajorDisciplines] WITH CHECK ADD CONSTRAINT [FK_MajorDiscipline_DegreeID] FOREIGN KEY([DegreeID]) REFERENCES [dbo].[Degree] ([DegreeID]) GO ALTER TABLE [dbo].[MajorDisciplines] CHECK CONSTRAINT [FK_MajorDiscipline_DegreeID] GO ALTER TABLE [dbo].[MajorDisciplines] WITH CHECK ADD CONSTRAINT [FK_MajorDiscipline_MajorID] FOREIGN KEY([MajorID]) REFERENCES [dbo].[Majors] ([MajorID]) GO ALTER TABLE [dbo].[MajorDisciplines] CHECK CONSTRAINT [FK_MajorDiscipline_MajorID]
USE [C:COLLEGE ACADEMIC TRACKERCOLLEGE ACADEMIC TRACKERCOLLEGE.MDF] GO /****** Object: Table [dbo].[MajorDisciplines] Script Date: 04/17/2008 22:38:16 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[MajorDisciplines]( [MajorDisciplineID] [int] IDENTITY(0,1) NOT NULL, [DegreeID] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [MajorID] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [DisciplineName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Description] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Criteria] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL, CONSTRAINT [PK_MajorDiscipline] PRIMARY KEY CLUSTERED ( [MajorDisciplineID] ASC )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO SET ANSI_PADDING OFF GO ALTER TABLE [dbo].[MajorDisciplines] WITH CHECK ADD CONSTRAINT [FK_MajorDiscipline_DegreeID] FOREIGN KEY([DegreeID]) REFERENCES [dbo].[Degree] ([DegreeID]) GO ALTER TABLE [dbo].[MajorDisciplines] CHECK CONSTRAINT [FK_MajorDiscipline_DegreeID] GO ALTER TABLE [dbo].[MajorDisciplines] WITH CHECK ADD CONSTRAINT [FK_MajorDiscipline_MajorID] FOREIGN KEY([MajorID]) REFERENCES [dbo].[Majors] ([MajorID]) GO ALTER TABLE [dbo].[MajorDisciplines] CHECK CONSTRAINT [FK_MajorDiscipline_MajorID]
SELECT MajorID FROM Majors majs WHERE majs.MajorID NOT IN (SELECT majDis.MajorID FROM MajorDisciplines majDis WHERE majDis.MajorDisciplineID NOT IN (SELECT sMajors.MajorDisciplineID FROM Student_Majors sMajors WHERE sMajors.StudentID = 0)) dbo.Majors MajorID Accounting Computer Science Mathematics
Oh also for the MajorIDs I don't want it to return duplicates such as the Accounting in MajorDisciplines...which I was hoping my query wouldn't do but it returns absolutely random data...
I need to perform an update where there are multiple scenarios which determine the value that is entered. Below is a sort've psuedo code of how it needs to be.
Update MyTable SET MyColumn = CASE WHEN MyCol1 = 'Value1' Then NewValue Else WHEN MyCol1 <> 'Value1' And MyCol2 = 'Active' Then 'Value1'
In the scenario where MyCol1 <> Value1 and MyCol2 <> 'Active' then no update would occur and the original value would remain intact.
I hope I'm posting this in the correct forum. If not I apologize. I have a nested select query that I imported from Oracle:
Oracle Version:
Code Snippetselect avg(days) as days from ( select dm_number, max(dm_closedate) - max(comment_closed_date) as days from dm_data where dm_type = 'prime' and dm_closedate <= '31-dec-2007' and dm_closedate >= '1-dec-2007' and program = 'aads' group by dm_number)
SQL Version:
select round(abs(avg(days)), 0) as days from (select dm.dm_number, abs(datediff(DAY,max(dm.dm_closedate), max(dm.comment_closed_date))) as days from dm_data dm, ProgramXref px where px.Program_Name = 'aads' and dm.Program_Id = px.Program_Id and dm.dm_type = 'prime' and dm.dm_closedate <= '31-dec-2007' and dm.dm_closedate >= '1-dec-2007' group by dm.dm_number)
In Oracle the query runs fine. In SQL I am getting a "Line 10: Incorrect syntax near ')'." error. If I run just the nested portion of the query, there are no errors. It only happens when the first query tries to query the nested query. Can anyone help me get the syntax correct?
I am trying to create a new mining structure with case table and nested table, the case table (fact table) has alread defined the relationships with the nested table(dimension table), and I can see their relationship from the data source view. But why the wizard for creating the new mining structure showed that message? Why is that? And what could I try to fix it?
Hope it is clear for your help.
Thanks a lot for your kind advices and I am looking forward to hearing from you shortly.
As we are allowed to select one table as both case table and nested table, however what is the benefit of using one table as both case table and nested table? Thanks in advance for your advices.
and just insert multiple rows per Id/Gender/Age? Is there any rule of thumb to use here when deciding to model with a nested table?
I understand that a nested table essentially pivots the data (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=408840&SiteID=1) and the data becomes an attribute, but how do you know if you want that, or you want the data as a value of an attribute?
I have a question about nested table use. I dont quite clearly see through why the nested table is very much necessary? I mean if we need information from both the current case table and nested table for mining models, we can just join both tables?
Thanks a lot in advance for any guidance and help.
Dear all,I have table called CATEGORY, which is defined as follows:CREATE TABLE CATEGORY(CATEGORY_ID INTEGER IDENTITY(1,1) NOT NULL,CATEGORY_NAME VARCHAR(40) NOT NULL CONSTRAINTUC__CATEGORY__CATEGORY_NAME UNIQUE,PARENT_CATEGORY_ID INTEGER,CATEGORY_ICON IMAGE,DEPTH INTEGER,CONSTRAINT PK__CATEGORY PRIMARY KEY (CATEGORY_ID))Supposly, the following snap shot was taken later:================================================== ============| CATEGORY_ID | CATEGORY_NAME | PARENT_CATEGORY_ID | DEPTH |================================================== ============| 1 | PC | NULL | 1 |--------------------------------------------------------------| 2 | Networks | 1 | 2 |--------------------------------------------------------------| 3 | Audio | 1 | 2 |--------------------------------------------------------------| 4 | Video | 1 | 2 |--------------------------------------------------------------| 5 | TV Cards | 4 | 3 |--------------------------------------------------------------| 6 | Graphics Cards | 4 | 3 |--------------------------------------------------------------| 7 | AGP | 6 | 4 |--------------------------------------------------------------| 8 | PCI | 6 | 4 |--------------------------------------------------------------| 9 | Input Devices | 1 | 2 |--------------------------------------------------------------and I'd like to create out of this hierarchy the following desired XMLfile:<?xml version="1.0" encoding="utf-8" ?><Hardware><Catgeory name="PC" id="1"><Catgeory name="Networks" id="2" /><Catgeory name="Audio" id="3" /><Catgeory name="Video" id="4"><Catgeory name="TV Cards" id="5" /><Catgeory name="Graphics Cards" id="6"><Catgeory name="AGP" id="7" /><Catgeory name="PCI" id="8" /></Category></Category><Catgeory name="Input Devices" id="9" /></Catgeory></Hardware>The reason for this file is that it will be a datasource of theTreeView Control new in asp.net 2.0.Now, programmateiclally using C#.net i started using the XmlDocument,XmlTextWriter and XmlTextReader Namespaces and started using susingrecurrsion to genearete this desired XML file out of the records in thesnapshot, but ...Is there an easy way of doing this using SqlServer 2005 with the newdatatype XML?*Any hint would also be ok*Best regards
I'm having some issues with nested table. This is my setup. [ProductTable] is the case table, and [CustomersTable] is a nested table. I'm trying to organize my algorithms around products.
[ProductTable]<---[CustomersTable]
[ProductTable] table only has product ID, and it is key.
[CustomersTable] table has variety of customer attributes (productID, customerID, location, demographics...) and CustomerRevenue is predict_only. ProductName is the key for the nested table.
I keep getting this error when I'm processing the mining models (Logical Regression and Neural Net).
Error (Data mining): In mining model, Estimate Neural Net, the algorithm does not allow table column as predictable.
Error (Data mining): Error validating attribute for the 'Estimate Neural Net' mining model.
When using Decision Tree, it processes OK, but the result is totally wrong. The model is empty.
Any ideas?
-Young K.
P.S. I'm trying to great a single model for multiple products. This is a label saving device that I'm trying. If this doens't work, I'll have to create a model for each product.
