doug_mize
Posts: 12
|
| Posted: 04/09/2008, 5:48 PM |
|
Hello! I was wondering how, in the VSB to add these two WHERE conditions:
WHERE custlist.custnum = $user AND custlist.listname = $custom_list_name
(comparing table field data against variables)
If I set the variables as parameters in the VQB they simply don't work.
If I set them as expressions surrounded by double quotes (") it works, but..
and this is a HUGE BUT..
These conditions become absolute, in that the grid only displays records that match my conditions.
The problem is: I need the results of my WHERE conditions displayed in descending order, AND also have the non-matches displayed BELOW the matching results (by symbol, which is the only common field between the two tables that I'm puling data from for my grid).
I'm currently using a RIGHT JOIN between the two tables but it isn't doing what I want. Again, if I set the variables to expressions, it's only displaying the records that match my WHERE conditions and not the rest of the records, sorted by stockstatus.symbol., which is what I need.
Basically I need to pull data from tow tables and JOIN them by the common field symbol. Then I need the results filtered out by comparing fields from one of the tables to my variables, then display first; the matching results, then the non-matches below the matching ones.
What I'm trying to do is let users make custom lists of stocks they are tracking. I created a table called (custlist) where a record is written with their unique user# ($user - taken from a cookie) and their custom list name ($custom_list_name - made by a different form) which is then the filter by which my grid displays their unique list.
Every stock that is on their unique list needs to be displayed first in the results, then, every other stock that is not on their list needs to be displayed below their listed ones. And no stock symbol can be listed twice. If a stock is on their list (denoted by symbol), then it isn't listed below, because each stock can only exist once in my grid. Either on their custom list, or not.
What am I doing wrong??? Any ideas?
Thanks in advance!!!!
Doug
|
 |
 |
mamboBROWN
Posts: 1713
|
| Posted: 04/11/2008, 8:41 AM |
|
doug_mize
How are you passing the variables to this query (through the url or as session variables)??
Have you attemped to dynamically change the where or order by sections of the query? ( http://docs.codecharge.com/studio40/html/ProgrammingTec...e/Overview.html )
|
|
|
|
doug_mize
Posts: 12
|
| Posted: 04/14/2008, 12:59 PM |
|
Are you asking me about the variables of the WHERE condition? If so, I actually hard coded the values of my WHERE condition rather than use my session variables for the sake of testing.
And per your suggestion ( http://docs.codecharge.com/studio40/html/ProgrammingTec...e/Overview.html ) I added this to the Before Build Select event for my grid:
if ($custlist_stockstatus1->DataSource->Where <> "") {
$custlist_stockstatus1->DataSource->Where .= " AND ";
}
$custlist_stockstatus1->DataSource->Where .= "custlist.custnum = '200' AND custlist.listname = 'Kevins List'";
Again, I actually hard coded the values of my WHERE condition rather than use my session variables.
When I publish the files, the grid filters out all records that don't match my WHERE conditions and simply IGNORES my RIGHT JOIN from the VQB.
So the problem is my RIGHT JOIN. Whenever I use a RIGHT JOIN without CCS I can get my records to display exactly as I want them like a true RIGHT or LEFT JOIN should. By that I mean:
(http://dev.mysql.com/doc/refman/5.0/en/left-join-optimization.html)
"The LEFT JOIN condition is used to decide how to retrieve rows from table B. (In other words, any condition in the WHERE clause is not used.)... The implementation of RIGHT JOIN is analogous to that of LEFT JOIN with the roles of the tables reversed."
So, the problem continues to be that I need the grid to display the records that match my WHERE conditions, then, below those results I need it to display rest of the results that do not match.
It's really that plain and simple. It's a simple RIGHT JOIN with a measly two part WHERE condition.
So why in the hell doesn't it work with CCS?????????
This has been driving me mad for over a week with nobody able to explain why the RIGHT JOIN seems to conflict with the pre-made search functions and they get all wacky.
or
Why when I put my two simple WERE conditions into the VQB they simply get ignored.
or
Why when I put my two simple WHERE conditions into the Before Build Select event for my grid via the process described here: ( http://docs.codecharge.com/studio40/html/ProgrammingTec...e/Overview.html ) the grid also IGNORES my RIGHT JOIN and only displays the records that match my conditions and nothing more.
Without the ability to combine tables into a single grid, sort them by RIGHT or LEFT JOINS and custom WHERE conditions AND use the pre-made search capabilities of CCS then CCS is worthless to me.
Any further help will be appreciated.
Doug
|
|
|
|
Oper
Posts: 1195
|
| Posted: 04/15/2008, 3:29 PM |
|
post complete select / where / join etc
_________________
____________________________
http://www.7bz.com (Free CMS,CRM Developed in CCS)
http://www.PremiumWebTemplate.com
Affiliation Web Site Templates
Please do backup first |
|
|
|
|
