Using awk to sort SQL Statements 
Author Message
 Using awk to sort SQL Statements

I'm new to awk and find it very useful for working in both the NT and
Unix environments, but receintly I've come across a problem that I
have not been able to solve. SQL Server generates views in no apparent
order. It does not check to see if the view that it's creating depends
on a view that exists later in the script. Let me give you an example.

SQL SCRIPT ---------------------------------
CREATE VIEW dbo.vw_Properties_Fix_PR
AS
SELECT     TOP 100 PERCENT Prop#, PropertyName, Division, ROC, State,
Prop_Statu
s, Prop_Type, Filing_Location, Payroll_Processor,
LEN(Payroll_Processor)
                      AS Len, CHARINDEX(',', Payroll_Processor) AS
Comma, LEN(Pa
yroll_Processor) - CHARINDEX(',', Payroll_Processor) AS Diff
FROM         dbo.vw_All_Props
WHERE     (Payroll_Processor IS NOT NULL)
ORDER BY Payroll_Processor

APPEARS BEFORE ---------------------------------
CREATE VIEW dbo.vw_All_Props_Org
AS
SELECT tb_PS_All_Properties.Prop#,
    tb_PS_All_Properties.PropertyName,
    LEFT(tb_PS_Org_Structure.PA_NODE_DESCR, CHARINDEX('-',
    tb_PS_Org_Structure.PA_NODE_DESCR) - 2) AS Division,
    RIGHT(tb_PS_Org_Structure.PA_NODE_DESCR,
    LEN(tb_PS_Org_Structure.PA_NODE_DESCR) - CHARINDEX('-',
    tb_PS_Org_Structure.PA_NODE_DESCR) - 1) AS DVP,
    LEFT(tb_PS_Org_Structure1.PA_NODE_DESCR, CHARINDEX('-',
    tb_PS_Org_Structure1.PA_NODE_DESCR) - 2) AS ROC,
    RIGHT(tb_PS_Org_Structure1.PA_NODE_DESCR,
    LEN(tb_PS_Org_Structure1.PA_NODE_DESCR)
    - CHARINDEX('-', tb_PS_Org_Structure1.PA_NODE_DESCR) - 1)
    AS RVP, tb_PS_Org_Structure2.PA_NODE_DESCR AS RPM,
    tb_PS_Org_Structure1.PA_NODE_DESCR AS comboROC,
    tb_PS_Org_Structure.PA_NODE_DESCR AS comboDivision
FROM tb_PS_Org_Structure RIGHT OUTER JOIN
    tb_PS_All_Properties ON
    tb_PS_Org_Structure.PropertyID = tb_PS_All_Properties.Prop# LEFT
     OUTER JOIN
    tb_PS_Org_Structure tb_PS_Org_Structure2 ON
    tb_PS_All_Properties.Prop# = tb_PS_Org_Structure2.PropertyID LEFT
     OUTER JOIN
    tb_PS_Org_Structure tb_PS_Org_Structure1 ON
    tb_PS_All_Properties.Prop# = tb_PS_Org_Structure1.PropertyID
WHERE (tb_PS_Org_Structure.LEVEL_DESCR = 'SupRoc') AND
    (tb_PS_Org_Structure1.LEVEL_DESCR = 'Roc') AND
    (tb_PS_Org_Structure2.LEVEL_DESCR = 'RPM')

WHICH IS REQUIRED TO BUILD THE ABOVE STATEMENT.

I need awk to break sql statements into their componet parts and sort
them based on the content of other blocks.

Normally I have to run this file and based on the error messages move
views from the body of the script to the top until the correct order
is established.

Fun ain't it?



Wed, 31 Dec 2003 05:02:00 GMT  
 
 [ 1 post ] 

 Relevant Pages 

1. Advise on using awk to sort and give totals

2. Using a variable in an awk statement.

3. : using sort in awk W/O tmp file.

4. How can I sort a fixed width file using awk on a HP unix systems

5. Generate SQL statements using Templates

6. Binary Sort/Merge Sort in awk

7. SQL Select Statement Help - SQL Gurus!

8. Outputting info from awk and using the info to name the awk output file

9. data to sql server using labview and labview sql toolkit

10. how to access SQL server using SQL toolkit from LabVIEW

11. Using SQL Anywhere (Watcom SQL 5.0) with REXX

12. Using SQL Anywhere (Watcom SQL 5.0) with REXX

 

 
Powered by phpBB® Forum Software