Hello,
I have a header and detail table. I want to create a constraint on the detail table, based on a value it's linked to in the header table. If the bit is checked in header then a unique value is required , if it's not checked then a NULL value is acceptable.
Many thanks in advance.
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.
Thanks, Val
Hi,
I'm new to SSRS. I was just wondering how do I make the header for a detail record appear once per grouping rather than once per detail record?
Thanks.
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
/*Execute sp_copyDetailsOne to instantiate @crsrVar*/
EXEC sp_copyDetailsOne @invoiceIdFrom, @outCrsr = @crsrVar OUTPUT
/*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.
Thanks
Hi Anyone
How to export a header and details data from two different table and export it in the below format ?
RecordCount = 129 ------------> Header
001|Manager|2399.00|12 ------------> Detail Lines
002|Technican|1800.00|15
003|Mechanic|1500.00|18
.......
Total Amount = 180000.00 ------------> Footer Line
I want to use the SSIS to do this job can anyone explain step by step.
Thanks,
Madhu
I have a flat file I need to generate, wanted to create my file from a SQL view.
Is there a way to have a Header and Detail Record for each Record in my view?
Fields would be:
Line no type period ref amt date Inv_no
0 M 1 3/3/2014
1 M Pay inv: 400.00 12345
where 0 is the header and 1 is the detail. Only certain fields will be in the header and others in the detail.
Hi All,There is some additional info I forget on this same topic I just posted.I have a database design question, pls give me some help..I want to define tables for salesman's sales target commission . Thecommission could be given per EITHER sales amount of : Group of Products ORGroup of Brand. e.g : the data example :For one salesman_A :product_1, product_2, product_3 etc.. => sales = $100 - $200 =>commission = 5%product_1, product_2, product_3 etc.. => sales = $201 - $400 =>commission = 10%Brand_A, Brand_B, Brand_C .. etc => sales = $100 - $200 =>commission = 2.5%Brand_A, Brand_B, Brand_C .. etc => sales = $201 - $400 =>commission = 5%Below is my table design, is this a good design or something is wrong here ?Thank you for your help.CREATE TABLE Sales_Commission_Header (Sales_ID Char(4) ,Sales_Commission_Group Char(4),Note Varchar(30),Constraint Sales_Commission_Header_PK Primary Key(Sales_ID,Sales_Commission_Group))Alter Table Sales_Commission_Header Add ConstraintFK_Sales_Commission_Header Foreign Key (Sales_Commission_Group)References Commission_Group_Header(Sales_Commission_Group)CREATE TABLE Sales_Commission_Detail (Sales_ID Char(4) ,Sales_Commission_Group Char(4),Sales_From Decimal(12,2) ,Sales_To Decimal(12,2) ,Commission Decimal(5,2),Constraint Sales_Commission_Detail_PK Primary Key(Sales_ID,Sales_Commission_Group, Sales_From, Sales_To))Alter Table Sales_Commission_Detail Add Constraint FK_Sales_CommissionForeign Key (Sales_ID, Sales_Commission_Group) ReferencesSales_Commission_Header(Sales_ID, Sales_Commission_Group)--------------------------------------------** ALTERNATIVE _1 :CREATE TABLE Commission_Group_Header (Sales_Commission_Group Char(4) Primary Key,Note Varchar(30))CREATE TABLE Commission_Group_Detail_Product (Sales_Commission_Group Char(4),Product_ID VarChar(10), -- This product_ID will be FKreference to master productConstraint Commission_Group_Detail_Product_PK PrimaryKey(Sales_Commission_Group, Product_ID))Alter Table Commission_Group_Detail_Product Add ConstraintFK_Commission_Group_Detail_Product Foreign Key (Sales_Commission_Group)References Commission_Group_Header(Sales_Commission_Group)CREATE TABLE Commission_Group_Detail_Brand (Sales_Commission_Group Char(4),Brand_ID VarChar(10), -- This brand_ID will be FKreference to master brandConstraint Commission_Group_Detail_Brand_PK PrimaryKey(Sales_Commission_Group, Brand_ID))Alter Table Commission_Group_Detail_Brand Add ConstraintFK_Commission_Group_Detail_Brans Foreign Key (Sales_Commission_Group)References Commission_Group_Header(Sales_Commission_Group)** ALTERNATIVE _2 :CREATE TABLE Commission_Group_Header (Sales_Commission_Group Char(4),Group_Type Char(1), -- 'B': Brand Group 'P': Product GroupNote Varchar(30),Constraint Commission_Group_Header_PK Primary Key(Sales_Commission_Group,Group_Type))CREATE TABLE Commission_Group_Detail (Sales_Commission_Group Char(4),Group_Type Char(1), -- 'B': Brand Group 'P': Product GroupProduct_Brand_ID VarChar(10),Constraint Commission_Group_Detail_PK Primary Key(Sales_Commission_Group,Group_Type, Product_Brand_ID))Alter Table Commission_Group_Detail Add ConstraintFK_Commission_Group_Detail Foreign Key (Sales_Commission_Group)References Commission_Group_Header(Sales_Commission_Group, Group_Type)The PROBLEM here is : with Product_Brand_ID , I CAN NOT make foreign keyinto both Master Product and Master Brand.So which one is better design ?split the Commission_Group_Detail into Two tables, product and brand , andmake the FOREIGN KEYto master product and master brand (previous mail)ORcombile Commission_Group_Detail for Product and Brand into one table likeaboveand NOT make any FK to master Product or Brand ?Thank you for your help,Tristant
View 1 Replies View RelatedI can't believe it's been a few days and I can't figure this out. We have a flat file (purchaseOrder.txt) that has header and detail lines. It gets dropped in a folder. I need to pick it up and insert it into normalized tables and/or transform it into another file structure or .NET class.
10001,2005/01/01,some more data
SOME PRODUCT 1, 10
SOME PRODUCT 2, 5
Can somebody place give me some guidance on how to do this in SSIS?
I have a flat file with header and detail information, it is actually employee punch card data. I need to parse the header line which contains the Employee ID and don't save it to a table just save the value. Then with the detail line, parse the different data elements and save them along with the employee ID to one table. Then continue until the next header line is read.
The file looks something like this:
FINNEY,RONNIE 0001005420
Mon 09/03 700a HOL 8.00
Tue 09/04 630a*E 326p 8.50 8.50
Wed 09/05 645a 330p 8.00 16.50
Thu 09/06 639a 2.40 18.90
HALL,MARK 0001005601
Mon 09/03 700a HOL 8.00
Tue 09/04 608a*E 257p 8.40 8.40
Wed 09/05 601a*E 259p 8.50 16.90
Thu 09/06 606a*E 3.30 20.20
JONES,WILLA JEAN 0001005702
Mon 09/03 700a HOL 8.00
Tue 09/04 556a*E 326p 9.10 9.10
Wed 09/05 600a*E 328p 9.00 18.10
Thu 09/06 554a*E 3.50 21.60
So I think I need a data flow transformation object that let's me save the Employee ID into a variable available when the next record is read. What type of transformation would be best?
I have a report with two groups and a detail row (subtotals & totals to follow). When I add the child (detail row) it pushes out to the right of the parent column. Is there any way to start the detail row all the way back to the left hand side of the page? I lose a lot or real estate with the group descriptions.
View 5 Replies View RelatedI€™ve created with the help of some great people an SSIS 2005 package which does the follow so far:
1) Takes an incoming txt file. Example txt file: http://www.webfound.net/split.txt
The txt file going from top to bottom is sort of grouped like this
Header Row (designated by €˜HD€™)
Corresponding Detail Rows for the Header Row
€¦..
Next Header Row
Corresponding Detail Rows
€¦and so on
http://www.webfound.net/rows.jpg
2) Header Rows are split into one table, Maintenance Detail Rows into another, and Payment Detail Rows into a third table. A uniqueID has been created for each header and it€™s related detail rows to form a PK/FK relationship as there was non prior to the import, only the relation was in order of header / related rows below it when we first started. The reason I split this out is so I can massage it later with stored proc filters, whatever€¦
Now I€™m trying to somehow bring back the data in those table together like it was initially using a query so that I can cut out each of the Header / Detail Row sections into their own txt file. So, if you look at the original txt file, each new header and it€™s related detail rows (example of a cut piece would be http://www.webfound.net/rows.jpg) need to be cut out and put into their own separate txt file.
This is where I€™m stuck. How to create a query to combine it all back into an OLE DB Souce component, then somehow read that souce and split out the sections into their own individual txt files.
The filenames of the txt files will vary and be based on one of the column values already in the header table.
Here is a print screen of my package so far:
http://www.webfound.net/tasks.jpg
http://www.webfound.net/Import_MaintenanceFile_Task_components.jpg
http://www.webfound.net/DataFlow_Task_components.jpg
Let me know if you need more info. Examples of the actual data in the tables are here:
http://www.webfound.net/mnt_headerRows.txt
http://www.webfound.net/mnt_MaintenanceRows.txt
http://www.webfound.net/mnt_PaymentRows.txt
Here's a print screen of the table schema:
http://www.webfound.net/schema.jpg
I need to insert data into Header & Detail table. As shown in the below xml,
RecordID is identity-column and incremented by 1, after new record is saved into Header table. Need to assign the same recordID for the detail also.
Expecting output should be like as shown below:
How can we accomplish this requirement.
I have a report with 3 groups, and a toggle on the first cell in the group header, and group totals on that line also. So it renders as:
- Group 1 G1Total G1Total G1Total
- Group 2 G2Total G2Total G2Total
- Group 3 G3Total G3Total G3Total
Detail DAmt DAmt DAmt
I would like to save some space and render more like
- Group 1 G1Total G1Total G1Total
- Group 2 G2Total G2Total G2Total
- Group 3 G3Total G3Total G3Total
Detail DAmt DAmt DAmt
I haven't been able to find a way to have the first group cells overlap each other. Is there a way to do that and am I missing something obvious?
I have a need to show a row inside a table group to simulate a header row for the data rows inside the group. The table will not have a real header or footer. Thanks for the help.
View 1 Replies View RelatedI have a province table in a my database. I would like to link this province table to a resource table's Province_ID. This Province_ID is an int.
Vic Valentic
CEO/President
Open Door
2 Elite Dr. #33
Hamilton, Ontario
L8W 2N3
905-389-7492
http://www.wlu.ca/next/opendoor
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.
tblShipmentHdr --> tblShipmentDet (Key = ShipmentID)
tblAdjustments --> standalone
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'))
Greetings all:
I am looking for a way to get detailed information about each created table (regardless of permanancy) in a stored procedure, similar to the information one received from using sp_help on an individual table.
Does anyone know of such an animal?
Thank you, and Happiest of New Years,
Jack Cole
Magellan Healthcare, Inc.
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.
Many thanks,
Leonard
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.
Hi,
Suppose the following table definition in Sql Server 2005,
create table CompanySymbol
CompanyId int,
SymbolId int,
IsPrimarySymbol bit
Primary Key (CompanyId, SymbolId)
How can I create a constraint which wil ensure that IsPrimarySymbol will be set to 1(true) only once per CompanyId while allowing it to be set to 0 an unlimited amount of time per CompanyId. i.e.:
CompanyId SymbolId IsPrimarySymbol
-----------------------------------------------------------
1 1 1 (IsPrimarySymbol to 1 for CompanyId 1)
1 2 0
1 3 0
2 1 1 (IsPrimarySymbol to 1 for CompanyId 2)
2 2 0
3 1 0
4 1 1 (IsPrimarySymbol to 1 for CompanyId 4)
4 2 0
4 3 0
Thanks
Hi All,
I need to create a query which gives me something like this
HH20060831160342
DDasb IT 3000
FF20060831160709000000001
Where 'HH' is the header(followed by Date and time) and 'FF' is the footer (followed by Date, time and no of records)and 'DD' has some details (few fields) from database.I am using UNION to get this result but the problem is that if the count in the footer is 0 then query should not give any output.but If I am using the following query
select 'HH'+convert(varchar,getDATE(),112)+replace(convert(varchar,getdate(),8),':','') as filename,'' as name,'' as dept,'' as sal
union all
select 'DD'+'',filename,dept,sal from emp where empno like '%1%'
union all
select 'FF'+convert(varchar,getDATE(),112)+replace(convert(varchar,getdate(),8),':','')+ REPLICATE(0, 9-len(COUNT(*)))+''+convert(VARchar(10),COUNT(*)) as filename,'' as name,'' as dept,'' as sal from emp where empno like '%1%'
I am getting the result as
HH20060831161226
FF20060831161226000000000
if the second select statement has no records
Please help
I am attempting to create a unique constraint on an nvarchar field named theology (it is not the primary key field)
that allows nulls. The table contains multiple rows with the value of null for
field theology. The documentation says one can create a unique constraint on a
field with all unique value except for null. Here is the error message:
'testtable1' table
- Unable to create index 'IX_testtable1'.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]CREATE UNIQUE INDEX
terminated because a duplicate key was found. Most significant primary key
is ''.
[Microsoft][ODBC SQL Server Driver][SQL Server]Could not create constraint. See
previous errors.
Any ideas? I am creating a unique constraint and not a unique index. Is there
some other database option to set to allow this?
.
I need to create a check constraint for an email column/field, where the field must contain an "@" symbol. Does sql (oracle or SQL in general) let you do this.
1. Tried myself:
ALTER TABLE Q_Customer
ADD CONSTRAINT Q_chk_Cus_email CHECK (Cus_email LIKE '%@%');
...but
ORA-02293: cannot validate (C3267304.Q_CHK_CUS_EMAIL) - check constraint violated
2. Tried from a forum: alter table Q_CUSTOMER
add constraint Q_Chk_cus_email check (Cus_email like '%_@__%.__%')
...but
ORA-02293: cannot validate (C3267304.Q_CHK_CUS_EMAIL) - check constraint violated
Hi.I have a procedure with this in it (there are no other references toasset_number_bak_tmp_pk in the procedure and it calls nothing else written byme, just system calls or normal dml).create table #asset_bak(asset_number varchar(60) not null,asset_desc varchar(100) null,location varchar(40) null,constraint asset_number_bak_tmp_pk primary key clustered (asset_number))When I run the procedure, I get this message:(1 row(s) affected)Msg 2714, Level 16, State 4, Procedure updatenavharrierdb, Line 19There is already an object named 'asset_number_bak_tmp_pk' in the database.Msg 1750, Level 16, State 0, Procedure updatenavharrierdb, Line 19Could not create constraint. See previous errors.How can I find where else the system thinks this constraint exists?I tried this but it only finds it in one place (one row in the result set),i.e. my procedure:select sysobjects.name, syscomments.textfrom sysobjects, syscommentswhere sysobjects.id = syscomments.id and((lower(sysobjects.name) like '%asset_number_bak_tmp_pk%') or(lower(syscomments.text) like '%asset_number_bak_tmp_pk%'))Is this somehow a case where I need to do something dynamically, or purge someinformation? I thought temp tables and their crony constraints disappearedafter the procedure exited.thanksJeff Kish
View 2 Replies View Relatedwhy it is not possible to create a Foreign key to a Unique constraint?
Table A has column 1 holding a Primay key and two columns (2 and 3) holding a Unique combination (and some more columns).He created an Unique constraint on column 2 and 3 together.
He wanted to use this Unique combination to point to table B (instead of the table 1's PK) so he tried to create a foreign key on a column in table B but an error popped up prompting;
The columns in table 'TABLE_A' do not match an existing primary key or UNIQUE constraint.
Ok - these two columns ar no PK but the hold an Unique constraint......
I need to create constraint in column to add only alphabet .
like "adc" ,"sdfsd" and not "1234adfd".plz reply soon.
I want to create a check constraint on a table but the constraint values depend upon another table column as well, now one possible way is to create a function to check the column value. But I don’t want to use the function.
Can I do this in a view if so then how can I achieve this.
I have a merge replication in place. I increased the identity_range to 100000 for a table. I have done this both ways, via the properties of the publication on SSMS, and via TSQL. I have call sp_adjustpublisheridentityrange. Then I recreated the sanpshot.  EXEC sp_adjustpublisheridentityrange @table_name = N'Label', @table_owner = N'caseup';but after synchronization, the range defined in the table's constraint has not changed and now all of the identity values are used up. All inserts are failing.What needs to be done to force the publisher to recreate the identity range on a subscriber(s).
View 3 Replies View RelatedI am trying to create a Unique Constraint on a SQL Server 2005 table where the uniqueness is based on 2 columns.
Could anybody provided some help on how I could enforce this on an existing table (link, or example) I have been looking around without luck.
Thanks in advance
John.
Is it possible to have a constarint between two tables based on a FK relationship and a value based on another column. For example to have a valid record in table b the TableA_ID value needs to exist in tableA and the charge Value can't be null. So row number 3 would be invalid in table B in this example.
TableA
IDCharge_Value
1100
2Null
34
4Null
TableB
TableB_IDTableA_IDSome other data
1 1 A
2 3 B
3 4 C
I have 2 tables in a report and have set the FixedHeader property of the 3 left columns on both tables to True.
The report displays perfectly when previewing, but when deployed and viewed in IE6 fixes only the columns from the first table
(all columns from 2nd table display, but the 3 columns with FixedHeader set to true scroll out of view when report is scrolled right)
and additionally displays an error.
Error:
Line: 13
Char: 1080
Error: 'children.0.children.0.children.1.children' is null or not an object
Code: 0
URL : <URL of report displayed here>
Possible additional points of interest.
1)There are some groupings in the second table which are initially hidden and toggled by cells in the 1st or 2nd column of the table.These 2 columns are the first 2 of the 3 columns having FixedHeader set to True.
2)If the 1st table is moved physically to below the 2nd table, FixedHeader fails on both tables.
Is there something that I've set up incorrectly?
Any help is appreciated.
Hi all,
I am trying to add a unique index/constraint on a column that allows NULL values. The column does have NULL values and when I try to create a unique constraint, I get the following error.
CREATE UNIQUE INDEX terminated because a duplicate key was found for index ID 9. Most significant primary key is '<NULL>'.
Are'nt you allowed to create a UNIQUE constraint on a NULL column? Books Online says that you are allowed to create a unique constraint on NULL columns, then why am I getting this error.
Any help would be appreciated.
Thanks,
Amir
Hello,
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.
Hope this makes sense.
How can I do this?
GrkEngineer