Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
pharoah35
Yak Posting Veteran
81 Posts |
 Posted - 2007-09-26 : 11:38:12
|
| Hello allOnce again I'm back to ask the tallented professionals for assistance again. I appreciate any assistance in advance. I have the following SQL statement which does not return any values. Although all the data is in the table(s). Would you be so kind as to take a look and let me know what i'm doing wrong. I used Query Design SELECT dbo.[TransNet Corporation$Sales Invoice Header].[Sell-to Customer No_], dbo.[TransNet Corporation$Sales Invoice Line].[Unit Price], dbo.[TransNet Corporation$Sales Invoice Header].No_ AS [Inv No.], dbo.[TransNet Corporation$Sales Invoice Line].Description, dbo.[TransNet Corporation$Sales Invoice Header].[Sell-to Customer Name], dbo.[TransNet Corporation$Sales Invoice Header].[Posting Date], dbo.[TransNet Corporation$Sales Invoice Header].[Order No_], dbo.[TransNet Corporation$Sales Invoice Line].No_ AS [Item No.], dbo.[TransNet Corporation$Sales Line].[Warehouse Order Purchase No_], dbo.[TransNet Corporation$Purch_ Inv_ Line].[Unit Cost (LCY)], dbo.[TransNet Corporation$Sales Line].[Purchase Order No_]FROM dbo.[TransNet Corporation$Purch_ Inv_ Header] INNER JOIN dbo.[TransNet Corporation$Purch_ Inv_ Line] ON dbo.[TransNet Corporation$Purch_ Inv_ Header].No_ = dbo.[TransNet Corporation$Purch_ Inv_ Line].[Document No_] INNER JOIN dbo.[TransNet Corporation$Sales Line] INNER JOIN dbo.[TransNet Corporation$Sales Header] ON dbo.[TransNet Corporation$Sales Line].[Document No_] = dbo.[TransNet Corporation$Sales Header].No_ ON dbo.[TransNet Corporation$Purch_ Inv_ Header].[Order No_] = dbo.[TransNet Corporation$Sales Line].[Warehouse Order Purchase No_] INNER JOIN dbo.[TransNet Corporation$Sales Invoice Line] INNER JOIN dbo.[TransNet Corporation$Sales Invoice Header] ON dbo.[TransNet Corporation$Sales Invoice Line].[Document No_] = dbo.[TransNet Corporation$Sales Invoice Header].No_ ON dbo.[TransNet Corporation$Sales Header].No_ = dbo.[TransNet Corporation$Sales Invoice Header].[Order No_]WHERE (dbo.[TransNet Corporation$Sales Invoice Header].[Posting Date] > CONVERT(DATETIME, '2007-08-31 00:00:00', 102)) AND (dbo.[TransNet Corporation$Sales Invoice Line].No_ <> '') |
|
|
Van
Constraint Violating Yak Guru
462 Posts |
Posted - 2007-09-26 : 11:45:44
|
| This one might be a little difficult for us to figure out (unless someone sees an obvious sql problem). Can you run these:select * from [TransNet Corporation$Sales Invoice Header] where [Posting Date] > '8/31/2007'select * from [TransNet Corporation$Sales Invoice Line] where No_ <> ''Make sure both of these have data returned.Are you sure that No_ has '' instead of NULLs? Could it be that you need 'where No_ is not null' |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-09-26 : 12:10:42
|
| Don't use this:CONVERT(DATETIME, '2007-08-31 00:00:00', 102)102 is not the matching format for 'yyyy-mm-dd hh:mm:ss', although I expect it will work just fine.Use 'yyyymmdd' for date literals, or CONVERT(datetime, 'yyyymmdd') for an explicit datetime datatype constant, if you wish. Note: No hyphens!But that ain't your problem, and my money would be on comparing dbo.[TransNet Corporation$Sales Invoice Line].No_ and the empty string:''What awful table and column names you have to deal with!Kristen |
|
|
|
|
|
|
|
|
SQL Statement does not return any data