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]

Your command should work as is. Are you sure you are selecting {command.pclass} in the value dropdown list when you go to populate the pick list by appending all values and NOT the field itself from the tables in your main report? Does "..All" appear in the list? You might also check for case sensitivity, although it didn't seem to matter when I tested that here. You also don't need to use "nt like"--you could just use <>--but again it doesn't actually matter.

-LB

 

[kimprogrammer]

I do have '...All' appearing in my dropdown box. And in the prompt I am selecting pclass command.

I did try changing it to a capital Z and <> but neither seem to make a difference.

I double checked the database to make sure the name was right. And it looks like the Z is the first character. When I was working on the vb part of m project I did have to use the TRIM command on alot on the fields because there were leading/trailing spaces. Could this be an issue here?

 

[kimprogrammer]

Also after I select the prompts - I get the message bad formula code. This command is the only thing I've changed - it was working prior to this.

 

[lbass]

I this relates back to previous posts, but I don't recall the details. If you get an error message like that, the formula causing the error should be popping up. What is the formula?

It is not the command, as the command would not compile if done incorrectly. You are not using the command in the body of the report, are you? Only to populate your picklist?

You will have to provide more information. And, yes you should try using trim in your command.

-LB

 

[kimprogrammer]

No this is a different report so it so it doesn't relate to the other posts. But I will have to incorporate it in that report later as well. The report looks for an absent paycode that is within the payperiod parameter grouped by division.

First - I tried adding the trim into the command but I get an error saying 'trim is not a recognized function name'
So maybe I don't have it correct in the formula.
------------------
SELECT PCLASS FROM CPY10050
where left(trim(cpy10050.pDescription),1) <> 'Z'
UNION
SELECT '...ALL' FROM CPY10050
------------------------

When I get the message bad formula name - there is no more information given - no formula pops up.

I do get the following message when I select the button -used saved data.
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.

----------------------------

The command is not on the body of the report. I do have the report working - so when I open the report and run it it works I can select 'all' to get all my divisions (so the bad formula error does not show up)- But then I go into the command and add the where statement it stops working.

But I've tried removing the where from the command and I still get the bad error formula.

-----------------------------------------------
There is one thing - when I'm in the modify command window and make a change to the sql query and press the ok button. The box just flashes but does not close (I'm not sure if this is the way it normally works - it seems odd to me cause i would have thought the window should close.
-------------------------
In the select expert I have Command.Pclass with a formula
({Command.PClass} = {?Division} or {?Division} = "...ALL")

My record select is
{PPStartDate.PPStart} = {?PayPeriodStartDate} and
{SwipeAccumulativeHours.PPDay} >= {?PayPeriodStartDate} and
{SwipeAccumulativeHours.PPDay} <= {PPStartDate.PPEnd} and
{SwipeAccumulativeHours.SupReasonCode} = "ABSENT"

There is no group select

The parameter value is Plass

 

[lbass]

No, you should NOT be using {command.Pclass} in the selection formula. Instead you should be using the field from the table in your main report.

Try ltrim in the command instead of trim.

Are you currently getting the correct list of options in the picklist related to your parameter?

Also, why are you distinguishing the select expert formula and the record selection formula--they are one and the same! Please go to report->selection formula->record and copy what you have there into the thread.

What is the exact error message you get--first you mentioned "bad formula code" but later you said "bad formula name".

Also if you are looking for an "absent" record from the SwipeAccumulativeHours table, you should not be using any selection criteria on that table.

-LB

 

[kimprogrammer]

Ok ltrim doesn't give me an error but my list is still the same - so no I'm not getting the list correctly.

The reason why I have all the selctions and command how and where they are set up is because I couldn't get '...All' to appear in my dropdown box and I was on another help site and this is how the person set the report up to get it working (I emailed it to them and this is what they sent back).

 

[lbass]

You did not respond to each of the issues I raised in my last post.

-LB

 

[kimprogrammer]

I have changed the record selection to have CPY10050.Pclass and not the command.

I have changed it to ltrim - and am still not getting my dropdown list correctly.

Here is the record select
{PPStartDate.PPStart} = {?PayPeriodStartDate} and
{SwipeAccumulativeHours.PPDay} >= {?PayPeriodStartDate} and
{SwipeAccumulativeHours.PPDay} <= {PPStartDate.PPEnd} and
({CPY10050.PClass} = {?Division} or {?Division} = "...ALL") and
{SwipeAccumulativeHours.SupReasonCode} = "ABSENT"


The message is 'bad formula code'

Sorry but I don't understand the last sentence - if I dont put it in the record select where does it go?

 

[kimprogrammer]

More about that last sentence - don't I need the selection criteria I only want records with paycodes that are absent that fall in between the payperiod that was choosen from the prompt.

 

[lbass]

This is exactly the issue I attempted to explain in your previous post, and I don't see how this report differs from that one. You cannot select on a left-joined table since it effectively undoes the left join and removes any nulls. Further, your selection criteria above does NOT select PEOPLE who don't have pay codes in the selected period. Instead, it only selects those records that don't fall into the period.

Regarding the picklist, I would use a formula in a new report that references the CPY10050 table to see what is returned by:

left({CPY10050.PClass},1)

-LB

 

[kimprogrammer]

Ok I see where the misunderstanding is - This is not a left join report like the last one. This is an actual right joined report with records on the file. I wrote that it is "The report looks for an absent paycode that is within the payperiod parameter grouped by division". Maybe I should have put "" around the word absent. My record select said:
{PPStartDate.PPStart} = {?PayPeriodStartDate} and
{SwipeAccumulativeHours.PPDay} >= {?PayPeriodStartDate} and
{SwipeAccumulativeHours.PPDay} <= {PPStartDate.PPEnd} and
({CPY10050.PClass} = {?Division} or {?Division} = "...ALL") and
{SwipeAccumulativeHours.SupReasonCode} = "ABSENT"

I should have explained that SupReasonCode is also another name for paycode:

So this is not an issue.
---------------------------------------------
But I still have the issue with the filter. I created a new report - brought in only CPY10050 - created the command (I left out the where first and all the data is loaded) then added the where and I still have the codes in there that i don't want.
This is the command and I tried ltrim in it again as well.
----
Just to point that PClass is the key but the where clause is on the description(name) of Pclass.

--------------------------
SELECT PCLASS FROM CPY10050
where left(cpy10050.pDescription,1) <> 'Z'
UNION
SELECT '...ALL' FROM CPY10050
-------------------

This is the sql statement that is in my VB project and is does work.

Select pClass, pDescription from CPY10050 where pDescription not like 'Z%' order by pDescription

 

[kimprogrammer]

Forgot to ask in the last message - Is there no way to have some kind of debug statements is CR to see what is going on? To see what SQL statement CR command translates to.

 

[kimprogrammer]

Found it
The show SQL query says this:
SELECT PCLASS FROM CPY10050
where left(cpy10050.pDescription,1) <> 'Z'

UNION
SELECT '...ALL' FROM CPY10050



SELECT "CPY10050"."PClass", "CPY10050"."PDescription"
FROM "HYLTD"."dbo"."CPY10050" "CPY10050"

 

[kskid]

Not sure why you have a command statement. Here's what I have done to handle parameters with 'ALL' option


left({cpy10050.pDescription},1) <> "Z" and
(
if {?Division} = "ALL" then
True
else
{cpy10050.pClass} in {?Division}
)

 

[lbass]

What are you saying in your last post? I don't know what the current problem is anymore.

The command is used to populate the pick list only, but by itself it will not limit the records if "All" is chosen. Is the problem NOT with the picklist, but that when you choose all you are seeing records that start with "z"?

-LB

 

[kimprogrammer]

I though my last post was clear - the last post was made by someone else and I think they are refering to the record select. But I need the command to update the dropdown box

I'll start over
I want a prompt to select division - and I want them to be able to select 'all'. But the divisions(descripion) that start with z are inactive - so I don't need them on the list.
Pclass is the key and Pdescription is the name

When I have the command
select Pclass from cpy10050
union
select '...all' from Cpy10050
The list populates the entire cpy10050 file

But I don't want the ones with the Pdescription(name) starting with z.

So the command is now
SELECT PCLASS FROM CPY10050
where left(cpy10050.pDescription,1) <> 'Z'
UNION
SELECT '...ALL' FROM CPY10050

But I still get the entire cpy10050.

The Show SQL Query gives me this:
-----------------------------
SELECT PCLASS FROM CPY10050
where left(cpy10050.pDescription,1) <> 'Z'

UNION
SELECT '...ALL' FROM CPY10050



SELECT "CPY10050"."PClass", "CPY10050"."PDescription"
FROM "HYLTD"."dbo"."CPY10050" "CPY10050"
--------------------------
So it appears to me that the where statment is not being converted into a proper sql statement to pull the data into the list.

 

[lbass]

No, what you are seeing is the query for the command--which will show exactly as you have entered it--plus you have added the table to the main report with no record selection. You need to test what is in the first position of your field. In the main report, add a formula:

left({cpy10050.pDescription},1)

...and report back with what it returns.

-LB

 

[kimprogrammer]

I added a formula field called Formula1.
I dragged it onto the report header.

in the preview formula1 says H

I only have one employee with an "absent" record which falls in Pclass = "HYO" and Pdescription = "HYOTA...." and they are showing on the report

But the prompt still shows all the Pclass's on the file -