SQL command - filter not working

[kimprogrammer]

Hello

I'm trying to filter out some data in a command and it is not working.
I have found were it says the command actually translates the command to sql - so it is not true sql

When I refresh and ask for a new prompt - all the divisions still come up - so the statement is not being translated and is being ignored (as I understand from my book)

I've done some searches and found someone using the following and used it as a guide:
WHERE right(t.eventstr,1) NOT LIKE '%B'



Here is the statement; I don't want descriptions that start with Z.
-----------------------------------------------
SELECT PCLASS FROM CPY10050
WHERE LEFT(PDescription,1) not like 'z'
UNION
SELECT '...ALL' FROM CPY10050
-----------------------------------------------------
Thanks for the help

 

[lbass]

When you set up the parameter, you are using the dropdown to select {command.PClass}, NOT {cpy10050.pclass}, as your value field, correct? And then you are clicking on Action->append all values, correct?

Another issue is that the command that you use to populate your picklist should NOT appear in your Show SQL query. If it does, it means you have either linked it to your main report table (it should NOT be linked), or that you have referenced a field from the command in the body of the report (you shouldn't).

As a test, what happens if you start a new report ONLY with the command (no parameter), and place pclass in the detail section. What do you see?

-LB

 

[kimprogrammer]

Yes when I set up the parameter I'm using Command.Pclass.

I do not get the action-append showing up because I choose dynamic and not static. My file is live and I always want to show current divisions.

I'm on my real report not the once i was playing with
this is the show sql- and I don't see the command in it

-----------------
SELECT "CPY10100"."PLastName", "CPY10100"."PFirstName",
"CPY10050"."PClass", "CPY10050"."PDescription", "CPY10100"."PEmployeeID"
FROM "HYLTD"."dbo"."CPY10050" "CPY10050" INNER JOIN
"HYLTD"."dbo"."CPY10100" "CPY10100" ON
"CPY10050"."PClass"="CPY10100"."PEmployeeClass"
ORDER BY "CPY10050"."PClass"


SELECT "SwipeAccumulativeHours"."EmpNum",
"SwipeAccumulativeHours"."PPDay", "SwipeAccumulativeHours"."InputType",
"SwipeAccumulativeHours"."SupReasonCode", "SwipeAccumulativeHours"."Amount"
FROM "keyscan-payroll"."dbo"."SwipeAccumulativeHours"
"SwipeAccumulativeHours"
WHERE "SwipeAccumulativeHours"."PPDay">={ts '2008-11-03 00:00:00'} AND
"SwipeAccumulativeHours"."SupReasonCode"='ABSENT'
ORDER BY "SwipeAccumulativeHours"."EmpNum",
"SwipeAccumulativeHours"."InputType", "SwipeAccumulativeHours"."PPDay"


SELECT "PPStartDate"."PPEnd", "PPStartDate"."PPStart"
FROM "keyscan-payroll"."dbo"."PPStartDate" "PPStartDate"
WHERE ("PPStartDate"."PPStart">={ts '2008-11-03 00:00:00'} AND
"PPStartDate"."PPStart"<{ts '2008-11-03 00:00:01'})
---------------------

I truly don't see any command in the body of the report.

I did the test and when I did that only the pclass codes I wanted showed up - so I know that works.

So I copied code in that command(the test) over to the one in my report. Because I wasn't saving changes to it. I selected ok of the modify command. The window didn't close automatically - so I closed it with the x at the top right side. That left the database expert window open. So I selected ok on that.

A database warning window popped up saying
"More than one datasource or stored procedure has been - used in this report. Please make sure no sql expression is added and not server side group is performed"
I selected ok.
Then a crystal reports window popped up saying
"Bad formula code"
I select on the ok and the preview screen is blank.

Could there be something in this report that had gotten corrup and this just will not work? I'm hoping not because all the reports I wrote are from the same original one and i don't want to deal with in every single report.

 

[lbass]

You didn't answer this:

"As a test, what happens if you start a new report ONLY with the command (no parameter), and place pclass in the detail section. What do you see?"

Also, please explain how you have your tables linked in the report that is generating the above query. It looks like you have three different sets of tables not linked to each other--this is generally a bad idea.

-LB

 

[kimprogrammer]

I did answer the question in the previous post. This is what I wrote:
"I did the test and when I did that only the pclass codes I wanted showed up - so I know that works." Only the active Pclass code printed on the report.
---------
My tables are:
CPY10050 (division table)
CPY10100 (employee master)
CPY10020 (department table)
PPStartDate (payperiod start date table)
SwipeAccumulativeHours table (stores paycodes)

CPY10050.PClass --> CPY10100.PEmployeeClass(inner join, not enforced and =)
CPY10020.PDeparment --> CPY10100.PDepartment(inner join not enforced and =)I have this as a group for printing
SwipeAccumulativeHours.EmpNum --> CPY10100.PEmployeeID(inner join not enforced and =)
PPStartDate is not linked to anything
Command.PCLASS --> CPY10050.PClass(inner join not enforced and =)

 

[lbass]

If the test worked, and you only saw PClass that had no Z descriptions, then the command should work fine to populate the list.

But you should never have multiple tables pointing to the same table, which is what you appear to be showing. It should be like this:

CPY10100->CP10050
->CP10020
->SwipeAccumulativeHours

The Command should NOT be linked to CP10050 and PPStartDate should not be linked to anything or referenced in the selection criteria--if it too is meant only to populate the date picklist. You would just use it to populate the date picklist, and then just reference the SwipeAccumulativeHours date field in the selection formula in comparison to the selected date parameter.

-LB

 

[kimprogrammer]

Hi I need to start over - I just can't get this working in this report and it shouldn't be since this is not a difficult thing. So I appologize for this because i know I'm quite frustrated trying to figure this out and I'm sure you are as well.
-----
I created a new report from scratch because there has to be something else causeing the problem in this. So I'm keeping this simple going step by step
--------
I first created the command - it works well when dragged on the screen. I then deleted from the design page
I then created the prompt it works well only the divisions I want show up.
And all i have in the design page is the employee id from CPY10100. So when I run the report only the employees from that division are printed.
The Command is not on the screen!!
So so far so good.
But when I add in the swipeaccumulativehours file - is where I'm now having problems
----------------------
I have the command linked to nothing
CPY10100.PEmployeeClass --> CPY10050.PClass
the record select is:
({CPY10050.PClass} = {?Division} or {?Division} = "...ALL") //and
//{SwipeAccumulativeHours.PayCode} = "Comment"
-------------------
So now I'm trying to add in the swipeaccumulative hours
CPY10100.PEmployeeID --> SwipeAccumulativeHours.EmpNum
innerjoin
I take Paycode from SwipeAccumulative hours and put in in the detail.
So all that is on the detail is CPY10100.PemployeeId and SwipeAccumulativeHours.PayCode

I get one message - failed to retrieve data from database - then another - invalid arguement provide.
-------------------------------
What could be the issue?


Thanks for the help
Kim