| Author |
Message |
nhopp4
Groupie
Joined: 03 Oct 2012
Location: United States
Online Status: Offline
Posts: 62
|
  
Posted: 22 May 2013 at 9:19am |
Thank you for getting back to me so quick! I tried what you suggested and I am still getting an error.
Error Occurred: Query must contain the required condition "messaging_ID is not null"
SELECT "ORG"."ORG_CODE", "LISTING"."NAME", "LISTING"."MESSAGING_ID", "LISTING"."EMPLOYEE_ID"
FROM "ATMS"."LISTING" "LISTING" INNER JOIN "ATMS"."ORG" "ORG" ON "LISTING"."ORG_SEQNUM"="ORG"."ORG_SEQNUM"
WHERE "ORG"."ORG_CODE" LIKE '060%' and (NOT ("listing"."messaging_id" IS NULL))
ORDER BY "LISTING"."NAME"
Thanks again!
Nick
|
 IP Logged |
|
|
|
nhopp4
Groupie
Joined: 03 Oct 2012
Location: United States
Online Status: Offline
Posts: 62
|
Posted: 22 May 2013 at 9:30am |
I found this in the help file..
The SQL statement follows a set of rules that must be followed:
• select columns must be l.listing_id, l.messaging_id, l.name
• the table listing has be included in the FROM list and have an alias of l
• the condition clause has to have l.messaging_id IS NOT NULL
The SQL statement must always begin as:
SELECT l.listing_id,l.messaging_id,l.name FROM listing l WHERE l.messaging_id IS NOT NULL |
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 22 May 2013 at 9:39am |
Help file is in what application?
based on the help you listed I would try
SELECT "ORG"."ORG_CODE", "L"."NAME", "L"."MESSAGING_ID", "L"."EMPLOYEE_ID"
FROM "ATMS"."LISTING" "L" INNER JOIN "ATMS"."ORG" "ORG" ON "LISTING"."ORG_SEQNUM"="ORG"."ORG_SEQNUM"
WHERE "ORG"."ORG_CODE" LIKE '060%' and "L"."messaging_id" IS NOT NULL
ORDER BY "LISTING"."NAME"
|
| IP Logged |
|
nhopp4
Groupie
Joined: 03 Oct 2012
Location: United States
Online Status: Offline
Posts: 62
|
Posted: 23 May 2013 at 4:17am |
The application called Amcom is where the help file is where I am trying to write the SQL code. Part of this I took from Crystal Reports XI in the show SQL Query after I wrote the report. The Crystal Reports report works good. But I need this SQL Query in this application to work for other reasons but cannot get it figured out.
I tried to do what you told me and I still got the same error about the messaging ID. When I took the " off of the l.messaging_ID it gave me a different error.
Error Occurred: -904 ORA-00904: "Listing"."ORG_SEQNUM": Invalid Identifier
SELECT "ORG"."ORG_CODE", "L"."NAME", "L"."MESSAGING_ID", "L"."EMPLOYEE_ID"
FROM "ATMS"."LISTING" "L" INNER JOIN "ATMS"."ORG" "ORG" ON "LISTING"."ORG_SEQNUM"="ORG"."ORG_SEQNUM"
WHERE "ORG"."ORG_CODE" LIKE '060%' and L.messaging_id IS NOT NULL
ORDER BY "LISTING"."NAME"
Thanks guys for helping!!
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 23 May 2013 at 4:31am |
Need to replace LISTING with L now that the alias for the table was altered
SELECT "ORG"."ORG_CODE", "L"."NAME", "L"."MESSAGING_ID", "L"."EMPLOYEE_ID"
FROM "ATMS"."LISTING" "L" INNER JOIN "ATMS"."ORG" "ORG" ON "L"."ORG_SEQNUM"="ORG"."ORG_SEQNUM"
WHERE "ORG"."ORG_CODE" LIKE '060%' and "L"."messaging_id" IS NOT NULL
ORDER BY "L"."NAME" Edited by DBlank - 23 May 2013 at 4:32am
|
| IP Logged |
|
nhopp4
Groupie
Joined: 03 Oct 2012
Location: United States
Online Status: Offline
Posts: 62
|
Posted: 23 May 2013 at 4:41am |
Ok I changed this to the code below and took the " off of L.Messaging_ID because I was still getting the Messaging_ID is not Null error. When I removed the " I am now getting:
Error Occurred: -920 ORA-00920: Invalid relational operator
SELECT "ORG"."ORG_CODE", "L"."NAME", "L"."MESSAGING_ID", "L"."EMPLOYEE_ID"
FROM "ATMS"."LISTING" "L" INNER JOIN "ATMS"."ORG" "ORG" ON "L"."ORG_SEQNUM"="ORG"."ORG_SEQNUM"
WHERE "ORG"."ORG_CODE" LIKE '060%' and L.messaging_id IS NOT NULL
ORDER BY "L"."NAME"
Thanks so much!
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 23 May 2013 at 4:51am |
I am not seeing an issue other than you have to identify the table and field correctly with the quotes.
what error do you get with
SELECT "ORG"."ORG_CODE", "L"."NAME", "L"."MESSAGING_ID", "L"."EMPLOYEE_ID"
FROM "ATMS"."LISTING" "L" INNER JOIN "ATMS"."ORG" "ORG" ON "L"."ORG_SEQNUM"="ORG"."ORG_SEQNUM"
WHERE "ORG"."ORG_CODE" LIKE '060%' and "L"."MESSAGING_ID" IS NOT NULL
ORDER BY "L"."NAME"
|
| IP Logged |
|
nhopp4
Groupie
Joined: 03 Oct 2012
Location: United States
Online Status: Offline
Posts: 62
|
Posted: 23 May 2013 at 4:54am |
When I leave the quotes on I get the message:
Error Occurred: Query must contain required condition of "messaging_ID is not null"
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 23 May 2013 at 5:46am |
|
I am at a loss as this is seems to be unique to the Software that you are using and not a Crystal rule. Perhaps someone reading the threas might jump in...?
|
| IP Logged |
|
nhopp4
Groupie
Joined: 03 Oct 2012
Location: United States
Online Status: Offline
Posts: 62
|
Posted: 23 May 2013 at 10:17am |
Thanks for looking DBlank... If you are at a loss then I am most certainly at a loss. Anyone else that can look at the code would be greatly appreciated!
Thanks!
|
| IP Logged |
|
|
|
