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
|
|
|
