Solved

Improving SQL

Posted on 2010-11-25
12
346 Views
Last Modified: 2012-06-27
I have the following scenario:
Title: 00000@0@ABC@0000-000@Test2
Title: 000010@10@ABCD@0200-000@Test321

result should be:
column A        column B     column C    column D    column E
------------       ------------    ------------   -------------  -----------
00000                   0              ABC          0000-000     Test2
000010              10              ABCD        0200-000      Test321

... which I used the following SQL and is working:
      SELECT
      'PubAtom' as 'Source',            
    'Standard' as 'Transaction Type',
      'PUBATOM' as 'Supplier Site',
      convert(varchar(10), InvoiceDate, 111) as 'Invoice Date',
      DisplayNumber as 'Invoice Number',
      'EUR' as 'Invoice Currency',
      0.00 as 'Invoice Amount',
      'GL Date' = convert(varchar(10), InvoiceDate, 111),      
      'Immediate' as 'Payment Terms',
      'Clearing' as 'Payment Method',
      'Internal Clearing' as 'Pay Group',
      'CUST REF:'+CustomerReference+' | Discount: '+convert(varchar(255),Discount) +
      ' | Shipping: '+convert(varchar(255),ShippingCost) +
      ' | DISP: '+(select d.DispatchTypeDescription from DispatchType d where d.DispatchTypeId = I.DispatchTypeId) +
      ' | Weight: '+ ' | Bill To: '+ dbo.GetCustomerName(BillToCustomerNumber)+'; '+dbo.GetCustomerAddress(BillToCustomerNumber) +
      ' | Ship To:'+ case when dbo.GetCustomerName(ShipToCustomerNumber) is null then ''
                                         else dbo.GetCustomerName(ShipToCustomerNumber)
                         end +
      '; '+case when dbo.GetCustomerAddress(ShipToCustomerNumber)  is null then ''
                                         else dbo.GetCustomerAddress(ShipToCustomerNumber)
                         end
      as 'Header Description',
      null as 'Payment Number',
    null as 'Remit-TO Supplier Name',
      null as 'Remit-TO Supplier Site',
    1 as 'Line Number',
      'Item' as 'Line Type',
      TotalCost/2 as Amount,
      null as 'Line Description', --Rever
      null as 'GL Reconciliation Ref',
      null as 'PO Number',
      null as 'PO Line Number',
      null as 'PO Shipment Number',
      null as 'PO Distribution Number',      
                  
      (SELECT
                  max(case when f.row_num = 1 then f.value end)
            FROM
            Invoice I2
            cross apply dbo.ParmsToList(SUBSTRING(PaymentTextDescription, 20,CHARINDEX('$', PaymentTextDescription)), '$' ) f
            WHERE
                  I2.invoicenumber = I.invoicenumber and
                  I2.PaymentTextDescription like 'Charged to project%' and
                  I2.PaymentTextId = 2 and
                  I2.InvoiceTypeCode = 1
                  and dbo.CountNumberCharacterInString(PaymentTextDescription,N'$', null) = 4) 'Project Number',

      (SELECT
                  max(case when f.row_num = 2 then f.value end)
            FROM
            Invoice I2
            cross apply dbo.ParmsToList(I2.PaymentTextDescription, '$' ) f
            WHERE
                  I2.invoicenumber = I.invoicenumber and
                  I2.PaymentTextDescription like 'Charged to project%' and
                  I2.PaymentTextId = 2 and
                  I2.InvoiceTypeCode = 1      
                  and dbo.CountNumberCharacterInString(PaymentTextDescription,N'$', null) = 4) 'Task Number',

      (SELECT
                  max(case when f.row_num = 3 then f.value end)
            FROM
            Invoice I2
            cross apply dbo.ParmsToList(I2.PaymentTextDescription, '$' ) f
            WHERE
                  I2.invoicenumber = I.invoicenumber and
                  I2.PaymentTextDescription like 'Charged to project%' and
                  I2.PaymentTextId = 2 and
                  I2.InvoiceTypeCode = 1      
                  and dbo.CountNumberCharacterInString(PaymentTextDescription,N'$', null) = 4) 'Award Number',

      (SELECT
                  max(case when f.row_num = 4 then f.value end)
            FROM
            Invoice I2
            cross apply dbo.ParmsToList(I2.PaymentTextDescription, '$' ) f
            WHERE
                  I2.invoicenumber = I.invoicenumber and
                  I2.PaymentTextDescription like 'Charged to project%' and
                  I2.PaymentTextId = 2 and
                  I2.InvoiceTypeCode = 1      
                  and dbo.CountNumberCharacterInString(PaymentTextDescription,N'$', null) = 4) 'Expenditure Type',

      (SELECT
                  max(case when f.row_num = 5 then f.value end)
            FROM
            Invoice I2
            cross apply dbo.ParmsToList(I2.PaymentTextDescription, '$' ) f
            WHERE
                  I2.invoicenumber = I.invoicenumber and
                  I2.PaymentTextDescription like 'Charged to project%' and
                  I2.PaymentTextId = 2 and
                  I2.InvoiceTypeCode = 1      
                  and dbo.CountNumberCharacterInString(PaymentTextDescription,N'$', null) = 4) 'Expenditure Organization',

            null as 'Tax Regime',
            null as 'Tax Classification',
            null as 'Tax Status',
            null as 'Tax Rate Name',
            null as 'Included Tax in Line?',
            null as 'Final Match',
            null as Fund,
            null as Programme,
            null as Organization,
            null as Account,
            null as 'Year Indicator',
            null as 'Payment PO Number(s)'
            FROM
            Invoice I
            WHERE
                  'Project Number' IS NOT NULL and
                  PaymentTextId = 2 and
                  InvoiceTypeCode = 1      and
                  InvoiceStateCode = 5 and
                  I.ExportedToAFIMSDate IS NULL

I would like to know if there is a better way to write it mainly taking into account the subquery in the SELECT statement as in the example:
(SELECT
                  max(case when f.row_num = 1 then f.value end)
            FROM
            Invoice I2
            cross apply dbo.ParmsToList(SUBSTRING(PaymentTextDescription, 20,CHARINDEX('$', PaymentTextDescription)), '$' ) f
            WHERE
                  I2.invoicenumber = I.invoicenumber and
                  I2.PaymentTextDescription like 'Charged to project%' and
                  I2.PaymentTextId = 2 and
                  I2.InvoiceTypeCode = 1
                  and dbo.CountNumberCharacterInString(PaymentTextDescription,N'$', null) = 4) 'Project Number',
0
Comment
Question by:DistillingExperts
  • 6
  • 2
  • 2
  • +2
12 Comments
 
LVL 42

Expert Comment

by:sedgwick
Comment Utility

>>... which I used the following SQL and is working:

great, so what's the problem?
0
 

Author Comment

by:DistillingExperts
Comment Utility
I don't like this:
  (SELECT
                  max(case when f.row_num = 1 then f.value end)
            FROM
            Invoice I2
            cross apply dbo.ParmsToList(SUBSTRING(PaymentTextDescription, 20,CHARINDEX('$', PaymentTextDescription)), '$' ) f
            WHERE
                  I2.invoicenumber = I.invoicenumber and
                  I2.PaymentTextDescription like 'Charged to project%' and
                  I2.PaymentTextId = 2 and
                  I2.InvoiceTypeCode = 1
                  and dbo.CountNumberCharacterInString(PaymentTextDescription,N'$', null) = 4) 'Project Number',

Seems to be too much and error prone.
0
 
LVL 1

Expert Comment

by:beyazlale
Comment Utility
You can use Common Table Expression like

WITH RowNum AS (
(SELECT
                  max(case when f.row_num = 1 then f.value end) ,I2.invoicenumber
            FROM
            Invoice I2
            cross apply dbo.ParmsToList(SUBSTRING(PaymentTextDescription, 20,CHARINDEX('$', PaymentTextDescription)), '$' ) f
            WHERE
                  I2.PaymentTextDescription like 'Charged to project%' and
                  I2.PaymentTextId = 2 and
                  I2.InvoiceTypeCode = 1
                  and dbo.CountNumberCharacterInString(PaymentTextDescription,N'$', null) = 4) 'Project Number',
) (Your select statement)

Iyou can join with this CTE in your statement then. So this statement is executed once not with every row.
0
 

Author Comment

by:DistillingExperts
Comment Utility
Could you explain how it would stay in the original SQL? Please note I have different SELECTs within the original SELECT.
0
 
LVL 1

Expert Comment

by:beyazlale
Comment Utility
You can write like that below. Creating a table valued function for these inner select may be more useful for you.
   
 WITH  ProjectNumber AS(
                  SELECT
                  max(case when f.row_num = 1 then f.value end) as PNumber,I2.invoicenumber
            FROM
            Invoice I2
            cross apply dbo.ParmsToList(SUBSTRING(PaymentTextDescription, 20,CHARINDEX('$', PaymentTextDescription)), '$' ) f
            WHERE
                  I2.PaymentTextDescription like 'Charged to project%' and
                  I2.PaymentTextId = 2 and
                  I2.InvoiceTypeCode = 1
                  and dbo.CountNumberCharacterInString(PaymentTextDescription,N'$', null) = 4
                  GROUP BY I2.invoicenumber
      ),TaskNumber AS (
                  SELECT
                  max(case when f.row_num = 2 then f.value end)  as TNumber,I2.invoicenumber
            FROM
            Invoice I2
            cross apply dbo.ParmsToList(I2.PaymentTextDescription, '$' ) f
            WHERE
                  I2.PaymentTextDescription like 'Charged to project%' and
                  I2.PaymentTextId = 2 and
                  I2.InvoiceTypeCode = 1      
                  and dbo.CountNumberCharacterInString(PaymentTextDescription,N'$', null) = 4
                  GROUP BY I2.invoicenumber
      ),AwardNumber AS (
                  SELECT
                  max(case when f.row_num = 3 then f.value end) as ANumber, I2.invoicenumber
            FROM
            Invoice I2
            cross apply dbo.ParmsToList(I2.PaymentTextDescription, '$' ) f
            WHERE
                  I2.PaymentTextDescription like 'Charged to project%' and
                  I2.PaymentTextId = 2 and
                  I2.InvoiceTypeCode = 1      
                  and dbo.CountNumberCharacterInString(PaymentTextDescription,N'$', null) = 4
                  GROUP BY I2.invoicenumber
), ExpenditureType AS (
                  SELECT
                  max(case when f.row_num = 4 then f.value end) EType,  I2.invoicenumber
            FROM
            Invoice I2
            cross apply dbo.ParmsToList(I2.PaymentTextDescription, '$' ) f
            WHERE
                  I2.PaymentTextDescription like 'Charged to project%' and
                  I2.PaymentTextId = 2 and
                  I2.InvoiceTypeCode = 1      
                  and dbo.CountNumberCharacterInString(PaymentTextDescription,N'$', null) = 4
                  GROUP BY I2.invoicenumber
), ExpenditureOrganization AS (
                  SELECT
                  max(case when f.row_num = 5 then f.value end) EOrganization,I2.invoicenumber
            FROM
            Invoice I2
            cross apply dbo.ParmsToList(I2.PaymentTextDescription, '$' ) f
            WHERE
                  I2.PaymentTextDescription like 'Charged to project%' and
                  I2.PaymentTextId = 2 and
                  I2.InvoiceTypeCode = 1      
                  and dbo.CountNumberCharacterInString(PaymentTextDescription,N'$', null) = 4
                  GROUP BY I2.invoicenumber
)
      SELECT
      'PubAtom' as 'Source',            
    'Standard' as 'Transaction Type',
      'PUBATOM' as 'Supplier Site',
      convert(varchar(10), InvoiceDate, 111) as 'Invoice Date',
      DisplayNumber as 'Invoice Number',
      'EUR' as 'Invoice Currency',
      0.00 as 'Invoice Amount',
      'GL Date' = convert(varchar(10), InvoiceDate, 111),      
      'Immediate' as 'Payment Terms',
      'Clearing' as 'Payment Method',
      'Internal Clearing' as 'Pay Group',
      'CUST REF:'+CustomerReference+' | Discount: '+convert(varchar(255),Discount) +
      ' | Shipping: '+convert(varchar(255),ShippingCost) +
      ' | DISP: '+(select d.DispatchTypeDescription from DispatchType d where d.DispatchTypeId = I.DispatchTypeId) +
      ' | Weight: '+ ' | Bill To: '+ dbo.GetCustomerName(BillToCustomerNumber)+'; '+dbo.GetCustomerAddress(BillToCustomerNumber) +
      ' | Ship To:'+ case when dbo.GetCustomerName(ShipToCustomerNumber) is null then ''
                                         else dbo.GetCustomerName(ShipToCustomerNumber)
                         end +
      '; '+case when dbo.GetCustomerAddress(ShipToCustomerNumber)  is null then ''
                                         else dbo.GetCustomerAddress(ShipToCustomerNumber)
                         end
      as 'Header Description',
      null as 'Payment Number',
    null as 'Remit-TO Supplier Name',
      null as 'Remit-TO Supplier Site',
    1 as 'Line Number',
      'Item' as 'Line Type',
      TotalCost/2 as Amount,
      null as 'Line Description', --Rever
      null as 'GL Reconciliation Ref',
      null as 'PO Number',
      null as 'PO Line Number',
      null as 'PO Shipment Number',
      null as 'PO Distribution Number',      
        PN.PNumber 'Project Number',
        TN.TNumber 'Task Number',
      AN.ANumber 'Award Number',
      ET.EType 'Expenditure Type',
      EO.EOrganization 'Expenditure Organization',
            null as 'Tax Regime',
            null as 'Tax Classification',
            null as 'Tax Status',
            null as 'Tax Rate Name',
            null as 'Included Tax in Line?',
            null as 'Final Match',
            null as Fund,
            null as Programme,
            null as Organization,
            null as Account,
            null as 'Year Indicator',
            null as 'Payment PO Number(s)'
            FROM
            Invoice I
                        INNER JOIN ProjectNumber PN ON I.invoicenumber = PN.invoicenumber
                        INNER JOIN TaskNumber TN ON I.invoicenumber = TN.invoicenumber
                        INNER JOIN AwardNumber AN ON I.invoicenumber =AN.invoicenumber
                        INNER JOIN ExpenditureType ET ON I.invoicenumber =ET.invoicenumber
                        INNER JOIN ExpenditureOrganization EO ON I.invoicenumber =EO.invoicenumber
            WHERE
                  'Project Number' IS NOT NULL and
                  PaymentTextId = 2 and
                  InvoiceTypeCode = 1      and
                  InvoiceStateCode = 5 and
                  I.ExportedToAFIMSDate IS NULL
0
 

Author Comment

by:DistillingExperts
Comment Utility
What is the f used for here:
                  SELECT
                  max(case when f.row_num = 1 then f.value end) as PNumber,I2.invoicenumber
            FROM
            Invoice I2
            cross apply dbo.ParmsToList(SUBSTRING(PaymentTextDescription, 20,CHARINDEX('$', PaymentTextDescription)), '$' ) f
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

Author Comment

by:DistillingExperts
Comment Utility
Sorry, I did that f. Any other simpler solution than beyazlale's one?
0
 
LVL 7

Expert Comment

by:Cboudroz
Comment Utility
Not sure but I think this will do what you want, if the both cross apply on the function alway return row:


SELECT
	'...'
	, max(case when f2.row_num = 1 and dbo.CountNumberCharacterInString(PaymentTextDescription,N'$', null) = 4) then f2.value end) 'Project Number'
	, max(case when f.row_num = 2 and dbo.CountNumberCharacterInString(PaymentTextDescription,N'$', null) = 4) then f.value end) 'Task Number'
	, max(case when f.row_num = 3 and dbo.CountNumberCharacterInString(PaymentTextDescription,N'$', null) = 4) then f.value end) 'Award Number'
	, '...'
FROM 
	Invoice I
	cross apply dbo.ParmsToList(I2.PaymentTextDescription, '$' ) f
	cross apply dbo.ParmsToList(SUBSTRING(PaymentTextDescription, 20,CHARINDEX('$', PaymentTextDescription)), '$' ) f2
WHERE
      'Project Number' IS NOT NULL and
      PaymentTextId = 2 and 
      InvoiceTypeCode = 1      and
      InvoiceStateCode = 5 and
      I.ExportedToAFIMSDate IS NULL

Open in new window

0
 
LVL 11

Accepted Solution

by:
yuching earned 350 total points
Comment Utility
Perhaps you can try the below statement by combining all the select max into one since they all having the same conditions

 Select ...
   TotalCost/2 as Amount,
   I2.Project_Number,
   I2.Task_Number,
   I2.Award_Number,
   I2.Expenditure_Type,
   I2.Expenditure_Organization,
          ...
FROM Invoice I
Left Outer Join (
  Select InvoiceNumber,
    Max(case when f.row_num = 1 then f.value end) Project_Number,
    max(case when f.row_num = 2 then f.value end) Task_Number,
    max(case when f.row_num = 3 then f.value end) Award_Number,
    max(case when f.row_num = 4 then f.value end) Expenditure_Type,
    max(case when f.row_num = 5 then f.value end) Expenditure_Organization
  FROM Invoice I2
     cross apply dbo.ParmsToList(I2.PaymentTextDescription, '$' ) f
     Where I2.PaymentTextDescription like 'Charged to project%' and
           I2.PaymentTextId = 2 and I2.InvoiceTypeCode = 1      
           and dbo.CountNumberCharacterInString(PaymentTextDescription,N'$', null) = 4
  Group By InvoiceNumber              
)I2 On I2.invoicenumber = I.invoicenumber
Where 'Project Number' IS NOT NULL And PaymentTextId = 2 and InvoiceTypeCode = 1      
 And InvoiceStateCode = 5 And I.ExportedToAFIMSDate IS NULL
0
 

Author Comment

by:DistillingExperts
Comment Utility
Thanks for the effort beyazlale. Cboudroz, I got an error with parenthesis in your query.
yuching, your query returns no value.
0
 
LVL 7

Assisted Solution

by:Cboudroz
Cboudroz earned 150 total points
Comment Utility

SELECT
	'...'
	, max(case when f2.row_num = 1 and dbo.CountNumberCharacterInString(PaymentTextDescription,N'$', null) = 4 then f2.value end) 'Project Number'
	, max(case when f.row_num = 2 and dbo.CountNumberCharacterInString(PaymentTextDescription,N'$', null) = 4 then f.value end) 'Task Number'
	, max(case when f.row_num = 3 and dbo.CountNumberCharacterInString(PaymentTextDescription,N'$', null) = 4 then f.value end) 'Award Number'
	, '...'
FROM 
	Invoice I
	cross apply dbo.ParmsToList(I2.PaymentTextDescription, '$' ) f
	cross apply dbo.ParmsToList(SUBSTRING(PaymentTextDescription, 20,CHARINDEX('$', PaymentTextDescription)), '$' ) f2
WHERE
      'Project Number' IS NOT NULL and
      PaymentTextId = 2 and 
      InvoiceTypeCode = 1      and
      InvoiceStateCode = 5 and
      I.ExportedToAFIMSDate IS NULL

Open in new window

0
 

Author Comment

by:DistillingExperts
Comment Utility
yuching, actually your query worked. I will try your solution later Cboudroz.
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how the fundamental information of how to create a table.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now