I Have 3 table Like this:
Table 1:
Name = Tbl_Mian_code
| M_Code | Master_Code | Master_Name | Main_Type | Main_Gruop | Main_Txt |
| 1001 | 1001 | Bank | 01 | 01 | |
| 1002 | 1002 | Cash | 01 | 02 | |
| 1003 | 1003 | Good's | 02 | 01 | |
Table 2:
Name = Tbl_Mian_code
| Id_2 | Main_Code | Sub_Code | Master_Code | Master_Name | Sub_Txt |
| 1 | 1001 | 0001 | 10010001 | Bank_Visa | |
| 4 | 1001 | 0002 | 10010002 | Bank_234 | |
| 5 | 1002 | 0001 | 10020001 | Cash_1 | |
| 6 | 1002 | 0002 | 10020002 | Cash_2 | |
| 7 | 1003 | 0001 | 10030001 | Nokia | |
| 9 | 1003 | 0002 | 10030002 | Samsung | |
Table 3:
Name = Tbl_Sub_T
| Id_3 | Sub_Master Code | Sub_T_Code | Master_Code | Master_Name | Sub_T_Txt |
| 1 | 10030001 | 0001 | 100300010001 | Nokia 6600 | |
| 2 | 10030001 | 0002 | 100300010002 | Nokia 6630 | |
| 3 | 10030001 | 0003 | 100300010003 | Nokia 6230 | |
| 4 | 10030002 | 0001 | 100300020001 | Samsung T 60 | |
| 5 | 10030002 | 0002 | 100300020002 | Samsung E500 | |
| 6 | 10030002 | 0003 | 100300020003 | Samsung E700 | |
I want make table 4 like this one in SQL server 2005 or 2000
| Master_Code | Master_Name |
| 1001 | Bank |
| 10010001 | Bank_Visa |
| 10010002 | Bank_234 |
| 1002 | Cash |
| 10020001 | Cash_1 |
| 10020002 | Cash_2 |
| 1003 | Good's |
| 10030001 | Nokia |
| 100300010001 | Nokia 6600 |
| 100300010002 | Nokia 6630 |
| 100300010003 | Nokia 6230 |
| 10030002 | Samsung |
| 100300020001 | Samsung T 60 |
| 100300020002 | Samsung E500 |
| 100300020003 | Samsung E700 |
The sub code is unique but depends on main code. As you see master Code is unique.
How I can generate it by function in SQL.
And How to make table 4
Say me how.
Tanks For your help.
Make table 1, 2, 3 by this code & help me to make table 4 with your code.
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Tbl_Sub_Code_Tbl_Mian_code]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Tbl_Sub_Code] DROP CONSTRAINT FK_Tbl_Sub_Code_Tbl_Mian_code
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Tbl_Sub_Code]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Tbl_Sub_Code]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Tbl_Mian_code]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Tbl_Mian_code]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Tbl_Sub_T]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Tbl_Sub_T]
GO
CREATE TABLE [dbo].[Tbl_Mian_code] (
[Main_Code] [char] (4) COLLATE Arabic_CI_AS NOT NULL ,
[Master_Code] AS ([Main_code]) ,
[Name] [nvarchar] (255) COLLATE Arabic_CI_AS NOT NULL ,
[Main_Type] [char] (2) COLLATE Arabic_CI_AS NULL ,
[Main_Gruop] [char] (2) COLLATE Arabic_CI_AS NULL ,
[Main_Txt] [text] COLLATE Arabic_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [dbo].[Tbl_Sub_T] (
[Id_3] [int] NOT NULL ,
[Sub_Master_Code] [char] (8) COLLATE Arabic_CI_AS NOT NULL ,
[Sub_T_Code] [char] (4) COLLATE Arabic_CI_AS NOT NULL ,
[Master_Code] AS ([sub_master_code] + [sub_T_code]) ,
[Master_Name] [nvarchar] (255) COLLATE Arabic_CI_AS NOT NULL ,
[Sub_T_Txt] [ntext] COLLATE Arabic_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [dbo].[Tbl_Sub_Code] (
[Id_2] [int] IDENTITY (1, 1) NOT NULL ,
[Main_Code] [char] (4) COLLATE Arabic_CI_AS NOT NULL ,
[Sub_Code] [char] (4) COLLATE Arabic_CI_AS NOT NULL ,
[Master_Code] AS ([main_code] + [sub_code]) ,
[Master_Name] [nvarchar] (255) COLLATE Arabic_CI_AS NOT NULL ,
[Sub_Txt] [ntext] COLLATE Arabic_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[Tbl_Mian_code] WITH NOCHECK ADD
CONSTRAINT [PK_Tbl_Mian_code] PRIMARY KEY CLUSTERED
(
[Main_Code]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Tbl_Sub_T] WITH NOCHECK ADD
CONSTRAINT [PK_Tbl_Sub_T] PRIMARY KEY CLUSTERED
(
[Id_3]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Tbl_Sub_Code] WITH NOCHECK ADD
CONSTRAINT [PK_Tbl_Sub_Code] PRIMARY KEY CLUSTERED
(
[Id_2]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Tbl_Sub_Code] WITH NOCHECK ADD
CONSTRAINT [IX_Tbl_Sub_Code] UNIQUE NONCLUSTERED
(
[Master_Code]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Tbl_Sub_Code] ADD
CONSTRAINT [FK_Tbl_Sub_Code_Tbl_Mian_code] FOREIGN KEY
(
[Main_Code]
) REFERENCES [dbo].[Tbl_Mian_code] (
[Main_Code]
)
GO
looks like a simple union view. . .
caveat emptor (not checked, just hacked)!!!
select [master_code], [master_name]
from
(
select [master_code], [master_name],
cast([master_code] as int) [master_sort],
0 [detail_sort],
0 [sub_detail_sort] from [Tbl_Mian_code]
union
select [detail].[master_code], [detail].[master_name],
cast([master].[master_code] as int),
cast([detail].[sub_code] as int),
0
from [Tbl_Mian_code] [master] inner join [Tbl_sub_code] [detail]
on [master].[master_code] = [detail].[main_code]
union
select [subdetail].[master_code], [subdetail].[master_name],
cast([master].[master_code] as int),
cast([detail].[sub_code] as int),
cast([subdetail].[sub_t_code] as int)
from (Tbl_Mian_code [master] inner join [Tbl_sub_code] [detail]
on [master].[master_code] = [detail].[main_code]) inner join [Tbl_sub_t] [subdetail]
on [detail].[Master_Code] = [subdetail].[Sub_Master_Code]
)temp
order by [master_sort], [detail_sort], [sub_detail_sort]
|||
Hi .
That's realy work . tanks.
But How I can use it in progaming?
If it was table I can make a relation. and load it in combo box.
whit this code how I can use it?
I use VB.NET 2005 Beta 2 ( Non express )
Thanks for your help.
|||well, from a .Net 1.1 viewpoint as I am not yet doing much with 2.0.
given that sql is set to the above select statement and aCon is an initialized SQLConnection:
Dim da as SQLDataAdapter = new SQLDataAdapter(sql, aCon)
dim ds as DataSet = new DataSet()
da.Fill(da)|||Hi
Tnanks for your answer.
But let me ask in other way .
how I can save code you write as a view?
I khow how to load a view or table in windows form.
but I can't save your code as View!!!|||create view MyViewName as
[enter select sql here]
great examples in the help files|||
Hi
I use this code to make view
create view My_Code as
select [master_code], [master_name]
from
(
select [master_code], [master_name],
cast([master_code] as int) [master_sort],
0 [detail_sort],
0 [sub_detail_sort] from [Tbl_Mian_code]
union
select [detail].[master_code], [detail].[master_name],
cast([master].[master_code] as int),
cast([detail].[sub_code] as int),
0
from [Tbl_Mian_code] [master] inner join [Tbl_sub_code] [detail]
on [master].[master_code] = [detail].[main_code]
union
select [subdetail].[master_code], [subdetail].[master_name],
cast([master].[master_code] as int),
cast([detail].[sub_code] as int),
cast([subdetail].[sub_t_code] as int)
from (Tbl_Mian_code [master] inner join [Tbl_sub_code] [detail]
on [master].[master_code] = [detail].[main_code]) inner join [Tbl_sub_t] [subdetail]
on [detail].[Master_Code] = [subdetail].[Sub_Master_Code]
)temp
order by [master_sort], [detail_sort], [sub_detail_sort]
and I got this Erorr
Server: Msg 1033, Level 15, State 1, Procedure MyViewName, Line 26
The ORDER BY clause is invalid in views, inline functions, derived tables, and subqueries, unless TOP is also specified.
|||ORDER BY clause is only relevant in the outer-most query that returns the results. You can use it in derived tables or sub-queries or views or inline TVFs with TOP clause. In your example, you have to query the view with the appropriate ORDER BY clause otherwise there is no guarantee that the results will be returned in the order requested even if you include TOP in the view definition. This may have worked in the past with SQL Server 2000 but it will not when you upgrade to SQL Server 2005.|||
Hi
Thanks for your answer.
If it is not possble in SQL server 2005 what I Can do in SQL server 2005?
what I can do In SQL server 2000?
I Can chenge My program to Use SQL server 2000. but I need a view that showed in my frist post.
How I can make it . I need that table . the way of making it is not major.
by the way . I need to use that table very much in my program . ( may be 1 time evry min ) but my table 1 . 2 . 3 dont change very much. ( once when a Good's add or . . . )
the performance is my main problem too.
|||
You can use the view without any problem. You just need to remove the ORDER BY clause and put in the SELECT statement that is used to query the view. This will work fine both in SQL Server 2000 and 2005.
|||
WOW It's realy works.I cant belive it .
thanks . thanks For All.
I got my Answer . And I am very happy.
|||I think you can do:
create view My_Code as
select TOP 100 PERCENT [master_code], [master_name]
from
(
select [master_code], [master_name],
cast([master_code] as int) [master_sort],
0 [detail_sort],
0 [sub_detail_sort] from [Tbl_Mian_code]
union
select [detail].[master_code], [detail].[master_name],
cast([master].[master_code] as int),
cast([detail].[sub_code] as int),
0
from [Tbl_Mian_code] [master] inner join [Tbl_sub_code] [detail]
on [master].[master_code] = [detail].[main_code]
union
select [subdetail].[master_code], [subdetail].[master_name],
cast([master].[master_code] as int),
cast([detail].[sub_code] as int),
cast([subdetail].[sub_t_code] as int)
from (Tbl_Mian_code [master] inner join [Tbl_sub_code] [detail]
on [master].[master_code] = [detail].[main_code]) inner join [Tbl_sub_t] [subdetail]
on [detail].[Master_Code] = [subdetail].[Sub_Master_Code]
)temp
order by [master_sort], [detail_sort], [sub_detail_sort]
|||Oh yeah. . .
I want to say:
'Way To Go!!! Including the script to create the tables you are working with!!!'
Thats the way to do it if you really want help!
cheers and good luck!
