Report Design: SQL Code help

Print Page | Close Window

SQL Code help

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2022
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=19533
Printed Date: 20 Sep 2024 at 2:59pm

Topic: SQL Code help

Posted By: nhopp4
Subject: SQL Code help
Date Posted: 13 May 2013 at 9:06am

Hi guys I am trying to build a query through SQL and having some troubles. I dont use SQL at all but I am only left with this option in the application. In the application there has to be a certain line in every SQL command which is:

SELECT l.listing_id,l.messaging_id,l.name FROM listing l WHERE l.messaging_id IS NOT NULL

I have created this report in Crystal fine but it is a different query inside this application and is where I need it. Here is my SQL code for Crystal:

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%'
ORDER BY "LISTING"."NAME"

Can someone help me combine the 2 codes I keep getting a too many values error.

Thanks!

Nick

SELECT l.listing_id,l.messaging_id,l.name, listing.messaging_ID, "ORG"."ORG_CODE", "LISTING"."EMPLOYEE_ID"
FROM listing l, "ATMS"."LISTING" "LISTING" INNER JOIN "ATMS"."ORG" "ORG" ON "LISTING"."ORG_SEQNUM"="ORG"."ORG_SEQNUM" WHERE l.messaging_id IS NOT NULL AND listing.messaging_ID = '27811'

Replies:

Posted By: bwsanders
Date Posted: 14 May 2013 at 2:23am

have you tried using a sql expression field inside of crystal? i'm not 100% sure of what you are trying to do. if you have your crystal report set up and you just need to pas a sql query to it you will want to use the sql expression field to do so.

Posted By: nhopp4
Date Posted: 15 May 2013 at 7:44am

I have the crystal reports created for this in SQL which works fine. However, I need to create a SQL Query inside an application that I cannot figure out.

I need to combine

SELECT l.listing_id,l.messaging_id,l.name FROM listing l WHERE l.messaging_id IS NOT NULL

With

Thank you for getting back to me I hope this makes sense. In the application is must have this line: SELECT l.listing_id,l.messaging_id,l.name FROM listing l WHERE l.messaging_id IS NOT NULL

I cannot figure out how to combine the 2 I keep getting a too many values error and I am pretty new at writing SQL code. Any help would be appreciated.

Thank you!

Posted By: bwsanders
Date Posted: 15 May 2013 at 8:02am

so you need to just input the extra items from your first query into your second query in whatever order you want them to appear.

then, in your where clause you need to also include "and NOT IsNull(l.messaging)

since the join is already done that should be that simple.

Posted By: nhopp4
Date Posted: 15 May 2013 at 8:10am

I get an error SQL not properly ended when I try to combine the two now.

SELECT l.listing_id,l.messaging_id,l.name

FROM listing l

WHERE l.messaging_id IS NOT NULL, "ORG"."ORG_CODE" =

(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%'
ORDER BY "LISTING"."NAME")

Not sure if this is the way to go about it or not.

Thanks!

Posted By: nhopp4
Date Posted: 15 May 2013 at 8:15am

Didnt see your last message. I tried what you said I think and I get an error Query must contain required condition of "messaging_ID is not null"

Thanks!

Posted By: bwsanders
Date Posted: 15 May 2013 at 8:16am

NOT IsNull(input whatever field you want to make sure is not null here)

Posted By: nhopp4
Date Posted: 15 May 2013 at 8:22am

Still getting the same error. Listing.messaging_ID is the field that I need to not be null.

Thanks!

Posted By: nhopp4
Date Posted: 22 May 2013 at 8:34am

Does anyone have any ideas on this?

Posted By: DBlank
Date Posted: 22 May 2013 at 8:52am

and (NOT ("listing"."messaging_id" IS NULL))

Posted By: nhopp4
Date 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"

Thanks again!

Nick

Posted By: nhopp4
Date 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

Posted By: DBlank
Date 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"

Posted By: nhopp4
Date 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!!

Posted By: DBlank
Date 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"

Posted By: nhopp4
Date 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

Thanks so much!

Posted By: DBlank
Date 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

Posted By: nhopp4
Date 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"

Posted By: DBlank
Date 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...?

Posted By: nhopp4
Date 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!

Posted By: nhopp4
Date Posted: 31 May 2013 at 7:54am

Figured this out. DBlank you were right there are weird rules for this application. Apparently the dynamic message group sql columns HAVE to be three in number, and the columns HAVE to be in the order of listing_id, messaging_id, name. I don't know why... We think it must just be in the order the columns exist in the (listing) table.

It also:

has to end with "messaging_id is not null" can't end with a semi-colon (which is a pl/sql standard, but the form is driving these queries);

and extra things at the end, like "order by l.messaging_id", isn't part of the form's built-in sql (hopefully it's already built in to it, so the end user doesn't have to specify it).

Here is the code that works!

select l.listing_id, l.messaging_id, l.name

from listing l, org o

where l.org_seqnum = o.org_seqnum

and o.org_code like '060%'

and l.messaging_id is not null

Thank you for looking into this!

Print Page | Close Window