• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 362
  • Last Modified:

Improving SQL

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
DistillingExperts
Asked:
DistillingExperts
  • 6
  • 2
  • 2
  • +2
2 Solutions
 
Meir RivkinFull stack Software EngineerCommented:

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

great, so what's the problem?
0
 
DistillingExpertsAuthor Commented:
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
 
beyazlaleCommented:
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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
DistillingExpertsAuthor Commented:
Could you explain how it would stay in the original SQL? Please note I have different SELECTs within the original SELECT.
0
 
beyazlaleCommented:
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
 
DistillingExpertsAuthor Commented:
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
 
DistillingExpertsAuthor Commented:
Sorry, I did that f. Any other simpler solution than beyazlale's one?
0
 
CboudrozCommented:
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
 
yuchingCommented:
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
 
DistillingExpertsAuthor Commented:
Thanks for the effort beyazlale. Cboudroz, I got an error with parenthesis in your query.
yuching, your query returns no value.
0
 
CboudrozCommented:

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
 
DistillingExpertsAuthor Commented:
yuching, actually your query worked. I will try your solution later Cboudroz.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 6
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now