Solved

SQL Command Statement Crystal Reports XI

Posted on 2011-02-22
20
406 Views
Last Modified: 2012-05-11
I can not figure out why, when I add the Union statement my Hours field from the first select statment becomes all 0?
below you will find my SQL Statements:
SELECT "PRT_CURRENT__TIME"."Expense_Account" AS "Account", "PRT_CURRENT__TIME"."Employee" AS "ID", "PRM_MASTER__EMPLOYEE"."Employee_Name" AS "Description", "PRT_CURRENT__TIME"."Period_End_Date", Sum(IF("PRT_CURRENT__TIME"."Pay_Type" = 'Cash fringe' Or "PRT_CURRENT__TIME"."Pay_Type"='Regular',"PRT_CURRENT__TIME"."Units",0)) AS Hours, Sum("PRT_CURRENT__TIME"."Amount") AS "Amount"
FROM "PRM_MASTER__EMPLOYEE" INNER JOIN "PRT_CURRENT__TIME" ON "PRM_MASTER__EMPLOYEE"."Employee" = "PRT_CURRENT__TIME"."Employee"
WHERE "PRT_CURRENT__TIME"."Period_End_Date">={d '2011-01-01'} AND  "PRT_CURRENT__TIME"."Expense_Account" > '1-C0-CEH-5AA'
GROUP BY "PRT_CURRENT__TIME"."Expense_Account", "PRT_CURRENT__TIME"."Employee", "PRM_MASTER__EMPLOYEE"."Employee_Name", "PRT_CURRENT__TIME"."Period_End_Date"
UNION
SELECT "APT_CURRENT__TRANSACTION"."Expense_Account" AS "Account", "APT_CURRENT__TRANSACTION"."Vendor" AS "ID", "APM_MASTER__VENDOR"."Name" AS "Description", "APT_CURRENT__TRANSACTION"."Accounting_Date" AS "Period_End_Date", Sum(0) AS Hours, Sum("APT_CURRENT__TRANSACTION"."Amount") AS "Amount"
FROM "APM_MASTER__VENDOR" INNER JOIN "APT_CURRENT__TRANSACTION" ON "APM_MASTER__VENDOR"."Vendor" = "APT_CURRENT__TRANSACTION"."Vendor"
GROUP BY "APT_CURRENT__TRANSACTION"."Expense_Account", "APT_CURRENT__TRANSACTION"."Vendor", "APM_MASTER__VENDOR"."Name", "APT_CURRENT__TRANSACTION"."Accounting_Date", "APT_CURRENT__TRANSACTION"."Transaction_Type"
HAVING ((("APT_CURRENT__TRANSACTION"."Expense_Account")>='1-C0-CEH-5AA') AND (("APT_CURRENT__TRANSACTION"."Accounting_Date")>={d '2011-01-01'}) AND (("APT_CURRENT__TRANSACTION"."Transaction_Type")='Invoice' Or ("APT_CURRENT__TRANSACTION"."Transaction_Type")='Reverse Invoice'))

Open in new window

0
Comment
Question by:CBlaisdell
  • 7
  • 6
  • 4
  • +1
20 Comments
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
Do you get 0'for the hours field for all records?

mlmcc
0
 

Author Comment

by:CBlaisdell
Comment Utility
Yes
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
DO you get records from PRT_CURRENT__TIME?

mlmcc
0
 
LVL 26

Expert Comment

by:Kurt Reinhardt
Comment Utility
If I had to guess, I'd say it's because you use UNION, not UNION ALL.

UNION filters out duplicate records from each recordset, whereas UNION ALL returns explicit values from each recordset.  Ideally, you shouldn't have any duplicates between the two, because they should return distinct recordsets.

Something else I noticed is you have  HAVING clause for the second recordset, but a WHERE clause would be more approprate.  HAVING is really intended to be used for summarized data.  You should make the HAVING a WHERE.

~Kurt
 
0
 
LVL 26

Expert Comment

by:Kurt Reinhardt
Comment Utility
Something like this:


SELECT
  PRT_CURRENT__TIME.Expense_Account AS Account,
  PRT_CURRENT__TIME.Employee AS ID,
  PRM_MASTER__EMPLOYEE.Employee_Name AS Description,
  PRT_CURRENT__TIME.Period_End_Date,
  Sum(IF(PRT_CURRENT__TIME.Pay_Type = 'Cash fringe' Or PRT_CURRENT__TIME.Pay_Type='Regular',PRT_CURRENT__TIME.Units,0)) AS Hours,
  Sum(PRT_CURRENT__TIME.Amount) AS Amount
FROM
  PRM_MASTER__EMPLOYEE
  INNER JOIN PRT_CURRENT__TIME ON PRM_MASTER__EMPLOYEE.Employee = PRT_CURRENT__TIME.Employee
WHERE
  PRT_CURRENT__TIME.Period_End_Date>={d '2011-01-01'}
  AND  PRT_CURRENT__TIME.Expense_Account > '1-C0-CEH-5AA'
GROUP BY
  PRT_CURRENT__TIME.Expense_Account,
  PRT_CURRENT__TIME.Employee,
  PRM_MASTER__EMPLOYEE.Employee_Name,
  PRT_CURRENT__TIME.Period_End_Date

UNION ALL

SELECT
  APT_CURRENT__TRANSACTION.Expense_Account AS Account,
  APT_CURRENT__TRANSACTION.Vendor AS ID,
  APM_MASTER__VENDOR.Name AS Description,
  APT_CURRENT__TRANSACTION.Accounting_Date AS Period_End_Date,
  Sum(0) AS Hours,
  Sum(APT_CURRENT__TRANSACTION.Amount) AS Amount
FROM
  APM_MASTER__VENDOR
  INNER JOIN APT_CURRENT__TRANSACTION ON APM_MASTER__VENDOR.Vendor = APT_CURRENT__TRANSACTION.Vendor
GROUP BY
  APT_CURRENT__TRANSACTION.Expense_Account,
  APT_CURRENT__TRANSACTION.Vendor,
  APM_MASTER__VENDOR.Name,
  APT_CURRENT__TRANSACTION.Accounting_Date,
  APT_CURRENT__TRANSACTION.Transaction_Type
WHERE
  (
  APT_CURRENT__TRANSACTION.Expense_Account>='1-C0-CEH-5AA'
  AND APT_CURRENT__TRANSACTION.Accounting_Date>={d '2011-01-01'}
  AND (
      APT_CURRENT__TRANSACTION.Transaction_Type='Invoice'
      Or APT_CURRENT__TRANSACTION.Transaction_Type='Reverse Invoice'
      )
  )

Open in new window

0
 

Author Comment

by:CBlaisdell
Comment Utility
Before adding the Union Statement my report populates as follows.
Account                   ID          Description    Period_End_Date     Units    Amount
1-C0-CES-6AA     23564      XXX, Bill            02/13/2011              42     1320.46

After:
Account                   ID          Description    Period_End_Date     Units    Amount
1-C0-CES-6AA     23564      XXX, Bill            02/13/2011              0       1320.46

But i receive all the new lines from APT_CURRENT__TRANSACTION with Amounts.

There is no Units field in APT_CURRENT__TRANSACTION so I made one up.  i think that may be my issue.
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
Have you tried reversing the parts of the union?
Try putting the second select first

mlmcc
0
 
LVL 26

Expert Comment

by:Kurt Reinhardt
Comment Utility
And have you tried the updated SQL?

At minimum, verify how many records you received by running the first query on it's own.  Next, how many records do you receive running the second on it's own.  If you UNION them and don't see the combined total from each of the separate queries, then you have a problem (unless you're explicitly trying to filter out duplicate records, but here are better ways to do it..).

~Kurt
0
 
LVL 34

Expert Comment

by:James0628
Comment Utility
What kind of db are you using?  That might affect the solution.  Judging from your use of IF in the first SELECT, I'm guessing that it's not MS SQL, unless this is a new syntax that I'm not familiar with (which is possible).

 My first thought was the same as Kurt's, that the problem was that you were using UNION instead of UNION ALL, so the rows from the second SELECT were replacing the rows from the first, but that doesn't make sense.  If Hours is not 0 in the first SELECT, and always 0 in the second SELECT, then the rows won't be duplicates and you should get both sets.

 I don't see why the first SELECT would work by itself, but give you 0 for Hours when you add the UNION and second SELECT.


 FWIW, I also agree with Kurt that the HAVING seems unnecessary and could be replaced by a WHERE, in which case you could also drop Transaction_Type from the GROUP BY.  But I don't see how any of that could affect the first SELECT.

 James
0
 

Author Comment

by:CBlaisdell
Comment Utility
I am querying Sage Timberline through an ODBC  
I have cleaned up the 2nd query by changing HAVING to WHERE.
I have verifyed that I am getting All records from Both queries.
I have tried reversing the queries, but still the hours field becomes all Zero's.

I think it has to do with my Sum(IF()) Statement.
SELECT
  APT_CURRENT__TRANSACTION.Expense_Account AS Account,
  APT_CURRENT__TRANSACTION.Vendor AS ID,
  APM_MASTER__VENDOR.Name AS Description,
  APT_CURRENT__TRANSACTION.Accounting_Date AS Period_End_Date,
  Sum(0) AS Units,
  Sum(APT_CURRENT__TRANSACTION.Amount) AS Amount
FROM
  APM_MASTER__VENDOR
  INNER JOIN APT_CURRENT__TRANSACTION ON APM_MASTER__VENDOR.Vendor = APT_CURRENT__TRANSACTION.Vendor
WHERE
  APT_CURRENT__TRANSACTION.Accounting_Date>={d '2011-01-01'}
  AND APT_CURRENT__TRANSACTION.Expense_Account>='1-C0-CEH-5AA'
  AND (APT_CURRENT__TRANSACTION.Transaction_Type='Invoice'
  OR APT_CURRENT__TRANSACTION.Transaction_Type='Reverse Invoice')
GROUP BY
  APT_CURRENT__TRANSACTION.Expense_Account,
  APT_CURRENT__TRANSACTION.Vendor,
  APM_MASTER__VENDOR.Name,
  APT_CURRENT__TRANSACTION.Accounting_Date

UNION ALL

SELECT
  PRT_CURRENT__TIME.Expense_Account AS Account,
  PRT_CURRENT__TIME.Employee AS ID,
  PRM_MASTER__EMPLOYEE.Employee_Name AS Description,
  PRT_CURRENT__TIME.Period_End_Date,
  Sum(IF(PRT_CURRENT__TIME.Pay_Type = 'Regular' Or PRT_CURRENT__TIME.Pay_Type = 'Cash fringe', PRT_CURRENT__TIME.Units, 0)) AS Units,
  Sum(PRT_CURRENT__TIME.Amount) AS Amount
FROM
  PRM_MASTER__EMPLOYEE
  INNER JOIN PRT_CURRENT__TIME ON PRM_MASTER__EMPLOYEE.Employee = PRT_CURRENT__TIME.Employee
WHERE
  PRT_CURRENT__TIME.Period_End_Date>={d '2011-01-01'}
  AND  PRT_CURRENT__TIME.Expense_Account > '1-C0-CEH-5AA'
GROUP BY
  PRT_CURRENT__TIME.Expense_Account,
  PRT_CURRENT__TIME.Employee,
  PRM_MASTER__EMPLOYEE.Employee_Name,
  PRT_CURRENT__TIME.Period_End_Date

Open in new window

0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:CBlaisdell
Comment Utility
I figured out a work around.  I dropped the Field units and created a separate SQL Command Table and linked the two by a Left Outer Join.  Thanks for all of your suggestions.
0
 
LVL 26

Expert Comment

by:Kurt Reinhardt
Comment Utility
If everything is working fine for you and is reasonably quick that's probably ok.  As a matter of best practices, however, it's a bad idea to have multiple commands linked together.  That's because each command has to execute 100% first before they can even be joined.  You can mitigate the performance risks by ensuring each command has the exact same parameters, however, so that each command returns as small a recordset as possible.

~Kurt
0
 
LVL 34

Expert Comment

by:James0628
Comment Utility
 > I think it has to do with my Sum(IF()) Statement.

 Perhaps, but I don't see why it would work when you just had that one SELECT, but not when you added the second one.  I don't see how the second SELECT could affect the Sum (IF ()) in the first SELECT.

 FWIW, if you just can't get the Sum (IF ()) to work, I thought of another way that you might be able to handle this, without the IF.  You could split that query up into two SELECT's.  In one, have

  Sum (PRT_CURRENT__TIME.Units) AS Units

  and add this to the WHERE

  AND (PRT_CURRENT__TIME.Pay_Type = 'Regular' Or
   PRT_CURRENT__TIME.Pay_Type = 'Cash fringe')

 In the second SELECT, have

  0 AS Units   (or use Sum (0) instead of 0, if you prefer, or the db requires)

  and add this to the WHERE

  AND (PRT_CURRENT__TIME.Pay_Type <> 'Regular' AND
   PRT_CURRENT__TIME.Pay_Type <> 'Cash fringe')

 You might need to replace <> with !=.  Whatever syntax that db uses for "not equal".

 In theory, that should work.  Then again, I don't see why your old query isn't working.  :-)
 I don't know how breaking that up into two SELECT's will affect the performance, but you could give it a try and see how it goes.

 James
0
 

Author Comment

by:CBlaisdell
Comment Utility
James that is a good suggestion.   However, I got an error message about "non unique table" when I tried that.  but this error message is something i have to deal with because i have two other statements that share a common table field.

Does anybody know if a Sub-Select Statement or a WITH Statement would work best to fix this problem?
0
 
LVL 34

Expert Comment

by:James0628
Comment Utility
Hmm.  I don't know why you'd get an error when you split that SELECT.  It seems like it should work.  Could you post the query that got the error?  Maybe we can find what's causing it.

 James
0
 

Author Comment

by:CBlaisdell
Comment Utility
I can not add

 AND (PRT_CURRENT__TIME.Pay_Type <> 'Regular' AND
   PRT_CURRENT__TIME.Pay_Type <> 'Cash fringe')

because I need total Amount of all records including Overtime. I only want to see regular hours. the way our system is set up, it pays total hours at the regular hourly rate and those hours over 40 at 1/2 time.  I.e. Sam works 45 hrs in our system he gets paid 45 hrs at $18 and 5 hrs at $9.
so it is two entries that look like this.
Name         Pay_Type       Hours        Rate         Amount
Sam           Regular              45        $18.00       $810.00
Sam           Overtime              5        $  9.00       $  45.00


I am thinking about bringing in Pay Type as a field and then creating a formula field in  crystal for Units.
0
 
LVL 34

Expert Comment

by:James0628
Comment Utility
I sort of see what you're saying about regular vs overtime, but, at the same time, I don't see how it's a problem with the query that you posted.  Units is a total of PRT_CURRENT__TIME.Units from the rows in the group where Pay_Type is 'Regular' or 'Cash fringe'.  Amount is the total of PRT_CURRENT__TIME.Amount from all of the rows in the group.  If you split the query into two parts, Units and Amount in the first part are the totals from the 'Regular' and 'Cash fringe' rows.  Units in the second part is 0 and Amount is the total from all of the other Pay_Type's.  Combine the two in the report and you get the same totals, right?  Or am I missing something?

 Anyway, adding Pay_Type and then doing the totaling in CR sounds like it would work too.

 James
0
 

Author Comment

by:CBlaisdell
Comment Utility
I see what you are saying but when I try that i get a Non Unique Table Error.
0
 
LVL 34

Accepted Solution

by:
James0628 earned 500 total points
Comment Utility
Ah.  Interesting.  That reminds me of the problem in your other question:

http://www.experts-exchange.com/Database/Reporting_/Crystal_Reports/Q_26847544.html

 I hadn't made that connection before.

 Maybe there really is some kind of issue with using UNION with Sage/Pervasive.

 If there is, then adding "Pay Type" as a field and letting CR sort it out might be the way to go.

 James
0
 
LVL 34

Expert Comment

by:James0628
Comment Utility
FWIW, given the resolution of your other question, about the "non-unique table" error, maybe you could use 2 SELECT's, as I described earlier, and just use different aliases for the tables in each SELECT.

 James
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

772 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

10 Experts available now in Live!

Get 1:1 Help Now