Active TopicsMemberlistSearchHelpRegisterLogin

Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Report Design
Message Icon Topic: SQL Code help Post Reply Post New Topic
Page  of 3 Next >>
Author Message
nhopp4
Groupie
Groupie


Joined: 03 Oct 2012
Location: United States
Online Status: Offline
Posts: 62
Quote nhopp4 Replybullet Topic: SQL Code help
    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'
IP IP Logged
bwsanders
Senior Member
Senior Member


Joined: 05 Sep 2012
Location: United States
Online Status: Offline
Posts: 177
Quote bwsanders Replybullet 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. 
IP IP Logged
nhopp4
Groupie
Groupie


Joined: 03 Oct 2012
Location: United States
Online Status: Offline
Posts: 62
Quote nhopp4 Replybullet 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
 
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"
 
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!
IP IP Logged
bwsanders
Senior Member
Senior Member


Joined: 05 Sep 2012
Location: United States
Online Status: Offline
Posts: 177
Quote bwsanders Replybullet 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.
IP IP Logged
nhopp4
Groupie
Groupie


Joined: 03 Oct 2012
Location: United States
Online Status: Offline
Posts: 62
Quote nhopp4 Replybullet 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!
IP IP Logged
nhopp4
Groupie
Groupie


Joined: 03 Oct 2012
Location: United States
Online Status: Offline
Posts: 62
Quote nhopp4 Replybullet 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"
 
 
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 IsNull(l.messaging)
 ORDER BY "LISTING"."NAME"
 
Thanks!
IP IP Logged
bwsanders
Senior Member
Senior Member


Joined: 05 Sep 2012
Location: United States
Online Status: Offline
Posts: 177
Quote bwsanders Replybullet Posted: 15 May 2013 at 8:16am
NOT IsNull(input whatever field you want to make sure is not null here)
IP IP Logged
nhopp4
Groupie
Groupie


Joined: 03 Oct 2012
Location: United States
Online Status: Offline
Posts: 62
Quote nhopp4 Replybullet 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.
 
 
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 IsNull("listing"."messaging_id")
 ORDER BY "LISTING"."NAME"
 
Thanks!
IP IP Logged
nhopp4
Groupie
Groupie


Joined: 03 Oct 2012
Location: United States
Online Status: Offline
Posts: 62
Quote nhopp4 Replybullet Posted: 22 May 2013 at 8:34am
Does anyone have any ideas on this?
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 22 May 2013 at 8:52am
and (NOT ("listing"."messaging_id" IS NULL))
IP IP Logged
Page  of 3 Next >>
Post Reply Post New Topic
Printable version Printable version

Forum Jump 		You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.015 seconds.
HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | CLICK PLAYS | COACH FORUMS | TROPICAL FISH

© 2004-2024 Brian Bischof. All Rights Reserved.