In crystal 9 what is the correct sql syntax for this... 1

[boardburner]

I need to "add Command" in my report and generate a composite table using SQL.
I am unsure what the correct syntax is for this expression that crystal will accept. I am getting information thru OBDC visual foxpro.

SELECT
(syntax for table fields?)
FROM
(syntax for table fields?)
WHERE
(syntax for table fields?)
AS
(syntax for table fields?)

All I know is that the following will not work.


CODE
SELECT
SelectedParts."IPPART", IPART."IPDESC", IPART."IPQOH", IPART."IPQAL", IPART."IPMINQ", JHEAD."JHJOB", JHEAD."JHPQTY", JHEAD."JHPART"
FROM
(
SELECT
IPART."IPPART", IPART."IPDESC", IPART."IPQOH", IPART."IPQAL", IPART."IPMINQ"
FROM
IPART
WHERE
({IPART.IPCLAS}>=PWS0 and {IPART.IPCLAS}<=PWSU5)
)
AS selectedParts
left join
(
SELECT
JHEAD."JHJOB", JHEAD."JHPQTY", JHEAD."JHPART"
FROM
JHEAD
WHERE
({JHEAD.JHHIST}<>"H")
)
AS selJobs
ON
selectedParts."IPPART"= selJobs."JHPART"

[/code]


Thanks

 

[synapsevampire]

Depending upon your version of Fox, try creating a SQL Query in Fox and copy and paste.

-k

 

[boardburner]

Thanks for the direction...but, I haven't a clue how.
I did some digging and discovered that the fox version is 6.
Which still does me no good because I have to access the tables with crystal.
This question might seem redundent but is it crystal that can't correctly interpret the code or is it fox that can't understand crystal ? And if so, what other options do I have to get the information from these fields in these required tables ?

Thanks again

 

[synapsevampire]

You don't need to use SQL anyway, and Fox isn't a SQL database. By virtue of the ODBC connection, you might use SQL.

Since you don't know Fox, yet you're tasked with Querying Fox, I would suggest using the standard of adding in tables, and for each derived table as demonstrated in your code, use a subreport to obtain the data.

From the looks of your SQL, you're a pretty sophisticated SQL developer.

You might try opening VFP and the database and using the query generator to generate some SQL.

I used to code xBase ages ago, but I've forgotten it all.

-k

 

[boardburner]

Thanks,
I can make 2 seperate reports in crystal and get the information I need perfectly. This is the problem, when I try combining the reports into one to get a more user friendly layout the second table info overrides the first and I start loosing part #'s. Basically it becomes usless. I would like to create a "vertual" table somehow in crystal and pull the field data from it. Very similar to the code...again, I don't have a clue.

 

[lbass]

How about revisiting your approach? I think you only need to create a left join FROM the parts table to the jobs table and then create a formula {@openjob}:

if isnull({JHEAD.JHHIST}) or
{JHEAD.JHHIST} <> "H" then
{JHEAD.JHJOB} + " " + {JHEAD.JHPQTY} else ""

Place {IPART.IPPART} and any other fields you want to show for all part numbers on the report, along with {@openjob}.

Since you say (in your previous post) that you want to show the part numbers whether or not they have an open job, and then if they do, show each open job, this should do the trick. If you are getting duplicates, you can choose "select distinct records" or use field or group suppression to eliminate redundant records.

-LB

 

[boardburner]

Thanks for the try...but, I have tried a number of varations & selections on this formula and it always brings out accurately all the open jobs and a blank space for each job (closed) in multiple places. "select distinct records" has no effect on the multiple blanks and if I filter out all the "blank" jobs all it leaves me are the open ones and my other part #'s are gone.
Again why is something so simple....so frustrating !!


Thanks again

 

[lbass]

Please don't give up yet...

What do you mean by:

"it always brings out accurately all the open jobs and a blank space for each job (closed) in multiple places"

How do you have the report sorted? Or are you seeing duplicate part numbers with corresponding blanks?

Please provide a sample of the results you get when you try this.

-LB

 

[boardburner]

Thanks....can't give up now

Here is the brief overview again;
CR9 pulling from FoxPro thru OBDC_VFP
Table (1) master parts "{ipart}"
Fields to be used: Part # "{ippart}", part class "{ipclas}", Description "{ipdesc"}, On hand "{ipoh}"& Min inventory "{ipminq}", Quantity allocated "{ipaloc}"
Table (2) master jobs "{jhead}"
Fields to be used: Part# "{jhpart}", History ("H" means closed)"{jhhist}", Job# "{jhjob}"& Job Quantity "{jhpqty}".

From table (1) linked "to" table (2) "left outer " "=" using Part#. (only common field)

Report: primary sort by "part#"
Report Grouping: by "part class" (only report on class (PWS1) to (PWSU5))
All History jobs; ("H")flag field, need to be excluded from the search before checking "open" jobs.

Report intent: For all part #'s in each class there should be a job# and quantity if jobs are open. All remaining part #'s in the class should have just a single entry line with text "no job" or " "

It seems that no matter how I construct the formulas, I always get the part #'s in each class with the associated job #'s. If the job/jobs are open I get the correct/accurate job #. Jobs that are closed come in with " " or text as specified.... BUT... when I filter out the closed jobs I lose all the parts #'s that should have a single listing because they belong to the part class.

Back to your question...I get correct and accurate single instances of each job for each "open job" on each part # in each class. I get multiple (this report should have correctly appr 150 records) appr 1500 record returns because it is pulling all the past "closed" jobs for each of the part #'s. For some reason "jobs" are controlling the final output and not the part #'s in each class.

I need a way to get only (1) listing of the part #'s that are closed and not multiples.

Everything "seems" correct in the setup stated above but it still comes out with the "jobs" running the show.

Thanks for the help
G

 

[lbass]

The issue is that you NEED the closed jobs in order to get the parts from the IPPARTS table that ONLY have closed jobs. If you have the following:

Part JobHistory
1 Open
1 Open
1 Closed
1 Closed
2 Closed
3 Open
4 Open
4 Closed
4 Closed
5 Closed
5 Closed

...then if you only select the open jobs from the JobHistory table, then you would get:

Part JobHistory
1 Open
1 Open
3 Open
4 Open

I guess you want to say "Closed" if the field = "H", and if there are multiple open jobs, show each one. While you should have a group on {IPART.IPPARTNO}, you should suppress the header and footer, and display the details in order to show possible multiple "Open" entries. Create a formula {@History}:

if isnull({Job.History}) then "Open" else
"Closed"

Place {@History} on the report and add {@History} as a sort field (report->sort records). Then create a formula {@open}:

if isnull({Job.History}) then 1 else 0

Then go to the section expert (format->section)->details->suppress->x+2 and enter:

({Job.History} = "H" and
{Job.History} = previous({Job.History})) or
(sum({@open}, {IPART.IPPART}) > 0 and
{Job.History} = "H") //where {IPART.IPPART} is your group field. Substitute the correct field names and value

This should result in:

Part JobHistory
1 Open
1 Open
2 Closed
3 Open
4 Open
5 Closed

...which is my understanding of what you want. The same large number of records will be pulled for this report, but a much smaller number will actually be displayed.

-LB

 

[boardburner]

You are absolutely a genius.......I would never have gotten it to work in that manner.
I had to tweak it a little here & there but it works perfect for all manufactured parts....for some reason it dumps out all my pruchased parts in the classes ?

Not sure where I need to change code to let in the rest of the parts...but I think I am on the right track with your help.

If you have ideas on how to keep it from dumping my non manufactured parts let me know.

Thanks Again

 

[lbass]

I don't recall seeing anywhere how you are distinguishing between purchased and manufactured parts. Can you explain which fields you are referring to? You are only working with two tables, right?

Can you copy your record selection formula here? I'm better at reading that than the SQL statement. Go to report->edit selection formula->record and copy and paste here.

You don't have any group selection or section suppression formatting, do you?

-LB

 

[boardburner]

Thanks,
I found out after running the report instituting your method that I am also pulling in "completed" jobs along with "closed" jobs. I now need to also check for "production complete" (true or false field) on the part #'s along with the closed (history) or "H" field. I need to "not see" the completed jobs also. I am not sure where to apply the check for "production complete" or how this would affect the logic of the rest of the method.?

Back to your original question; When I was running just the "inventory" style report, (no jobs @ that point) I sorted by part# & grouped primaryly by class (a must) then grouped using a "part type" field; 1=purchased, 2=manufactured. I noticed immediately on the new report that the purchased parts dissapeared from the class groups. I know what to check, purchased parts "never" have had a job#, or just check the "part type" field, how to institute the check & where to apply it is the problem.
Here is the record selection,

{ipart.ipclas} in "PWS0" to "PWSU5"

Thanks again
G

 

[lbass]

Can you verify that you still have a left join from the parts table to the jobs table? Your purchased parts should appear with the left join.

I'm assuming that the purchased vs manufactured part type field in in the parts table, correct?

I don't have a sense of how the production completed field relates to the other fields. It sounds like it would only apply to manufactured parts, not purchased parts, as does the open/closed field. Is that correct? Is it found in the jobs table? And how does the open/closed field relate to the production completed field?

Could you provide some sample data at the detail level of what you are getting and what you want returned?

-LB

 

[boardburner]

That is the strangest thing..I have checked and played with the {IPART} to {JHEAD} table join on more than one occasion and it seems to have no effect on the outcome of the report..but yes @ this time it is an "outer left = ".
Correct, The purchased vs manufactured part type field is in the parts table. {IPART}
I found out after, that the Mfg software that the co is using uses (2) seperate fields for ending jobs. Production complete means that the Mfg is "complete", parts are in inventory, Closed means that all accounting is done for the complete job and accounting dept. "closes" it.
I find that I need to "not see" production complete jobs on the report also.
I will try to outline this report structure to help to get an idea of my method, I only needed to use the (2) tables
basically for part info and job info. I have seperated in the mfg software all the parts #'s used in a "project" into "classes" this is why I am using part #'s as the primary sort and classes as primary group. I have put all necessary purchased and mfg. parts into these classes. If I run a report on just the {IPART} & grouping by class, & part type all my part #'s (purchased & manufactured) and class grouping works perfect. The instant I pull one little piece of information in from the {JHEAD} table, part #'s start disappearing. The "classes" give me my "project" component status in inventory or in manufacturing so a live picture of project status can be had @ any time. This completed report should give me inventory status for all part #'s in all specified classes... and... all "open" jobs on the factory floor to include the job # and job Qty. "IF" there are any open jobs for any of the part #'s in the classes. Multiple open jobs are possible.

OK, reading across from left to right on the header I need to see; part#, description, Qty. on-hand, Qty allocated, min inventory qty., job# (single or multiple), job quantity and going down the report I need to see all parts grouped by (project) class and sub grouped by part type (purchased & manufactured). There will never be any jobs in the "purchased" grouping.

If there is an open job...show the job# & qty

Hope this is better to understand, being so close to this for the past weeks I sometimes fail to state the detail in enough detail.

Thanks again
G

 

[lbass]

I think you need to copy your current SQL statement and paste it here after all.

Also, please provide a sample of data as I requested earlier. Specifically, I think we need to see what happens at the detail level for these fields:

Class Part Type JobHistory ProductionComplete
GH1
GH2
Detail (Your data here)

-LB

 

[boardburner]

OK
As I wrote in the beginning of this thread I don't have a current SQL statement in place, everything I tried failed. That is why I feel I need to get your method or a similar one to work.

Typical layout with items following,
--------------------------------------------------------

Part;       on-hand  allocated  min inven.  job#  job qty
----------------------------------------------------------
Class PWS1:
   Purchased:
#7001-324......2 ........1.......10........
#9003-024......26 ......11.......50........
   Manufactured:
#8001-120......23 ......15.......20........
#8001-121......23 ......15.......20.........wp4352...10
#8001-121......23 ......15.......20.........wp4352...15
#6006-132......53 ......45.......70.........
-------------------------------------------------------

Class PWS2:
Purchased:
(detail)
Manufactured:
(detail)
------------------------------------------------------
Class PWS3: etc..etc...etc
-------------------------------------------------------
From;
{IPART} PN#7001-324
Prime group; CLASS- pws1
Secondary group; PART TYPE 1
(quantities as noted)

{IPART} PN#9003-024
Prime group; CLASS- pws1
Secondary group; PART TYPE 1
(quantities as noted)

{IPART} PN#8001-120
Prime group; CLASS- pws1
Secondary group; PART TYPE 2
(quantities as noted)
{JHEAD}
No current jobs {jhead.jhist}="H" and/or {jhead.jpcmpl}=true

{IPART} PN#8001-121
Prime group; CLASS- pws1
Secondary group; PART TYPE 2
(quantities as noted)
{JHEAD}
Current job {jhead.jhist}<>"H" and {jhead.jpcmpl}=false
{jhead.jhjob} = wp4352
{jhead.jpqty} = 10

{IPART} PN#6006-132
Prime group; CLASS- pws1
Secondary group; PART TYPE 2
(quantities as noted)
{JHEAD}
No current job {jhead.jhist}<>"H" and {jhead.jpcmpl}=true

Hope this helps ?

Thanks Again
G

 

[lbass]

If you go to Database->Show SQL Query, then you can copy the contents and paste it here (although I'm not sure where this is in 9.0). The point of this is that even though you have said you have a left join, your report is acting as if if doesn't. If you go to database->visual linking expert->click on the link between tables, does the tool tip say "left outer join"? If it says equal join, then go to link options and check "left outer."

Does the following:

"No current jobs {jhead.jhist}="H" and/or {jhead.jpcmpl}=true"

...mean that:

No current jobs: {jhead.jhist}="H" and {jhead.jpcmpl}=true
{jhead.jhist}="H" or {jhead.jpcmpl}=true

...and along with this:
{jhead.jhist}<>"H" and {jhead.jpcmpl}=true

...doesn't it mean that whenever the {jhead.jpcmp1} is complete it is not a current job? I'm not sure the job history field is even relevant then, since a job wouldn't ever = "H" without the completion field being true, would it?

For the moment, assuming both sets of criteria are necessary, I would also try to build in both null checks and <> values into your formulas, as in:

//{@history}:
if (isnull({jhead.jhist}) or
{jhead.jhist} <> "H") and
(isnull({jhead.jpcmpl} or
{jhead.jpcmpl} = false) then
"Open" else
"Closed"

//{@open):
if (isnull({jhead.jhist}) or
{jhead.jhist} <> "H") and
(isnull({jhead.jpcmpl} or
{jhead.jpcmpl} = false) then 1 else 0

Let me know whether any of these steps helps.

-LB

 

[boardburner]

Sorry..thought you were asking for my code from above.

SELECT DISTINCT 'ipart'.'ippart','ipart'.'ipdesc','ipart'.'ipclas',
'ipart'.'ipqoh','ipart'.'ipqal','ipart'.'ipminq',
'jhead'.'jhhist','jhead'.'jhpqty','jhead'.'jhjob',
'jhead'.'jhpcmp'
FROM 'ipart''ipart'
LEFT OUTER JOIN 'jhead' 'jhead' 
ON 'ipart'.'ippart'='jhead'.'jhpart'
WHERE ('ipart'.'ipclas'> 'PWS0' AND 'ipart'.'ipclas'<='PWSU5')
ORDER BY 'ipart'.'ipclas','ipart'.'ippart'

@ this moment I am unsure if the production complete flag actually holds after the job is "closed". Either way I don't want to see "closed" or "production complete" .

I will try your folmulas tomorrow and give you the result.
Thanks again
G

 

[boardburner]

If this thread would let me I would give you another star...but it won't
I guess the planets must have lined up or something....but...after checking the status if the "H" field was necessary (your suggestion), I found it wasn't. I then restructured the formulas and your suppression method to compensate and guess what...It works like a bear. The only thing I had to change was the suppression formula to;

if{ipart.iptype}=2
then 
previous((ipart.ippart})={ipart.ippart}

If I didn't, I lost all my purchased parts. Something about checking for that "H" field was throwing a wrench in the complete report.

I wish to thank you very much for sticking with this and helping.

Thanks
G