T-SQL (SS2K8) :: Different Levels To Make Table Flat
May 6, 2015
I am trying to fill my table in different levels (hierarchy). The different levels are not properly processed. All layers now have the same value. It is intended that the layers are beaten flat. This implies multiple joins in order to be able to get the different levels.
'Ledgertableinterval' could be used to determine the hierarchy, but I did not come all the way out ...
So my suggestion is to fill with 1 character level 1, level 2 with 2 character and so on. If a level is not available anymore than filling up with higher level. For this you can use the replace function.
The DIM that I use;
USE [NJ]
GO
/****** Object: Table [DIM].[FA] Script Date: 05/06/2015 09:57:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [DIM].[FA](
[Code] .....
View 1 Replies
ADVERTISEMENT
Aug 20, 2007
hi
I've a table with coln names
ID
Name
ParentID
Level
I've list with different levels
say
ex.
the Data is:-
ID Name ParentID Level
1 Root null 1
2 Trunk 1 2
3 Branch 2 3
4 Leaf 3 4
5 Stem 3 4
How to write the query for getting the Names for different levels for corresponding ParentID....
Output should be like:-
Leaf Branch Trunk Root
Stem Branch Trunk Root
View 1 Replies
View Related
Jan 28, 2015
I thought this would be somewhat easy but I'm having trouble with this one. I have a statement that if 'ACTLABCOST' or 'ACTMATCOST' has a value of 0.00 then I need to make it .00.
Here's the statement:
Select
CONVERT(VARCHAR(10), xn_approveddate, 101) + ' ' + convert(VARCHAR(8), xn_approveddate, 108)as "Approved Date",
WORKTYPE,
[Code]....
View 5 Replies
View Related
Oct 3, 2006
The script below can be used to determine the reference levels of all tables in a database in order to be able to create a script to load tables in the correct order to prevent Foreign Key violations.
This script returns 3 result sets. The first shows the tables in order by level and table name. The second shows tables and tables that reference it in order by table and referencing table. The third shows tables and tables it references in order by table and referenced table.
Tables at level 0 have no related tables, except self-references. Tables at level 1 reference no other table, but are referenced by other tables. Tables at levels 2 and above are tables which reference lower level tables and may be referenced by higher levels. Tables with a level of NULL may indicate a circular reference (example: TableA references TableB and TableB references TableA).
Tables at levels 0 and 1 can be loaded first without FK violations, and then the tables at higher levels can be loaded in order by level from lower to higher to prevent FK violations. All tables at the same level can be loaded at the same time without FK violations.
Tested on SQL 2000 only. Please post any errors found.
Edit 2006/10/10:
Fixed bug with tables that have multiple references, and moved tables that have only self-references to level 1 from level 0.
-- Start of Script - Find_Table_Reference_Levels.sql
/*
Find Table Reference Levels
This script finds table references and ranks them by level in order
to be able to load tables with FK references in the correct order.
Tables can then be loaded one level at a time from lower to higher.
This script also shows all the relationships for each table
by tables it references and by tables that reference it.
Level 0 is tables which have no FK relationships.
Level 1 is tables which reference no other tables, except
themselves, and are only referenced by higher level tables
or themselves.
Levels 2 and above are tables which reference lower levels
and may be referenced by higher levels or themselves.
*/
declare @r table (
PK_TABLE nvarchar(200),
FK_TABLE nvarchar(200),
primary key clustered (PK_TABLE,FK_TABLE))
declare @rs table (
PK_TABLE nvarchar(200),
FK_TABLE nvarchar(200),
primary key clustered (PK_TABLE,FK_TABLE))
declare @t table (
REF_LEVEL int,
TABLE_NAME nvarchar(200) not null primary key clustered )
declare @table table (
TABLE_NAME nvarchar(200) not null primary key clustered )
set nocount off
print 'Load tables for database '+db_name()
insert into @table
select
TABLE_NAME = a.TABLE_SCHEMA+'.'+a.TABLE_NAME
from
INFORMATION_SCHEMA.TABLES a
where
a.TABLE_TYPE = 'BASE TABLE'and
a.TABLE_SCHEMA+'.'+a.TABLE_NAME <> 'dbo.dtproperties'
order by
1
print 'Load PK/FK references'
insert into @r
selectdistinct
PK_TABLE =
b.TABLE_SCHEMA+'.'+b.TABLE_NAME,
FK_TABLE =
c.TABLE_SCHEMA+'.'+c.TABLE_NAME
from
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS a
join
INFORMATION_SCHEMA.TABLE_CONSTRAINTS b
on
a.CONSTRAINT_SCHEMA = b.CONSTRAINT_SCHEMA and
a.UNIQUE_CONSTRAINT_NAME = b.CONSTRAINT_NAME
join
INFORMATION_SCHEMA.TABLE_CONSTRAINTS c
on
a.CONSTRAINT_SCHEMA = c.CONSTRAINT_SCHEMA and
a.CONSTRAINT_NAME = c.CONSTRAINT_NAME
order by
1,2
print 'Make copy of PK/FK references'
insert into @rs
select
*
from
@r
order by
1,2
print 'Load un-referenced tables as level 0'
insert into @t
select
REF_LEVEL = 0,
a.TABLE_NAME
from
@table a
where
a.TABLE_NAME not in
(
select PK_TABLE from @r union all
select FK_TABLE from @r
)
order by
1
-- select * from @r
print 'Remove self references'
delete from @r
where
PK_TABLE = FK_TABLE
declare @level int
set @level = 0
while @level < 100
begin
set @level = @level + 1
print 'Delete lower level references'
delete from @r
where
PK_TABLE in
( select TABLE_NAME from @t )
or
FK_TABLE in
( select TABLE_NAME from @t )
print 'Load level '+convert(varchar(20),@level)+' tables'
insert into @t
select
REF_LEVEL =@level,
a.TABLE_NAME
from
@table a
where
a.TABLE_NAME not in
( select FK_TABLE from @r )
and
a.TABLE_NAME not in
( select TABLE_NAME from @t )
order by
1
if not exists (select * from @r )
begin
print 'Done loading table levels'
print ''
break
end
end
print 'Count of Tables by level'
print ''
select
REF_LEVEL,
TABLE_COUNT = count(*)
from
@t
group by
REF_LEVEL
order by
REF_LEVEL
print 'Tables in order by level and table name'
print 'Note: Null REF_LEVEL nay indicate possible circular reference'
print ''
select
b.REF_LEVEL,
TABLE_NAME = convert(varchar(40),a.TABLE_NAME)
from
@table a
left join
@t b
on a.TABLE_NAME = b.TABLE_NAME
order by
b.REF_LEVEL,
a.TABLE_NAME
print 'Tables and Referencing Tables'
print ''
select
b.REF_LEVEL,
TABLE_NAME = convert(varchar(40),a.TABLE_NAME),
REFERENCING_TABLE =convert(varchar(40),c.FK_TABLE)
from
@table a
left join
@t b
on a.TABLE_NAME = b.TABLE_NAME
left join
@rs c
on a.TABLE_NAME = c.PK_TABLE
order by
a.TABLE_NAME,
c.FK_TABLE
print 'Tables and Tables Referenced'
print ''
select
b.REF_LEVEL,
TABLE_NAME = convert(varchar(40),a.TABLE_NAME),
TABLE_REFERENCED =convert(varchar(40),c.PK_TABLE)
from
@table a
left join
@t b
on a.TABLE_NAME = b.TABLE_NAME
left join
@rs c
on a.TABLE_NAME = c.FK_TABLE
order by
a.TABLE_NAME,
c.PK_TABLE
-- End of Script
Results from Northwind database:
Load tables for database Northwind
(13 row(s) affected)
Load PK/FK references
(13 row(s) affected)
Make copy of PK/FK references
(13 row(s) affected)
Load un-referenced tables as level 0
(0 row(s) affected)
Remove self references
(1 row(s) affected)
Delete lower level references
(0 row(s) affected)
Load level 1 tables
(7 row(s) affected)
Delete lower level references
(9 row(s) affected)
Load level 2 tables
(4 row(s) affected)
Delete lower level references
(3 row(s) affected)
Load level 3 tables
(2 row(s) affected)
Done loading table levels
Count of Tables by level
REF_LEVEL TABLE_COUNT
----------- -----------
1 7
2 4
3 2
(3 row(s) affected)
Tables in order by level and table name
Note: Null REF_LEVEL nay indicate possible circular reference
REF_LEVEL TABLE_NAME
----------- ----------------------------------------
1 dbo.Categories
1 dbo.CustomerDemographics
1 dbo.Customers
1 dbo.Employees
1 dbo.Region
1 dbo.Shippers
1 dbo.Suppliers
2 dbo.CustomerCustomerDemo
2 dbo.Orders
2 dbo.Products
2 dbo.Territories
3 dbo.EmployeeTerritories
3 dbo.Order Details
(13 row(s) affected)
Tables and Referencing Tables
REF_LEVEL TABLE_NAME REFERENCING_TABLE
----------- ---------------------------------------- ----------------------------------------
1 dbo.Categories dbo.Products
2 dbo.CustomerCustomerDemo NULL
1 dbo.CustomerDemographics dbo.CustomerCustomerDemo
1 dbo.Customers dbo.CustomerCustomerDemo
1 dbo.Customers dbo.Orders
1 dbo.Employees dbo.Employees
1 dbo.Employees dbo.EmployeeTerritories
1 dbo.Employees dbo.Orders
3 dbo.EmployeeTerritories NULL
3 dbo.Order Details NULL
2 dbo.Orders dbo.Order Details
2 dbo.Products dbo.Order Details
1 dbo.Region dbo.Territories
1 dbo.Shippers dbo.Orders
1 dbo.Suppliers dbo.Products
2 dbo.Territories dbo.EmployeeTerritories
(16 row(s) affected)
Tables and Tables Referenced
REF_LEVEL TABLE_NAME TABLE_REFERENCED
----------- ---------------------------------------- ----------------------------------------
1 dbo.Categories NULL
2 dbo.CustomerCustomerDemo dbo.CustomerDemographics
2 dbo.CustomerCustomerDemo dbo.Customers
1 dbo.CustomerDemographics NULL
1 dbo.Customers NULL
1 dbo.Employees dbo.Employees
3 dbo.EmployeeTerritories dbo.Employees
3 dbo.EmployeeTerritories dbo.Territories
3 dbo.Order Details dbo.Orders
3 dbo.Order Details dbo.Products
2 dbo.Orders dbo.Customers
2 dbo.Orders dbo.Employees
2 dbo.Orders dbo.Shippers
2 dbo.Products dbo.Categories
2 dbo.Products dbo.Suppliers
1 dbo.Region NULL
1 dbo.Shippers NULL
1 dbo.Suppliers NULL
2 dbo.Territories dbo.Region
(19 row(s) affected)
CODO ERGO SUM
View 20 Replies
View Related
May 19, 2014
I've the table like
create table expense
(
bill_date datetime,
travel int,
fixed int,
food int,
lodge int
)
insert into expense values('01-04-2014',1200,250,0,0)
('02-04-2014','0',0,500,600)
('0-04-2014','800',300,0,0)
Like I've 30th onwards.....
Expecting o/p:
month Travel Fixed Food Lodge
apr-14 200 550 500 600
These cum column values how to make a code ?????
View 5 Replies
View Related
Feb 3, 2015
i wanna create a procedure for P& L cost sheet , i had done that procedure now include a cursor instead of replacing sql queries .
create procedure pl_test
@fmdate datetime,
@todate datetime,
@categ varchar(2000)
begin
create table #temp
[code]....
how to include cursor on if part and else part
View 2 Replies
View Related
Oct 15, 2014
This statement adds two additional months to which is fine :
DATENAME(MM,dd.date)+ ' ' + DATENAME(D,dd.date) + ', ' + DATENAME(YY,dd.date)
but if my month is November and two months is added, the year does not change, it stays the same. how do I make the year change when two months are added toward the end of the year.
View 7 Replies
View Related
Dec 4, 2014
I need to insert and update a table. One column of the table needs to conform to a format as: XXXXXXX.XXXXX
If any data is not complying to the format an error needs to be thrown.
How to implement this constraint!
View 3 Replies
View Related
Oct 8, 2015
I need DIFFERENCE('Kolton','Colton') to equal 4 rather than 3.
But SOUNDEX keeps the first letter of the word:
SOUNDEX('Kolton') = K435
SOUNDEX('Colton') = C435
View 6 Replies
View Related
Sep 4, 2014
I have a simple script where I want to pull GLAcct, GLDesc and Amounts by Period. I want my results to look like attached snip.
I tried playing around with the dates; however, I'm receiving errors. Just to note that when I ran for 07/01/14 - 07/31/14 with the transaction date in where clause I was able to retrieve the correct results. Now I want to expand to get a view set up for the whole year....automation!
select
gl_account.id as GLAcct,
gl_account.descr as GLDesc,
sum(gl_ledger.amount_n) as Net
from gl_account
[Code] ....
View 9 Replies
View Related
Feb 11, 2008
First, a couple of important bits of information. Until last week, I had never touched SISS, and therefore, I know very little about it. I just never had the need to use it...until now. I was able to convert my first 3 flat files to SQL2005 tables by right clicking on "SISS Package" and choosing "SISS Import and Export Wizard". That is the extent of my knowledge! So please, please, please be patient with me and be as descriptive as possible.
I thought I could attach some sample files to this post, but it doesn't look like I can. I'll just paste the information below in two separate code boxes. The first code box is the flat file specifications and the second one is a sample single line flat file similar to what I'm dealing with (the real flat file is over 2 gigs).
My questions are below the sample files.
Code Snippet
Record Length 400
Positions Length FieldName
Record Type 01
1,2 L=2 Record Type (Always "01")
3,12 L=10 Site Name
13,19 L=7 Account Number
20,29 L=10 Sub Account
30,35 L=6 Balance
36,37 L=1 Active
37,41 L=5 Filler
Record Type 02
1,2 L=2 Record Type (Always "02")
3,4 L=2 State
5,30 L=26 Address
31,41 L=11 Filler
Record Type 03
1,2 L=2 Record Type (Always "03")
3,6 L=4 Coder
7,20 L=14 Locator ID
21,22 L=2 Age
23,41 L=19 Filler
Record Type 04
1,2 L=2 Record Type (Always "04")
3,9 L=7 Process
10,19 L=10 Client
20,26 L=6 DOB
26,41 L=16 Filler
Record Type 05
1,2 L=2 Record Type (Always "05")
3,16 L=14 Guarantor
17,22 L=6 Guar Account
23,23 L=1 Active Guar
**There can be multiple 05 records, one for each Guarantor on the account**
and the single line flat file...
Code Snippet
01Site1 12345 0000098765 Y 02NY1155 12th Street 03ELL 0522071678 29 04TestingSmith,Paul071678 05Smith, Jane 445978N 05Smith, Julie 445989N 05Smith, Jenny 445915N 01Site2 12346 0000098766 N 02MN615 Woodland Ct 04InfoJones,Chris 012001 01Site3 12347 0000098767 Y 02IN89 Jade Street 03OWB 6429051282 25 04Screen New,Katie 879500
As you can see, each entry could have any number of records and multiples of some of the record types, with one exception, every entry must have a "01" record and can only have one "01" record. Oh, and each record has a length of 400.
I need to get this information into a SQL 2005 database so I can create a front end for accessing the data. Originally, I wanted one line for each account and have null values listed for entries that don't have a specific record. Now that I've looked at the data again, that doesn't look like a good idea. I think a better way to do it would be to create 5 different tables, one for each record type. However, records 2 through 5 don't have anything I can make a primary key. So here are my questions...
Is it possible to make 5 tables from this one file, one table for each of the record types?
If so, can I copy the Account number in record 01, position 13-19 in each of the subsequent record types (that way I could link the tables as needed)?
Can this be done using the SISS Import and Export Wizard to create the package? If not, I'm going to need some very basic step by step instructions on how to create the package.
Is SISS the best way to do this conversion or is there another program that would be better to use?
I know this is a huge question and I appreciate the help of anyone who boldly decides to help me! Thank you in advance and I welcome anyone's suggestions!
View 13 Replies
View Related
Apr 16, 2014
I have an source file and i have to load it into the data base by changing datatype of the columns in ssis
View 1 Replies
View Related
Nov 19, 2004
Hi All,
Any assistance would be greatly appreciated.
I have a current table which I create on a regular basis from a text file with a layout similar to this:
TypePolicy #AmountRider 1 AmtRider 2 Amt
B1112H24.341212.34
This text file is brought into a staging table with each field (even the amount field) as a varchar (12). I then assign types in a later step in my DTS package.
What I need to do is stack the riders under each policy so for each policy where there is a rider, there is a new row for every rider.
So in the example I've given, there would be 2 additional rows for the original first row since there are two riders.
TypePolicy #Amount
B1112H24.34
R11112H12
R21112H12.34
I plan on doing this by first creating a table with just the Type, Policy #, and Amt fields, and then using a series of insert queries where I take the rider (if there is one) and append it onto the table.
However, I'm getting the following error message when I try:
Server: Msg 213, Level 16, State 4, Line 1
Insert Error: Column name or number of supplied values does not match table definition.
Basically, it wouldn't let me put an 'R1' in the Type column.
How can I get this to work!?!?
Thanks in advance for your help
View 6 Replies
View Related
Mar 25, 2004
I want to use a Make Table Query to generate a new read-only Table. This I can do but my problem is that I want to be able to name the new Table containing only the records from a calendar year; e.y. Rents2001 and the next year another new table would be created and called Rents2002 and next year Rents2003 ...............
I require the Table to be generated yearly. I know I could do this in other ways but I really require the Table as once I have it I will be doing other things with it to give the final report.
Any suggestions how I can generate the Table with the YEAR being in the Table Name as part of running the Make Table Query? Thanks
View 4 Replies
View Related
Apr 27, 2007
What is the best way (short of backing up the entire DB) to make a copy of a Table so that It can be easily restored. We have a table that we want to make some serious changes to, but I want to make sure I can restore if if I need to (if the changes don't work)
View 10 Replies
View Related
Sep 19, 2007
Hi all, I have a problem. I need a query that blocks table and makes it unreadable. I decided to use WAITFOR to long blocking.
BEGIN TRAN myStopReadTrans
USE MyDatabase
SELECT * From dbo.AB with(readpast,updlock)
WAITFOR DELAY '1:00:00'
COMMIT
USE MyDatabase
Select name from dbo.Clients
View 10 Replies
View Related
Dec 31, 2007
Hi,
In my application i need to access mutiple table. I'm writing a stored procedure
in which i need to access tables such as TB01,TB02 .. Basically TBFY
where FY is parameter.
I tried for OPENQUERY, but that needs me to add a linked server, which i don't
think is a good idea.
Can anyone suggest on how can i do so?
I'm using SqlServer2000.
Thanks.
View 6 Replies
View Related
Nov 19, 2007
Hi,
I have a requirement where i need to build the table name dynamically in the following queries.
declare @REF_DATA_TYPE nvarchar(20)
set @REF_DATA_TYPE='COUNTRY'
these are 4 cases where i need to use the table name dynamically
1. IF exists(select 1 from 'MD_REF_'+@REF_DATA_TYPE where code=@code_T)
2. Update 'MD_TB_REF_'+@REF_DATA_TYPE
3. from @ACTUAL_DATA p join 'MD_REF_'+@REF_DATA_TYPE T on T.code=P.code
4. INSERT INTO 'MD_REF_'+@REF_DATA_TYPE(Code,[Name],Description)
But i am getting error when i do this.
Please let me know what to do to solve this
Thanks in advance
View 5 Replies
View Related
Feb 13, 2006
We have the following two tables :
Link ( GroupID int , MemberID int )
Member ( MemberID int , MemberName varchar(50), GroupID varchar(255) )
The Link table contains the records showing which Member is in which Group. One particular Member can be in
multiple Groups and also a particular Group may have multiple Members.
The Member table contains the Member's ID, Member's Name, and a Group ID field (that will contains comma-separated
Groups ID, showing in which Groups the particular Member is in).
We have the Link table ready, and the Member table' with first two fields is also ready. What we have to do now is to
fill the GroupID field of the Member table, from the Link Table.
For instance,
Read all the GroupID field from the Link table against a MemberID, make a comma-separated string of the GroupID,
then update the GroupID field of the corresponding Member in the Member table.
Please help me with a sql query or procedures that will do this job. I am using SQL SERVER 2000.
View 1 Replies
View Related
Mar 7, 2007
I want to execute a vb.net
file
When I make some changes in a table.
How can I handle this situation?
I am using sql server
2005 express edition sujith
View 1 Replies
View Related
Nov 7, 2007
I am doing a shopping basket type demo.I have difficulties going from table with fields like (Name,ProductCode) to table(Name, ProductCode, NumberOfItems). One way could be adding just Name and ProductCode fields and let NumberOfItems come automatically. It could have some common value like 1. How should I do this with VB.
View 1 Replies
View Related
Aug 30, 2003
Hello,
I want to take a table (SQL server) and show all it's data in a form of INSERT... (text file)
so that I can show it on a web-page and just paste the INSERT text into a MyTableData.sql
and then I can just run this script on the Query analyzer and fill my table with data
I need it so I can backup my DATA both in English and other languages...
(a replacement for the DTS packages that gives me hard time with the LOCALE / UNICODE translation)
did anyone already made such program ?
where can I find something like this ?
View 3 Replies
View Related
Mar 18, 2004
.
Hi,
How can I make relations between two tables using query? Tables are already there with data.
Regards
View 3 Replies
View Related
Mar 28, 2006
Hiim new to ms sql server, having previously used mysql. How do i make a auto number field? What datatype shall i use for it? like autonumber for mysql. Ive tried setting my primary key field to uniqueidentifier data type but then i still need to manually add a guid key in there. i want it so it automatically generates a unique key everytime i add a new row. is this possible?!hope someone can help!thanks
View 2 Replies
View Related
Jun 27, 2007
We have what I think is a pretty common setup for records with a dynamic set of descriptive fields. Something like:
People
PersonID PersonType
1 Consultant
2 Partner
PeopleFieldDefs
FieldDefID FieldName
1 FirstName
2 LastName
PeopleFields
PersonID FieldDefID FieldValue
1 1 John
1 2 Smith
2 1 Alice
2 2 Johnson
Of course, we need to be able to search and display this data in a tabular format like:
PersonID PersonType FirstName LastName
1 Consultant John Smith
2 Partner Alice Johnson
We have been building dynamic queries based on which fields are needed (users can select the fields), e.g.:
SELECT p.PersonID, p.PersonType, pf1.FieldValue AS 'First Name', pf2.FieldValue AS 'Last Name'
FROM People p
LEFT JOIN PeopleFields pf1 ON (p.PersonID=pf1.PersonID AND pf1.FieldDefID=1)
LEFT JOIN PeopleFields pf2 ON (p.PersonID=pf2.PersonID AND pf2.FieldDefID=2)
This is very flexible but slow. We've done lots of optimization but can't get this below 5 seconds for common scenarios.
So I'm back to the drawing board now trying to figure out a better way to approach this.
I'm wondering if it would be better (if even possible) to break this out into some sort of view or table UDF that would contain a full representation of all person data, pre-joined. Problem is, this is almost certainly going to have to involve dynamic SQL since we can't know anything about what fields are defined. I think that rules out any sort of view or table UDF, no?
Does anyone have a suggestion for a good approach? Thanks.
View 4 Replies
View Related
Jul 23, 2005
I have a scenario where two tables are in a One-to-Many relationshipand I need to move the data from the Many table to the One table sothat it becomes a One-to-One relationship.I need to salvage the records from the many table and without goinginto detail, one of the reasons I can't do the opposite asthere are records in the ONE table that I need to keep even if theydon't have any child records in the MANY table.Below I created the code to create the sample tables:1- tblProducts is the ONE side table2- tblProductDetails is the MANY side table3- tblProductsResult is the RESULT I expect to get after runningsome T-SQL code4- tblProductComponents is another MANY side table to tblProducts5- tblProductComponentsResult is the RESULT I expect to get...Some of the points to consider:6- Normally all UniqueID columns are to be IDENTITY. Forthis sample i am entering the UniqueID values myself.7- I don't want to create new tables like tblProductsResultand tblProductComponentsResult. I want to update the real tables.I have created the tblxxxResult tables only for this post.8- The goal is to update the name of the Product by giving it thename of the first matching Name from tblProductDetails.9- If there are more than one entry in tblProductDetails for eachProduct, then I need to create new Products inheriting the originalProduct's information including its child records from tblProductComponents.If you run the code and open the tables it will be much clearerto visually see what I want to achieve.CREATE DATABASE MyTestDBGOUSE MyTestDBGOCREATE TABLE [dbo].[tblProducts] ([UniqueID] [int] NOT NULL PRIMARY KEY ,[Name] [varchar] (80) NULL,[TagNo] [int] NULL) ON [PRIMARY]GOINSERT INTO tblProducts VALUES (1, 'ABC', 55)INSERT INTO tblProducts VALUES (2, 'DEF', 66)INSERT INTO tblProducts VALUES (3, 'GHI', 77)INSERT INTO tblProducts VALUES (4, 'JKL', 88)CREATE TABLE [dbo].[tblProductDetails] ([UniqueID] [int] NOT NULL PRIMARY KEY ,[Name] [varchar] (80) NULL,[ProductID] int) ON [PRIMARY]GOINSERT INTO tblProductDetails VALUES (1, 'ABC1', 1)INSERT INTO tblProductDetails VALUES (2, 'DEF', 2)INSERT INTO tblProductDetails VALUES (3, 'GHI', 3)INSERT INTO tblProductDetails VALUES (4, 'GHI2', 3)INSERT INTO tblProductDetails VALUES (5, 'GHI3', 3)INSERT INTO tblProductDetails VALUES (6, 'JKL2', 4)INSERT INTO tblProductDetails VALUES (7, 'JKL', 4)INSERT INTO tblProductDetails VALUES (8, 'JKL3', 4)INSERT INTO tblProductDetails VALUES (9, 'JKL4', 4)CREATE TABLE [dbo].[tblProductComponents] ([UniqueID] [int] NOT NULL PRIMARY KEY ,[ProductID] int,[Component] [varchar] (80) NULL) ON [PRIMARY]GOINSERT INTO tblProductComponents VALUES (1, 1, 'ABCa')INSERT INTO tblProductComponents VALUES (2, 1, 'ABCb')INSERT INTO tblProductComponents VALUES (3, 1, 'ABCc')INSERT INTO tblProductComponents VALUES (4, 2, 'DEFa')INSERT INTO tblProductComponents VALUES (5, 2, 'DEFb')INSERT INTO tblProductComponents VALUES (6, 2, 'DEFc')INSERT INTO tblProductComponents VALUES (7, 2, 'DEFd')INSERT INTO tblProductComponents VALUES (8, 3, 'GHIa')INSERT INTO tblProductComponents VALUES (9, 4, 'JKLa')INSERT INTO tblProductComponents VALUES (10, 4, 'JKLb')CREATE TABLE [dbo].[tblProductComponentsResult] ([UniqueID] [int] NOT NULL PRIMARY KEY ,[ProductID] int,[Component] [varchar] (80) NULL) ON [PRIMARY]GOINSERT INTO tblProductComponentsResult VALUES (1, 1, 'ABCa')INSERT INTO tblProductComponentsResult VALUES (2, 1, 'ABCb')INSERT INTO tblProductComponentsResult VALUES (3, 1, 'ABCc')INSERT INTO tblProductComponentsResult VALUES (4, 2, 'DEFa')INSERT INTO tblProductComponentsResult VALUES (5, 2, 'DEFb')INSERT INTO tblProductComponentsResult VALUES (6, 2, 'DEFc')INSERT INTO tblProductComponentsResult VALUES (7, 2, 'DEFd')INSERT INTO tblProductComponentsResult VALUES (8, 3, 'GHIa')INSERT INTO tblProductComponentsResult VALUES (9, 4, 'JKLa')INSERT INTO tblProductComponentsResult VALUES (10, 4, 'JKLb')INSERT INTO tblProductComponentsResult VALUES (11, 5, 'GHIa')INSERT INTO tblProductComponentsResult VALUES (12, 6, 'GHIa')INSERT INTO tblProductComponentsResult VALUES (13, 7, 'JKLa')INSERT INTO tblProductComponentsResult VALUES (14, 7, 'JKLb')INSERT INTO tblProductComponentsResult VALUES (15, 8, 'JKLa')INSERT INTO tblProductComponentsResult VALUES (16, 8, 'JKLb')INSERT INTO tblProductComponentsResult VALUES (17, 9, 'JKLa')INSERT INTO tblProductComponentsResult VALUES (18, 9, 'JKLb')CREATE TABLE [dbo].[tblProductsResult] ([UniqueID] [int] NOT NULL PRIMARY KEY ,[Name] [varchar] (80) NULL,[TagNo] [int] NULL) ON [PRIMARY]GOINSERT INTO tblProductsResult VALUES (1, 'ABC1', 55)INSERT INTO tblProductsResult VALUES (2, 'DEF', 66)INSERT INTO tblProductsResult VALUES (3, 'GHI', 77)INSERT INTO tblProductsResult VALUES (4, 'JKL', 88)INSERT INTO tblProductsResult VALUES (5, 'GHI2', 77)INSERT INTO tblProductsResult VALUES (6, 'GHI3', 77)INSERT INTO tblProductsResult VALUES (7, 'JKL2', 88)INSERT INTO tblProductsResult VALUES (8, 'JKL3', 88)INSERT INTO tblProductsResult VALUES (9, 'JKL4', 88)I appreciate your assistance on this.Thank you very much
View 6 Replies
View Related
Nov 11, 2006
Hi
In access i can make a rule
like if i have a Coloumn to date
i can make a rule to say that this fields data
shall be > date
can i do this also in sql and how?
regards
alvin
View 1 Replies
View Related
Aug 4, 2015
I would like to create a procedure which create views by taking parameters the table name and a field value (@Dist).
However I still receive the must declare the scalar variable "@Dist" error message although I use .sp_executesql for executing the particularized query.
Below code.
ALTER Procedure [dbo].[sp_ViewCreate]
/* Input Parameters */
@TableName Varchar(20),
@Dist Varchar(20)
AS
Declare @SQLQuery AS NVarchar(4000)
Declare @ParamDefinition AS NVarchar(2000)
[code]....
View 9 Replies
View Related
Mar 17, 2014
This seems simple enough but for some reason, my brain isn't working.
I have a lookup table:
Table A: basically dates every 30 days
1/1/2014
2/3/2014
3/3/2014
4/3/2014
I have Table b that has records and dates created assocated with each record
I want all records that fall between the 1st 30 days to have an additional column that indicates 30
union
records with additional column indicating 60 days that fall between the 30 and 60 day
union
records with additional column indicating 90days that fall between the 60 and 90 day mark.
Is there an easy way to do this?
View 6 Replies
View Related
Nov 25, 2007
Ok, here is my situation.....
When someone navigates to a user's profile page on my site, I present them with a slideshow of the user's photos using the AJAX slideshow extender. I obtain the querystring value in the URL (to determine which user's page I'm on) and feed that into a webservice via a context value where an array of photos is created for the slideshow. Now, in order to create the array's size, I do a COUNT of all of that specific user's photos. Then, I run another SQL statement to obtain the path of those photos in the file system. However, during the time of that first SQL query's execution (the COUNT statement) to the time of the second SQL query (getting the paths of the photos), the owner of that profile may upload or delete a photo from his profile. I understand this would be a very rare occurrence since SQL statements 1 and 2 will be executed within milliseconds of each other, but it is still possible I suppose. When this happens, when I try to populate the array, either the array will be too small or too large. I'm using SqlDataReader for this as it seems to be less memory and resource intensive than datasets, but I could be wrong since I'm a relative beginner and newbie. This is what I have in my vb file for the webservice.....Public Function GetSlides(ByVal contextKey As String) As AjaxControlToolkit.Slide() Dim dbConnection As New SqlConnection("string for the data source, etc.") Try dbConnection.Open() Dim memberId = CInt(contextKey) Dim photoCountLookupCmd As New SqlCommand _ ("SELECT COUNT(*) FROM Photo WHERE memberId = " & memberId, dbConnection) Dim thisReader As SqlDataReader = photoCountLookupCmd.ExecuteReader() Dim photoCount As Integer While (thisReader.Read()) photoCount = thisReader.GetInt32(0) End While thisReader.Close() Dim MySlides(photoCount - 1) As AjaxControlToolkit.Slide Dim photoLookupCmd As New SqlCommand _ ("SELECT fullPath FROM Photo WHERE memberId = " & memberId, dbConnection) thisReader = photoLookupCmd.ExecuteReader()
Dim i As Integer For i = 0 To 2 thisReader.Read() Dim photoUrl As String = thisReader.GetString(0) MySlides(i) = New AjaxControlToolkit.Slide(photoUrl, "", "") Next i thisReader.Close() Return MySlides Catch ex As SqlException Finally dbConnection.Close()
End Try
End FunctionI'm trying to use the most efficient method to interact with the database since I don't have unlimited hardware and there may be moderate traffic on the site. Is SqlDataReader the way to go or do I use something else? If I do use SqlDataReader, can someone show me how I can run those 2 SQL statements in best practice? Would I have to somehow lock writing to that table when I start the first SQL statement, then release the lock after I execute the second SQL statement? What's the best practice in this kind of scenario.
Thanks in advance.
View 3 Replies
View Related
Mar 26, 2008
I'm having trouble creating a temp table out of a select statement that uses multipe union alls.
Here's what I have, I'm trying to get the results of this query into a temp table...
select
parent,
(select cst_id from co_customer (nolock) where cst_key = Parent) as cst_id,
(select cst_name_cp from co_customer (nolock) where cst_key = Parent) as cst_name_cp,
(select org_total_assets_ext from dbo.co_organization_ext where org_cst_key_ext = parent) as Parent_Total_assets,
sum(own_assets) as Total_child_own_assets
from
(
Select parent,
Child,
(select org_own_assets_ext from dbo.co_organization_ext where org_cst_key_ext = child) as Own_assets
from
(Select Cst_key as Child,
dbo.return_org_parent(cst_key,0,1) as Parent
from co_customer (nolock)
where cst_type = 'Organization'
and cst_delete_flag = 0
and dbo.return_org_parent(cst_key,0,1) is not null
union all
Select Cst_key as Child,
dbo.return_org_parent(cst_key,0,2) as Parent
from co_customer (nolock)
where cst_type = 'Organization'
and cst_delete_flag = 0
and dbo.return_org_parent(cst_key,0,2) is not null
union all
Select Cst_key as Child,
dbo.return_org_parent(cst_key,0,3) as Parent
from co_customer (nolock)
where cst_type = 'Organization'
and cst_delete_flag = 0
and dbo.return_org_parent(cst_key,0,3) is not null
union all
Select Cst_key as Child,
dbo.return_org_parent(cst_key,0,4) as Parent
from co_customer (nolock)
where cst_type = 'Organization'
and cst_delete_flag = 0
and dbo.return_org_parent(cst_key,0,4) is not null
union all
Select Cst_key as Child,
dbo.return_org_parent(cst_key,0,5) as Parent
from co_customer (nolock)
where cst_type = 'Organization'
and cst_delete_flag = 0
and dbo.return_org_parent(cst_key,0,5) is not null
union all
Select Cst_key as Child,
dbo.return_org_parent(cst_key,0,6) as Parent
from co_customer (nolock)
where cst_type = 'Organization'
and cst_delete_flag = 0
and dbo.return_org_parent(cst_key,0,6) is not null
union all
Select Cst_key as Child,
dbo.return_org_parent(cst_key,0,7) as Parent
from co_customer (nolock)
where cst_type = 'Organization'
and cst_delete_flag = 0
and dbo.return_org_parent(cst_key,0,7) is not null )as c
) as d
group by parent
having sum(own_assets) <> (select org_total_assets_ext from dbo.co_organization_ext where org_cst_key_ext = parent)
View 8 Replies
View Related
Apr 16, 2008
HI
I have the following scenario in my report.
-The data is displayed in a table
-The table groups by one field
-Each table row calls a subreport
-There is about 6 paramaters in the report
-The last paramater of the list of paramters is a multivalue paramater and based on what is selected in the list the corresponding subreport must be shown.
-So i use a custom vbscript funtion to determine if a specific value was selected or not.
This functionality is working fine.
My problem is if the user does not select all the values in the multi select then i want to make the row invisble
and remove the whitespace so that there is not a gap between the other subreports which is shown.
I can make the subreport invisible inside the row but there is still the white space which does not display very nicly.
How can i make the row invisible if the vbscript function that is called returns a false value?
Here is the funtion I call -> Code.InArray("ValueToSearchFor", Parameters!MultiValueDropDown.Value)
The Function returns a true or false.
Thanks.
View 3 Replies
View Related
Dec 19, 2005
Hi all,
Iīm a real SQL rookie and after two days of intensive research and reading of dozens of articles and web pages a bit confused about all that knowledge concerning database technology ... and I only want to do something very easy ...
Now, my question is, how can I produce a diagram from that data, that is stored in a table, maybe called "DiagData1"?
I have installed SQL Server Express 2005 and Visual Basic Express 2005 and did some first steps with it as where shown in an brilliant tutorial that Iīve found on a Microsoft (?) webpage. It shows the complete way building a database and connecting it to a form which was build with Visual Basic. So all what is left is find out how the database tables are connected to a diagram.
thanksīn greets,
VBFan
View 3 Replies
View Related
