Re: Opinions on approach, please...

On Tue, 27 May 2008 12:03:55 +0000 (UTC), docdwarf@xxxxxxxxx () wrote:

In article <6a2b0aF35f7liU1@xxxxxxxxxxxxxxxxxx>,
Pete Dashwood <dashwood@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:


"Robert" <no@xxxxxx> wrote in message
news:t5vm34t5gtsfc2mv4qce963d8nra23bcu3@xxxxxxxxxx
On Mon, 26 May 2008 22:42:39 +0100, Frederico Fonseca
<real-email-in-msg-spam@xxxxxxxxx>
wrote:

[snip]

Table A with index as follows.
company
accounting_year
accounting_month
product_code
sequence_number

[snip]

On this situation, programs that do NOT require the product code to be
a specific one would set w-product-ind to "0". The others would set it
to "1", and supply the product code.

where company = :w-company
and accounting_year = :w-year
and accounting_month = :w-month
and product_code =
case when :w-product-code > ' ' then :w-product-code
else product_code
end

This will not work if product_code in the database is null.


OK.

Given that Mr Fonesca did not specify any keys other than the one given it
might be reasonable to conclude that Mr Fonesca has specified the table's
primary key. With that in mind... are there many databases out there
which allow for nulls in the middle of a primary key?

DD
DD,

You are, perhaps, confusing allowing nulls on a key, with not
supplying it when selecting which records to retrieve.


Going back to COBOL.

select file ... key is key1

fd file
01 rec1
05 key1
10 company
10 accounting_year
10 accounting_month
10 product_code
10 sequence_number

The above would translate to my sql example above.

In cobol when you wish to start on the above key, you do not need to
populate all fields as I know you are aware. Same applies to SQL.

So a start in COBOL as follows...
initialize key1
move 1 to company
move 2005 to accounting_year
start .... invalid key...

can be translated into SQL as
where company >= :w-company
and accounting_year >= :w-year

If the programer KNOWS that access to this table, with rare
exceptions, is always with a unique company value, then the above SQL
would be better written as
where company = :w-company
and accounting_year >= :w-year


Frederico Fonseca
ema il: frederico_fonseca at syssoft-int.com
.

Relevant Pages

  • Re: 3vl 2vl and NULL
    ... although for some analytical reporting a list ... I'll grant) of SQL for the same tasks. ... issue in practice in PICK than in SQL shops, and less of an issue for ...
    (comp.databases.theory)
  • Re: Help with derived table SQL statement in Access
    ... Table aliases increase the clarity of SQL by explicitly noting each ... If I remove the where clause from the subquery, it works, but I get ... SELECT expression on the FROM clause, but that is just that, a table ...
    (microsoft.public.access.queries)
  • Re: Resolved: NOT (w/ informal proof). --Was Re: Static vs. Dynamic typing (big advantage or not)-
    ... > does not have a statically bound method implementing that message. ... Thank goodess SQL isn't statically compiled. ... given a row from the database with a column called contractKey I ...
    (comp.object)
  • Re: Just one more anecdote
    ... >> Marshall Spight wrote: ... ignorant, but when I'm not, I ... > Your recurring theme is limitations of SQL and the RM, ...
    (comp.databases.theory)
  • Re: EXEC syntax for SP which returns row set
    ... I admit that I am new to Oracle stored procedures, ... > SQL> create table employee as select empno id from emp; ... I don't know why you are supplying the above example. ... before anyone else and I posted the website were I found the answer. ...
    (comp.databases.oracle.server)