Solved

sql query and cfoutput

Posted on 2003-12-12
4
252 Views
Last Modified: 2013-12-24
I have a database as follows

ID      acccode          title                               set
1           A                 Header                           T
2           A1               sub heading                    T
3           A1333         company1                        T
4           A1335         company2                        Z

i need to output

HEADER

SUB HEADING

      company 1

I do not output company 2 as its code is Z


so far:
SELECT ACCNT_CODE , ACCNT_NAME
FROM  dbo.dbo_SSRFACC
WHERE SUSPEND <> 'Z'
ORDER BY ACCNT_CODE

doesnt work because of the WHERE clause for some reason, its a varchar

I can use multiple sql statements and loop over the sub departments and companies, can you please help

0
Comment
Question by:Ken-doh
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 4

Expert Comment

by:sandy12879
ID: 9927478
select  title from tablename  where set ='t'
0
 
LVL 11

Expert Comment

by:hart
ID: 9927744
how is that u have the main heading, sub heading and companies under these in the same table...

if u want just the first three records then what sandy wrote is fine just change
select  title from tablename  where set ='t'

to

select  title from tablename  where set ='T'
set is a database key word i think ur fieldname is SUSPEND  so it would be suspend = 'T'

or if u want to show the heading first then the sub heading and then list all the companies that come under these sub heading

Q1. will the acccode  for a heading be always 'A'
Q2. will the accode for a subheading be always 'A1'

see i would have kept a dept / heading table [with headingid and heading]
a sub department / sub heading table [with subheadingid and subheading ]

and in the other table i would have stored data as
headingid
subheadingid
company

this way by using joins i would have got the heading, sub heading and companies in one query..

Regards
Hart

0
 
LVL 1

Accepted Solution

by:
3gg earned 25 total points
ID: 9967196
This should get you started in the right direction. I don't have a debugger here to check this SQL, but you should be able to do this with MS-SQL Server 2000, mySQL and PostgreSQL. Oracle changes the function to SUBSTR()

<cfquery name="q" datasource="yourdatasource">
   SELECT SUBSTRING(acccode,1,1) AS groupA,
                SUBSTRING(acccode,1,2) AS groupB,
                title
   FROM dbo.dbo_SSRFACC
   WHERE suspend <> 'Z'
   GROUP BY acccode, title
   ORDER BY groupA, groupB
</cfquery>

<cfoutput query="q" group="groupA">
 <p>#q.groupA#</p>
 <cfoutput group="groupB">
  <p>#q.groupB#</p>
  <cfoutput>
   #q.title#<br />
  </cfoutput>
 </cfoutput>
</cfoutput>
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
IIS 8.5 2 52
Unsearchable in Google,Yahoo and Bing. 6 66
Problem to get function 52 116
Configure IIS to process JSON 10 92
Most ColdFusion developers get confused between the CFSet, Duplicate, and Structcopy methods of copying a Structure, especially which one to use when. This Article will explain the differences in the approaches with examples; therefore, after readin…
One of the typical problems I have experienced is when you have to move a web server from one hosting site to another. You normally prepare all on the new host, transfer the site, change DNS and cross your fingers hoping all will be ok on new server…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

735 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question