Need Help With ORACLE SQL Statement

I have an SQL coding issue that I need help with.  I am using Powerbuilder
5.0.2 and ORACLE 7.1 Database.
I have 2 tables that are needed for my Select statements.

I am including my 3 Scenarios with my Select results.

 Note: Scenario 1 & 2 include the SQL statements and the returned data.

          Scenario 3 is my DESIRED data result, But I need help with the
appropriate SQL statement to produce it.

Please post your replies here in the newsgroup or email me in private at:

bently.turner@midata.com

Thanking you in advance
Bently Turner


Here are the facts and my scenarios

Table:	"T_REQUEST_STATUS"
Columns:
	"ENTITY_ID"   
	"DATA_DTE"
	"LOAD_DTE"			
	"BANK_NBR"

Table:  "T_ENTITY_DEF"
Columns:
	"ENTITY_ID"
	"ENTITY_DESC"


Scenario 1:  Correct Columns But, Too Many Rows

SELECT  DISTINCT "T_REQUEST_STATUS"."ENTITY_ID",   
       	 
MAX(Substr(To_Char("T_REQUEST_STATUS"."DATA_DTE",'YYYY-MM-DD'),1,10))
"Data_Date",
	"T_ENTITY_DEF"."ENTITY_DESC", 
        	"T_REQUEST_STATUS"."BANK_NBR",
	"T_ENTITY_DEF"."ENTITY_ID",
	"T_REQUEST_STATUS"."LOAD_DTE"			
FROM 	"T_REQUEST_STATUS", 
	"T_ENTITY_DEF"
WHERE  ("T_ENTITY_DEF"."ENTITY_ID" = "T_REQUEST_STATUS"."ENTITY_ID") AND
       ("T_REQUEST_STATUS"."BANK_NBR" = '959')	
GROUP BY "T_REQUEST_STATUS"."ENTITY_ID",
	"T_ENTITY_DEF"."ENTITY_DESC", 
        	"T_REQUEST_STATUS"."BANK_NBR",
	"T_ENTITY_DEF"."ENTITY_ID",
	"T_REQUEST_STATUS"."LOAD_DTE"
ORDER BY "T_REQUEST_STATUS"."ENTITY_ID" ASC,
	"Data_Date" DESC ;		   

Entity_Id	Data_Date	Entity_Desc		Bank_Nbr Entity_Id	Load_Dte
dwgledbl   	1997-07-31	GLDBAL - DETAIL         959      dwgledbl   	8/1/97 
03:48:51
dwgledbl   	1997-07-30	GLDBAL - DETAIL         959  	 dwgledbl   	7/31/97 
04:09:14
dwgledbl   	1997-07-29	GLDBAL - DETAIL         959  	 dwgledbl   	7/30/97 
03:12:30
dwgledcc   	1997-07-31	GLDCC - DETAIL         	959  	 dwgledcc   	8/1/97 
03:48:51
dwgledcc   	1997-07-30	GLDCC - DETAIL         	959  	 dwgledcc   	7/31/97 
04:09:14
dwgledcc   	1997-07-29	GLDCC - DETAIL         	959  	 dwgledcc   	7/30/97 
03:12:30
dwgledhi   	1997-07-31	GLDHIER - DETAIL       	959  	 dwgledhi   	8/1/97 
03:48:51
dwgledhi   	1997-07-30	GLDHIER - DETAIL       	959  	 dwgledhi   	7/31/97 
04:09:14
dwgledhi   	1997-07-29	GLDHIER - DETAIL       	959  	 dwgledhi   	7/30/97 
03:12:30
dwgledlv   	1997-07-31	GLDDESC - DETAIL      	959      dwgledlv   	8/1/97 
03:48:51
dwgledlv   	1997-07-30	GLDDESC - DETAIL      	959  	 dwgledlv   	7/31/97 
04:09:14
dwgledlv   	1997-07-29	GLDDESC - DETAIL      	959  	 dwgledlv   	7/30/97 
03:12:30
dwglesbl   	1997-07-31	G/L acct balance snap  	959      dwglesbl   	8/1/97 
03:48:51
dwglesbl   	1997-07-03	G/L acct balance snap  	959  	 dwglesbl   	7/4/97 
02:49:35
dwglesbl   	1997-06-30	G/L acct balance snap  	959  	 dwglesbl   	7/1/97 
04:10:29


Scenario 2:  Correct Rows, But Need Load_Dte Column

SELECT  DISTINCT "T_REQUEST_STATUS"."ENTITY_ID",   
	MAX(Substr(To_Char("T_REQUEST_STATUS"."DATA_DTE",'YYYY-MM-DD'),1,10))
"Data_Date",
	"T_ENTITY_DEF"."ENTITY_DESC", 
        	"T_REQUEST_STATUS"."BANK_NBR",
	"T_ENTITY_DEF"."ENTITY_ID"			
FROM 	"T_REQUEST_STATUS", 
	"T_ENTITY_DEF"
WHERE  ("T_ENTITY_DEF"."ENTITY_ID" = "T_REQUEST_STATUS"."ENTITY_ID") AND
       ("T_REQUEST_STATUS"."BANK_NBR" = '959')	
GROUP BY "T_REQUEST_STATUS"."ENTITY_ID",
   	"T_ENTITY_DEF"."ENTITY_DESC", 
   	"T_REQUEST_STATUS"."BANK_NBR",
	"T_ENTITY_DEF"."ENTITY_ID"
ORDER BY "T_REQUEST_STATUS"."ENTITY_ID" ASC,
	"Data_Date" DESC ;		   

Entity_Id	Data_Date	Entity_Desc		Bank_Nbr Entity_Id
dwgledbl   	1997-07-31	GLDBAL - DETAIL         959  	 dwgledbl   
dwgledcc   	1997-07-31	GLDCC - DETAIL          959  	 dwgledcc   
dwgledhi   	1997-07-31	GLDHIER - DETAIL        959  	 dwgledhi   
dwgledlv   	1997-07-31	GLDDESC - DETAIL        959  	 dwgledlv   
dwglesbl   	1997-07-31	G/L acct balance snap   959  	 dwglesbl 


Scenario 3:  Desired Result, Need The Proper SQL Statement

Entity_Id	Data_Date	Entity_Desc		Bank_Nbr Entity_Id	Load_Dte
dwgledbl   	1997-07-31	GLDBAL - DETAIL         959      dwgledbl   	8/1/97 
03:48:51
dwgledcc   	1997-07-31	GLDCC - DETAIL         	959  	 dwgledcc   	8/1/97 
03:48:51
dwgledhi   	1997-07-31	GLDHIER - DETAIL       	959  	 dwgledhi   	8/1/97 
03:48:51
dwgledlv   	1997-07-31	GLDDESC - DETAIL      	959      dwgledlv   	8/1/97 
03:48:51
dwglesbl   	1997-07-31	G/L acct balance snap  	959      dwglesbl   	8/1/97 
03:48:51

0
Bently
8/7/1997 3:17:06 PM
sybase.powerbuilder.datawindow 28057 articles. 4 followers. Follow

1 Replies
674 Views

Similar Articles

[PageSpeed] 12

Hi Bently,

This is my inference:

You are trying to retrieve records from the T_REQUEST_STATUS where the
"T_REQUEST_STATUS"."BANK_NBR" = '959'  but also need the description of the
'ENTITY_ID'   field from the "T_ENTITY_DEF table.  If I am correct then the
following sql statement may work

SELECT "T_REQUEST_STATUS"."ENTITY_ID",   
	"T_REQUEST_STATUS"."DATA_DTE",'
 	"T_ENTITY_DEF"."ENTITY_DESC", 
         	"T_REQUEST_STATUS"."BANK_NBR",
	"T_ENTITY_DEF"."ENTITY_ID",
 	"T_REQUEST_STATUS"."LOAD_DTE"			
 FROM 	"T_REQUEST_STATUS", 
 	"T_ENTITY_DEF"
 WHERE  ("T_ENTITY_DEF"."ENTITY_ID" = "T_REQUEST_STATUS"."ENTITY_ID") AND
        ("T_REQUEST_STATUS"."BANK_NBR" = '959')	

To display the "DATA_DTE" in YYYY-MM-DD format, use the PB edit mask for
that field in the Datawindow
(Properties->Edit->Editmask)

I could not understand why you were grouping the data, if you are not
performing any calculations for a group.  If you want to sort the data,
then add the ORDER BY clause as desired.  Remember, you can also group on
the client side.  

HTH

Paresh Shah
Bently Turner <bently.turner@midata.com> wrote in article
<01bca34d$38791580$25c2267b@indurain.mi.data.services.com>...
> I have an SQL coding issue that I need help with.  I am using
Powerbuilder
> 5.0.2 and ORACLE 7.1 Database.
> I have 2 tables that are needed for my Select statements.
> 
> I am including my 3 Scenarios with my Select results.
> 
>  Note: Scenario 1 & 2 include the SQL statements and the returned data.
> 
>           Scenario 3 is my DESIRED data result, But I need help with the
> appropriate SQL statement to produce it.
> 
> Please post your replies here in the newsgroup or email me in private at:
> 
> bently.turner@midata.com
> 
> Thanking you in advance
> Bently Turner
> 
> 
> Here are the facts and my scenarios
> 
> Table:	"T_REQUEST_STATUS"
> Columns:
> 	"ENTITY_ID"   
> 	"DATA_DTE"
> 	"LOAD_DTE"			
> 	"BANK_NBR"
> 
> Table:  "T_ENTITY_DEF"
> Columns:
> 	"ENTITY_ID"
> 	"ENTITY_DESC"
> 
> 
> Scenario 1:  Correct Columns But, Too Many Rows
> 
> SELECT  DISTINCT "T_REQUEST_STATUS"."ENTITY_ID",   
>        	 
> MAX(Substr(To_Char("T_REQUEST_STATUS"."DATA_DTE",'YYYY-MM-DD'),1,10))
> "Data_Date",
> 	"T_ENTITY_DEF"."ENTITY_DESC", 
>         	"T_REQUEST_STATUS"."BANK_NBR",
> 	"T_ENTITY_DEF"."ENTITY_ID",
> 	"T_REQUEST_STATUS"."LOAD_DTE"			
> FROM 	"T_REQUEST_STATUS", 
> 	"T_ENTITY_DEF"
> WHERE  ("T_ENTITY_DEF"."ENTITY_ID" = "T_REQUEST_STATUS"."ENTITY_ID") AND
>        ("T_REQUEST_STATUS"."BANK_NBR" = '959')	
> GROUP BY "T_REQUEST_STATUS"."ENTITY_ID",
> 	"T_ENTITY_DEF"."ENTITY_DESC", 
>         	"T_REQUEST_STATUS"."BANK_NBR",
> 	"T_ENTITY_DEF"."ENTITY_ID",
> 	"T_REQUEST_STATUS"."LOAD_DTE"
> ORDER BY "T_REQUEST_STATUS"."ENTITY_ID" ASC,
> 	"Data_Date" DESC ;		   
> 
> Entity_Id	Data_Date	Entity_Desc		Bank_Nbr Entity_Id	Load_Dte
> dwgledbl   	1997-07-31	GLDBAL - DETAIL         959      dwgledbl   
8/1/97 
> 03:48:51
> dwgledbl   	1997-07-30	GLDBAL - DETAIL         959  	 dwgledbl   	7/31/97

> 04:09:14
> dwgledbl   	1997-07-29	GLDBAL - DETAIL         959  	 dwgledbl   	7/30/97

> 03:12:30
> dwgledcc   	1997-07-31	GLDCC - DETAIL         	959  	 dwgledcc   	8/1/97 
> 03:48:51
> dwgledcc   	1997-07-30	GLDCC - DETAIL         	959  	 dwgledcc   	7/31/97

> 04:09:14
> dwgledcc   	1997-07-29	GLDCC - DETAIL         	959  	 dwgledcc   	7/30/97

> 03:12:30
> dwgledhi   	1997-07-31	GLDHIER - DETAIL       	959  	 dwgledhi   	8/1/97 
> 03:48:51
> dwgledhi   	1997-07-30	GLDHIER - DETAIL       	959  	 dwgledhi   	7/31/97

> 04:09:14
> dwgledhi   	1997-07-29	GLDHIER - DETAIL       	959  	 dwgledhi   	7/30/97

> 03:12:30
> dwgledlv   	1997-07-31	GLDDESC - DETAIL      	959      dwgledlv   	8/1/97

> 03:48:51
> dwgledlv   	1997-07-30	GLDDESC - DETAIL      	959  	 dwgledlv   	7/31/97 
> 04:09:14
> dwgledlv   	1997-07-29	GLDDESC - DETAIL      	959  	 dwgledlv   	7/30/97 
> 03:12:30
> dwglesbl   	1997-07-31	G/L acct balance snap  	959      dwglesbl   
8/1/97 
> 03:48:51
> dwglesbl   	1997-07-03	G/L acct balance snap  	959  	 dwglesbl   	7/4/97 
> 02:49:35
> dwglesbl   	1997-06-30	G/L acct balance snap  	959  	 dwglesbl   	7/1/97 
> 04:10:29
> 
> 
> Scenario 2:  Correct Rows, But Need Load_Dte Column
> 
> SELECT  DISTINCT "T_REQUEST_STATUS"."ENTITY_ID",   
> 	MAX(Substr(To_Char("T_REQUEST_STATUS"."DATA_DTE",'YYYY-MM-DD'),1,10))
> "Data_Date",
> 	"T_ENTITY_DEF"."ENTITY_DESC", 
>         	"T_REQUEST_STATUS"."BANK_NBR",
> 	"T_ENTITY_DEF"."ENTITY_ID"			
> FROM 	"T_REQUEST_STATUS", 
> 	"T_ENTITY_DEF"
> WHERE  ("T_ENTITY_DEF"."ENTITY_ID" = "T_REQUEST_STATUS"."ENTITY_ID") AND
>        ("T_REQUEST_STATUS"."BANK_NBR" = '959')	
> GROUP BY "T_REQUEST_STATUS"."ENTITY_ID",
>    	"T_ENTITY_DEF"."ENTITY_DESC", 
>    	"T_REQUEST_STATUS"."BANK_NBR",
> 	"T_ENTITY_DEF"."ENTITY_ID"
> ORDER BY "T_REQUEST_STATUS"."ENTITY_ID" ASC,
> 	"Data_Date" DESC ;		   
> 
> Entity_Id	Data_Date	Entity_Desc		Bank_Nbr Entity_Id
> dwgledbl   	1997-07-31	GLDBAL - DETAIL         959  	 dwgledbl   
> dwgledcc   	1997-07-31	GLDCC - DETAIL          959  	 dwgledcc   
> dwgledhi   	1997-07-31	GLDHIER - DETAIL        959  	 dwgledhi   
> dwgledlv   	1997-07-31	GLDDESC - DETAIL        959  	 dwgledlv   
> dwglesbl   	1997-07-31	G/L acct balance snap   959  	 dwglesbl 
> 
> 
> Scenario 3:  Desired Result, Need The Proper SQL Statement
> 
> Entity_Id	Data_Date	Entity_Desc		Bank_Nbr Entity_Id	Load_Dte
> dwgledbl   	1997-07-31	GLDBAL - DETAIL         959      dwgledbl   
8/1/97 
> 03:48:51
> dwgledcc   	1997-07-31	GLDCC - DETAIL         	959  	 dwgledcc   	8/1/97 
> 03:48:51
> dwgledhi   	1997-07-31	GLDHIER - DETAIL       	959  	 dwgledhi   	8/1/97 
> 03:48:51
> dwgledlv   	1997-07-31	GLDDESC - DETAIL      	959      dwgledlv   	8/1/97

> 03:48:51
> dwglesbl   	1997-07-31	G/L acct balance snap  	959      dwglesbl   
8/1/97 
> 03:48:51
> 
> 
0
Teresa
8/8/1997 2:35:51 AM
Reply:

Web resources about - Need Help With ORACLE SQL Statement - sybase.powerbuilder.datawindow

User:Jimbo Wales/Statement of principles - Wikipedia, the free encyclopedia
As we move forward with software and social changes, I think it is imperative that I state clearly and forcefully my views on openness and the ...

Category:Articles containing potentially dated statements from June 2006 - Wikipedia, the free encyclopedia ...
This is an administration category . It is used for administration of the Wikipedia project and is not part of the encyclopedia. It contains ...

The innovation nation: Elitist statement which won't generate change
Readers discuss the Turnbull government's innovation statement.

The statements murdered grandmother Helen Dawson Key's family will never get to read
If the family members of Helen Dawson Key had their chance, they would have faced her killer and told of their pain.&nbsp;

Innovation Statement: Tech industry asks Turnbull for more money, please
The government's Innovation Statement on Monday was widely applauded, but did it go far enough?

Obama's statement on ISIS - Business Insider Deutschland
US President Barack Obama spoke at the Pentagon on Monday to give an overview of the country's strategy to combat the terrorist group ISIS.

FOMC Statement: Fed Funds Rate target range increased to 1/4 to 1/2 percent
FOMC Statement: Information received since the Federal Open Market Committee met in October suggests that economic activity has been expanding ...

What changed in new Fed statement
This is a comparison of today's FOMC statement with the one issued after the Fed's previous policy-making meeting on Oct. 28.

Dallas Stars: Cold facts: Loss to Blues 'clearly a statement' that Stars' upcoming divisional games will ...
Blues 3, Stars 0Click here for the full box score.Three Stars1. David Backes, Blues Goal, assist and fight give St. Louis captain the Gordie... ...

Comic-Con Organizers Issue Statement After Fan Confusion
... desire to expand their annual convention in San Diego. Comic-Con International made the unusual move today of putting out a statement regarding ...

Resources last updated: 12/18/2015 12:34:45 AM