Can you use Date_time timestamp columns for Whereclause 
Author Message
 Can you use Date_time timestamp columns for Whereclause

I am trying to create a view which compares records in two
similar tables  in 2 different daatabases
in the parent table (starbuilder.dbo.JCCostMast) when a record
is updated it is timestamped in a column called time_stamp.

The child table (FlashReport.dbo.JCCostMast) it may have
records which are the same record but it has an older
timestamp (also called time_stamp)

I have joined the tables on 5 fields which comprise the
primary key in Starbuilder  ( I did not create the same keys
in the FlashReport table)

to test it i have both tables with matching data.  I then
changed an amount column value ( not a primary key) and
manualy changed the StarBuilder.dbo.time_stamp  just as the
application would do. ( the timestamp is not trigger driven)

SO  if all the primary keys are = and the timestamp is not
then I want to see the records in my view.

ALAS  I get no records back

Here's the code

Any Ideas what's wrong?

Thanks BeforeHand

AJ  (Bubba Ray)
*************************************************

SELECT StarBuilder.dbo.JCCostMast.cmpny_cd,
    StarBuilder.dbo.JCCostMast.job_no,
    StarBuilder.dbo.JCCostMast.phase_no,
    StarBuilder.dbo.JCCostMast.cost_no,
    StarBuilder.dbo.JCCostMast.cost_type,
    StarBuilder.dbo.JCCostMast.acct_no,
    StarBuilder.dbo.JCCostMast.c_est_cst AS Est,
    StarBuilder.dbo.JCCostMast.c_est_qty AS Qty,
    StarBuilder.dbo.JCCostMast.c_uom,
    StarBuilder.dbo.JCCostMast.time_stamp AS Expr1
FROM StarBuilder.dbo.JCCostMast INNER JOIN
    JCCostMast ON
    StarBuilder.dbo.JCCostMast.cmpny_cd = JCCostMast.cmpny_cd
     AND
    StarBuilder.dbo.JCCostMast.job_no = JCCostMast.job_no AND
    StarBuilder.dbo.JCCostMast.phase_no = JCCostMast.phase_no
AND
     StarBuilder.dbo.JCCostMast.cost_no = JCCostMast.cost_no
AND
     StarBuilder.dbo.JCCostMast.cost_type =
JCCostMast.cost_type AND
     StarBuilder.dbo.JCCostMast.time_stamp >
JCCostMast.time_stamp



Thu, 01 Jul 2004 14:46:48 GMT
 Can you use Date_time timestamp columns for Whereclause

Bubba,

Posting a query that does not work is unfortunately of very little value.
Posting (simplified) table schemas (CREATE TABLEs), sample data (INSERTs).
and the results you want back using the sample data, on the other hand, is
of great value.

It's a lot like, if you go to a user to ascertain requirements, and the user
shows you what he doesn't want, but doesn't show you what he does want   :-)

-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.


Quote:
> I am trying to create a view which compares records in two
> similar tables  in 2 different daatabases
> in the parent table (starbuilder.dbo.JCCostMast) when a record
> is updated it is timestamped in a column called time_stamp.

> The child table (FlashReport.dbo.JCCostMast) it may have
> records which are the same record but it has an older
> timestamp (also called time_stamp)

> I have joined the tables on 5 fields which comprise the
> primary key in Starbuilder  ( I did not create the same keys
> in the FlashReport table)

> to test it i have both tables with matching data.  I then
> changed an amount column value ( not a primary key) and
> manualy changed the StarBuilder.dbo.time_stamp  just as the
> application would do. ( the timestamp is not trigger driven)

> SO  if all the primary keys are = and the timestamp is not
> then I want to see the records in my view.

> ALAS  I get no records back

> Here's the code

> Any Ideas what's wrong?

> Thanks BeforeHand

> AJ  (Bubba Ray)
> *************************************************

> SELECT StarBuilder.dbo.JCCostMast.cmpny_cd,
>     StarBuilder.dbo.JCCostMast.job_no,
>     StarBuilder.dbo.JCCostMast.phase_no,
>     StarBuilder.dbo.JCCostMast.cost_no,
>     StarBuilder.dbo.JCCostMast.cost_type,
>     StarBuilder.dbo.JCCostMast.acct_no,
>     StarBuilder.dbo.JCCostMast.c_est_cst AS Est,
>     StarBuilder.dbo.JCCostMast.c_est_qty AS Qty,
>     StarBuilder.dbo.JCCostMast.c_uom,
>     StarBuilder.dbo.JCCostMast.time_stamp AS Expr1
> FROM StarBuilder.dbo.JCCostMast INNER JOIN
>     JCCostMast ON
>     StarBuilder.dbo.JCCostMast.cmpny_cd = JCCostMast.cmpny_cd
>      AND
>     StarBuilder.dbo.JCCostMast.job_no = JCCostMast.job_no AND
>     StarBuilder.dbo.JCCostMast.phase_no = JCCostMast.phase_no
> AND
>      StarBuilder.dbo.JCCostMast.cost_no = JCCostMast.cost_no
> AND
>      StarBuilder.dbo.JCCostMast.cost_type =
> JCCostMast.cost_type AND
>      StarBuilder.dbo.JCCostMast.time_stamp >
> JCCostMast.time_stamp



Fri, 02 Jul 2004 02:14:11 GMT
 Can you use Date_time timestamp columns for Whereclause
Bubba,

Does it return the expected results with the following query :

SELECT StarBuilder.dbo.JCCostMast.cmpny_cd,
    StarBuilder.dbo.JCCostMast.job_no,
    StarBuilder.dbo.JCCostMast.phase_no,
    StarBuilder.dbo.JCCostMast.cost_no,
    StarBuilder.dbo.JCCostMast.cost_type,
    StarBuilder.dbo.JCCostMast.acct_no,
    StarBuilder.dbo.JCCostMast.c_est_cst AS Est,
    StarBuilder.dbo.JCCostMast.c_est_qty AS Qty,
    StarBuilder.dbo.JCCostMast.c_uom,
    StarBuilder.dbo.JCCostMast.time_stamp AS Expr1
FROM StarBuilder.dbo.JCCostMast INNER JOIN
    JCCostMast ON
    StarBuilder.dbo.JCCostMast.cmpny_cd = JCCostMast.cmpny_cd
     AND
    StarBuilder.dbo.JCCostMast.job_no = JCCostMast.job_no AND
    StarBuilder.dbo.JCCostMast.phase_no = JCCostMast.phase_no
AND
     StarBuilder.dbo.JCCostMast.cost_no = JCCostMast.cost_no
AND
     StarBuilder.dbo.JCCostMast.cost_type =
JCCostMast.cost_type
WHERE      StarBuilder.dbo.JCCostMast.time_stamp >
JCCostMast.time_stamp

If not, then a complete script to reproduce the problem would be required
to figure what could be going wrong.

Hope that helps.

Regards,
Rishi M. Agrawal, MCDBA

This posting is provided "AS IS" with no warranties, and confers no rights.

Are you secure?  For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.
--------------------

| Newsgroups: microsoft.public.sqlserver.programming
| Subject: Can you use Date_time timestamp columns for Whereclause
| Date: Sun, 13 Jan 2002 00:46:48 -0600
| Lines: 57
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2600.0000
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000
| NNTP-Posting-Host: ip-dsl-207-218-251-35.ev1.net

| X-Trace: newsa.ev1.net 1010904096 ip-dsl-207-218-251-35.ev1.net (13 Jan
2002 00:41:36 -0600)
| Path:
cpmsftngxa09!tkmsftngp01!newsfeed00.sul.t-online.de!t-online.de!news-spur1.m
axwell.syr.edu!news.maxwell.syr.edu!nnxp1.twtelecom.net!newsa.ev1.net
| Xref: cpmsftngxa09 microsoft.public.sqlserver.programming:222617
| X-Tomcat-NG: microsoft.public.sqlserver.programming
|
| I am trying to create a view which compares records in two
| similar tables  in 2 different daatabases
| in the parent table (starbuilder.dbo.JCCostMast) when a record
| is updated it is timestamped in a column called time_stamp.
|
| The child table (FlashReport.dbo.JCCostMast) it may have
| records which are the same record but it has an older
| timestamp (also called time_stamp)
|
| I have joined the tables on 5 fields which comprise the
| primary key in Starbuilder  ( I did not create the same keys
| in the FlashReport table)
|
| to test it i have both tables with matching data.  I then
| changed an amount column value ( not a primary key) and
| manualy changed the StarBuilder.dbo.time_stamp  just as the
| application would do. ( the timestamp is not trigger driven)
|
| SO  if all the primary keys are = and the timestamp is not
| then I want to see the records in my view.
|
| ALAS  I get no records back
|
| Here's the code
|
| Any Ideas what's wrong?
|
| Thanks BeforeHand
|
| AJ  (Bubba Ray)
| *************************************************
|
| SELECT StarBuilder.dbo.JCCostMast.cmpny_cd,
|     StarBuilder.dbo.JCCostMast.job_no,
|     StarBuilder.dbo.JCCostMast.phase_no,
|     StarBuilder.dbo.JCCostMast.cost_no,
|     StarBuilder.dbo.JCCostMast.cost_type,
|     StarBuilder.dbo.JCCostMast.acct_no,
|     StarBuilder.dbo.JCCostMast.c_est_cst AS Est,
|     StarBuilder.dbo.JCCostMast.c_est_qty AS Qty,
|     StarBuilder.dbo.JCCostMast.c_uom,
|     StarBuilder.dbo.JCCostMast.time_stamp AS Expr1
| FROM StarBuilder.dbo.JCCostMast INNER JOIN
|     JCCostMast ON
|     StarBuilder.dbo.JCCostMast.cmpny_cd = JCCostMast.cmpny_cd
|      AND
|     StarBuilder.dbo.JCCostMast.job_no = JCCostMast.job_no AND
|     StarBuilder.dbo.JCCostMast.phase_no = JCCostMast.phase_no
| AND
|      StarBuilder.dbo.JCCostMast.cost_no = JCCostMast.cost_no
| AND
|      StarBuilder.dbo.JCCostMast.cost_type =
| JCCostMast.cost_type AND
|      StarBuilder.dbo.JCCostMast.time_stamp >
| JCCostMast.time_stamp
|
|
|



Wed, 21 Jul 2004 06:14:05 GMT
 
 [ 3 post ] 

 Relevant Pages 

1. whereclause and timestamp

2. replicating timestamp column + using warm standby

3. Binding to timestamp column using string data

4. Using a timestamp column and VB 5.0

5. Using TimeStamp column

6. Problem loading to Timestamp column using SQL Loader

7. Binding to timestamp column using string data

8. Using the timestamp column for unique identifier

9. Comparing against a TimeStamp column using TSEqual() function.

10. Not possible to convert SQLServer timestamp column to an ORACLE column

11. INFORMIX SQL DATE_TIME type expressed in C-ISAM

12. converting date_time to seconds


 
Powered by phpBB® Forum Software