Re: Serious errors with 'Create view' command
- From: Mark <Mark@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Fri, 22 Sep 2006 06:00:02 -0700
Hi,
The statement below is ALWAYS true
and can't give yo any results.WHERE (?mydivno <= 0 Or divno = ?mydivno)
So you might have to rewrite it like this
WHERE IIF(?mydivno <= 0,0,divno = ?mydivno) ...
WHERE IIF(?mydivno <= 0,.T.,divno = ?mydivno) ...
WHERE IIF(?mydivno <= 0,.F.,divno = ?mydivno) ...
Please test, maybe you have to put quotes around like below
WHERE IIF(?mydivno <= 0,0,"divno = ?mydivno") ...
hth
Mark
"Barley Man" wrote:
Hi Anders..
I don't understand what you are suggesting/asking. The 0s (zeros) are the
method to detect if the ?variable (?mydivno, ?mydeptno, etc.) are to be used
at all and don't (I think) refer to the values within the table being search
at all.
Let me explain the principle. The structure of the compnay is set into a
heirarchy where the stock is recorded at it's lowest level in stock control
numbers (SKUs), groups of these skusc make Classes, groups of classes make
subdepts, groups of subdepts make depts anmd groups of depts make Divisions
and that is the highest level. To identify a single class, subdept, dept,
division you need to supply the number of ALL the hierarchies above it as
they have re-used the numbers (for example) of different classes if they
exist in different sub-depts (I know that's BIZARRE but....).
They want 'outputs' on each layer of the hierarchy. The way they decide how
far 'down' the hierarcy that want to 'look' is by supplying the number of the
hierearchies ABOVE the level they need and they show they don't want the data
further 'drilled down' is by enetering a 0 to the lowest level they whoich to
view.
So........If they want an 'output' of the whole company, they supply the
following values to the system, mydivno=0, mydeptno=0, mysubdeptno=0,
myclassno=0. The '0' (szeros) are saying 'don't filter on 'this' hierarchy).
However, if they want an 'output' at subdept level, they suipply the
following values: mydivno=1, mydeptno=30, mysubdeptno=110, myclassno=0.
Effectively, the '0' (zero) is being used to say, don't 'filter' on this
basis.. Thus, they's get an (outpout) contai8ning a\ll the SKUS from supdept
110 ewhich is a subset of Dept 30 which is a subset of divsion 1!
I am still stuck
There is the facility to use 'edit stored procedures' but I can;t get that
to work at ALL!
Ian
"AA" wrote:
Using VFP9 your CREATE VIEW displays like this in the View Designer View SQL
window:
SELECT Inventory.inumbr, Inventory.divno, Inventory.deptno,;
Inventory.subdeptno, Inventory.classno, Inventory.istyl;
FROM ;
inventory;
WHERE ( ( ( ( ?mydivno ) <= ( 0 );
OR Inventory.divno = ( ?mydivno ) );
AND ( ( ?mydeptno ) <= ( 0 );
OR Inventory.deptno = ( ?mydeptno ) ) );
AND ( ( ?mysubdeptno ) <= ( 0 );
OR Inventory.subdeptno = ( ?mysubdeptno ) ) );
AND ( ( ?myclassno ) <= ( 0 );
OR Inventory.classno = ( ?myclassno ) )
However DEBUGOUT ('combinedview', 'View', 'Sql')
returns the exact code that was entered:
Select inventory.inumbr, inventory.divno, inventory.deptno,
inventory.subdeptno, inventory.classno, inventory.istyl FROM inventory
WHERE (?mydivno <= 0 Or divno = ?mydivno) AND (?mydeptno <= 0 Or deptno =
?mydeptno) AND (?mysubdeptno <= 0 Or subdeptno = ?mysubdeptno) AND
(?myclassno <= 0 Or classno = ?myclassno)
There are four columns that may take any positive or negative integer values
in any of the 16 possible combinations. Right?
If these are foreign keys that reference primary keys in the Divisions,
Departments, Supdepartment and/or Classes tables there shouldn't exist any 0
or less values, as I see it.
-Anders
"Barley Man" <BarleyMan@xxxxxxxxxxxxxxxxxxxxxxxxx> skrev i meddelandet
news:A4DB29BB-1787-4E90-83A6-2209790A74EA@xxxxxxxxxxxxxxxx
I have used the following 'create view' statment:-
Create Sql View combinedview;
AS Select inventory.inumbr, inventory.divno, inventory.deptno,
inventory.subdeptno, inventory.classno, ;
inventory.istyl;
FROM inventory ;
WHERE (?mydivno <= 0 Or divno = ?mydivno);
AND (?mydeptno <= 0 Or deptno = ?mydeptno);
AND (?mysubdeptno <= 0 Or subdeptno = ?mysubdeptno);
AND (?myclassno <= 0 Or classno = ?myclassno)
However, when I 'inspect' (modify) the created View by looking at the sql,
the 'view' created is NOT the same as the one I requtested and appears as
follows:-
SELECT Inventory.inumbr, Inventory.divno, Inventory.deptno,;
Inventory.subdeptno, Inventory.classno, Inventory.istyl;
FROM drilldown2!inventory;
WHERE ?mydivno <= 0;
OR (Inventory.divno = ?mydivno;
AND ?mydeptno <= 0);
OR (Inventory.deptno = ?mydeptno;
AND ?mysubdeptno <= 0);
OR (Inventory.subdeptno = ?mysubdeptno;
AND ?myclassno <= 0);
OR (Inventory.classno = ?myclassno)
The change is not immediately obvious to see but it IS there. The
positioning (and thus the effects) of the brackets have changed
SUBSTANTIALLY
and the routine simply fails to perform properly. I need the EXACT
functionality of the statement contained within the 'create
view'instructions
but I simply can't get it to 'transfer' into the database as a 'view'!
It does not appear to be a simple error in the 'view sql' routine as the
view BEHAVES EXACTLY in line with the version being displayed by 'view
sql'
and that's NOT what I want, nor what I requested!
What on EARTH am I supposed to do about THAT?
Ian
- Follow-Ups:
- Re: Serious errors with 'Create view' command
- From: Barley Man
- Re: Serious errors with 'Create view' command
- References:
- Re: Serious errors with 'Create view' command
- From: Barley Man
- Re: Serious errors with 'Create view' command
- Prev by Date: Re: Serious errors with 'Create view' command
- Next by Date: Re: Serious errors with 'Create view' command
- Previous by thread: Re: Serious errors with 'Create view' command
- Next by thread: Re: Serious errors with 'Create view' command
- Index(es):
Relevant Pages
|
