Re: Serious errors with 'Create view' command

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Again,mark, I don't understand.

Firstly:

You say that:-

"WHERE (?mydivno <= 0 Or divno = ?mydivno) won't work..."

.......whereas it works PERFECTLY when run within the qusry in whcih I
designed and tested the routine BEFORE I attempted to us it to 'create a
view'! The whole section of the first 'cut and paste' works EXACTLY as I need
it to perform. It's NOT a fault with my coding, it's a fault the the Create
View command!

Secondly:-

I can't see how I would insert the "WHERE IIF(?mydivno <= 0,0,divno =
?mydivno)" etc. into my coding. I didn't think the 'IIF' statement would
function within an SQL statement, I've tried it with 'Evaluate' in there as
well and that won't work either.



"Mark" wrote:

Hi,
The statement below is ALWAYS true
WHERE (?mydivno <= 0 Or divno = ?mydivno)
and can't give yo any results.

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



.

Relevant Pages

  • Re: Serious errors with Create view command
    ... Open the view designer - new view, just select the database/tables ... Close sql view ... It does not appear to be a simple error in the 'view sql' routine as ...
    (microsoft.public.fox.helpwanted)
  • RE: Parameterized components?
    ... I know how to parameterize a SQL string, ... In that walkthrough...all the data adapters are configured on the component ... I want to populate a listbox called "Location". ... > a designer will popup that looks like the MS Access SQL designer, ...
    (microsoft.public.dotnet.distributed_apps)
  • Re: Enterprise Manager confusion
    ... case of Microsoft introducing functionality in a Service Pack. ... On my old laptop, I had SQL Server Enterprise Manager installed, I believe ... sizes in the View designer, and properly handles CASE statements as well. ... It also take *dreadfully* long to load any complex queries into the View ...
    (microsoft.public.sqlserver.tools)
  • Re: Serious errors with Create view command
    ... subdepts, groups of subdepts make depts anmd groups of depts make Divisions ... division you need to supply the number of ALL the hierarchies above it as ... Using VFP9 your CREATE VIEW displays like this in the View Designer View SQL ... inventory.subdeptno, inventory.classno, inventory.istyl FROM inventory ...
    (microsoft.public.fox.helpwanted)
  • Re: How to solve DataSet Designer SQL errors?
    ... generating SQL to configure data adapters. ... to the 2003 style of dragging data adapters onto a component designer ... Builder, using a Jet 4.0 connection to an Access 2003 DB. ...
    (microsoft.public.dotnet.framework.adonet)