I want make table 4 like this one in SQL server 2005

I want make table 4 like this one in SQL server 2005

• diego jaramillo

  
   
  WOW It's realy works.
  
  I cant belive it .
  
  thanks . thanks For All.
  
  I got my Answer . And I am very happy.
  
  

• Group By Expressions

  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)
  
  

• tgallagher

  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.

  
  
  

• David Deen

  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.

  
  
  

• SmartDumb

  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.
  

  
  
  

• hvymtl

  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!!!
  
  

• Ken Morley

  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.
  

  
  
  

• Ian Bavey - MSFT

  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!
  
  

• davide_c

  create view MyViewName as
  
  [enter select sql here]
  
  great examples in the help files
  
  

• PatGO

  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.
  
  

• QingY

  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]

  
  
  

• gamesplant

  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]
  

  
  
  
• I want make table 4 like this one in SQL server 2005