Dynamic Order by clause

Question

Post reply

Dynamic Order by clause

deepa_nandan

SSC Enthusiast

Points: 119
More actions
March 23, 2006 at 8:12 am

#171008

I have a table REF_Preferance which as three rows in it, values are Schd_dt,Plan_dt,Ship_dt. Now each of our business unit has a copy of this table. Based on what sequence these rows come in their local copy of the table, that's how order by should happen in the actual PROD_Scheduled table where Ship_dt,Plan_dt and Schd_dt are actual fields in PROD_Scheduled table. Also since an insert is involved I could not use dynamic SQL. This is what I am looking for.. If my local Ref table has Ship_dt,Schd_dt, Plan_dt in that order then I should be able to Insert into PROD_Monthly Select * from PROD_Scheduled Order by Ship_dt,Schd_dt, Plan_dt.
Please let me know how to do this....

Raghu M Nandan

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Ray M SSC-Insane Points: 21093 More actions · Answer 1

Is this what your trying to explain

-- One table with only 3 rows?

Create table #Ref_Preference (Field varchar(50), Value varchar(10))

insert into #Ref_Preference (Field, Value)

select 'Schd_dt', 'Foo' union

select 'Plan_dt', 'Bar' union

select 'Ship_dt', 'BarFoo'

-- One Table with 3 columns?

Create table #PROD_Scheduled (Schd_dt varchar(10), Plan_dt varchar(10), Ship_dt varchar(10))

-- Insert each row into its own column?

insert into #PROD_Scheduled (Schd_dt, Plan_dt, Ship_dt)

select min(case Field when 'Schd_dt' then value end) as Schd_dt,

min(case Field when 'Plan_dt' then value end) as Plan_dt,

min(case Field when 'Ship_dt' then value end) as Ship_dt

from #Ref_Preference

select * from #PROD_Scheduled

Note this works only if the table has 3 rows. If this is not what your trying to accomplish can you provide some examples.

Thanks

deepa_nandan SSC Enthusiast Points: 119 More actions · Answer 2

Hi Ray,

Actually I have:

/** first table; each business unit has a copy where OrderID,OrderName combination is in any combination ***/

Create Table #Ref_Preferance(OrderID smallint not null

,OrderName varchar(10) Not Null)

Insert into #Ref_Preferance (OrderID,OrderName)

Select 1,'Schd_dt'

select 2,'Plan_dt'

select 3,Ship_dt'

-- Second table

Create table #PROD_Scheduled (Part_Num varchar(10),Lot_Num varchar(10),Quantity numeric(5,2), CustomerID smallint,

Schd_dt varchar(10), Plan_dt varchar(10), Ship_dt varchar(10) )

---- third table

Create table #PROD_Monthly (Part_Num varchar(10),Lot_Num varchar(10),Quantity numeric(5,2), CustomerID smallint,

Schd_dt varchar(10), Plan_dt varchar(10), Ship_dt varchar(10) )

--this is what I am trying to do

insert into #Prod_Monthly

select * from #Prod_Scheduled

ORDER BY Schd_dt,Plan_Dt,Ship_Dt

because that is how it is sequenced in #Ref_Preferance table for this business unit, someone else may have in their copy

of #Ref_Preferance as

OrderID OrderName

1 Ship_dt,

2 Schd_dt,

3 Plan_dt

for that business unit my order by should be

Order by Ship_dt,Schd_dt,Plan_dt in the insert statement above.

Hope this helps

Thanks a lot

Raghu M Nandan