I'm new to SQL and need help with a query. Not sure if this is the right place.
I have 2 tables, one MASTER and one DETAIL.
The MASTER table has a masterID, name and the DETAIL table has a detailID, masterID, and value columns.
I want to return a populated MASTER table with entries based on the DETAIL.value.
SELECT MASTER.* FROM MASTER WHERE DETAIL.value > 3
This is a simplified version of my problem. I can't figure out how to set the relationship between MASTER.masterID and DETAIL.masterID. If I do an INNER JOIN, the number of results are based on the number of DETAIL entries. I only want one entry per MASTER entry.
The plant number field is a location based field that the application uses to filter/select data on for the end users. What they want to be able to do is to select a record, select another location from a dropdown list and then click a button that duplicates the master record and the detail records to the new location.
I am thinking that a stored procedure passing the JSAID and new Location number to do it, I am just not sure how to get the new ID when I go to copy the detail records.
The CUSTOMERS table holds the record date span into which the FLAGS table records have to "fit". In no case will the date spans from the FLAGS table fall outside the STARTDATE - ENDDATE span in the CUSTOMERS table for any CUSTOMERNBR. Here is an example of the final expected output in the DHS table from combining records in the CUSTOMERS and FLAGS tables:
Hello community,I think my problem is easy to solve even though I did not find a solution through different tutorials and help pages. Here it is (select statements are hier simplified):In the gridview "GridView1" I have a master record with person_id, which is the data-key-value. There is also another id-field named task_id (This record comes from a database view which joins the persons- and the tasks- table) SelectCommand="SELECT [id], [person_id], [task_id] FROM [ViewPersonTasks] WHERE ([id] = @id)"For both fields I want to display details in two different detail-views. One for the person (depending on person_id) and one for the tasks (depending on the task_id).The first one is easy. I declare a details-view for the person data based on a SqlDataSource with a control-parameter like this: SelectCommand="SELECT [person_id], [first_name], [last_name], [birth_date] FROM [TabPersons] WHERE ([person_id] = @person_id)" .... <SelectParameters> <asp:ControlParameter ControlID="GridView1" Name="person_id" PropertyName="SelectedValue" Type="Int32" /> </SelectParameters>But now the problem: how should I declare a parameter @task_id for the task_id, so that the second select statement for the tasks-details-view retrievs the data for the tasks: SelectCommand="SELECT [task_id], [task_name], [task_date], [task_description] FROM [TabTasks] WHERE ([task_id] = @task_id)"@task_id should have the value from the task_id-field of the master record, displayd in the master grid-view.Thank you in advance for your help
I'd like to put together a summary report for some of my management staff. The report should show the # of jobs that match about 20 different statuses. Each status has different criteria. For example, one might look for a date in a datetime field and the type of job. Another might look at whether a date has passed and the quantity we're shipping.
Sort of like this: Status Items Jobs Status #1: 50000 15 Status #2: 25251 3
I want the user to see this summary information, but also have drill down capability. I'd like the ability to print the summary or just a drill-down.
I'm also considering using Dundas Charts for RS on the report.
Can I accomplish this with Reporting Services? If so, any tips on how to do it? If this a bunch of sub-reports? Can I sum the # of jobs on a sub-report? Should I be looking at BI for this?
Hi, can anyone help me? I want to know whether is it possible to insert records simultaneously in both master & detail tables? For elaboration, say there is a master table contains (orderid,orderdate,amount) and details table contains (orderid, productid,qty,price). 1 record of master table associated with n records of details table. Can it be possible to insert both the 1 record at master table with n records in details table in a single sql statement?
I need to know the best approach to save data in master table and then in detail table. I know this method but i know it's not a good approach why i will explain
Insertion in Master Table..................................... A
Insertion in Detail Table........................................B
Now if there is any exception occurred while step A then the step B will not take place which is ok but if there is exception while step B then the process A will have completed i.e the data in master table will be Inserted/Deleted/Updated but there will not be a corresponding action in Detail table which is not good approach.
So please can any one tell me a good approach for this.
As part of a project, I'm not allowed to use foreign keys, well can't. But I have a problem with this master detail relationship (and a very simple one too). Tables to begin with: Invoice: invoiceid : bigint indentity autoincrement primarykey Detail: invoiceid: bigint - detailnumber:bigint - desc : char20 Okay - I want to set these two tables up such than when I create a new invoice, and subsequently get the new invoiceid, and I start to add detail records to the detail table, I want the detailnumber to autoincrement when I do an SQL Insert. Right now I have detailnumber as the primary key, so when you view the detail records, it looks like: (invoiceid, detailnumber, desc) 1,1,text - 1,2,text - 1,3,text - 2,4, text on another invoice - 3,5, text I want it to work like: (invoiceid, detailnumber, desc) 1,1,text - 1,2,text - 1,3,text - 2,1, text on another invoice - 3,1, text where the invoiceid and the detail number make up the primary key. I seem to recall you could do this is PHP before it had foreign keys & such, and I'm completely drawing a blank on this. BTW - I'm using enterprise manager to set this up, but I can use QA with a sample script to acheive the same. I realize this may be (and probably is) off-topic here, but I can't figure out where to ask. Thanks in advance.
Help! Surely this has happened to others before me. A new customer wants to send updates in a fixed-width txt file in which master and detail rows alternate.
How do you do this?
Do you: 1) Painfully muck around with 100,000 rows in the .txt file first--split it into 2 files, one for master records and one for detail records and then process? If so, what do you use? I'd probably hack at it with a VB Script module in a DTS package.
or
2) Is there a way to feed it into 2 tables where rows starting with x go to one table and rows starting with y go to another?
The plan was to use a fast and dirty DTS package to shove this stuff into a table (probably 2 but we might just toss the stuff we don't need and put in in one) but I'd like some advice on how to proceed.
In second Table store those records who are selected and stored in 2nd table. NameAppidFunctionCodeFunNameSubFunCode Data810Summary0 Data820View0 Data830&View0 Ad630Mbl20
Our requirements we use one query, In query fetch total 5 rows. and output show like this
I'd like to create a master-detail output from SQL2005 to display in a page using classic ASP. Ideally, I want the output to contain a <div> to show/hide the order details beneathe the order header, as shown below.
I currently just have a stored procedure returning the results and it is very slow as I'm simply querying the details section for each order header. Does anyone have any ideas on how I can create an efficient and fast method for returning these results in the format above?
I have multiple drop down lists that I would like to have send parameters to a stored procedure that does a select on the data. (Kind of like a search feature) But if a ddl isnt populated, I want that to act like a * and get all. For instance, I have a persontype, state, and country drop down lists. If I drop the state one down to WA, I want the gridview to populate with all the people in WA regardless or their type. If I populate type and state, have it give me both regardless of country. What does the stored procedure or select syntax look like for this? (What would the default values of my dropdown boxes?) my current sql select statement (The bottom 3 are bit fields which are throwing me off a bit too). do i need to have some IsNull statments around each variable in the select?SELECT
tblPerson.PersonTypeID = @PersonTypeID and tblPerson.StateID = @StateID and tblPerson.CountryID = @CountryID and tblPerson.SchoolID = @SchoolID and tblPerson.InterestChapterLeaders = @InterestChapterLeaders and tblPerson.InterestMembers = @InterestMembers and tblPerson.InterestUniLeader = @InterestUniLeader
now i want to learn how to make a stored procedure to insert a record to `purchase` table, and many records to `purchase_detail` table with transaction where the some value are passed from vb6 through the parameters. i've made a SP to insert 1 record to `purchase` table n 1 record to `purchase_detail` just for testing, so i set the disc value to 10. it works fine... --------------------------------------------------------------------------------- CREATE PROCEDURE `usp_save_purchase`(xpurch_id VARCHAR(10), xpurch_date VARCHAR(10), xsupp_id VARCHAR(10), xitem_id VARCHAR(10), xqty TINYINT(3), xprice DOUBLE(15,2)) BEGIN START TRANSACTION; INSERT INTO purchase(purch_id,purch_date,supplier_id) VALUES(xpurch_id, xpurch_date, xsupplier_id); INSERT INTO purchase_detail(purch_id,item_id,qty,price,disc) VALUES(xpurch_id, xitem_id, xqty, xprice, 10); COMMIT; END --------------------------------------------------------------------------------- what i need is something like that but i only pass 3 variables (purch_id, purch_date, and supp_id) to SP, and then the SP will insert 1 record of purchase to `purchase` table, and add the purchase items to `purchase_detail` automatically from `purch_temp` table, and use the disc rate based on `supplier_id` and `item_id` from supplier_disc table, which will be looked something like this: --------------------------------------------------------------------------------- CREATE PROCEDURE `usp_save_purchase`(xpurch_id VARCHAR(10), xpurch_date VARCHAR(10), xsupp_id VARCHAR(10)) BEGIN START TRANSACTION; INSERT INTO purchase(purch_id,purch_date,supplier_id) VALUES(xpurch_id, xpurch_date, xsupplier_id); /*start looping here get the disc rate for each items where supp_id = xsupplier_id and item_id = the item_id from purch_temp table, and save it in a local variable (let's say local_disc) INSERT INTO purchase_detail(purch_id,item_id,qty,price,disc) VALUES(xpurch_id, xitem_id, xqty, xprice, local_disc); */ COMMIT; END --------------------------------------------------------------------------------- can anyone help me please? thank you in advance...
I need to write a sql query which is a master-detail query. Here's the example structure of tables:
Master table: ColID as longint, ColA as int, ColB as int, ColPartID as longint, ColPartName as longint
Child table -- Wheel: ColID as longint, ColA as int, ColB as int Child table -- Door: ColID as longint, ColA as int, ColB as int Child table -- Window: ColID as longint, ColA as int, ColB as int ..... etc
From the master table, it needs to join with its child in order to get the detailed information. However, there're more than one child table for it to join. In other words, the query has to choose the correct child table to join for each row selectively. The use of correct child depends on one of the columns in its master table (ColPartName).
My question is: Does it worth of me keep finding a solution for this query or should I abandon this? I really need some advice, please.
Almost new to ssrs, accessing Oracle's stored procedures to feed the reports (no other choice), working on a master/detail report and badly stuck. This report needs to access 3 stored procedures one for master and two for small tables under it. The master's order ID is fed to the two stored procedures that may or may not pull any thing for certain order ID's I am approaching it by creating 2 datasets and a page-long list, added desired text boxes and a table in the list, grouped the list on order ID and passing it to tables using default values etc. but keep getting the following message. "The Group expression for the table €˜table1€™ refers to the field €˜ProductName€™. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope". The list seems to be associated to one dataset only and any item in it cannot refer to anyother dataset, even if I try to access the table's dataset in its properties (The 'current data set scope' implies that). (Not sure what will happen when I will try to add the second table and dataset with the same parameter name). How should I approach this issue. I will appreciate help, if explained in steps but won't mind a good tip. Thanks.
I'm currently strugling with the setup of our packages for deployment to a new environment.
We are working with a master/detail package setup. One master package is created that will call all child packages. In the master package we don't have any connection towards our source and/or target databases/sourcesystems.
Everything works fine, however, starting to deploy the whole set of packages, it seems that we don't have the option to set specific properties of our detailed packages, e.g. connection properties. But this is just what we need.
When we are adding a job in the Job Agent for our master package to be scheduled, we want to be able to set all different connection manager properties, not only the one from the master package and definitely the ones from the detailed packages as there we switch the connections from the development environment towards the acceptance environment.
I tried to fix this with parent package variables, but I can't set the password property, only the ServerName and UserName can be set, not the Password.
Anyone an idea what the easiest and best approach is to solve this burden?
I have a master detail reports in one page. Master report displays all the products and details report shows their orders... What I want is, when user clicks on products graph its detail graph show the orders of that particular record on the same page. Both reports would remain visible on the same page side by side.
I am attempting to setup a Master-Details with GridView/DetailsView but I can't seem to find any information on using a stored procedure that requires parameters with the SqlDataSource control. SelectCommandType specifies that you are using a stored proc. SelectCommand specifies the name of the proc, but I haven't found any information on how to pass a parameter to the stored procedure.Is it even possible or do I have to forget about using the DetailsView control altogether?
I am creating a DTS package to export a text file. My question is: does anyone have any ideas on how for one read of the tables I can produce 2 lines of output. Here is how the file layout needs to be...
HEADER DETAIL HEADER DETAIL HEADER DETAIL
I am a little confused about how I can stagger header and detail using the same data.
I appreciate any help you can give. Hopefully my explanation of the problem is understandable.
Im trying to copy details from a specific header as details of a different header (eg. all sales items from invoice #10 copied as sales items of invoice #11).
So far I have two stored procedures: 1) sp_copyDetailsOne /*Create a recordset of the desired items to be copied*/ CREATE PROCEDURE sp_copyDetailsOne @invoiceIdFrom INT, @outCrsr CURSOR VARYING OUTPUT AS SELECT itemId, itemPrice, itemDescription, itemQuantity FROM tblSalesItems WHERE (invoiceId = @invoiceIdFrom) OPEN @outCrsr
2) sp_copyDetailsTwo CREATE PROCEDURE sp_copyDetailsTwo @invoiceIdFrom INT, @invoiceIdTo INT /*Allocate a cursor variable*/ DECLARE @crsrVar CURSOR
/*Loop through the recordset and insert a new record using the new invoiceId*/ FETCH NEXT FROM @crsrVar WHILE @@FETCH_STATUS = 0 BEGIN
/*Run the insert here*/ INSERT INTO tblSalesItems (invoiceId, itemId, itemPrice, itemDescription, itemQuantity) VALUES (@invoiceIdTo , 5, $25.00, N'Black T-Shirt', 30)
/*Fetch next record from cursor*/ FETCH NEXT FROM @crsrVar END
CLOSE @crsrVar DEALLOCATE @crsrVar
My question comes on the Insert of sp_copyDetailsTwo, as you can see the values are hard coded and I want them pulled from the cursor. However I don't know how to do this - do I need varables or can I access the cursor values directly in my VALUES clause? Or is this whole approach needing overhauled. Any advice is welcome.
Following is a stored procedure that currently runs on the system (compacted version). I need to combine this data with data from another Table .. tblAdjustments. The schema for this table is fairly close to tblShipmentDet.
Result: combine tblShipmentHdr + attached tblShipmentDet records with tblAdjustments records.
Would the best approach be to use a UNION SELECT?
@XToDate datetime = '7/31/2005' ,@XBegDate datetime = '7/1/2005' AS SELECT SHPH.ProductID, SHPH.ReceivedDate, SHPH.ShipmentNo, SHPD.Vendor, SHPD.Quantity, QRecvdDate = CASE WHEN SHPH.ReceivedDate < convert(varchar(40),@XBegDate,121) THEN NULL ELSE SHPH.ReceivedDate END, QShipQty = CASE WHEN SHPD.TransCd = 'F' THEN NULL WHEN SHPH.ReceivedDate < convert(varchar(40),@XBegDate,121) THEN NULL ELSE SHPH.ShippingQty END, PROD.ProductName, QOpenAccrual = CASE WHEN MEND.OpeningAccrual is Null THEN 0 ELSE MEND.OpeningAccrual END FROM dbo.tblShipmentHdr SHPH LEFT OUTER JOIN dbo.tblProducts as PROD ON Left(SHPH.ProductID,7) = Left(PROD.ProductID,7) LEFT OUTER JOIN dbo.tblShipmentDet as SHPD ON SHPH.ShipmentID = SHPD.ShipmentID LEFT OUTER JOIN dbo.tblMonthend as MEND ON SHPH.ProductID = MEND.ProductID And MEND.MEPeriod = convert(varchar(40),@XBegDate,121) WHERE ((SHPH.ReceivedDate >= '7/1/2005' AND SHPH.ReceivedDate <= '7/31/2005') OR (SHPD.DatePaid >= '7/1/2005' AND SHPD.DatePaid <= '7/31/2005'))
I am fairly new to SQL Server 2005 and was curious if this was possible.
In my VB applications I always used Begin Transaction, Commit and Rollback to process records. I just found out that I can perform the same functionality in a stored procedure.
So the question is, if I have an order record and four line item records is there anyway to pass all that to the stored procedure as a unit. I can pass the order record as individual parameters but it is the four (or however many) detail records that is the reason for my question. How can I pass the detail records at one time? Can I pass these as an array or a dataset or something else or am I just out of luck? SQL Server 2005 has impressed me a few times already with what you can do and I am really hoping this can be accomplished also.
I am listing detail transaction lines in a table sorted by account and order number. the problem is that I only want to see the detail if the sum of a value field is zero for all the transactions in an order otherwise ignore the detail for that order.
I was trying Group by and Having but this doesn't seem to do what I need.
Being relatively new to Reporting services, any nudge in the right direction would be useful.
i have go master records as well child records how do i delete all the master records so that all my corresponding child records get deleted ,as we do in oracle using undeleted cascade
I need to insert a record in a master table and 20 records in a child table. I want to do this using stored procedure. Is it better to do it in stored procedure? Have somebody already tried this? Or is there any sample that I can use?
I am developing an application in vb.net 2005 using SQL Server 2000. In this I have two tables SessionMaster and SessionChild. Fields of session master - SessionMastId, Start_Date, End_Date, Session_Type, Fields of session child - SessionChildId, SessionMastId, UserName, Comment. SessionMastId and SessionChildId are primary keys of respective tables and also they are auto increment fields. Please how to write trigger to insert record into both tables at a time.
Background: In my current company the business users maintain a huge quantity of master data using excel. Then a series of SSIS jobs are edited and manually executed.
Goal: the challenge is to replace this process using MDS. One of the requested features is the possibility for the users to edit or insert new master data using the Web UI or the Excel Add-in and when they are done perform a merge of the master data in the target, in this case in the reporting DB.
The perfect solution for me is something like trigger the execution of a SSIS package to export the data from the subscription views to the reporting DB after the business rules are apply to a specific entity.
I have a master securities table which has 7 fields. As a part of the daily process I am uploading flat files into database tables. The flat files contains the master(static) security data as well as the analytics(transaction) data. I need to
1) separate the master (static) data from the flat files,
2) check whether that data is present in the master table, if not then insert that data into the master table
3) If data present then move that existing record to an history table and then update the main master table.
All the 7 fields need to be checked to uniquely identify a single record in the master table.
How can this be done? Whether we can us a combination of data flow items or write a sql procedure to do all this.
Hi, I want to export data/records coming from the database and save it as a .txt file but tab-delimited. The flow of my project is something this.
Web Form->SQL Database->Web Report->Tab-Delimited file.
I will explain more..What we want to do is an online application form. We have a form and will save all the data to sql server database. We also want to save all those information in a tab-delimited file. I would like to save this first in the database(no problem in this part). Then later on export this in tab-delimited file.
If you can give me a little bit tutorial of this i really appreciated..Even 3 records can do as long i can see how to do this..Ooops btw, i also want to name the .txt file as (userid+transactionid).
